Query Performance with C Level Rules

[mce]

Hi,

I am trying to understand how TM1 query engine handles C level rules in relation to a query performance problem that I have with C level rules.

Lets say we have a simple currency translation formula such as the following one in CubeA:

Code: Select all

skipcheck;
['USD Amount'] = ['TC Amount'] \ DB('Rates', !Date, !Currency, 'USD Rate');
feeders;
['TC Amount'] => ['USD Amount'];

The idea for not using :N here is that when I query the top level numbers from this cube for USD Amount , it should do the aggregation first for TC Amount and then do the currency translation for the aggregate numbers to find numbers for USD Amount, rather than doing the currency translation for each an every leaf level data for USD Amount and then aggregate USD Amount to find the aggregate level USD Amount numbers.

Obviously first doing the aggregation and then doing the currency translation will require less number of calculations as currency translation happens only for few aggregate numbers rather than millions of leaf level records. Therefore the query performance for USD Amount on aggregate levels should be much better when we do not use :N in this rule. However when I tested this, unfortunately I could not see this performance improvement when I delete :N section from the rule. Therefore I am wondering if there is anything that I am missing here or if I am not aware of any known inefficiency of TM1's query engine for C level rules maybe in a way that it still requires to evaluate leaf level calculations for USD Amount even if it does not use them in C level results.

Any comments or clarifications by experts will be appreciated.

Regards,

[tomok]

However when I tested this, unfortunately I could not see this performance improvement when I delete :N section from the rule.

Exactly how did you test? Just remove the N: and resave the rule? This is not going to be a valid test due to the caching of calculated nodes that TM1 does. To be a valid test you need to recycle the service each time you change the rules so you are always starting from the same point, caching-wise.

[mce]

However when I tested this, unfortunately I could not see this performance improvement when I delete :N section from the rule.

Exactly how did you test? Just remove the N: and resave the rule? This is not going to be a valid test due to the caching of calculated nodes that TM1 does. To be a valid test you need to recycle the service each time you change the rules so you are always starting from the same point, caching-wise.

Hi Tomok,

The data in my cube is refreshed via a TI process every 2 minutes. Therefore the cube cache is wiped out every 2 minutes. While testing, I make sure the cube is refreshed between test queries.

Regards,

[PlanningDev]

1. When you say query performance do you mean within TM1 or query from Cognos BI?

Im thinking here that TM1's engine is almost always going to be faster at aggregating detail than calculating a rule. So in this case your expected performance increase may actually decrease or your model may not be large enough to allow you to see a difference.

Basically I think it doesn't matter what you do in this situation as using N: level is going to probably be your fastest option.

Heres what Im thinking.

If you have the rule calculate both N: and C: levels you are still going to be dependent on the N: levels anyway since you need the base data in order to get an aggregate to be calculated anyway. Since TM1 stores aggregates and to my knowledge will only have to recalculate on data change you get the stored aggregate either way. If data changes at the base level then having the calc on both N: and C: doesn't buy you anything as again you still need base data to get the base aggregate for the calculated aggregate. standard hierarchy aggregations are faster than rule calculations so you come back to having the best performance by only calculating the N: level and having aggregates be standard aggregates instead of calculated.

Basically rule based aggregate calculations rely on the N: level data anyway so you are better off haning a summation rather than a rule calculation.

Someone let me know if this theory is wrong?

[lotsaram]

Actually exchange rates are a textbook example of where you can use C level rules to very good effect and eliminate the requirement for feeders AND achieve good performance. This is possible because the exchange rate varies with time but critically not by measure or account (excepting different standard treatments for income statement vs. balance sheet accounts). Therefore as the same exchange rate applies at each unit of time as you consolidate up the account tree you achieve the same mathematical result by applying the conversion at the top of the tree rather than at the base level and then consolidating up (as in a typical N rule). Rather than doing the multiplication or division at the leaf level and then consolidating up the tree it makes sense to use TM1's fast consolidation algorithm for the input data and then perform the multiplicative conversion only once as this should give better performance, and this is in fact the case.

However it is not just a matter of amending the rule; you need to also modify the dimension structure of the currency dimension. Typically this is done by having one leaf element "Local" and each currency (USD, GBP, EUR, etc...) is a parent of local. You then don't need to really amend the rule other then by adding the C: qualifier and removing the feeder.

... but that's not the end of the story as that is an oversimplification. As exchange rate varies over time (typically month to month in a financial cube) to achieve the correct result for a quarter, half, YTD or year you need to use the ConsolidateChildren function on the month dimension. Depending on cube structure and data density this can cause not so great performance when reporting on consolidated time periods. However the point remains that if you need to report in multiple currencies and/or the primary concern is to report on a single time period then this is a very elegant solution with very good performance.

IBM do have a worked example, if you know who to talk to you should be able to get a copy.

As always whenever testing different rules and especially where changes to feeders are involved you need to unload the cube or restart the server to ensure that you have a valid test.