The SSAS Financial Cube–Part 1–Ragged Hierarchies
It’s been way too long since I’ve posted anything. As one of my new year’s resolutions was to blog more and it’s nearly March, I ‘m failing miserably at that goal. Not to make excuses but seems like at my day job all I do is write and when I get home, writing is the last thing I want to do. So after what seems like hours of commuting, at home I’ve been trying to make up for a lack of SharePoint knowledge, a product I have been trying to avoid for nearly a decade but have been unable to over the last few months. Maybe a post or two on that later just to embarrass myself.
So instead of embarrassing myself with little SharePoint knowledge that I’ve gained, I’m going to post on something that I know a bit about. I’m going to try to commit to writing a few posts around building a financial cube in SSAS. I’ll try not to completely duplicate previous blogs, particularly those of Garrett Edmondson.
On my first BI engagement over a decade ago, I was involved in a multidimensional database product selection. The potential choices were Hyperion Essbase, Microsoft OLAP Services 7.0 (pre-2000), Cognos whatever it was called at the time, and Oracle Express. The scope of the project was limited to the Profit&Loss accounts so we did not need to handle complex per account semi-additive measures, though I will discuss those later in another post. We selected Essbase and one of the principal reasons was that this product gracefully handled ragged hierarchies while the Microsoft offering did not. Well, since then, the Microsoft offering has matured quite a bit. Been some bumps along the way such as that annoying MDX Compatibility setting issue but seems like now the kinks have ironed themselves out. They just seem to work now. However, there’s still a way and probably a better way to implement.
What is a Ragged Hierarchy?
So first, what is a ragged hierarchy? MSDN actually has a great page defining a ragged hierarchy and how to work with one in SSAS. It may be the best page on the site! Succinctly, it’s a hierarchy with a varying number of levels dependent upon the navigation path taken. Usually, you’ll run into ragged hierarchies with financially based dimensions and particularly with an Accounts dimension. For Cost and Revenue related expenses, the navigation path may have many, many levels depending on the complexity of an business’ value chain while for Operational expenses, the navigation path may only contain just two or three levels.
There exists two ways to address the implementation of a ragged hierarchy; using a parent-child hierarchy or flattening out that parent-child relationship and taking advantage of the HideMemberIf property in a user-navigable hierarchy. Which approach you use depends on the requirements of your BI solution.
The most common approach to addressing a ragged hierarchy is with a Parent-Child dimension. In fact, using a parent-child hierarchy is exactly how the Account dimension is defined in the AdventureWorks database as shown below.
|Net Income||Operating Profit||Operating Expenses||$27,661,868.50|
|Gross Margin||Net Sales||$64,626,468.00|
|Total Cost of Sales||$20,236,365.00|
|Gross Margin Total||$44,390,103.00|
|Operating Profit Total||$16,728,234.50|
|Other Income and Expense||Interest Income||$100,687.00|
|Gain/Loss on Sales of Asset||($123,795.00)|
|Curr Xchg Gain/(Loss)||$214,625.00|
|Other Income and Expense Total||$50,017.50|
|Net Income Total||$12,609,503.00|
So that the raggedness of the hierarchy is more evident, the PivotTable was displayed in tabular form.
What are the pros and cons of a parent-child hierarchy? Ella Maschiach does an excellent job describing the pros in her blog post. It’s also the approach that is the easiest to understand and the approach that is the easiest to implement. However, there’s a serious performance penalty that one pays for this simplicity. One of the primary reasons one uses SSAS in a BI solution is for aggregation management. Rather than constructing summary fact tables to address performance problems with the most commonly references dimensional attributes, aggregations in SSAS handle that for me. However, with a parent-child hierarchy, as mentioned in the Analysis Services Performance Guide, aggregations will only be created on the leaf level of the parent-child hierarchy and the All level only if the IsAggregatable property is not set to false. Therefore, with very large parent-child hierarchies that are used heavily in end user queries, there will be a tremendous performance penalty paid. If performance is at a premium, avoid parent-child hierarchies.
Naturalizing a Parent-Child Hierarchy
When your solution contains a parent-child hierarchy with many members, you’ll want to naturalize the dimension. This approach flattens out the parent-child hierarchy so that a separate column is explicitly created for each potential level in the hierarchy. You may want to still model your dimension using a recursive relationship because of its ease of maintenance. When creating a new member in the hierarchy, only the parent member would have to be specified. Once a dimension has been flattened, creating a new member requires that all ancestors of that member would also have to be specified on that dimension record.
From a recursive relationship, you’ve got a couple choices. You can flatten the dimension out first by using a CTE and then building your SSAS dimension from that structure. Itzik Ben-Gan described an approach in a SQL Server Mag article where a recursive relationship was flattened out so that a record was created for each node in the hierarchy. With that table, you can build a dimension where each level of the hierarchy is a separate attribute of the dimension. The dimension structure once flattened would look something like the following.
and the Attribute Relationships table for that dimension would define a natural hierarchy from Account Key –> Account Level 3 –> Account Level 2 –> Account Level 1.
Each level in this case is associated with a Row Number attribute that defines the natural order in which the hierarchy should be displayed.
When flattened in this manner, a ragged hierarchy is converted into a regular hierarchy where every navigation path has the same number of levels. For navigation paths where levels should be missing, the child name is repeated in that level as shown below.
Level 3 Key
Level 3 Desc
Level 2 Key
Level 2 Desc
Level 1 Key
Level 1 Desc
|1||Net Income||1||Net Income||1||Net Income||1||Net Income|
|2||Operating Profit||2||Operating Profit||2||Operating Profit||1||Net Income|
|3||Operating Expenses||3||Operating Expenses||2||Operating Profit||1||Net Income|
|4||Gross Margin||3||Operating Expenses||2||Operating Profit||1||Net Income|
To “hide” these levels, the HideMemberIf property of each level in the user-navigable hierarchy needs to be set to Parent Name as shown below.
Why flatten out a hierarchy when it’s much less intuitive and harder to maintain? Simple answer is while aggregations cannot be created on intermediate levels of a parent-child hierarchy, aggregations can be created on any level of a flattened hierarchy.
So say you have a parent-child hierarchy that’s performing less than optimally. Do you have to do all this to change it? Not exactly. You can use the Naturalize Parent-Child Dimension menu option in Solution Explorer that is installed with BIDSHelper. This added feature will do much of what’s been described in this section for you. Pretty cool.
Handling ragged hierarchies is just one of the requirements that’s required in constructing a financial. In a couple future posts, if I ever get around to it, I’ll discuss the ByAccount aggregation function that allows differing aggregation functions to be applied to different sections of an Account hierarchy, and a couple of approaches for addressing currency conversion.