I'm trying to solve a runaway query problem with Cognos Analytics 11.0.9 against an Oracle 12c star schema. The runaway query came from a dashboard reporting on a Framework Manager package, and involves multi-fact calculations. As expected, Cognos generates SQL that uses common table expressions to query the invididual fact tables, and then uses a "stitch query" to merge those results based on common conformed dimensions.
The specific runaway query had 4 fact tables and 3 common dimensions. Our Oracle DBA says that the OR conditions in the WHERE clause are causing Oracle to try to join the fact tables before the common table expressions are evaluated, which is very expensive.
The OR conditions appear to be designed to handle NULL values in the common dimension. In my data model, there are no NULL values present. Is there a way to get Cognos to generate multi-fact SQL that assumes no NULLs are present?
More detail... I've edited this a little for readability, but these queries look something like:
WITH "FS1" AS (
SELECT dim.fiscal_year as fiscal_year, SUM(fact1.cost) as cost
FROM fact1
INNER JOIN dim on fact1.dim_key = dim.dim_key
GROUP BY fiscal_year
)
WITH "FS2" AS (
SELECT dim.fiscal_year as fiscal_year, SUM(fact2.cost) as cost
FROM fact2
INNER JOIN dim on fact2.dim_key = dim.dim_key
GROUP BY fiscal_year
)
WITH "FS3" AS (
SELECT dim.fiscal_year as fiscal_year, SUM(fact3.cost) as cost
FROM fact3
INNER JOIN dim on fact3.dim_key = dim.dim_key
GROUP BY fiscal_year
)
SELECT COALESCE("FS1".fiscal_year, "FS2".fiscal_year, "FS3".fiscal_year) AS fiscal_year,
("FS1".cost + "FS2".cost) +"FS3".cost AS cost
FROM "FS1" FULL OUTER JOIN "FS2" ON
("FS1".fiscal_year = "FS2".fiscal_year OR ("FS1".cost IS NULL AND"FS2".cost IS NULL))
FULL OUTER JOIN "FS3" ON
COALESCE( "FS2".cost, "FS1".cost) = "FS3".cost OR (COALESCE("FS2".cost, "FS1".cost) IS NULL AND "FS3".cost IS NULL)
FETCH FIRST 3001 ROWS ONLY
There is a governor setting in Framework Manager to control the stitch query join SQL, when using dynamic query mode. The setting is (DQM) Multi Fact Join Operator, and the default value of Is Not Distinct From generates SQL that assumes NULLs may be present. The default generates SQL like the above when there are more than two fact tables.
You can change the governor to Equal operator to only use '=' in the joins. There is also an Automatic setting that will toggle between the two modes, based on whether the model says that the common dimension allows NULLs or not.
That seems to solve the problem, as long as you know there are no NULLs in the common dimension. Has anybody seen a better solution to this problem?