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.
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
