Combine fact at 2 levels - Base and aggregate fact

        Lets now see how to combine data from 2 facts which are at different levels. i.e consider the following dimensions product, time and a fact. The fact has records at day level for each of the product. There is also another aggregated fact at the month level. The reports, when they query for a monthly or a yearly report, it would be beneficial to hit the aggregated fact rather than the day level fact for improved performance.

      To model this, have a time dimension aggregated at the month level. (i.e) for ex -

        Import this table into the physical layer and pull into the BMM layer as below.

     Import the aggregated fact into the physical layer and into the BMM layer as below.

     Go to the content tab of each of the LTS of the both the time dimension and the fact and set the levels to each of them as below.In the time dimension, for the aggregated dimension, set it to the level of Month so that when a report queries at month level or higher, this dimension is used. Similarly for the fact aggregate, set it to Month level for the Time dimension as below. For the base level time dimension and fact, set it to the leaf level (day) in this case.

 For aggregated fact,

 For base time dimension

   For base fact,

 
Now when the reports are generated with the month or a level greater then month included in the report, it would hit the month aggregated and fact aggregated tables. Only when a report uses the date (day level data), the base fact would be used. Thus the performance is improved.