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