How to: Create Hierarchies in PowerPivot for Excel 2013

Just like hierarchies in SSAS, hierarchies in PowerPivot for Excel 2013 provides a very convenient way to let you group data through different layers. Today we will use date dimension to demonstrate how to create hierarchies in PowerPivot.

First of all, you need to be in the Diagram View of the PowerPivot Window. Click Diagram View button under Home Tab.

Create Hierarchies

Select the table you would like to build hierarchies in. Here we choose DimDate table. Then click the “Create Hierarchies” button on the upper right corner of the table. After renaming the hierarchy you can drag the fields from the table down to the hierarchy that was just created. An alternative is to select a column first and then right click the column > Create Hierarchies.

Add Hierarchies

Right click the column that you would like to add to a hierarchy. A column can only be in one hierarchy. If a column already exists in a hierarchy, there won’t have an option to “Add to Hierarchy”.

If you would like to add a column from another table, you need to use RELATED DAX function to add columns from one table to another table. Before you can add columns, you must create a relationship between these tables.

Adjust Hierarchy Levels

If you would like to adjust the levels between hierarchies, right click any one and move up or down, or remove.

After hierarchies are created, you can directly drag it to PivotTable Fields just as columns.

How to: Enable PowerPivot Add-ins for Excel 2013

Power Pivot in Microsoft Excel 2013 is a build-in add-in you can use to perform powerful data manipulation and analysis in Excel 2013. However, it is hidden somewhere and you have to enable it first before using it.

Here are steps to dig it out:

1. Go to File > Options > Add-Ins.

   

2. Change In the Manage box, click COM Add-ins from the drop down list and click Go. Note that the default is Excel Add-ins.

   

3. Check Microsoft Office Power Pivot in Microsoft Excel 2013 box > OK. You can check Power View and Power Query in similar way as you can see they are also listed in the COM Add-ins list.

   

4. Bingo! You are ready to use Power Pivot for Excel 2013! PowerPivot tab should appear in your ribbon as shown below: 

   

How to: Install PowerPivot and Data Mining Add-ins for Excel 2010

For data analysts and BI developers, there are two add-ins for Excel 2010 that might be interesting to be installed for a tryout:

• PowerPivot add-in for Excel 2010
• Data mining add-in for Excel 2010

Where to download?

Microsoft SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010 can be downloaded at
http://www.microsoft.com/en-us/download/details.aspx?id=29074

Moreover, SQL Server 2012 Data Mining Add-ins for Office 2010 can be downloaded at http://www.microsoft.com/en-us/download/details.aspx?id=29061

For both add-ins, you need to decide which version you need since both a 32-bit and 64-bit version are available now. If you have a 32-bit version of Excel,  you need to choose 32-bit version of PowerPivot: ENU\x86\SQL_AS_DMAddin.msi. (Don't get confused with the machine version.) If you don't know which version your Excel is, go to File Tab and click Help. Then on the right of the screen, you will find out detailed information about your Excel including versions.

Memory Considerations

Note that for 32-bit version of Excel, the limit for memory usage is 2GB.  If you need to use more than 2GB of memory to work with your mining model, you must use the 64-bit version of Excel with the 64-bit version of the PowerPivot add-in. For more info, please refer to the article on Technet:  How to Use the SQL Server Data Mining Add-ins with PowerPivot for Excel