Determinants in Cognos Framework Manager

Determinants are imported based on the unique key and index information available in the database. These are generally used to avoid double-counting in de-normalized data. We will first understand why double-counting occurs. Sometimes, while joining tables, data present in the joining column may not be present at the lowest level of granularity. This causes the data of higher granularity to repeat in multiple rows, causing data to be over-counted.

For example, in Time dimension, Day Key is a unique determinant that contains data at lowest level of granularity however, Month Key is an non-unique determinant. When you join Time dimension with Product Forecast fact using Month Key, you may see same records repeated for Month those many days present in that month.

 

In order to avoid double counting, we need to specify following determinants in the framework manager model.

 

When you build a report with the above determinants specified, results will be displayed as follows:

 

If you do not specify the determinants, incorrect aggregation may occur and you will see it in the values of Expected Volume column. Here, each record value is multiplied by the number of days in the month.

 

SQL without Determinants:

SELECT

Time.Month as Month,

Sum(Product_Forecast.Expected_Volume) as "Expected volume"

FROM Time_Dimension Time, Product_Forecast

WHERE Time.Month_Key = Product_Forecast.Month_Key

GROUP BY Time.Month

SQL with Determinants:

SELECT

Time1.Month as Month,

Sum (Product_Forecast.Expected_Volume) as "Expected volume"

FROM

(

SELECT

Time.Month_Key as "Month_Key",

Min(Time.Month) as "Month"

FROM

Time_Dimension Time

GROUP BY

Time.Month_Key) Time1 INNER JOIN Product_Forecast ON Time1.Month_Key = Product_Forecast.Month_Key

GROUP BY

Time1.Month