Cognos Analytics

Expand all | Collapse all

Data Modules: relative date analysis - query behavior

  • 1.  Data Modules: relative date analysis - query behavior

    Posted 15 days ago
    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
    ------------------------------


  • 2.  RE: Data Modules: relative date analysis - query behavior

    Posted 15 days ago
    Hi,

    I hope IBM  make a change to datamodules, to make use of multi query expressions, I saw this behavior a while ago and made a video for one of the architects, explaining the issue. A possible solution like working with a cube, using the relative time filters / members as slices for the different measures would be nice :-)

    For now where ever it´s possible, use the filters together with the relative time members, on big data this helps.

    Br
    René

    ------------------------------
    René Kent Nielsen
    Brand manager
    CogniTech A/S
    Herning
    ------------------------------



  • 3.  RE: Data Modules: relative date analysis - query behavior

    Posted 15 days ago
    Edited by Sandeep Dhirad 15 days ago
    Takk René,
    Glad you have already been in contact with an architect - so option for multi vs single query feature will hopefully be implemented in the the near future.

    /Sandeep

    ------------------------------
    Sandeep Dhirad
    Elkjøp Nordic
    ------------------------------



  • 4.  RE: Data Modules: relative date analysis - query behavior

    Posted 14 days ago
    Hi Sandeep,
    Thank you for raising this issue. I'm replying mostly to stay in the loop on possible responses from IBM and/or people posting "one weird trick" that they discovered to alleviate some of the issues. I've more experience on Tableau and PowerBI and will loudly proclaim my n00b status in CA as a disclaimer.

    I implemented the relative time functionality per the documentation in order to more easily build rolling time reports based off of a particular date (business likes to see current period, trailing 3 periods, trailing 12 periods in many of their reports and I was tired of rebuilding that calculation for EVERY report). With the caveat, again, that I may have done this suboptimal, it does seem to massively balloon the sheer number of queries that get sent to the database. What's more, the same issue seems to happen when no relative time-involved fields are being used, I.e. even just a hard-coded 'August 2020' still seems to send far more queries to the DB than it does from a data module without the relative time stuff enabled.

    Interested to see how this develops.

    ------------------------------
    Tom Cook
    ------------------------------



  • 5.  RE: Data Modules: relative date analysis - query behavior

    Posted 2 days ago
    I have created an idea/RFE:
    https://ibm-data-and-ai.ideas.aha.io/ideas/CA-I-3057

    It says it is planned for future release, and if many people vote for it, maybe we will get this functionality sooner

    ------------------------------
    Sandeep Dhirad
    Elkjøp Nordic
    ------------------------------