Few words about: 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!

Advertisements

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