Performance issue with an TI Process

[guru1988fortm1]

Hi All,

I am using Cognos TM1 10.2.2 version with Cognos BI 10.2.2 for the integrated security

we have a TI Process called 'pCT_Expense_Allocation' , This process actually calls on 15 different other TI processes
(This process has an parameter named 'Country' so that individual country user can run only for their country)

These 15 different other processes deals with zero outs and data transactions around 9 different cubes
(The above said country is dimension present in all the above 9 different cubes)

The total process timing takes only 3 Minutes

But, we are facing the below two problems

Issue 1:
When two different country users run the 'pCT_expense_allocation' process at the same time, the second executed process is put on hold and it waits for first to complete and it takes 6 Minutes, they refer to the same cubes but the country parameter differs the combination of data that they have to deal with, please correct if am wrong on this

Issue 2:
As this process involves the zero out and huge transactions of data in 8 different cubes, Save data all takes a long time, I checked the tm1s Log files and found the size was almost like 3 GB
i disabled the Process Logs using cubesetLogchanges(cubename,0) command and executed the process, the tm1s Log file size became small but the save data is taking Long time as usual

My users wants to run this process country wise during peak hours and we have the data save all scheduled for every 4 hours

Has anyone have come across the same kind of issues before , please let me know your opinions on this

Thanks,
Regards
Guru Madhesh

[lotsaram]

Look up parallel interaction topics on this forum. There are already many threads describing how to make such jobs run in parallel.

[BrianL]

Issue 1 is a very complicated topic that's been covered elsewhere as lotsaram indicated.

Issue 2 is actually a common concern. It's perhaps counter-intuitive, but SaveDataAll will find all cubes that have any changed data and write the entire contents of that cube out to disk. This is generally the longest running part of the operation and the duration is a function of the number of cubes with changed data and the size of those cubes. Truncating the transaction logs is essentially a quick rename from tm1s.log to tm1sXXXXXX.log. Typically, you would disable cube logging to either improve the load time of bulk data operations and/or to limit the size of the tm1s.log file.

[guru1988fortm1]

Issue 1 is a very complicated topic that's been covered elsewhere as lotsaram indicated.

Issue 2 is actually a common concern. It's perhaps counter-intuitive, but SaveDataAll will find all cubes that have any changed data and write the entire contents of that cube out to disk. This is generally the longest running part of the operation and the duration is a function of the number of cubes with changed data and the size of those cubes. Truncating the transaction logs is essentially a quick rename from tm1s.log to tm1sXXXXXX.log. Typically, you would disable cube logging to either improve the load time of bulk data operations and/or to limit the size of the tm1s.log file.

Thanks Lotsaram and Brian!!, i will do more research on the parallel interaction to understand it better
Regarding issue2, I actually tested it by running only this process and there was no other activities in my server during that time, then i executed save data all which took almost 10 minutes for completion,From your comments, i understand that no matter we disable the cubelogs, it will still take the sametime for saving the transactions

And i am guessing it would take more time if my users execute it for more than 8-10 times (as every country user runs atleast once in a window of 4 hours)
May be disabling save data all would be a good idea

Thanks,
Regards
Guru Madhesh

[BrianL]

Regarding issue2, I actually tested it by running only this process and there was no other activities in my server during that time, then i executed save data all which took almost 10 minutes for completion,From your comments, i understand that no matter we disable the cubelogs, it will still take the sametime for saving the transactions

And i am guessing it would take more time if my users execute it for more than 8-10 times (as every country user runs atleast once in a window of 4 hours)

I'm not sure you understood me correctly. It doesn't matter if the TI gets run once or twenty times as long as it writes data to the same exact set of cubes. The granularity of a SaveDataAll isn't "write out just the cells I've changed", it's "write out just the cubes I've changed". This is an important distinction.

For example: Assume a single cube named "Cube A". After startup it has 10 million cells. If I change the value of all 10 million cells then call SaveDataAll, TM1 writes out all 10 million cells. This is intuitive and as expected. However, If I change only the value of a single cell then call SaveDataAll, TM1 will still write the entire 10 million cells to disk into the "Cube A.cub" file.

Since you describe your TI process as making data changes to 9 different cubes, I'm assuming that all 9 get data changes regardless of country. That would mean that your SaveDataAll should take the same 10 minutes if the TI has been run ten times or only once.

[guru1988fortm1]

Regarding issue2, I actually tested it by running only this process and there was no other activities in my server during that time, then i executed save data all which took almost 10 minutes for completion,From your comments, i understand that no matter we disable the cubelogs, it will still take the sametime for saving the transactions

And i am guessing it would take more time if my users execute it for more than 8-10 times (as every country user runs atleast once in a window of 4 hours)

I'm not sure you understood me correctly. It doesn't matter if the TI gets run once or twenty times as long as it writes data to the same exact set of cubes. The granularity of a SaveDataAll isn't "write out just the cells I've changed", it's "write out just the cubes I've changed". This is an important distinction.

For example: Assume a single cube named "Cube A". After startup it has 10 million cells. If I change the value of all 10 million cells then call SaveDataAll, TM1 writes out all 10 million cells. This is intuitive and as expected. However, If I change only the value of a single cell then call SaveDataAll, TM1 will still write the entire 10 million cells to disk into the "Cube A.cub" file.

Since you describe your TI process as making data changes to 9 different cubes, I'm assuming that all 9 get data changes regardless of country. That would mean that your SaveDataAll should take the same 10 minutes if the TI has been run ten times or only once.

Wow!!!,I understood it completely, So it does not matter the number of times or the number of cells, if a cell in the cube is updated, it writes the complete cube into the disk and take the same time.

I was actually doing some testing by using a command cubesavedata(cubename) instead of Datasaveall() in the epilog section of TI process, So that here i can exclude the 9 cubes as they are only output cubes and need not require to save them for every 3 hours and saving them by end of the day should take the same 10 minutes

Thanks a Lot for your inputs

Regards,
Guru Madhesh

[lotsaram]

I'm not sure you understood me correctly. It doesn't matter if the TI gets run once or twenty times as long as it writes data to the same exact set of cubes. The granularity of a SaveDataAll isn't "write out just the cells I've changed", it's "write out just the cubes I've changed". This is an important distinction.

Actually the granularity of a SaveDataAll would be better explained as "write out the entire contents of all cubes that have been changed by anyone since the previous save data."

Guru - I didn't realize you were doing a save data at every run of the process. That's not a good idea for performance reasons (and for causing locking prior to 10.2.2) but it should also be totally unnecessary. A scheduled save once or twice per day is all that you should need.

[BariAbdul]

Guru,Obviously your issue is SaveDataAll on each of your TI process run, Still,You could find good explanation of parallel interaction here:
http://www-01.ibm.com/support/knowledge ... ction.html