Improve TI Processing Time

Post Reply
kenship
Posts: 105
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Improve TI Processing Time

Post by kenship » Mon Mar 12, 2018 5:49 pm

Hi, I'm trying to find some idea on how to improve TI processing time.

I have a process that transfer salary budget data cube into operating budget cube.

Summarized salary data is mapped to operating budget cube based on gl accounts.

The process consists of:

Prolog - setting up variables, create source view and subsets, target view and subsets.
Data - 6 sets of CellPutN command. Each set includes 2 IFs and 1 CellPutN. First IF checks a few dimensions for the correct dimension element, second IF makes sure cell is updateable.

Performance wise, if there has not been any change in salary budget data, the process takes about 15 seconds, if there is, then about 90 seconds. If I remove all the commands in "Data" (all IF and CellPutN) to only create source and target views only, it's fast, only a few seconds even salary budget data is changed.

However, once "Data" tab commands are added back, no matter how many sets of command I'm running (I tried running only one set vs all 6), the processing time is basically the same (90s if budget data is changed, 16s if not).

I would like to see which direction I should take to diagnose the TI commands to shorten the time required to run the process.

Thank you.

Kenneth

User avatar
jim wood
Site Admin
Posts: 3563
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Improve TI Processing Time

Post by jim wood » Mon Mar 12, 2018 6:42 pm

Based on what you've said the main culprit is either under performing feeders or just a plain old overly complicated rule on either side. But saying that there are other variables like users entering data, etc that could explain the reasons for this. Without doing an investigation it'd be difficult to say 100% for sure what could be done,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
paulsimon
MVP
Posts: 578
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Improve TI Processing Time

Post by paulsimon » Mon Mar 12, 2018 10:13 pm

Hi

Does your source view have all three skips?

Particularly if it is not skipping Consol values then performance may be slow. It is often better to read from base level data and accumulate using CellIncrementN

My guess would be that the Source View will not be built if there is nothing on the Data Tab, since logically the process does not have to process any data. (That is to say that the view may be created, but TM1 will not evaluate the view to create the source data)

Regards

Paul Simon

PeteB
Posts: 21
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.4, PAW 2.0.32
Excel Version: 2010

Re: Improve TI Processing Time

Post by PeteB » Tue Mar 13, 2018 2:15 am

Hi Everyone reading this thread,

It is very likely we each have had a similar problem, which can be fixed by IBM in a future release. I am told by IBM this should be a trivial piece of work to deliver the copy function from a slice of rules cells to value cells and the performance should be significantly faster for TM1 to internalise the copy rather than the current approach of exporting the rule values and importing into the snapshot version including across cubes.

Please vote for an RFE for IBM to provide the function to copy rule based cells to values.
https://www.ibm.com/developerworks/rfe/ ... _ID=116669 - TM1 Data Snapshot functionality - Copy rule to values in a TM1 cube

Typically to solve the performance issue we have built our copy using the Bedrock and Hustle code, which has significantly improved our performance of the copy. Counter intuitively, we have found exporting the data from the cube and re-importing is significantly faster than directly between the cubes especially where there is a rule between the cubes.

User avatar
gtonkin
MVP
Posts: 593
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Improve TI Processing Time

Post by gtonkin » Tue Mar 13, 2018 6:09 am

PeteB wrote:
Tue Mar 13, 2018 2:15 am
Please vote for an RFE for IBM to provide the function to copy rule based cells to values.
https://www.ibm.com/developerworks/rfe/ ... _ID=116669 - TM1 Data Snapshot functionality - Copy rule to values in a TM1 cube
Noted that the comments on this RFE say that on 1 March 2018, IBM declined this request as functionality is available through TI.

kenship
Posts: 105
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: Improve TI Processing Time

Post by kenship » Tue Mar 13, 2018 5:40 pm

Thank you all.

I'll try to experiment with exporting data then importing it.

Kenneth

PeteB
Posts: 21
Joined: Sun Sep 23, 2012 2:30 am
OLAP Product: Planning Analytics Local, PAW
Version: PAL 2.0.4, PAW 2.0.32
Excel Version: 2010

Re: Improve TI Processing Time

Post by PeteB » Tue Mar 13, 2018 11:14 pm

gtonkin correctly wrote:
Noted that the comments on this RFE say that on 1 March 2018, IBM declined this request as functionality is available through TI.
However, given this is easy for IBM to do, and will eliminate the performance issues with copy values, even after making the rules, feeders as efficient as possible and then exporting and reimporting using Hustle. On of our processes still takes half an hour, a significant improvement on the hour and a half it use to take, but the benefits of TM1 is speed

Please continue to vote until IBM at least rejects the RFE (If you can see value in the proposal). When IBM rejects the proposal I will ask for the RFE to be reconsidered. Currently the RFE has 32 votes placing it fourth on the Planning Analytics (TM1) list, however it is the only RFE specifically for the TM1 component of the software.

https://www.ibm.com/developerworks/rfe/ ... _ID=116669 - TM1 Data Snapshot functionality - Copy rule to values in a TM1 cube

User avatar
PavoGa
Community Contributor
Posts: 193
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Improve TI Processing Time

Post by PavoGa » Wed Mar 14, 2018 2:05 pm

Are you writing values to cells that are used as sources (on the LHS) for feeders? If so, it can be slow because it is feeding. Another thing to check is if you are using dynamic MDX subsets in your data source. Although problems with that were supposed to go away by 10.2, I noticed occasions where that would still impact performance. If some of the subsets are dynamic, may want to consider converting them to static as a practice to avoid any problem.

Ty
Ty
Cleveland, TN

kenship
Posts: 105
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: Improve TI Processing Time

Post by kenship » Thu Mar 15, 2018 2:27 pm

MDX is used as data source, but cells being written is not used for rules nor feeders.

Also tried using external file, doesn't really help. I will stop worrying about this for now.

Thanks.

Kenneth
PavoGa wrote:
Wed Mar 14, 2018 2:05 pm
Are you writing values to cells that are used as sources (on the LHS) for feeders? If so, it can be slow because it is feeding. Another thing to check is if you are using dynamic MDX subsets in your data source. Although problems with that were supposed to go away by 10.2, I noticed occasions where that would still impact performance. If some of the subsets are dynamic, may want to consider converting them to static as a practice to avoid any problem.

Ty

Post Reply