Problem
I want to know if it is possible to create hierarchies in PowerPivot tables similar to the hierarchies in SSAS. Also, I want to know if it is possible to create hierarchies using columns from different PowerPivot tables.
Solution
In this tip we will take a look at building hierarchies in PowerPivot tables for these two different scenarios:
- Creating Hierarchies using Columns from the Same Table
- Creating Hierarchies using Columns from Different Tables
Pre-requisites
To start working with column properties using the steps in this tip, you need to first go through the following tips in the same sequence and setup your Excel/PowerPivot workbook using the steps outlined in these tips.
- Import “DimDate”, “DimEmployee”, “DimReseller”, “DimGeography”, “Sales Territory Query” (“DimSalesTerritory” table imported using a query) and “FactResellerSales” tables from AdventureWorksDW database into PowerPivot (Refer to Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel)
- Add a New Column “SortedMonth” (Equal to “Month”) and set the column sorting to “MonthNumber” in “DimDate” (Refer to “Applying Custom Sorting on Column Data” in this tip: Working with Columns and Properties in PowerPivot for Excel)
Creating Hierarchies using Columns from the Same Table
This is the first scenario and is a simple scenario in which we can create a hierarchy using columns belonging to a single PowerPivot table. For this demonstration, let’s create a “CalendarHierarchy” using “Year”, “Quarter”, and “Month” from the “DimDate” table.
Follow the steps below to create a hierarchy:
- Go to Diagram View by clicking on “Diagram View” in the Top Ribbon or by clicking on the “Diagram View” button in the bottom right corner of the PowerPivot window.
- Locate the “Year” column, Right Click on the “Year” column and select “Create Hierarchy” from the context menu as shown below.
- This will create a new hierarchy and add “Year” as the first level in the hierarchy as shown below.
- Rename the hierarchy as “CalendarHierarchy”.
- Right click on “Quarter”, select “Add to Hierarchy”, and then select “CalendarHierarchy” hierarchy to add “Quarter” as the next level in the hierarchy.
- With those steps, “Quarter” gets added as the next level in the “CalendarHierarchy” hierarchy.
- Similarly Right Click on “SortedMonth”, select “Add to Hierarchy”, and then select “CalendarHierarchy” hierarchy to add “SortedMonth” as the next level and last level in the hierarchy.
- Once the “SortedMonth” is added to the hierarchy, rename “SortedMonth” (Under the “CalendarHierarchy” hierarchy) to “Month” by Right Clicking on “SortedMonth” and selecting Rename from the context menu. Now your hierarchy will look as shown below.
Note that the Column Names in brackets are the actual column names from the PowerPivot table. If you wish, you can create another hierarchy called “FiscalHierarchy” as “FiscalYear” –> “FiscalQuarter” –> “Month”
Creating Hierarchies using Columns from Different Tables
This is the second scenario and it requires creating a hierarchy containing columns from two or more tables. To demonstrate this scenario, we will create a “GeoHierarchy” in “DimGeography” table using “SalesTerritoryGroup” and “SalesTerritoryRegion” columns from the “Sales Territory Query” table and “EnglishCountryRegionName” and “StateProvinceName” columns from the “DimGeography” table.
First we need to bring all the columns required in the hierarchy into one table. Let’s bring “SalesTerritoryGroup” and “SalesTerritoryRegion” columns from “Sales Territory Query” table into “DimGeography” table by following the below steps.
- Go to Grid View by clicking on “Grid View” in the Top Ribbon or by clicking on the “Grid View” button in the bottom right corner of the PowerPivot window.
- Add a New Column by typing the following formula in the first cell under the column which reads “Add Column”.
=RELATED(‘Sales Territory Query'[SalesTerritoryGroup])
- Rename this newly added column to “TerritoryGroup”. Now the PowerPivot window should look as shown below.
- Now add the “SalesTerritoryRegion” column from the “Sales Territory Query” into the “DimGeography” table.
=RELATED(‘Sales Territory Query'[SalesTerritoryRegion])
- Rename this newly added column to “TerritoryRegion”. Now the PowerPivot window should look as shown below.
Now that we have all the columns required for our hierarchy in “DimGeography” table, let’s start creating the “GeoHierarchy” using the below steps, which are similar to the steps which we followed for the first approach.
- Go to Diagram View by clicking on “Diagram View” in the Top Ribbon or by clicking on the “Diagram View” button in the bottom right corner of the PowerPivot window.
- Go to DimGeography table, Right Click on the “TerritoryGroup” column and select “Create Hierarchy” from the context menu.
- Rename the Hierarchy to “GeoHierarchy”. Add the following columns to this hierarchy in the same sequence.
- EnglishCountryRegionName
- TerritoryRegion
- StateProvinceName
- City
- After Renaming “EnglishCountryRegionName” to “Country”, “TerritoryRegion” to “Region”, and “StateProvinceName” to “StateProvince” under the “GeoHierarchy, the “DimGeography” Table and “GeoHierarchy” looks as shown below.
Note that, we are able to add columns from the “Sales Territory Query” table to “DimGeography” table since there is an existing relationship between these two tables as shown below.
If there was no relationship between the “DimGeography” and “Sales Territory Query” tables and if we try adding a new column using the expression(s) above, we would get the following error.
To add columns from one table to another table, it is important to first create a relationship between these tables within PowerPivot.
Hierarchies are a great way to slice and dice the data during analysis. They also help in establishing a relationship between data columns and make it easier for end users to understand the relationships and levels within the data.
Next Steps
- Try the above two approaches for creating hierarchies in PowerPivot for Excel.
- Check out the following tips:
- Check out my previous tips
