The SSAS Financial Cube–Part 1a–Ragged Hierarchies Cont.

In this post, I was supposed to discuss the ByAccount aggregation function. However, before changing focus, I thought I would write some more on Ragged Hierarchies.

In my last post, I discussed how ragged hierarchies can be handled in SSAS using two different approaches. One can model the dimension using a parent-child hierarchy or one can flatten out a parent-child hierarchy, model each potential level as a separate attribute in that dimension, and hide missing levels in an user-navigable hierarchy using the HideMemberIf property. The advantage of the first approach is that it is more dynamic and easier to implement; the advantage of the second is that it is more performant. But what I left out of the last post is a few additional advantages of the second approach. Rather than move on this week to discuss the ByAccount aggregation function or currency conversion, I want to discuss a situation where you have to address multiple ragged hierarchies in the same dimension. How do you handle the situation where you have an Account dimension and two different, maybe only slightly different but yet still different, rollups? One case that I encountered in the past was with a downstream oil and gas company where product transportation costs were included in COGS in one case but due to its unpredictable nature, were excluded from COGS in another. Essbase handled this situation beautifully as in our case, the Accounts dimension was modeled as a dense dimension. Using Essbase’s Shared Member functionality, a second rollup was created by simply defining that rollup in the Dimension Outline based on leaf members used to define the primary account rollup.

Implementing multiple ragged hierarchies in the same dimension is a bit more difficult to implement in SSAS than it was with Essbase. If you choose to model the ragged hierarchies as parent-child hierarchies, you’ll encounter problems as SSAS has a limitation that a dimension can have one and only one parent-child hierarchy. One can overcome that limitation by using the Multiple Parent/Child Hierarchies approach described by Marco Russo and Alberto Ferrari starting on page 70 of the often referenced Many-to-Many Revolution white paper. However, there’s a double performance penalty to be paid using that approach. Not only can you not build aggregations on the intermediate levels in the parent-child hierarchy, now the many-to-many relationship has to be resolved now when an account hierarchy is included in a query. If one sets the IsAggregatable property to False on an account hierarchy, performance can become unacceptable even with a relatively small cube.

The more performant approach will be to flatten out each hierarchy into a separate physical table where the key for each table is the leaf level accounts. A separate physical table is created for each hierarchy just to simplify maintenance as trying to create a single procedure to flatten out all hierarchies into a single physical table becomes a bit difficult to maintain. A view is created to join all tables representing the distinct hierarchies into a single representation. As each table is defined by a primary key on the leaf level account, a view uniting each of these tables would be easy to created. A single SSAS dimension is created from that view where a distinct user-navigable hierarchy is created for each physically flattened table.

Which solution works best for your application? Depends. If you need to respond dynamically to changing number of hierarchies or levels, an approach like that described in The Many-to-Many Revolution is probably going to be the best approach. If you need a solution that addresses aggressive performance requirements, flattening out each hierarchy and combining those hierarchies into a single dimension is going to be the better approach.

What’s been your experiences addressing multiple ragged hierarchies in a single dimension?

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.

Parent-Child Hierarchy

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.

Amount
				USA Operations
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
		Interest Expense		$154,750.00
		Gain/Loss on Sales of Asset		($123,795.00)
		Other Income		$13,250.50
		Curr Xchg Gain/(Loss)		$214,625.00
	Other Income and Expense Total			$50,017.50
	Taxes			$4,168,749.00
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.

Account Key	Account Desc	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.

Next Steps

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.