Dimensional Modeling Financial Data in SSAS pt2

new: Financial Data Warehouse video 

Unary Operator and Aggregating over time

In my last post I discussed how to arrange all of the GLs into a Parent Child Hierarchy. I also mentioned that I would address the issue of having the child GLs sum to their parent through addition or subtraction.

Using Unary Operators as an attribute in the GL Account dimension controls the aggregations of child GLs to their parents.

See example below:

Net Sales = Goss Sales – Returns and Adjustments – Discounts

ni-statement

Unary Operator Attribute in the Adventureworks DimAccount dimension:

SELECT
AccountCodeAlternateKey
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[Operator]

FROM [AdventureWorksDW2008R2].[dbo].[DimAccount]
where AccountDescription = ‘Net Sales’
or AccountDescription = ‘Gross Sales’
or AccountDescription = ‘Discounts’
or AccountDescription = ‘Returns and Adjustments’

image

Cube configuration in BIDS:

image

The last step is to create another attribute for controlling how these GLs aggregate across time. The Balance Sheet GLs are semi-additive (meaning like personal bank accounts they have running balances) and the Net Income GLs are fully additive across time. More SSAS Cube configurations are  needed to be able to have different parts of the same GL Parent Child Hierarchy aggregate differently over time.

Configure GL Account dimension to an Account Type then configure the SSAS Database like the following:

image

Keep in mind that you will need to create the ‘Account Type’ attribute to hold the values shown above.

Here is what it looks like in the AdventureWorks DimAccounts table:

SELECT
AccountCodeAlternateKey
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[Operator]
,AccountType

FROM [AdventureWorksDW2008R2].[dbo].[DimAccount]
where AccountDescription = ‘Net Sales’
or AccountDescription = ‘Gross Sales’
or AccountDescription = ‘Discounts’
or AccountDescription = ‘Returns and Adjustments’

image

Now that all the required GL Account  attributes have been created ( unary, ‘Account Type’) and the GLs have been arranged into a Parent Child Hierarchy, how to you manage these important attributes that are not present in the source system? the answer is:

Master Data Management with SQL Master Data Services

Advertisements

Tags: , , , ,

4 Responses to “Dimensional Modeling Financial Data in SSAS pt2”

  1. The SSAS Financial Cube–Part 1–Ragged Hierarchies « Martin's BI Blog Says:

    […] 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. […]

  2. 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 […]

  3. Haroon Says:

    Hi Garrett,

    We are currently building Fiannce cube and currently working on to build hierarchies. I have gone through your videos for POC but my question, is it possible to use unary operators concept on account dimension which is not in parent child mode. All I have is Account levels, Level 5, Level4, Level3, Level2m Level1 and then GL Account in tablular format. How we can use unary operators in this flat dimension? On level 5, we have Revenue, Expenses, Total Liabilities, Equity, other expenses. I need to calculate EBITDA, Profit calculations and some other calculations based on thes levels.Would that be in Level 6?

    Can you please advise what’s the best way to achieve this in SSAS?
    Cheers,
    Haroon

    • Garrett Edmondson Says:

      Yes, you can use unary operators in a flat dimension. You could then use calculated members to calculate things like EBITDA. However, the users would not be able to browse EBITDA like the Chart of Accounts hierarchy in my video. Adding a level 6 might work as long as the calculations do not share accounts (I think that this would be unlikely).
      I would recommend building a chart of accounts dim with a parent child hierarchy representation of the levels (gives them a lot of flexibility). Use calculated members for things like EBITDA and if they really want to see a breakdown of EBITDA that could even be a separate dim as well.

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: