Cognos Analytics

 View Only
  • 1.  How to overcome Master-Detail Relationship

    Posted Thu March 30, 2023 09:16 AM

    Hi Everyone,

    I am facing performance issue in a report which has master detail relationship. we are displaying 2 pages in PDF which has master detail relationship in page set.

    Eg : For an entry if master query has 2 rows then master and detail has executing each twice .For more rows in master then performance is poor. Not generating the report inn PDF.

    Instead of master -detail  can we change the design in any way for better performance, Please suggest

    Thanks in advance.



    ------------------------------
    Pavithra N
    ------------------------------


  • 2.  RE: How to overcome Master-Detail Relationship

    IBM Champion
    Posted Thu March 30, 2023 11:33 AM

    Hi,

    do you use a data module or a FM package as source? If it's a package you can look for the MTD optimization in the project governors. This setting will only execute one query to the database (if applicable) and cache the resulst on the server instead of sending multiple queries to the db.

    Project -> Edit Governors...



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------



  • 3.  RE: How to overcome Master-Detail Relationship

    Posted Fri March 31, 2023 03:19 AM

    Hi,
    in addition to the FM package setting recommendation from Robert, which is mandatory to get this optimization
    also consider the criteria which might prevent that this optimization is working
    https://www.ibm.com/support/pages/what-criteria-known-prevent-dqm-master-detail-optimization-working

    Criteria known to prevent the DQM Master-Detail Optimization from working:

    • The package governor (DQM) Master-Detail Optimization is not set to Cache Relational Detail Query
    • The package is CQM
    • The master-detail relationship has one or more parameters in the linkage. This optimization only support linkages that are data item to data item.
    • The detail data items of the linkage must be included in the report object (hiding them is fine); they cannot merely be part of the Query Explorer query
    • The Allow usage of local cache governor in the Framework Manager model is not selected when the package is published



    In case you are working with data modules, you need to refer to this workaround for master-detail optimization

    https://community.ibm.com/community/user/businessanalytics/discussion/master-detail-optimization-with-data-modules#bm470166ed-c7f8-4ea2-b9ab-0187235b0576

    In an upcoming release, the governors will be exposed in via the Web-based modeling UI.
    Governor settings are picked up from the Data Module if specified (but there is not UI at this time, so not very helpful). Or when these are not specified in the Data Module, they are read from the first FM model/package that is in the source of the current Data Module.

    So you can create an FM model that only has governors defined, publish it and add it to the Data Module.
    If you go this route, then there might be some extra work to double check that things are working properly once Data module support for governors is implemented via the modeling UI.
    To test if the effective setting of the governor, if you add the following expression to a report/query it should come back with 0 if not set and 1 if it is set.
    #sq( $_governor{'masterDetailOptimization'})#


    Kind regards
    Kai



    ------------------------------
    Kai Zeller
    ------------------------------