DMR model - cognos creating cartesian join

[Spyhop]

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 

[cognostechie]

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.

[Spyhop]

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 ) 

[cognostechie]

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.

[Spyhop]

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?

[Spyhop]

Also... If I use the date dim in a filter, instead of as a slicer, there are no issues.

[Spyhop]

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.