Zahid's blog on Data Engineering and BI

We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this:

Id    SubCategory                 Category

1    Phone                              Hardware

2    Tab                                  Hardware

3    Web Applications         Software

The key attribute is Id and attribute relationship is     Id->Sub Category->Category

There is one user-defined hierarchy called “Hierarchy” in DimCategory.

In BIDS 2008 cube browser, if we drag the user defined hierarchy in one axis and company names in another, we can see this report:

In Hardware category, total Apple sales is 8000, total Google sales is 8000, total Microsoft sales is 8000 and total Samsung sales is 6000.

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)

One possible solution could be to introduce another Sub Category called “Dummy”. So, DimCategory table looks like this:

Id    SubCategory                 Category

1    Phone                              Hardware

2    Tab                                  Hardware

3    Web Applications         Software

4    Dummy                          Hardware

And, we have to insert the unreported extra sales into Fact table (as “Dummy” sub category sales). Our Fact table has only three columns; CompanyId, CategoryId and Sales. So, the insert statement will look like this:

Insert into Fact (CompanyId, CategoryId, Sales) values (2, 4, 1000) –For Apple

Insert into Fact (CompanyId, CategoryId, Sales) values (4, 4, 4000) –For Google

Now, we process the cube and browse it:

It would be great if we could hide “Dummy”. For that, all we have to do is replace the value “dummy” with “Hardware” in DimCategory table, then go to the properties of sub-category level of user defined Hierarchy of DimCategory dimension and set “Hide Member If” to “Parent Name”.

Final result looks like this:

We can also insert total values instead of incremental values; 9000 for Apple and 12000 for Google and use MDX Scope statement to handle Parent sum.