Cognos Analytics

 View Only
  • 1.  Dashboard not using SQL Indexes - not utilizing WHERE clause

    Posted Fri June 07, 2024 08:11 AM
    Edited by brenda grossnickle Fri June 07, 2024 09:52 AM

    Creating a Dashboard in Cognos 11.2.4. The Dashboard has three parameterized cascading filters. The filters are currently setup as CASE statements that represent each of the combinations of the filter selections. Well ... in the generated SQL, the CASE statement is in the SELECT statement. We need the filter selections to be represented in the SQL WHERE clause not in the SELECT. So none of our SQL table indexes are being utilized and the dashboard performance in not good. I am new to Dashboards and trying to find some guidance on how to get my filter selections in the WHERE clause. Does anyone have any suggestions, links to good how to, or articles? Thanks.



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------



  • 2.  RE: Dashboard not using SQL Indexes - not utilizing WHERE clause

    IBM Champion
    Posted Mon June 10, 2024 02:52 AM

    It is a bit difficult to visualise what is going on from the description.  Can you clarify 'parameterized cascading filters' - do you mean in the dashboard filter bar?

    Do you have filters applied to tables that are all related together? 

    Within the dashboard visualisation do you see the filter icon, and does clicking on it show the filters you expect. For example:

    If you have filters applied then there are circumstances when DQM will not pass the filters through to the database and try and perform the filters locally. I believe this is to try and create a cache that can be reused by other queries. It can lead to terrible performance. If this is the case then try disabling the DQM cache either in the data module, or in the dashboard. Depending on the version: 



    ------------------------------
    Marc Reed
    ------------------------------



  • 3.  RE: Dashboard not using SQL Indexes - not utilizing WHERE clause

    Posted Fri June 14, 2024 12:29 PM

    Thanks for jumping in and offering to help. We got it sorted out. I was posting for someone else, and I have never created a dashboard, so guessing that my post did not provide enough detail.



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------