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:
This table shows that:
+ and – are not the only possible way – following table provide us other available unary operators that SSAS can deal with(click to zoom):
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:
And the only custom work for us is to set UnaryOperatorColumn property of Parent Group attribute as follows:
After that we can see the results in cube browser:
It works perfectly! But let’s change our dimension to get different – more sophisticated result:
And add some fact data:
After processing we will get very different 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!
Latest posts by Adrian Chodkowski (see all)
- SQLDay 2017 – one of the best data platform conferences - January 16, 2017
- Analysis Services Tabular translations and Tabular model explorer - December 18, 2016
- SQL Server 2016 is available! - June 1, 2016