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: 

   

Tableau, Qlikview, or Power BI?

Nowadays, there are lots of excellent products for turning fast-paced digital information into easily understood images and insights. So the common questions are: Which BI tool should I choose? Which BI tool has the most promising future? While waiting for this year’s Magic Quadrant for Business Intelligence and Analytics Platform, I’d like to share with you with Jen Stirrup’s opinion from a user or customer respect.

In her article Business Intelligence Barista: Mixing your choice of BI Coffee with Tableau, Power BI or Qlikview?, Jen compared Tableau, Qlikview and Microsoft Partner PivotStream from business criteria, data visualization criteria, and technical criteria. The comparisons are very clearly delivered in three tables for each category. She gave her scores in Excellent, Good, Yes/No, Limited for these three BI tools. If you would like to find why she gave the related scores, don’t forget to read her comments at the far right column.

Jen included the following as business criteria: time to implement, scalability, enterprise ready, long-term viability, free online help, big data support, size of partner network. As for visualization criteria, she compared data interactivity, visual drilldown, offline viewer, dashboard support, web/mobile clients etc. (It seems that Tableau has a “wow” factor in terms of eye-catching interactive visualization.) Last but not least, she discussed technical criteria in aspect of data integration, OLAP cubes, data modeling, data mining/science, multi-dimensional, xVelocity support, PowerPivot suppot, API, etc.

At last, Jen concluded that “There is no wrong choice, if it is the right choice for your organization.” The key to make decision on BI tools is to find out user requirement or buyers’ needs. I can't agree more on that. We need to know what we really need and what are the priorities before we go shopping for solutions.

Although Jen mentioned Power BI solution was not included in the comparison since it is still in preview, she mentioned Power View and Power Map as comparing to the other products. I wish that she could include product functionality and learning curve for developers for these three products as well.

Further reading for more qualitative analysis of BI market: 2013 Gartner Magic Quadrant for BI.