Hello,
I have enabled relative date analysis, as explained
here. This works great, but I see that query performance is very poor. The data module sends a single SQL to the database with case statements for each period/measure instead of sending one SQL per period. Are there any settings for changing behavior from single SQL to multiple?
We are using Snowflake as database and the difference between sending multiple SQLs vs sending multiple CASE statements in single SQL is huge. In this example the first SQL, with two case statements takes around 6 seconds. Sending the query with different where clauses, takes around 250 ms each.
Example on how it is done
SELECT
"Store"
, sum(CASE WHEN PERIODKEY BETWEEN 20210201 AND 20210208 THEN REVENUE ELSE NULL END) revex
, sum(CASE WHEN PERIODKEY BETWEEN 20200201 AND 20200208 THEN REVENUE ELSE NULL END) revex_LY
from TABLEA GROUP BY 1;
While I would prefer it to be like this
SELECT
"Store"
, sum(REVENUE) revex
FROM
TABLEA
WHERE
PERIODKEY BETWEEN 20210201 AND 20210208
GROUP BY
1;
SELECT
"Store"
, sum(REVENUE) revex_LY
FROM
TABLEA
WHERE
PERIODKEY BETWEEN 20200201 AND 20200208
GROUP BY
1;
------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------
#CognosAnalyticswithWatson