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
------------------------------
Original Message:
Sent: Thu March 30, 2023 11:33 AM
From: Robert Dostal
Subject: How to overcome Master-Detail Relationship
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
Original Message:
Sent: Thu March 30, 2023 09:16 AM
From: Pavithra N
Subject: How to overcome Master-Detail Relationship
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
------------------------------