ConsolidateChildren Performance Issue

[mce]

Hi,

I tried using ConsolidateChildren function in a cube rule and observed the following performance issue:

- I use this function to calculate consolidation level values for a specific dimension, and if the consolidation element in this dimension has many children but only one of them has weighting of 1 and all other childrens of the consolidation element has weighting of 0, then query for the consolidation level cells in this dimension takes too much more time than running the same query against the child element from this dimension that has the weighting of one. Both queries are returning the same values, but it looks the query for consolidation level cell causes all children to be also calculated even if their weighting is 0 for the consolidation level query.

For example, if I used ConsolidateChildren in Time dimension that has YearEnd as parent of Q1,Q2,Q3, and Q4, but only Q4 has weighting of 1, and Q1,Q2,Q3 had weighting of zero. In this case reports for YearEnd values take 4 times more time to return than the reports for Q4, although they display the same output.

Did anybody else observe the same issue? or do you observe it differently?
Due to this performance issue, I am not able to use ConsolidateChildren in my cube although its logic suits perfectly as I do not need to use feeders with this function.

Any comments would be appreciated.

Regards,

[David Usherwood]

How about writing a rule like

Code: Select all

['YearEnd'] = ['Q4'];

?
I do recall that ConsolidateChildren has been horribly inefficient in some releases, especially the early ones (8.2.10 comes to mind). I use it myself when I want to keep feeders down, and I did test with and without and didn't find material performance hits, but I'm using only using it lightly - the approach being

Code: Select all

[Value] = if(attrn('Line',!Line,'ConsolidateChildren') = 1,
  consolidatechildren('Line'),
#else
  continue)
  ;

and there aren't many flagged lines.

[paulsimon]

We have had similar issues in Cognos BI on top of TM1. In this case we had an All Dates consolidation but in practice people only ever reported for individual dates. Changing the weights from 1 to 0 on all base level date element links up to All Dates made the reports run faster. This seemed to have an effect, even though All Dates was never referenced in any report. That seems to be pretty much the opposite of your issue, and may not be related unless you are using Cognos BI as a front end.

I would review your rules and see if there is any way that you can avoid using ConsolidateChildren as it is still very inefficient even in 9.5.1. TM1 is designed to consolidate from the base level up. Forcing it to consolidate from an intermediate level causes a number of issues.

I am not clear as to why the consolidation you have already isn't giving you what you need without the use of ConsolidateChildren?

Having said that, we do use ConsolidateChildren in part of our design, but that is because of a very unusual requirement to convert to over 300 currencies.

Regards

Paul Simon

[mce]

Thanks for the replies.
I already changed my rule in this cube to minimize or avoid the use of ConsolidateChildren in cases mentioned above. Hence I now have acceptable performance in the cube as it mostly does ConsolidateChildren accross only one dimension and in less number of cells it does ConsolidateChildren accross 2 dimensions at the same time. So I mostly remediated the pain with the issue mentioned above. But it looks this is still a valid issue for potential uses.

I am not clear as to why the consolidation you have already isn't giving you what you need without the use of ConsolidateChildren?

Having said that, we do use ConsolidateChildren in part of our design, but that is because of a very unusual requirement to convert to over 300 currencies.

I have similar requirement to make currency translations and I am trying to avoid feeders (due to the issues mentioned in http://www.tm1forum.com/viewtopic.php?f=3&t=6110) as my cubes are already huge for data only in one currency.

[mce]

We have had similar issues in Cognos BI on top of TM1. In this case we had an All Dates consolidation but in practice people only ever reported for individual dates. Changing the weights from 1 to 0 on all base level date element links up to All Dates made the reports run faster. This seemed to have an effect, even though All Dates was never referenced in any report.

This is quite strange. I would recommend checking MDX qeuries passed to TM1 from BI for such reports that are claimed to be not using All Dates. Although report is not relevant to All Dates, the way it is authored in Cognos BI might have ended up queries sent to TM1 that includes All Dates. I have seen this happening many times. Therefore it is best to check for MDX expressions passed to TM1 (via TM1 server Logs), rather than relying on info from report authors.

[lotsaram]

I'm not sure about the merits of with versus without feeders when using ConsolidateChildren but I certainly would have thought that it stands to reason that the way this function works that if one consolidation contains 4x as many descendants then it would take 4c as long to calculate.

Still the relatively poor performance of ConsolidateChildren can be a price worth paying for using currency dimension rollups as faux feeders and not having to bother with feeders for multi-currency reporting.