Mutually exclusive facts. Should I create a new dimention in this case?

Question

There is a star schema that contains 3 dimensions (Distributor, Brand, SaleDate) and a fact table with two fact columns: SalesAmountB measured in boxes as the integer type and SalesAmountH measured in hectolitres as the numeric type. The end user wants to select which fact to show in a report. The report is going to be presented via SharePoint 2010 PPS.

So help me please determine which variant is suitable for me the most: 1) Add a new dimension like "Units" with two values Boxes, Hectolitres and use the in-built filter for this dim. (The fact data types are incompatible though) 2) Make two separate tables for the two facts and build two cubes. Then select either as the datasource. 3) Leave the model as it is and use the PPS API in SharePoint to select the fact to show.

So any ideas?

Answer 1

I think the best way to implement this is by using separate field for SalesAmountB and SalesAmountH in fact table. Then creating 2 separate measure in BIDS and controlling the visibility through MDX. By doing this, you can avoid complexity of duplicating whole data or even creating separate cubes.