Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Microsoft Business Intelligence and Data Warehousing

Garrett Edmondson, Independent Consultant MCITP & MCTS: 10+ years experience with BI stack.

Dimensional Modeling Financial Data in SSAS pt2

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


Comments

Leave a comment on the original post [garrettedmondson.wordpress.com, opens in a new window]

Loading comments...