Unary operators in SSAS

Today’s topic will be the unary operators in Analysis Services. They allow us to build custom rollup operations in hierarchies. In standard hierarchies, eg. Parent-Child child elements are aggregated to parent with one standard operator defined, in most cases it will be + operator. But sometimes we need to create something custom and then unary operators come up.

Unary operators allow us to build some interesting feature based on dimension table like this:

parent-child-table

This table shows that:

Total=LevelA+LevelB

LevelA=LevelA1-LevelA2-LevelA3

LevelB=-LevelB1

+ and – are not the only possible way – following table provide us other available unary operators that SSAS can deal with(click to zoom):

unary operators table

Ok, now let’s try to build dimension that will use unary operations based on datawarehouse dimension DimCosts that i have created in my test database. First of all we need to create standard parent- child dimension:

parent-child-dimension

And the only custom work for us is to set UnaryOperatorColumn property of Parent Group attribute as follows:

properities

After that we can see the results in cube browser:

cube browser

It works perfectly! But let’s change our dimension to get different – more sophisticated result:

table

And add some fact data:

table2

After processing we will get very different result:

result

As you can see we can get very complicated calculations with this type of operators. Without unary operators sometimes it will be very difficult or almost impossible to achieve something like in the picture above. I hope you enjoy this short post!

Leave a Comment

Your email address will not be published. Required fields are marked *