How To Enable User Defined Hierarchies in SQL Server Analysis Services SSAS

By:   |   Comments (9)   |   Related: > Analysis Services Development


I have a requirement to provide users with the capability to define their own reporting hierarchies for sales data. For instance they had a report which broke down sales by the four regional VPs then by office.  Later they created regions, assigned VPs to each region, then assigned offices to each VP.  Now they want to be able to create a number of other scenarios and choose which one when they run a report.  Can you provide some suggestions on how to do this? 


There is a relatively simple solution to your problem that leverages the many-to-many dimension capability in an SSAS cube.  Let's start out with a simple dimensional model then expand upon it to allow multiple reporting hierarchies.  Schema 1 is shown below:

simple schema

The above schema shows a Sales fact table, joined to a date dimension (Calendar) and an Office dimension.  This allows us to report on sales based on fiscal year and office.  Now let's introduce a revised schema to support your requirement of allowing users to define their own hierarchies for reporting.  Schema 2 is shown below:

hierarchy schema

The OfficeHierarchy table allows us to define multiple reporting hierarchies using a parent-child relationship.  The HierarchyName column value is defined by the users and appears in every row that belongs to a particular hierarchy.  Each row that we add to the table is either a parent (i.e. a grouping) or a child (i.e. an office).  Each child row belongs to a single group and contains an OfficeKey value which matches a row in the Office table.  Each parent row has one or more child rows, which can represent actual office(s), group(s) or both.  The OfficeHierarchyBridge table has a single row in it for every combination of OfficeHierarchy and Office.  The relationships between Office, OfficeHierarchyBidge and OfficeHierarchy will allow us to take advantage of the many-to-many dimension capability in SSAS.  In fact the cube wizard will automatically recognize the many-to-many dimension provided the foreign key relationships exist as shown in the above schema.

Let's build our first reporting hierarchy; we'll call it FY 2008 to represent our actual reporting hierarchy for our previous fiscal year.  We will add the following two staging tables to our schema.  Schema 3 is shown below:

staging schema

We'll use the stgOfficeGroup table to define the groups in our hierarchy.  There is a parent-child relationship in the stgOfficeGroup table, allowing us to have nested groups.  We'll use the stgOfficeMapping table to associate an office with a group.

The stgOfficeGroup table is shown below with some sample data:

stg office group

FY 2008 is a top-level group, and will be the name of our hierarchy.  The other four rows each represent a group and they are all children of the FY 2008 group.

A portion of the stgOfficeMapping table is shown below with some sample data:

stg office mapping

Each row represents an office assigned to a group as defined in the stgOfficeGroup table.

Next is a sample T-SQL script that we can use to populate the OfficeHierarchy and OfficeHierarchyBridge tables from our staging tables:

declare @hierarchyname nvarchar(50)
set @hierarchyname = N'FY 2008'
-- step 1: insert groups
insert into dbo.OfficeHierarchy(
, Description
, stgGroupNumber
, stgGroupParent
, GroupName 
, GroupNumber
, GroupParent
from dbo.stgOfficeGroup
-- step 2: insert children
insert into dbo.OfficeHierarchy (
, OfficeKey
, Description 
, stgGroupParent
, m.OfficeKey 
, o.OfficeName
, m.GroupNumber
from dbo.stgOfficeMapping m
join dbo.Office o on o.OfficeKey = m.OfficeKey
-- step 3: update ParentKey values
;with cte_group as (
 , stgGroupNumber
 from dbo.OfficeHierarchy 
 where HierarchyName = @hierarchyname
update dbo.OfficeHierarchy
set ParentKey = p.OfficeHierarchyKey
from dbo.OfficeHierarchy c
join cte_group p on p.stgGroupNumber = c.stgGroupParent
where c.HierarchyName = @hierarchyname
-- step 4: insert into OfficeHierarchyBridge
insert into dbo.OfficeHierarchyBridge (
, OfficeKey
, OfficeKey
from dbo.OfficeHierarchy
where HierarchyName = @hierarchyname
and OfficeKey IS NOT NULL

The following are the main points about the above script:

  • All rows from the stgOfficeGroup table are inserted into the OfficeHierarchy table.  The stgGroupNumber and stgGroupParent columns are used later to update the ParentKey with the OfficeHierarchyKey value which is an identity.

  • All rows from the stgOfficeMapping table are inserted into the OfficeHierarchy table; the stgGroupParent column is used later to update the ParentKey with the OfficeHierarchyKey value.

  • The ParentKey values are updated with the OfficeHierarchyKey value by joining the stgGroupParent values with the stgGroupNumber values for the HierarchyName we are processing.

  • The OfficeHierarchyBridge table is populated with the OfficeHierarchyKey and OfficeKey values, where the OfficeKey is not null; i.e. only child rows.

Now that we have populated the OfficeHierarchy and OfficeHierarchyBridge tables, we are ready to build an SSAS cube.  For the basics of building a cube, please refer to our earlier tip How To Build a Cube From an Existing Data Source Using SQL Server Analysis Services.  There are two settings in the Parent Key attribute of the Office Hierarchy dimension of our cube that should be highlighted:

dim properties

Since the Office Hierarchy dimension will hold numerous hierarchies, we really don't want an All member which would cause us to see measure totals that are greater than the actual detail in our fact tables.  Therefore we set the IsAggregatable property to False.  The MembersWithData property is set to NonLeafDataHidden so that the parent (or group) does not also appear in its list of children.

Now we are ready to see our multiple hierarchies in action.  The next two screen shots show an Excel pivot table connected to the SSAS cube where the Hierarchy Name column is used as the filter.  First we'll show our pivot based on the FY 2008 hierarchy:

fy2008 pivot

Next we'll show the pivot for our FY 2009 hierarchy:

fy2009 pivot

The FY 2009 hierarchy was another hierarchy that was added utilizing the same staging tables and T-SQL script as shown above.  The Red, White and Blue groups are meant to represent regions.  Specific VPs are assigned to each region, and offices are assigned to each VP.

The benefit of this approach is that you can support multiple reporting hierarchies for any dimension by simply adding a bridge table and a <dimensionname>hierarchy table.  Adding the actual hierarchies themselves does not require any further schema changes.

Next Steps
  • For an excellent paper on many-to-many dimensions please take a look at this link.  A PDF and some sample SSAS projects are available for download.  This tip was based in part on the Multiple Hierarchies section.
  • Download the sample code and experiment with the multiple hierarchies.  See the README.txt file for the details on the sample code.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, March 11, 2014 - 5:20:38 AM - Raii Back To Top (29701)



Tuesday, September 11, 2012 - 9:53:09 AM - Ray Barley Back To Top (19468)

Based on your table schema it looks like you just need a PK and a FK like this:








FOREIGN KEY ([ManagerEmployeeID])



When you create the parent child dimension it recognizes the self join automatically and the attributes get set appropriately.


You can also check this tip for additional details:

Monday, September 10, 2012 - 10:11:32 PM - kumar Back To Top (19460)

below example for 0 Employee Manager is 1040

-1 Manager is 0

when I implement parent child Hierarchy in Cube it show as






It is not showing o has children -1 and -2

Could you please let me know how to handle this scenerio









EmpID ManagerEmployeeID EmpName ManagerName Skey
-2 0     a NULL 391
-1 0     B NULL 392
0 1040      C NULL 356
1004 1040      D  zxc




Friday, March 18, 2011 - 10:24:07 AM - Sam Kane Back To Top (13249)

Here are this and some other articles on SSAS Hierarchy:
<a href=""></a>

Friday, July 2, 2010 - 7:54:53 AM - raybarley Back To Top (5779)

 Maybe you can leverage CurrentMember on the dimensionand and figure something out with the level.

Thursday, July 1, 2010 - 8:50:35 PM - santade Back To Top (5776)

Hi Ray,

In fact, I am using Executive Viewer (now, IBM Cognos Executive Viewer) as my front-end and I cannot change the sum as you can change on SSIS. Is there a way to define this on dimension properties? Or I need to do a new calc (replacing CALC DEFAULT), to do this? I've saw that I can use the SUM fuction: is it works? Maybe I could use IIF on Analysis Services to do that?

Thanks in advance!

Gustavo Santade


Thursday, July 1, 2010 - 4:39:55 PM - raybarley Back To Top (5775)

If you were using SQL Server Reporting Services, there is a Level function that you can use to determine the level of the hierarchy (starts with 1 or 0 then increases I think).  Where you would have an expression like SUM(measure) to print a measure you could change it to a conditional expression where if the level > 0 you print the value else you print blanks.

There is an earlier tip on using SSIS with parent-child hierarchies:


Thursday, July 1, 2010 - 3:24:52 PM - santade Back To Top (5774)

Excellent tutorial, but I have a question about Parent/Child: Is it possible no agregate the consolidade value on the Parent level? I want to leave the Parent (or Fisrt Level) as Label Only, without data. Thanks!

Friday, July 10, 2009 - 6:25:41 AM - rptodd Back To Top (3721)

This is brilliant! I'm JUST in the middle of conforming a products dimension from a variety of different business units here (Retail, Ticketing, Pro Shops, etc.) that have a variety of different reporting hierarchies, in addition to some periodic requests that I get for custom ones from end users. Now I have a good solution.

The only complex part will be building an SSIS package that can generate the staging table, but I'm sure I'm up to the task.


Thanks for the great tip,

Rick Todd

Data Warehouse Architect - Colonial Williamsburg Foundation (

get free sql tips
agree to terms