Alas, this is a data module so control of the DQM governors isn't available.
Using the Report Validate for Key Transformations also appears hit and miss. I am looking at a report that when I look at the Validate Report Key Transformations only shows the SQL for one of the lower queries, yet two are sent to the database.
The query is this:
Query Transformation window at the end shows this:
Which implies two queries are needed, yet below this message I only see one query:
And two distinct queries are sent to oracle by cognos for this test case.
Q1 and Q2 are very different, so there is no way DQM thinks it should use just one query to run both Q1 and Q2.
------------------------------
Marc Reed
------------------------------
Original Message:
Sent: Mon March 11, 2024 11:36 PM
From: NIGEL CAMPBELL
Subject: DQM Local Cache Policy
When you are using DQM, you have more choices to control re: explicit, query associated to data container (layout) etc.
In-report joins potentially will use re-use where may not be desired.
https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=mode-dynamic-query-governors.
Persons who want to understand the SQL statements that would be planned should always use one of:
- validate for information
- validate for key transformations
Show SQL for a BI-query does not take into consideration that layout (data container) object which would be related to it.
Layouts define projection, ordering an groupings etc.
------------------------------
NIGEL CAMPBELL
Original Message:
Sent: Mon March 11, 2024 04:35 AM
From: Marc Reed
Subject: DQM Local Cache Policy
Hi All,
I recently spent a few hours debugging a poor running report for a report author. The report had a query that joined two sub queries. This joining query was the only query used by the report. The sql in the report looked good (when you use the show sql in report studio). Report Studio showed a single query with all the query being performed Native, filters joins etc all being passed through to the database..
It was only when we looked at the real sql running in oracle we could see that cognos had really split the single query into two, ignoring a number of filters, and then doing a join and filters locally. Hence the poor performance. I could force the full query onto the database by telling the report to not use local cache. However, I don't want to have to debug every report so am thinking of switching this off completely.
Take the above. I have filters in Q2 and Q3 based on a parameters. Cognos sent Q2 and all its filters to the database, yet for Q3 it sent the query ignoring the filters, even though the filters are identical to Q2. This resulted in a slow long running Q3 query. It was only when attempting to do the join in Query 1 that Cognos then decided to filter q3. I wasn't using any local processing like functions which would result in local processing.
Switching off local cache forced the full query, with all the filters into the database resulting in something running much quicker.
I think my question is - why would Cognos not send the filtered sub queries to the database when using local cache.
I'm also pondering "what's the point in Report Studio show SQL", given that it can be very different after Key Transformations. Why not just show the SQL of the Key Transformations...
------------------------------
Marc Reed
------------------------------