Cognos Analytics

 View Only
  • 1.  Concatenated Fields - Detail Filter Slows Down Report

    Posted Mon November 23, 2020 02:38 PM
    Edited by System Fri January 20, 2023 04:41 PM
    Hello,
    I have a report set up in Cognos Analytics 11.xx. I created a data item that concatenates two other data items, such as below:

    [Qls Rpr Part - Long Name] + [Qls Rpr Conc - Long Name]                 (Named: Part/Concern)

    The concatenated field works as needed. However, when trying to use the new field as detail filter, the report slows down to an unusable rate, i.e ~10+minutes.... The detail filter is set using a text box prompt, with the expression written as below:

    [Part/Concern] in ?Prompt_Values?

    Any idea's why this would be so slow? If I remove one of the fields and set the filter accordingly, it only takes ~1 second to run. Image of resulting report shown below.

    Thanks!
    ------------------------------
    Dale Simpson
    ------------------------------
    ​​
    #CognosAnalyticswithWatson


  • 2.  RE: Concatenated Fields - Detail Filter Slows Down Report
    Best Answer

    Posted Tue November 24, 2020 08:17 AM
    Hello Dale,

    I saw your previous post with the title "Invalid coercion from 'member' to 'string'" and concluded that you are using a dimensional package. A so called DMR package.
    And if you are new to Cognos this is far more complex than a relational package. So my advice would be to use a relational package if available and you can use the statements in your post.
    Or create a dataset from the package with all the data you need for your report and add this to a datamodule and use the datamodule as the source for your report.

    But if that all is not possible, then keep in mind that you should only use dimensional functions in a dimensional package.
    The second is that you should not use detail filters in a dimensional model. They are performance killers as you have experienced.

    What you could do is:
    Remove the detail filter and create several query items:
    [Part/Concern] with the definition [Qls Rpr Part - Long Name] + [Qls Rpr Conc - Long Name]
    [Concern] with the definition filter ( [Qls Rpr Conc]; [Qls Rpr Conc - Long Name] in ( ?ConcLongname? ) )
    [Part] with the definition filter ( [Qls Rpr Part]; [Qls Rpr Part - Long Name] in ( ?PartLongname? ) )

    Then add these in the list and run the report. it should be faster.

    ------------------------------
    Thomas van der Meer
    ------------------------------



  • 3.  RE: Concatenated Fields - Detail Filter Slows Down Report

    Posted Tue November 24, 2020 01:05 PM
    The most likely explanation for the performance degradation is that your database has no index reflecting your concatenated "column," and therefore has to perform a complete scan to honor your filter. You could work with your DBA to create a calculated index to match your concatenated column, but this may not be an option in your environment.

    Likely the simplest approach to improving performance is to limit the use of your Part/Concern column to display, only, and break your filter into two parts: 

    [Qls Rpr Part - Long Name] = ?Part_prompt?
    AND
    [Qls Rpr Conc - Long Name] = ?Concern_prompt?

    Note: this will likely only address your performance issue if both of the ~Long Name columns already have existing indexes. If you don't have direct access to the underlying database (or the DBA) to check, you'll just have to test. (But ultimately, all query performance tuning is empirical, anyway.)

    Hope that helps. Good luck.

    ------------------------------
    Michael Walter
    Business Analyst
    TX HHSC
    Austin
    ------------------------------



  • 4.  RE: Concatenated Fields - Detail Filter Slows Down Report

    Posted Wed November 25, 2020 12:35 AM

    In general writing predicates (filters) along the lines of  the lines <expression> <comparison-operator> ... are more expensive for a system to resolve.

    Reduces opportunity for system to locate/skip data, system has to evaluate the expression on a row-by-row basis etc.

    Faster expressions would be those comparing the values to each column i.e. Qls Rpr Part in ('DOOR' , .... ) and  Qls Rpr Conc in  ('Dent', ...) etc.

    See also. Chap 6. http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------



  • 5.  RE: Concatenated Fields - Detail Filter Slows Down Report

    Posted Thu November 26, 2020 11:19 AM
    ​Thank you all for the great feedback. I resolved the issue using parts of each of your suggestions

    Issue resolved by:
    • Eliminating the use of concatenated field. This was originally needed due to the User's not having the separated fields to start with. The User's would run a prior report (from a separate software) to get the starting info used in this report. We revisited and modified the prior report so the user would receive the separated fields instead of the concatenated one. We ended up changing the data source for the first report from a separate software/application to the Cognos driven website as well.  After that we were able to use the separated fields to filter this report much faster.
    • In order for the first report to work, we worked with our IT team to add indexes to these fields in the database.

    Thanks again!

    ------------------------------
    Dale Simpson
    ------------------------------