Dimensional Modeling Financial Data in SSAS

Part 1 – Parent Child Hierarchies and Pseudo GL Accounts

new: Financial Data Warehouse video 

This post is born out of some real life  lessons that I learned while putting together a Financial Data Warehouse based on General Ledger (GL) data i.e. all the GL’s in the foundational financial statements from Net Income and  Balance Sheet to  Statistical GLs and  Cash Flow statements.

While there are many great sources for dimensional modeling, Kimball’s book The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition) is essential, but there are few resources which discuss dimensional modeling for Financial Data. I am going assume that the reader already has a firm knowledge base of Dimensional Modeling.  If not then see my recommendations for books that will help form that foundation: The Start of Something Great.

To be frank this could be a book sized topic in and of it’s self, but I am going to try to cover all the basics so that the reader can avoid the major pit falls.

The first step is gain an understanding of the business processes being modeled (GL creation and maintenance, Period End Close-Out etc..) and the associated requirements (financial statements: Net Income, Balance Sheet, Cash Flow etc…). It is absolutely critical to gain a deep understanding the GL analytical attributes e.g. Business Units, Cost Centers, and Sales Channels. Don’t be surprised when you find out that most of the critical GL attributes are not maintained in any source system but in excel spreadsheets!

I am going to dispense with all the gory details that can be encountered in this process and jump right into the nature and structure of Financial Statements, because their structure will have a significant effect on how the data is modeled.

To make thing easier, I am going to use AdventureWorks as a common point of reference. Here is an example Net Income Statement:

Adventure Works Cube -> Financial Reporting -> rows = named set -> Summary P&L, columns = Amount

I have rearranged the rows from the named set Summary P&L by click and dragging them into something that might look like a financial statement.

A typical financial system will generate financial statements like the one shown above through a series of calculations for each line in the financial statement.

Example:

Line/row 5 (Net Sales) = line 2 (Gross Sales) – line 3 (Returns and Adjustments) – line 4 (Discounts)

In other words Net Sales is calculated by aggregating its dependents/children (Gross Sales, Returns and Adjustments, and Discount).

Note: I know that Returns and Discounts need to be subtracted instead of added, I will discuss ways of dealing with that later.

In fact if you look at the rest of the P&L you will realize that all of the line items have a parent child relationship to each other with Net Income being the parent of all the other line items. The following image shows exactly what that a Financial GL Parent Child Hierarchy
might look like.

Create another pivot table by using the following:

Adventure Works Cube -> Financial Reporting -> Rows = Accounts, Columns = Amount

This is the full GL Accounts Parent Child Hierarchy and it is incredibly valuable to providing consistent and flexible financial analysis. The user can drill down to any level starting from the top (Net Income) to the bottom (individual GLs) and preform all their analysis at any level of granularity. Add to that the analytical attributes like Business Unit, Sales Channel, and Cost Center and you have an extremely valuable solution capable of analyzing any part of the company horizontally (GL Hierarchy) and vertically (Analytical attributes which can have their own hierarchies) instantly!!

Note: The full GL Hierarchy not only contains Net Income GL but Balance Sheet and Statistical GLs as well. Net Income is a child of the Balance Sheet which typically goes under one the Current Retained Earnings Accounts.

Take a look at the DimAccount table in the AdventureWorksDW database:

Notice how the Parent of Balance Sheet member is null. That is because Balance Sheet the parent of all GL’s it has no parent.

Ok now that we a explored the potential of the GL Parent Child Hierarchy, the down side that most financial source systems don’t have a nice GL Hierarchy pre-built for you. You are going to have to get business consensus on the structure of the hierarchy and build it yourself. and you will also need to way to manage it once it is built. You will also have to create pseudo GL accounts that don’t already exist in the source system.  These pseudo GLs will be parent GLs, like a Balance Sheet or Net Income which be required to complete the calculations in the financial statements.

For example what to do when new GLs are created and where do they go in the hierarchy?

Fortunately, there is a solution for that through Master Data Management. Microsoft’s Master Data Management’s solution is called Master Data Services which handles GL Parent Child Hierarchies very nicely.

Building the GL hierarchy is not a trivial task. The one that I built recently was for a larger company had 180,000 plus GLs. All the GLs (members of the GL Account dimension) had put into exactly the correct order for the calculations to be correct.

The next post will discuss the other critical parts of the Financial DW Solution

Things to consider: Parent Child Hierarchies and Cube performance:

See:Analysis Services 2008 R2 Performance Guide

Note: Chris Webb has a good blog post about how to change the order of the named set (Income Statement) http://cwebbbi.wordpress.com/2013/07/31/ordering-of-named-sets-in-excel/

About these ads

Tags: , , ,

12 Responses to “Dimensional Modeling Financial Data in SSAS”

  1. Dimensional Modeling Financial Data in SSAS pt2 « Garrett Edmondson Says:

    [...] Garrett Edmondson Microsoft Business Intelligence and Data Warehousing « Dimensional Modeling Financial Data in SSAS [...]

  2. Link Resource # 34 : Dec 08 – Dec 11 « Dactylonomy of Web Resource Says:

    [...] Dimensional Modeling Financial Data in SSAS [...]

  3. pius Says:

    hi garret
    thx for this nice comment, but i was a bit surprised about this statemend:
    [quote]I have rearranged the rows from the named set Summary P&L by click and dragging them into something that might look like a financial statement.[/quote]

    how did you rearrange the named set? i am not able to do so…
    thx in advance
    pius

    • Garrett Edmondson Says:

      You can change the order using several different methods. You can edit the excel formulas by clicking directly in the MDX expression in excel cells themselves or by using one of my favorite tools called OLAP PivotTable Extensions http://olappivottableextend.codeplex.com/

      • pius Says:

        Thank you for your fast answer garret, it works for me and I can now order my set. But I’ve got a new problem:
        when you add the set “Summary P&L” and want to display each account for the aggregated entry in the set, it shows all accounts for the child-members for example in “Net Income”. I want to display just the directly joined accounts to each level of the hierarchy, is that possible?
        thank you and snowy greetings from Switzerland
        Pius

      • Garrett Edmondson Says:

        There are a few ways of handling this. In the cube you can modify the MDX of the named set to display only the aggregated GLs that you want or you can of course create the named set directly in the excel document using the pivot table extensions. There is a new user interface that makes it easier to do in excel. In my financial cube I had to strike a balance between creating too many named sets in the cube and getting financial analysts to work with MDX in excel (which they weren’t happy with doing initially but once they got the hang of it they could produce their own reports using any GL at any level)

      • pius Says:

        Thank you again!

        I will give it a try with the options of sets and hope, this solves my problem :)

      • pius Says:

        thank you garret, i could solve my problem :)

        had to change my DB a little bit but now everything looks fine :)
        what I dislike a bit about the created set in the cube is the fact that i can’t order it for excel.
        it looks like there is an auto ordering function active in SSAS that I just can avoid when I create the named set in excel…

      • Garrett Edmondson Says:

        I am glad that it worked for you. Financial cubes are some of the best ways to provide value to the business. I hope that your CFO like it.

  4. Hugh Says:

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get four
    emails with the same comment. Is there any way you can remove people from
    that service? Many thanks!

  5. Dimensional Modeling Financial Data in SSAS | My Study to convert Essbase 6 to the latest SSAS Says:

    […] Dimensional Modeling Financial Data in SSAS by Garrett Edmondson […]

  6. Financial Data Warehouse Video | Garrett Edmondson Says:

    […] the last few years that I have blogging my  Dimensional Modeling Financial Data in SSAS and Dimensional Modeling Financial Data in SSAS pt2 posts have been by far my most popular blog […]

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


Follow

Get every new post delivered to your Inbox.

Join 54 other followers

%d bloggers like this: