Cognos 10.2
Oracle database
In Framework Manager I have created a simple dimesional model...
The time/date dimension...
Dimension - Approval Date
Hierarchy - Approval Dates (All)
Level - Fiscal Year
Level - Fiscal Quarter
Level - Fiscal Period (i.e. month)
Level - Day
Regular Dimension
Dimension - Statuses
Hierarchy - Statuses (All)
Level - Statuses
Level - unique key
Measure Dimension:
# Approvals
Order Amount
1) When I create a simple crosstab report, in Report Studio, with "Statuses" as rows and "# Approvals" I get correct results
generated query (native sql)
01 SELECT
02 "STATUS_D"."STATUS" "Statuseskey"
03 , SUM("MEASURE_F"."APPROVAL_CNT") "C___Approvals"
04 FROM
05 "AAA"."STATUS_D" "STATUS_D"
06 , "AAA"."MEASURE_F" "MEASURE_F"
07 WHERE
08 "MEASURE_F"."STATUS_SK" = "STATUS_D"."STATUS_SK"
09 GROUP BY
10 "STATUS_D"."STATUS"
11 ORDER BY
12 "Statuseskey" ASC NULLS LAST
2) When I add a slicer for a specific "Fiscal Year", cognos generates a query with a cartesian join on every day of the selected fiscal year.
(i.e. "# Approvals" x 365 )
the resulting query generated by cognos has some issues (see below):
a) two references to the date dimension table ( lines 07, 08 )
b) two predicates for the same filter ( lines 13, 14 )
c) the coguda01 reference in line 12 is from the slicer
d) the coguda00 alias for the date table is joined to the fact but the coguda01 is not ( causing the cartesian join )
generated query (native sql)
01 SELECT
02 "coguda00"."FISCAL_YEAR" "Fiscal_Yearkey"
03 , "coguda00"."PER_NAME_ENT_YEAR" "Fiscal_Year_Name"
04 , "STATUS_D"."STATUS" "Statuseskey"
05 , SUM("MEASURES_F"."APPROVAL_CNT") "C___Approvals"
06 FROM
07 "AAA"."DATE_D" "coguda00"
08 , "AAA"."DATE_D" "coguda01"
09 , "AAA"."STATUS_D" "STATUS_D"
10 , "AAA"."MEASURES_F" "MEASURES_F"
11 WHERE
12 "coguda01"."FISCAL_YEAR" = 2015
13 AND "coguda00"."DATE_WID" >= 20120401
14 AND "coguda00"."DATE_WID" >= 20120401
15 AND "MEASURES_F"."APPROVED_DT_WID" = "coguda00"."DATE_WID"
16 AND "MEASURES_F"."STATUS_SK" = "STATUS_D"."STATUS_SK"
17 GROUP BY
18 "coguda00"."FISCAL_YEAR"
19 , "coguda00"."PER_NAME_ENT_YEAR"
20 , "STATUS_D"."STATUS"
21 ORDER BY
22 "Statuseskey" ASC NULLS LAST
It will join the tables based on how the joins are defined in the relational layer. Check the joins of the Date dimension with the Fact. Also check if there is more than one join defined between the same tables (sometimes modelers do this and don't realize it - you will see two lines between the same tables in the Content explorer).
By the way, what does # of Approvals technically mean? If it is like Order Qty then you can sum it but if it like a Count of something then summing is not the correct way to count because Counts are semi-additive measures and 'sum' would make it fully additive.
Hi, Thanks for the quick response!
This is what we originally thought was the issue, but I have checked (and re-checked!) all the joins between the Fact and the date dim, there is only one.
FYI..."# Approvals" is simply a row count ( i.e. each row in the database table has a value of 1 in the Approvals_Cnt column )
Can't say about the joins without knowing more.
Regarding the counts, it would be give wrong results in some cases. Lets' say you make a report using a crosstab, put Status in rows and put something else as the 2nd level of Status. the grand total of count in that case is likely to be wrong.
Not sure if this will provide any more insight regarding the joins...
The date dimension I have created is based on an role-playing alias shortcut of our date dimension table.
FYI... I would think that if the joins were incorrect then our relational based reports would also be experiencing problems, but they work fine?
Also... If I use the date dim in a filter, instead of as a slicer, there are no issues.
Cognos is generating the following "IBM Cognos SQL"
(select
DATE_D.DATE_WID as DATE_WID,
DATE_D.PER_NAME_ENT_YEAR as PER_NAME_ENT_YEAR,
DATE_D.FISCAL_YEAR as FISCAL_YEAR
from
CDW..CDW.DATE_D DATE_D,
CDW..CDW.DATE_D DATE_D_Approval
where
(DATE_D_Approval.FISCAL_YEAR = 2016)
) DATE_D__Approval,
You can see it is joining in the Date dim table twice.