Hello
Here is my problem :
We are changing our DWH, we are migrating from Netezza to Snowflake.
All our reports are designed like this :
- Query - Date filter (for example get CURRENT DAY in the DWH context) :
(https://i.ibb.co/gjwghxr/1.png)
https://ibb.co/1vK2jk0
- Query - Facts (a measure with a filter on the date with the result of the query "Query - Date filter") :
(https://i.ibb.co/N6bFhsb/2.png)
https://ibb.co/C5DwdzD
A) With Netezza, Cognos generates 1 query with a sub-query correctly joined with the filter :
select "D_Date"."DayId" "Date1" , sum("F_Facts"."QTY") "Quantities"
from
"ETL_COGNOS"."D_Date" "D_Date",
"ETL_COGNOS"."F_Facts" "F_Facts",
(
select distinct "D_Date"."DayId" "Date1"
from "ETL_COGNOS"."D_Date" "D_Date"
where "D_Date"."CURRENTDAY" = 0 and "D_Date"."CURRENT_R_YEAR" between -4 and 1
) "Query1"
where
"D_Date"."CURRENT_R_YEAR" between -4 and 1
and "D_Date"."DayId" = "Query1"."Date1"
and "F_Facts"."Transaction_Date" = "D_Date"."Day_SID"
group by "D_Date"."DayId"
B) With Snowflake, Cognos generates 2 queries (stitch queries?) not joined...
select "DWH_TEST_COGNOS"."PUBLIC"."D_DATE"."DAYID" "Date1" , "DWH_TEST_COGNOS"."PUBLIC"."D_DATE"."CURRENTDAY" "Current_Day"
from
"DWH_TEST_COGNOS"."PUBLIC"."D_DATE"
where
"CURRENT_R_YEAR" between -4 and 1 and "DWH_TEST_COGNOS"."PUBLIC"."D_DATE"."CURRENTDAY" = 0
select "D_Date"."DAYID", "F_FACTS"."QTY"
from
"DWH_TEST_COGNOS"."PUBLIC"."D_DATE" "D_Date",
"DWH_TEST_COGNOS"."PUBLIC"."F_FACTS" "F_Facts"
where
"F_Facts"."TRANSACTION_DATE" = "D_Date"."DAY_SID"
and "D_Date"."CURRENT_R_YEAR" >= -4 and "D_Date"."CURRENT_R_YEAR" <= 1
order by "D_Date"."DAYID" asc
What is the problem?
How to generate a subquery like with Netezza?
Thank you!
Fabien