Learn more about SQL Server tools

   
   















































Creating Hierarchies in PowerPivot for Excel

MSSQLTips author Dattatrey Sindol By:   |   Read Comments (3)   |   Related Tips: More > Microsoft Excel Integration
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.

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.

    Start creating hierarchy with Year column at the top

  • This will create a new hierarchy and add "Year" as the first level in the hierarchy as shown below.

    Newly created hierarchy with Year at the top most level

  • 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.

    Add Quarter to the CalendarHierarchy

  • 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.

    Completed CalendarHierarchy hierarchy

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.

    Newly imported column TerritoryGroup

  • 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.

    Newly imported column TerritoryRegion

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.

    Completed GeoHierarchy hierarchy

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.

Existing relationship between 'Sales Territory Query' and 'DimGeography' tables

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.

Expected error in the absence of a relationship

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


Last Update: 3/18/2013


About the author
MSSQLTips author Dattatrey Sindol
Datta has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, March 06, 2014 - 6:42:10 AM - Andrew Read The Tip

Hi, Thanks for the article. I'm curious, does creating hierarchies in powwerpivot affect performance at all? Does it add overhead to slicing and dicing? or on the otherhand could it potentially speed up queries by informing powerpivot of the hierarchy structure? Thanks for the advice


Monday, May 27, 2013 - 4:09:46 AM - Dattatrey Sindol (Datta) Read The Tip

Hi Jack,

 

Please refer to the statement which reads "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." and the screenshot immediately below it. As you can see from the screenshot, there is a relationship between "Sales Territory Query" and "DimGeography" based on "SalesTerritoryKey".

 

Also notice the error we might get, in the next screenshot, in case if there was no relationship between those two tables.

 

RELATED() function is basically a Lookup Function which picks data from a related table. For more information on this function, please see this TechNet article: http://social.technet.microsoft.com/wiki/contents/articles/680.powerpivot-dax-filter-functions.aspx#related

 

Hope that clarifies.

 

Best Regards,

Dattatrey Sindol (Datta)


Wednesday, May 22, 2013 - 3:17:29 PM - Jack Owens Read The Tip

Datta,

You added 2 columns to "DimGeography" table using the following systax

=RELATED('Sales Territory Query'[SalesTerritoryGroup])
=RELATED('Sales Territory Query'[SalesTerritoryRegion])


Question: In above query, I'm not sure what column was used for reference between 2 tables. I mean where does the relationship
between 2 tables gets defined. Can you please explainwhat does the systax RELATED really does?

 




 
Sponsor Information