CustomRollup

In this article about SSAS, i want to show you an implementation of a custom rollup. In this example I’ll show you a customrollup with a parent-child relationship. Most of time a aggregation of the data is done with a sum, max, min or the other standard aggregation. 

An example is depreciation. I borrowed this from the book “Microsoft SQL ServerAnalysis Services 2008 with MDX”. Depreciation is about “mapping an assets expense over time to benefits gained through use of those assets”, meaning that the value of assets decrease over time.You can deprecate in different manners, exponential, lineair, etc.

In the foolowing example we’ll use an account dimension from the adventureworks database, that indicates the types of accounts of your company such as asset, liability, income and expenditure. Together with a amount we could rollup the information . In case of parent-child hierarchy, analysis services allows you to perform a custom rollup using a feature called unary operators. Unary operators allow a specification of basic aggregation functions, add, subtract, etc. 

If you look into the account dimension, available in the adventureWorks dimension and you incorporates this into an Analysis project, you’ll see something like this:

In the properties of an attribute you'll see a property called CustomRollupColumn. This property needs to be set to the relational column that has the MDX expression  for the customrollupformula. The MDX expression gets evaluated on the cells when the values corressponding members of the hierarchy are being retrieved in the query. 

The sample AdventureWorks database provides a column with custom rollups for the account dimension : operator.

So you can see how the hierarchy of account attributes needs to be rolled up by the operator column. Right click on the ParentAccountkey and specify the levelnames for the parent-child hierarchy like below:

Set the IsAggregatble to false and click on the '...' caption of the CustomRollupcolumn in the properties window. Below you can see the result of custom rollup in the Finance fact:

So this is an example of the customrollup property.

Greetz,

Hennie