In part 1, we saw user defined hierarchies. For parent child hierarchy, we can use Unary Operator Column. Let’s take the same example from Part1. We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this: Id CategoryItems Parent
1 Phone 4
2 Tab 4
3 Web Applications 5
4 Hardware 6
5 Software 6
6 AllCategory NULL
If we browse the cube taking DimCategory to axis 1 and DimCompany to axis 0, we get to see the below result:
Now, imagine that we received an ad-hoc request from our source data provider that total Hardware Sales for Apple should be 9000 and that of Google should be 12000. There have been some unreported sales for both these companies which do not fit into the available sub categories (phone and tab). So, the requirement becomes likes this: For Apple, Phone sales is 1000 and Tab Sales is 7000, but total Hardware Sales is 9000 (1000 greater than children sum) For Google, Phone sales is 8000 and Tab Sales is 0, but total Hardware Sales is 12000 (4000 greater than children sum) To solve this, first we have to insert Hardware sales for all companies into Fact Table. Id for Hardware is 4. Newly inserted records are shown below:
Now, we need to add unary operator column in Dim Category table. So, the table looks like this: Id CategoryItems Parent UnaryOperator
1 Phone 4 ~
2 Tab 4 ~
3 Web Applications 5 NULL
4 Hardware 6 NULL
5 Software 6 NULL
6 AllCategory NULL NULL
Tilde (~) has been used to ignore these two members while calculating parent sum. Now, we need to change two properties (MembersWithData and UnaryOperatorColumn) of Parent attribute of our parent child dimension:
Now, process the cube and final result looks like this:
Yes, we could insert incremental value for Hardware sales and avoid using unary operator column. Result would be same.