Cognos server generates odd SQL statement...why?

[florian]

Hi folks,

I'm new to this board. My company is working with cognos for some months and now I'm really struggling with an issue related to the SQL that is generated by cognos.

Let me explain the problem in a very simplified way:

I have one fact table and one dimension table, connected via a field ID_DIM on both tables.
I defined a very simple, flat dimension (KEY, NAME) based on the dimension table.
There are 2 measures defined on the fact table, one on column MEASURE1 with aggregation=sum, the other on column MEASURE2 with aggregation=distinct count.
When I report this data grouped by the members of the dimension, the following SQL is executed against the database:

Code Select Expand


SELECT T0.DIM_KEY, T1.DIM_NAME, T1.MEASURE_SUM, T0.MEASURE_DISTINCT_COUNT
FROM   (SELECT   DIM.DIM_KEY, count(DISTINCT FACT.MEASURE2) AS MEASURE_DISTINCT_COUNT
        FROM     DIM_TABLE DIM, FACT_TABLE FACT
        WHERE    FACT.ID_DIM = DIM.ID_DIM
        GROUP BY DIM.DIM_KEY) T0,
       (SELECT   DIM.DIM_KEY, DIM.DIM_NAME, sum(FACT.MEASURE1) AS MEASURE_SUM
        FROM     DIM_TABLE DIM, FACT_TABLE FACT
        WHERE    FACT.ID_DIM = DIM.ID_DIM
        GROUP BY DIM.DIM_KEY, DIM.DIM_NAME) T1
WHERE  T0.DIM_KEY = T1.DIM_KEY


This statement returns correct answers, but what I really ask myself: Why doesn't the SQL look like this:

Code Select Expand


SELECT   DIM.DIM_KEY, DIM.DIM_NAME, sum(FACT.MEASURE1) AS MEASURE_SUM, count(DISTINCT FACT.MEASURE2) AS MEASURE_DISTINCT_COUNT
FROM     DIM_TABLE DIM, FACT_TABLE FACT
WHERE    FACT.ID_DIM = DIM.ID_DIM
GROUP BY DIM.DIM_KEY, DIM.DIM_NAME


The upper statement (generated by cognos) is less performant, and in my real world that is less simple than this example, this way of generating the sql is forcing serious performance problems.


During my investigations on this issue i found out that the SQL is generated like in the second statement, when I set the aggregation of the second measure to 'count' (instead of distinct count). Than, everything is put into one single select, as expected.

Does anyone have a clue, how I can force cognos to generate performant sql?
What could be wrong in my model that leads cognos to generate the SQL that stupid way?


We run Cognos 10.1.1.

Thanks in advance!
Florian