Tuesday, March 26, 2013

What are the different ways by which a fact can be directed to a particular fact table?


There are three fact tables
Country_ID Revenue

Logical Size:100

Country_ID Year ID Revenue

Logical Size:200

Fact 3
Product_ID Revenue

Logical Size:300

I create a fact Revenue and check all the three tables.

In the report when I use Country Attribute and Revenue MSTR will hit fact table 1 as Fact 1 has the lowest logical size but if I want it to hit Fact 2 then four methods are available.

Method 1
I need to create a separate fact say Country_ID and check Fact2 table alone as source.
In the metric revenue right click the aggregation properties say Sum and you would get parameters in it. Select the Fact ID there as the newly created fact Country_ID

Method 2
Decrease the logical size of Fact 2 to 50 so that MSTR hits fact table 2. 

Method 3

Since Year Attribute is unique to Fact2 in the report add Year attribute to the report objects

Method 4

Create 3 separate facts for each fact table and three separate metrics and use them as required