Page 1 of 1

Improve TI Processing Time

Posted: Mon Mar 12, 2018 5:49 pm
by kenship
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

Re: Improve TI Processing Time

Posted: Mon Mar 12, 2018 6:42 pm
by jim wood
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.

Re: Improve TI Processing Time

Posted: Mon Mar 12, 2018 10:13 pm
by paulsimon
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

Re: Improve TI Processing Time

Posted: Tue Mar 13, 2018 2:15 am
by PeteB
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.

Re: Improve TI Processing Time

Posted: Tue Mar 13, 2018 6:09 am
by gtonkin
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.

Re: Improve TI Processing Time

Posted: Tue Mar 13, 2018 5:40 pm
by kenship
Thank you all.

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

Kenneth

Re: Improve TI Processing Time

Posted: Tue Mar 13, 2018 11:14 pm
by PeteB
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

Re: Improve TI Processing Time

Posted: Wed Mar 14, 2018 2:05 pm
by PavoGa
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

Re: Improve TI Processing Time

Posted: Thu Mar 15, 2018 2:27 pm
by kenship
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