How to deal with mixed granularity in a fact table (In Framework Manager, Report Studio and Metrics Designer)

Check list of all posts

Context

Please see sample below

The daily call average = (total calls) / (Total days). The days field is based on Month and Territory. There is days number for each month and each territory, while you have calls for different call type in the table. Based on this table, you can’t calculate daily call average by simply using ( total calls/total days).

Based on Kimball modeling method, this is a wrong design, as each fact table should be designed with the same granularity.  The right deign should be shown as below


This design may not be very convenient to calculate daily call average at all:

1. To get report work, you need to make queries to get total days and total calls, and then join both queries to get daily call average. It will present the same problem in query studio as well.
2. To get metric work in metric studio, it presents even more challenges, you may need to combine slice and dice these two tables at the different level to calculate metrics.

In case when the raw table has been defined and can’t be changed, what is the best way to handle fact table with different granularity.  This document is intended to provide a solution.
.

How to handle it in report (Framework Manager and Report Studio)

Define days based on average for Month and territory in framework manager

average ( [gosales].[FactCallAvg].[Days] for [Business view].[FactCallAvg].[Month] ,[Business view].[FactCallAvg].[Territory] )

see screenshot below


Based on this logic, report can be simply generated based on simple total.  Please see the first screenshot for reference. The SQL generated behind
SELECT T0.c0 AS C0,
       T0.c1 AS C1,
       T0.c2 AS C2,
       T0.c4 AS C3,
       T0.c3 AS C4 FROM   (SELECT T2.c0 AS C0,
               T2.c1 AS C1,
               T0.c2 AS C2,
               T1.c2 AS C3,
               T2.c2 AS C4
        FROM   (SELECT factcallavg.month        AS C0,
                       factcallavg.businessunit AS C1,
                       Sum(factcallavg.calls)   AS C2
                FROM   gosales.dbo.factcallavg FactCallAvg
                GROUP  BY factcallavg.month,
                          factcallavg.businessunit) T0,
               (SELECT factcallavg.month                   AS C0,
                       factcallavg.territory               AS C1,
                       Avg(Cast(factcallavg.days AS REAL)) AS C2
                FROM   gosales.dbo.factcallavg FactCallAvg
                GROUP  BY factcallavg.month,
                          factcallavg.territory) T1,
               (SELECT factcallavg.month        AS C0,
                       factcallavg.businessunit AS C1,
                       factcallavg.territory    AS C2
                FROM   gosales.dbo.factcallavg FactCallAvg) T2
        WHERE  ( T2.c0 = T0.c0
                  OR T2.c0 IS NULL
                     AND T0.c0 IS NULL )
               AND ( T2.c1 = T0.c1
                      OR T2.c1 IS NULL
                         AND T0.c1 IS NULL )
               AND ( T2.c0 = T1.c0
                      OR T2.c0 IS NULL
                         AND T1.c0 IS NULL )
               AND ( T2.c2 = T1.c1
                      OR T2.c2 IS NULL
                         AND T1.c1 IS NULL )) T0 ORDER  BY 1 ASC,
          2 ASC,
          4 ASC 

How to handle it in scorecard (Metrics Studio)

Applying for the same idea, you can handle it in Metric designer as follows:

total([Metric Types].[FactDailyAvg].[Calls])/ total ( average([Metric Types].[FactDailyAvg].[Days] for [Metric Types].[FactDailyAvg].[TerritoryCode],  [Metric Types].[FactDailyAvg].[DailyCallAvgDate]) )*

*Not the same table is used as above