Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (9)   |   Related Tips: > 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.


Last Update:






About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 

THANK YOU SO MUCH :)


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

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

 

ALTER TABLE TABLENAME

ADD CONSTRAINT [PK_TABLENAME] 

PRIMARY KEY CLUSTERED ([EmpID] ASC)

 

ALTER TABLE [dbo].[TABLENAME]

ADD CONSTRAINT [FK_TABLENAME] 

FOREIGN KEY ([ManagerEmployeeID])

REFERENCES [dbo].[TABLENAME] ([EmpID])

 

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:

http://www.mssqltips.com/sqlservertip/1943/using-a-parent-child-hierarchy-in-sql-server-to-implement-a-custom-security-scheme/


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

below example for 0 Employee Manager is 1040

-1 Manager is 0

when I implement parent child Hierarchy in Cube it show as

Actual:

1040

     1004

            0

 

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

Could you please let me know how to handle this scenerio

Expectedresult

1040

     1004

            0

             -1

             -2

 

 

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

17

 

 


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

Here are this and some other articles on SSAS Hierarchy:
<a href="http://ssas-wiki.com/w/Articles#Hierarchy">
http://ssas-wiki.com/w/Articles#Hierarchy</a>


Friday, July 02, 2010 - 7:54:53 AM - raybarley Back To Top

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


Thursday, July 01, 2010 - 8:50:35 PM - santade Back To Top

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 01, 2010 - 4:39:55 PM - raybarley Back To Top

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: http://www.mssqltips.com/tip.asp?tip=1939

 


Thursday, July 01, 2010 - 3:24:52 PM - santade Back To Top

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

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 (http://www.history.org)


Learn more about SQL Server tools