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

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?

Advertisements
Categories: SSAS Tags:
  1. Pramit
    March 22, 2012 at 8:19 PM

    Hi Martin,
    Am going to rewrite the Essbase cube to SSAS cube , please do guide me for the best approach.

    Regards
    Pramit

    • March 22, 2012 at 9:29 PM

      Hi Pramit,

      There’s not really a right way or a wrong way. I would suggest you look at CubePort. http://www.exologic.com/products.htm It’s probably going to be the best and most cost effective way to approach your migration effort, though I’ve never used it myself.

      Martin

  2. Lesley
    December 15, 2016 at 8:54 AM

    This is exactly what we are doing!!! So I am new to Analysis Services. I actually did the steps outlined above so far before I even read this. We blew out the parent/child into a physical table with multiple levels. We created a view to join the separate hierarchies into 1 with the top level being the hierarchy type. Is there any write-up that explains how to define the dimension in Analysis Services?

  3. February 3, 2017 at 3:43 PM

    The MSDN article that led me to this article says:

    “If your dimension contains more than one ragged hierarchy, you should use the
    first approach, setting HideMemberIf. BI Developers with practical experience in
    working with ragged hierarchies go further in advocating for additional changes
    in the physical data tables, creating separate tables for each level. See Martin
    Mason’s the SSAS Financial Cube–Part 1a–Ragged Hierarchies (blog) for details
    about this technique.”

    But your blog article doesn’t explicitly say “table per level”. I had interpreted what you said as “table per hierarchy with columns per level”. Hoping you could clarify – perhaps with a reference to an article that covers the implementation details.

  1. April 30, 2012 at 3:01 PM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: