Cube Performance Tuning

Post Reply
tm1_user
Posts: 18
Joined: Thu Nov 13, 2014 10:03 am
OLAP Product: TM1
Version: 10.2.0
Excel Version: 2010

Cube Performance Tuning

Post by tm1_user »

Hi All,

We have a problem in our TM1 system...a Reporting Cube, whose response time is too long i.e close to 4 minutes.

This "Report Cube" has 9 dimensions. This cube though sounds only for reporting purpose, it is used for forecasting also.
There are many business rules along with some Translation of currency rules to convert "Local Currency" to "USD" which fetch from Foreign Exchange cube (XE Cube) and long response time is due to these currency translation rules.
This is because, when i comment these rules & feeders, the cube response time is 90 sec.
The "Currency" dimension has two elements "USD" and "Local Currency".

Rules are:

Code: Select all

SKIPCHECK;
['USD']=N:IF (ATTRS( 'Versions', !Versions, 'Translation Currency' ) @= 'USD',
['Local Currency']
\ DB('XE Rate',!Versions,'Avg Rate',
IF( ATTRS('Divisions', !Divisions, 'Functional Currency')@='', 'USD', ATTRS('Divisions', !Divisions, 'Functional Currency') ),
!Period-Month),
CONTINUE
);

['USD']=N:IF (ATTRS( 'Versions', !Versions, 'Translation Currency' ) @= 'USD',
DB('Report Cube', !Versions, 'Local Currency', !Lines of Business,
!Period-Month | ' YTD',
!Business Units,!Depts,!Divisions,!Accounts,!Measures)
\ DB('XE Rate',!Versions,'Ending Rate',
IF( ATTRS('Divisions', !Divisions,  'Functional Currency')@='', 'USD', ATTRS('Divisions', !Divisions,  'Functional Currency') ),
!Period-Month)

- DB('Report Cube', !Versions, 'USD', !Lines of Business,
IF (SUBST(!Period-Month,1,3)@='Jan', 'Opening Balance ' | ATTRS( 'Period-Month', !Period-Month, 'Year'), ATTRS( 'Period-Month', !Period-Month, 'Prior') | ' YTD'),
!Business Units,!Depts,!Divisions,!Accounts,!Measures),
CONTINUE
);


Feeders;

['Local Currency'] =>DB(IF(ATTRS('Versions',!Versions,'Translation Currency')@='USD','Report Cube',''),
!Versions,'USD',!Lines of Business,!Period-Month,!Business Units,!Depts,!Divisions,!Accounts,!Measures);

['Local Currency']=>DB( IF(ATTRS( 'Versions', !Versions, 'Translation Currency' ) @<> 'USD', '', 'Report Cube'),
!Versions, 'USD', !Lines of Business,'Dec-' | ATTRS( 'Period-Month', !Period-Month, 'Year') | ' YTD',
!Business Units,!Depts,!Divisions,!Accounts,!Measures);
There are no Feeders on 'XE Rate' for these rules which is as expected.
Any best methods people have to share regarding rule (and feeder) tuning? How should the feeders be written to reduce overfeeding?


Please advise.
iansdigby
Community Contributor
Posts: 109
Joined: Thu Feb 26, 2009 8:44 am
OLAP Product: TM1
Version: 9 + 10 + Plan An
Excel Version: All
Location: Isle of Wight, UK

Re: Cube Performance Tuning

Post by iansdigby »

I would examine older data in the cube to see if you can hard-copy it; thus preventing the rule from calculating loads of historic results for what is probably no useful business purpose.
"the earth is but one country, and mankind its citizens" - Baha'u'llah
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cube Performance Tuning

Post by tomok »

Translating currencies in a reporting cube is not good practice. The definition of a "reporting" cube, at least in my eyes, is a cube designed to organize data in such a way as to maximize user effectiveness. You should not by generating or changing numbers in it. I assume you are moving data here with a TI, correct? Translate the currencies on the way in. Everyone will be way happier.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: Cube Performance Tuning

Post by mattgoff »

Random thoughts:
  • Eliminate the "IF" where you test to see if the Functional Currency is blank. Replace with TI or business process to always have a value in that attribute.
  • Looks like you're doing a movements calculation via rules for all periods. This going to be super slow. At a minimum, make actuals static. Better, do it via a consolidation.
  • Unless I'm missing something, the TRUE section of your second rule will never be reached since your first rule TEST is the same as the second. My guess is the first is supposed to an f/x translation for IS, the second for BS (but they also reference different cubes so <shrug>). Why not use the Accounts dim for your IF test?
  • Not explicitly identifying your dimensions is going to burn you one day when you least expect it (generally when you add an element to some other dimension which exists in another dimension in the same cube. e.g. ['USD'] is better written as ['Measures':'USD'].
  • Looks like you're applying all of this logic for all of your Versions. You should make historical versions static.
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
tm1_user
Posts: 18
Joined: Thu Nov 13, 2014 10:03 am
OLAP Product: TM1
Version: 10.2.0
Excel Version: 2010

Re: Cube Performance Tuning

Post by tm1_user »

Thanks all for your responses.

To answer your questions:
I assume you are moving data here with a TI, correct? Translate the currencies on the way in.
Yes, the data comes through TI process.

I am exploring approach to make historic versions data static so that it will not calculate each time for all periods.
Is there a best method for doing this? Also, can you direct me to a thread which was discussed before on making past data static.
tm1_user
Posts: 18
Joined: Thu Nov 13, 2014 10:03 am
OLAP Product: TM1
Version: 10.2.0
Excel Version: 2010

Re: Cube Performance Tuning

Post by tm1_user »

Hi All,

Please provide the steps to be followed to hard-copy/freeze the historic data.
Do we need to make changes to the Business Rules which are calculating for the historic versions made static.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Cube Performance Tuning

Post by declanr »

tm1_user wrote:Hi All,

Please provide the steps to be followed to hard-copy/freeze the historic data.
Do we need to make changes to the Business Rules which are calculating for the historic versions made static.
This topic comes up quite a lot; searching the forum or google for archiving history in TM1 will provide many results. The general concept however is as below (with everyone having their own way of doing it dependent on personal preference and the model/s in question.)

Put an attribute or control cube in place that references whether a version and/or period should be hardcoded.
Have an if statement at the start of the rule sheet that says if this version and/or period should be hardcoded then STET.

Have a TI/set of TIs to:
1/ copy all data for a version and/or period to a CSV or SQL table etc.
2/ Put the flag in place to STET the version and/or period
3/ Load the data from the CSV or SQL table back into the STET'd cells.
Declan Rodger
tm1_user
Posts: 18
Joined: Thu Nov 13, 2014 10:03 am
OLAP Product: TM1
Version: 10.2.0
Excel Version: 2010

Re: Cube Performance Tuning

Post by tm1_user »

Declan,

Thanks a lot. It is really helpful!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Cube Performance Tuning

Post by Wim Gielis »

An additional question from my side. Probably geared to the TM1 gurus :-)

If by doing this we add the STET rule for scenarios/periods that should be hard-coded and we copy in the static data with TI:
should we also reduce the feeders likewise ? What happens in TM1 if we have feeders for cells that are hard values anyhow?
Does TM1 still store the feeders ? Is there a slowdown performance-wise ?

For example, let's STET a number of years:

[{'2010','2011','2012','2013'}] = Stet;

But we have generic feeders not including the year. Does it matter and is there a cost of not adding the years to limit the feeders ?

Thanks a lot !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Cube Performance Tuning

Post by lotsaram »

Wim Gielis wrote:...should we also reduce the feeders likewise ? What happens in TM1 if we have feeders for cells that are hard values anyhow?
Does TM1 still store the feeders ? Is there a slowdown performance-wise ?

For example, let's STET a number of years:

[{'2010','2011','2012','2013'}] = Stet;

But we have generic feeders not including the year. Does it matter and is there a cost of not adding the years to limit the feeders ?
Yes most definitely the feeders should be written to exclude feeding to leaf cells that are not rule calculated. A feeder can be fired from any simple numeric cell, TM1 doesn't care if the leaf cell to feed from is data enterable vs. rule defined. SO if a feeder is written such that non-rule cells are the target then the feeder will still be evaluated which will waste processing time on rule save.

But I don't know the answer to your question of whether a feeder marker for a cell is actually stored for such cells. If it was then this would be a clear wastage of memory also. Wouldn't be too hard to test just feed a measure in a cube that contains data only and then see in performance monitor if there is memory used for feeders in the cube.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply