Cognos BI MDX Performance

[dan.kelleher]

Hi,

We're seeing some poor performance of MDX queries that are being run in TM1 via Cognos BI. These queries are run as part of a prompt page generation for a Report Studio report. We have noticed that i's not the MDX that's creating the view that's taking a long time, but that there is a large gap (30s) between some of the executed MDX expressions. Does anyone know why this gap exists, and how we can run the MDX concurrently?

Code: Select all

35404 DEBUG 14/01/2013 12:22:15 TM1.MdxViewCreate SELECT DESCENDANTS([caps_rp_m].[Total Amount], 2147483645, SELF_AND_BEFORE) PROPERTIES MEMBER_NAME, MEMBER_ORDINAL, CHILDREN_CARDINALITY, DESCRIPTION, PARENT_UNIQUE_NAME ON AXIS(0), {} ON AXIS(1) FROM [fsr_rp_agg]
35404 DEBUG 14/01/2013 12:22:15 TM1.MdxViewCreate View is created.               
35404 DEBUG 14/01/2013 12:22:15 TM1.MdxViewCreate SELECT DESCENDANTS([caps_rp_m].[Total Variance], 2147483645, SELF_AND_BEFORE) PROPERTIES MEMBER_NAME, MEMBER_ORDINAL, CHILDREN_CARDINALITY, DESCRIPTION, PARENT_UNIQUE_NAME ON AXIS(0), {} ON AXIS(1) FROM [fsr_rp_agg]
35404 DEBUG 14/01/2013 12:22:15 TM1.MdxViewCreate View is created.               
35404 DEBUG 14/01/2013 12:22:46 TM1.MdxViewCreate SELECT DESCENDANTS([caps_rp_m].[Total Amount], 2147483645, SELF_AND_BEFORE) PROPERTIES MEMBER_NAME, MEMBER_ORDINAL, CHILDREN_CARDINALITY, DESCRIPTION, PARENT_UNIQUE_NAME ON AXIS(0), {} ON AXIS(1) FROM [fsr_rp_agg]
35404 DEBUG 14/01/2013 12:22:46 TM1.MdxViewCreate View is created.  

[marco]

You can preload (nightly) the selection on the prompt page with promt caching:

http://www-01.ibm.com/support/docview.w ... wg21340352

[mce]

Hi Dan,

Hope you are well. Executing the Descendants function for a consolidation element in the measure dimension might be the reason for this performance issue. In response to the query, probably TM1 is sending too much information for Cognos BI to handle and evaluate and hence it is taking a lot of time for Cognos BI to decide and send the next MDX statement. In this case, I would try avoiding this sort of a hierarchy function in a measure dimension. Hope this helps.

Regards,

[sachin]

Hi,

We're seeing some poor performance of MDX queries that are being run in TM1 via Cognos BI. These queries are run as part of a prompt page generation for a Report Studio report. We have noticed that i's not the MDX that's creating the view that's taking a long time, but that there is a large gap (30s) between some of the executed MDX expressions. Does anyone know why this gap exists, and how we can run the MDX concurrently?

What is your version of BI and TM1? Below are couple of points that helped us. May be you will find it useful.

1. In case you have not seen this, please look at this article by IBM - http://www-01.ibm.com/support/docview.w ... wg21505253 ... following it, has tremendously helped us
2. Use DQM ... this is like a turbo boost for race cars. If you use DQM, reports run a LOT faster ... be advised, DQM has some stricter rules regarding the way reports need to be built. For some expressions that worked for CQM, they may not work with DQM. You will have to alter them a little