Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  CAST( #$current_timestamp# , date ) much better than current_date

    Posted 05/27/24 06:54 AM

    Query is much faster if instead of filter:
    [Date] = _add_days( current_date, -1 )

    we use:
    [Date] = _add_days( CAST( #$current_timestamp# , date ), -1 )

    When we use current_date, SQL code is something like this, there is no WHERE clause in it.
    SELECT * FROM Table
    If I try this version of a report, it is slow even if I query yesterdays data. Yesterday, it was Sunday, so there is no data for that day, but report is still slow.

    When we use CAST( #$current_timestamp# , date ), SQL code has WHERE clause, something like this:
    SELECT * FROM Table WHERE [Date] = '2024-05-26'

    The speed is the same if both queries are cached, but if they are not, then macro version is much faster.

    I don't know why is this happening.



    ------------------------------
    Milan Milovanovic
    ------------------------------


  • 2.  RE: CAST( #$current_timestamp# , date ) much better than current_date
    Best Answer

    Posted 05/28/24 01:51 AM

    Hi Milan,

    This is called lokal processing: Cognos pulls raw data from the database and transforms locally on the Cognos server. There are various factors that may have an influence: 

    • database type and version
    • Cognos version
    • Dynamic / Compatible Query Mode (if Framework Manager)
    • Framework Manager/Data Module modelling/complexiity
    • Report modelling/complexity

    If you get this even with simple data models and reports I recommend to contact the IBM support. We got similar issues fixed in a Cognos fix pack.

    Best regards

    Philipp



    ------------------------------
    Philipp Hornung
    Business Intelligence Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------



  • 3.  RE: CAST( #$current_timestamp# , date ) much better than current_date

    Posted 05/28/24 03:54 AM

    Now I understand. Cognos would read data for all the dates ( for 3 years in my case, so a 1000 dates ). It would then pull all 1000 rows in Cognos, and then it would filter only yesterdays date to show in the report.
    This also explains why Cognos sometimes creates two separate SQL queries. Something like:
    SELECT * FROM Table1
    SELECT * FROM Table2
    Cognos would fetch both tables, and then Cognos would create a join between these tables internally. Because Cognos is much slower than database, this means that report would be generated much slower.

    Yesterday, I have changed current_date to CAST( #$current_timestamp# , date ) in one report. Execution time for this one report went down from 17 minutes to 1 minute. This is a huge improvement. This is definitely something to be aware of.

    I don't have a clue why current_date made Cognos to process data locally. Report is simple, one crosstab and a few simple filters. Database is MS SQL, Cognos is 11.2.2. Model is simple, it is made in Framework manager and it is CQM. 



    ------------------------------
    Milan Milovanovic
    ------------------------------

    Original Message


Related Content