Cognos Analytics

 View Only

  • 1.  master detail optimization with data modules?

    Posted Fri March 24, 2023 09:34 AM

    Referring to the governor setting for packages with Framework Manager to optimize master detail query performance in large data sets,

    is there a similar optimization for queries possible when authoring reports on data modules?

    kind regards

    Kai



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


  • 2.  RE: master detail optimization with data modules?

    IBM Champion
    Posted Fri March 24, 2023 10:13 AM

    Hi Kai,

    I believe you can optimize the query when you optimize joins (in the data module) by applying filters to them.

    You will find useful information in the documentation in page (PDF page) 29:

    https://www.ibm.com/docs/en/SSEP7J_11.2.0/pdf/ca_mdlg.pdf

    Best regards,



    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    IBM Champion
    ------------------------------

    Original Message


  • 3.  RE: master detail optimization with data modules?

    Posted Fri March 24, 2023 11:21 AM

    Thx Patrick,
    what you referring to is filter join optimization. But what I meant was master detail optimization. In this case you have at least two nested data container in a report which are related by a master-detail relationship. What usually happens in this case is that Cognos is generating one query to get the list for the master grouping and then another query to get the nested details for each row of the master list. E.g. if you have 10 rows in the master list by the first query, you will get another 10 queries for the data of each detail data container in each section.
    The master detail optimization governor setting in Framework Manager is managing caching and query reuse capabilities in Cognos to manage everything in just two queries.
    I am looking for a way to optimize the amount of queries going against the database with reports having master detail data containers using data modules. Otherwise this is another feature in Framework Manager which is not covered by data modules.

    Kind regards
    Kai 



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

    Original Message


  • 4.  RE: master detail optimization with data modules?

    Posted Mon March 27, 2023 10:04 AM

    Hi Kai,

    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'})#
    Hope this helps.



    ------------------------------
    Kind regards,
    Henk Cazemier
    ------------------------------

    Original Message


  • 5.  RE: master detail optimization with data modules?

    Posted Mon March 27, 2023 01:47 PM

    Hello Henk,

    it's really nice to hear from you. This case remembers me at the time when we worked in Nürnberg - tracing the jdbc with Cognos ;-)

    Your suggested workaround works great. The parameter gives back a 1 and a jdbc trace confirmed that the master detail optimization works with data from a data module.

    Best regards,

    Kai



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

    Original Message