Leaf members do not sum up to parent – Part 2 (parent child hierarchy)

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.

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in MDX, SSAS (Analysis Service), Cube and tagged , , , . Bookmark the permalink.

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