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

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’

Cube configuration in BIDS:

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:

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’

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