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

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

Problem

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? 

Solution

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:

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:

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:

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:

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:

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(
 HierarchyName
, Description
, stgGroupNumber
, stgGroupParent
)
select  
 @hierarchyname
, GroupName 
, GroupNumber
, GroupParent
from dbo.stgOfficeGroup
-- step 2: insert children
insert into dbo.OfficeHierarchy (
 HierarchyName
, OfficeKey
, Description 
, stgGroupParent
)
select
 @hierarchyname
, 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 (
 select
  OfficeHierarchyKey
 , 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 (
 OfficeHierarchyKey
, OfficeKey
)
select
 OfficeHierarchyKey
, 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:

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:

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

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.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

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

View all my tips