I recently had a report that a dashboard was returning incorrect results. At first glance this isn’t exactly unexpected, and I had initially attributed it to user error. But then I tested it, first in Dashboards then in Report Studio, and I was getting the same results.
The request is simple. Show a list of product lines, revenue, and revenue per working day. The revenue fact is joined to the product dim and the date dim. Working days fact is joined only to the date dim.
I know that 2011 had around 200 working days, but let’s see what I get when I recreate the query
That seems wrong, 87,000 working days in a single year is a little excessive, even compared to my own workaholism. What is going on in the query?
WITH DaysFact0 AS ( SELECT DaysFact.DAY_KEY AS DAY_KEY, CASE DaysFact.DAY_OF_WEEK WHEN 1 THEN 0 WHEN 7 THEN 0 ELSE 1 END AS "Working Days", DaysFact.DAY_OF_WEEK AS DAY_OF_WEEK FROM great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM DaysFact ) SELECT SLS_PRODUCT_DIM.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE, SUM(DaysFact0."Working Days") AS Working_Days, SUM(SalesFact.SALE_TOTAL) AS SALE_TOTAL FROM great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM_Order INNER JOIN DaysFact0 ON GO_TIME_DIM_Order.DAY_KEY = DaysFact0.DAY_KEY INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_SALES_FACT SalesFact ON GO_TIME_DIM_Order.DAY_KEY = SalesFact.ORDER_DAY_KEY INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_PRODUCT_DIM SLS_PRODUCT_DIM ON SLS_PRODUCT_DIM.PRODUCT_KEY = SalesFact.PRODUCT_KEY WHERE GO_TIME_DIM_Order.CURRENT_YEAR = 2011 GROUP BY SLS_PRODUCT_DIM.PRODUCT_LINE_CODE
With a multiple fact query, I should be getting a stitch query in the SQL. In effect, it should be creating two queries – one for the revenue measures and one for the days. It should then join them together on the conformed dimension. In this case, there’s no conformed dimension, so the same number should repeat for each product line.
The problem must be in the physical layer – let’s take a look at the joins.
Do you see the problem? The join from the Date Dim to the Date Fact is a 1..1 on both sides. This instructs Cognos that the date fact is actually a snow-flaked dimension. But, you may argue, it is a 1 to 1 join! The join itself is on the day key, how much more 1 to 1 can you get? The answer is simple – cardinality simply describes wether a table is a fact (at the end of a 1..n join) or a dimension.
Switching that join to 1..n results in the following SQL.
WITH DaysFact0 AS ( SELECT DaysFact.DAY_KEY AS DAY_KEY, CASE DaysFact.DAY_OF_WEEK WHEN 1 THEN 0 WHEN 7 THEN 0 ELSE 1 END AS "Working Days", DaysFact.DAY_OF_WEEK AS DAY_OF_WEEK FROM great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM DaysFact ), FS1 AS ( SELECT SUM(DaysFact0."Working Days") AS Working_Days FROM great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM_Order INNER JOIN DaysFact0 ON GO_TIME_DIM_Order.DAY_KEY = DaysFact0.DAY_KEY WHERE GO_TIME_DIM_Order.CURRENT_YEAR = 2011 ), FS2 AS ( SELECT SLS_PRODUCT_DIM.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE, SUM(SalesFact.SALE_TOTAL) AS SALE_TOTAL FROM great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM_Order INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_SALES_FACT SalesFact ON GO_TIME_DIM_Order.DAY_KEY = SalesFact.ORDER_DAY_KEY INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_PRODUCT_DIM SLS_PRODUCT_DIM ON SLS_PRODUCT_DIM.PRODUCT_KEY = SalesFact.PRODUCT_KEY WHERE GO_TIME_DIM_Order.CURRENT_YEAR = 2011 GROUP BY SLS_PRODUCT_DIM.PRODUCT_LINE_CODE ) SELECT FS2.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE, FS1.Working_Days AS Working_Days, FS2.SALE_TOTAL AS SALE_TOTAL FROM FS1, FS2
It’s a little longer, but it makes much more sense now. It’s a single value on the days fact side with no conformed dimension, so there’s no join on the two FS queries.
And the results:
In this example it was fairly obvious where the problem was, but in a real-life example you may have dozens of tables connecting to many facts. If you are running into data problems, a careful review of the joins may often reveal the problem.