Managing Cube logging from Action Button TIs

[tm123]

Hi,

We have to keep Cube Logging on in our data entry cubes in order to be able to trace users transactions. In our model, we have multiple data entry cubes ( at different level or granularity ) so users enter transactions at a very detailed cube ( Cube_A) and in the Websheet they click an Action button which runs a TI process to rollup the data and refresh a more summarized cube ( Cube_B ). then the user can open another websheet which will show the summarized data, and also in the websheet they will make adjustments ( in another slice ). Also in the second websheet there is an Action button which will run another TI to perform calculations and move the calculated data into another cube ( which is also a data entry cube )

The TI processes have been designed in a way that multiple users can run the same TI in the same time and apply the parameters to the TI and they all run in parallel, so they don't lock each other, as long as users are working on different intersections.

This works well, but the only problem is we cannot turn off cube logging in prolog and turn it off in Epilog since if 2 users are trying to do this at the same time, they will lock each other.

So keeping the cube logging on, over-populated the transaction log files and this somehow slows down the server

Any recommendation?

Thanks

[declanr]

The users only directly manipulate "Cube A" so leave the logging turned on for that cube and just have it constantly off for "Cube B"; since "Cube B" is updated by TI process the users wouldn't need write access - therefore if your TIs and admins are trusted then it should be fine to leave logging off.
You can also add a single line into your TI code to do a simple manual logging saying who ran it and for what parameters etc without logging the full transactional detail.

[tm123]

The users only directly manipulate "Cube A" so leave the logging turned on for that cube and just have it constantly off for "Cube B"; since "Cube B" is updated by TI process the users wouldn't need write access - therefore if your TIs and admins are trusted then it should be fine to leave logging off.
You can also add a single line into your TI code to do a simple manual logging saying who ran it and for what parameters etc without logging the full transactional detail.

Hi Declan, thanks for your response. The problem is that Cube_B also is a data entry cube, so we have a dimension in that cube with 2 leaf elements ( Calcs and Adjustments ), so the calcs slice is populated from the TI processes executed from Action button and Adjustments slice is directly updated in the websheet, so that's why is tough to handle this.

All this intra-cube data movement was done through Rules and the performance was awful, so the performance is not too bad now, but the problem is transaction log files are getting huge. Ideally, IBM should make the transaction logging not a cube level but at dimension element level

[declanr]

To be clear is the problem that the process is running too slowly (which would obviously be improved by not logging transactions for the process) or are you just struggling with the size of log file it is creating?

[tm123]

To be clear is the problem that the process is running too slowly (which would obviously be improved by not logging transactions for the process) or are you just struggling with the size of log file it is creating?

The process execution time is OK, but if I turn off cube logging it can be better. Especially when the active tm1s.log gets large ( we have chore that runs every 6 hours to do a SaveData ), the I can see that processes take longer time to finish.

[lotsaram]

You can turn the logging off and on in the prolog and epilogue of the TI called by action button.

What do you mean by "log files are getting huge"? Define it. If you are talking 10s to 100s of GB of logs per day you may have a point, otherwise probably not.

What kind of data is being copied in? If you are copying in factors or drivers from a lower dimensioned source to a cube with more dimensions then this is more than likely needless data replication. (Not saying you are doing this, just a guess there might be some "data spraying" going on if the log files really are large.)

[tm123]

You can turn the logging off and on in the prolog and epilogue of the TI called by action button.

What do you mean by "log files are getting huge"? Define it. If you are talking 10s to 100s of GB of logs per day you may have a point, otherwise probably not.

What kind of data is being copied in? If you are copying in factors or drivers from a lower dimensioned source to a cube with more dimensions then this is more than likely needless data replication. (Not saying you are doing this, just a guess there might be some "data spraying" going on if the log files really are large.)

Thanks lotsaram, turning off/on cube logging will impact the parallel execution of processes, that's why I cannot do it. The users just started using the tool today, so I can't say how big these files are going to be, but they will be big. I can already see the active tm1s.log file is around 160 MBs, and only a few users are connected.

So let's say I have 3 websheets connected to 3 different cubes.
Websheets_1 connected to Cube_A, which is at Customer Level ( around 60 thousand customers ), for each customer, users can adjust around 10 measures. The Cube_A has the dimensions ( Region, Industry, Product, Account_Manager, Period, Version, Books, Customer, Cube_A_Measures ). Some of the data in this cube is loaded from an ODBC dimension so in our Books dimension there is one element called Original and one element called Adjustment (and one consolidation called Balance ). So the data loaded from ODBC goes to Original Slice and data entered by users goes to Adjustment. Then after users have entered adjustments in Cube_A, they click the Action button which aggregates the data and moves it in the Cube_B which has the following dimensions (Region, Industry, Product, Account_Manager, Period, Version, Books, Cube_B_Measures ). There is a mapping from Cube_A_Measures to Cube_B_Measures ( elements of Cube_B_Measures are COA Accounts, so there are around 15 accounts in this dim ). In Websheet2, users will be able to view data that they entered in Websheet1 ( in a aggregated ) and also they will enter further adjustments. Also, there is another Cube_0, which is only with 4 dimensions ( Region, Product , Period, Cube_0_Measures ), which is used to enter only some Costs and there is a TI process ( Action button ) that allocates these costs to Cube_B.

There are other cubes also, and the last one being updated is the Reporting Cube (thanks god this one has Cube logging turned off )

each user has access only to a Region and Account_Manager, so the TIs will run only for the selected Region and Account_Manager, and all works fine, no locking when multiple users run the same button in the same time

I hope this explains the scenario better

Thanks

[BrianL]

turning off/on cube logging will impact the parallel execution of processes, that's why I cannot do it.

Are you running with ParallelInteraction=T? If so, changing the cube logging property should be no more invasive (from a locking standpoint) than a data write. Maybe that was not the case in some older releases?

[tm123]

turning off/on cube logging will impact the parallel execution of processes, that's why I cannot do it.

Are you running with ParallelInteraction=T? If so, changing the cube logging property should be no more invasive (from a locking standpoint) than a data write. Maybe that was not the case in some older releases?

Any overlapping data write operation causes locking issues. If 2 processes try to update the same cell in the same time, the second one will be locked, and this is exactly what happens with setting cube logging on or off

[BrianL]

Any overlapping data write operation causes locking issues. If 2 processes try to update the same cell in the same time, the second one will be locked, and this is exactly what happens with setting cube logging on or off

No. Without parallel interaction two users writing to any cell in the same cube will IX lock and conflict. With parallel interaction enabled all data writes are done using a READONLY lock and will not conflict. Even if they are both writing to the same exact cell location.

[tm123]

Any overlapping data write operation causes locking issues. If 2 processes try to update the same cell in the same time, the second one will be locked, and this is exactly what happens with setting cube logging on or off

No. Without parallel interaction two users writing to any cell in the same cube will IX lock and conflict. With parallel interaction enabled all data writes are done using a READONLY lock and will not conflict. Even if they are both writing to the same exact cell location.

I forgot to say that YES, PI is turned ON, and what happens is, if I have 5 processes running in parallel, and let's say the first one changes the cube logging for Cube_A, when the second process tries to change the cube_logging for Cube_A, it cannot proceed, and after that point the execution of all processes becomes sequential.

[BrianL]

I forgot to say that YES, PI is turned ON, and what happens is, if I have 5 processes running in parallel, and let's say the first one changes the cube logging for Cube_A, when the second process tries to change the cube_logging for Cube_A, it cannot proceed, and after that point the execution of all processes becomes sequential.

What version of TM1? That's not what I'm seeing when I try in the latest 10.2 RP2 FP3.

[BrianL]

Also, I can't find any references to back this up; but for some reason I keep thinking you can just write to the }CubeProperties cube directly using CellPutS and that it *might* have a different contention footprint. Since I can't verify that right now, YMMV.

[tm123]

Also, I can't find any references to back this up; but for some reason I keep thinking you can just write to the }CubeProperties cube directly using CellPutS and that it *might* have a different contention footprint. Since I can't verify that right now, YMMV.

I am on 10.2.2 FP2 I guess, but this issue has been always there, in order to make processes run in parallel, they cannot write to the same cube slice/cube cell in the same time. And the TI command CUBESETLOGCHANGES, it write "YES" or "NO" to the cube }CubeProperties, so you cannot have multiple processes trying to update the same cell in }CubeProperties at the same time

Trying to use CELLPUTS does not help

[tm123]

I forgot to say that YES, PI is turned ON, and what happens is, if I have 5 processes running in parallel, and let's say the first one changes the cube logging for Cube_A, when the second process tries to change the cube_logging for Cube_A, it cannot proceed, and after that point the execution of all processes becomes sequential.

What version of TM1? That's not what I'm seeing when I try in the latest 10.2 RP2 FP3.

That is interesting though, I just tried it again, and yes it created the lock

[BrianL]

I just fired up my copy of 10.2 RP2 FP2 and I can't see your issue.

I created a dummy TI that just does this:

prolog

Code: Select all

CubeSetLogChanges('PriceCube',0);
sleep(10000);

epilog

Code: Select all

CubeSetLogChanges('PriceCube',1);

If I log in twice and run the same TI at the same time I can see the both run in parallel in tm1top. I suspect you have some other issue that's causing your TIs to serialize.

[paulsimon]

Hi

I would be interested to know why rules behaved poorly on this. They generally only do this when you are feeding from one cell and have to target many cells. However, this looks like a classic case of changing levels. Therefore the feeders should be simple to write.

Another alternative would be to do everything in one cube.

Users input at the base level
TM1 naturally consolidates
At the consolidated level, the second set of users view the data, and if they want to make changes they enter the data into an _Input element eg if we have countries consolidating to continents, then the world, you would have eg United Kingdom going into Europe, and you would enter the adjustments against a Europe_Input element that consolidates into Europe.

In that way you wouldn't need any rules or TIs.

You mentioned the need to do some calculations, which I would guess you could handle via rules.

Another possibility to consider is whether it really is necessary for the user to see the data in the summary cube as soon as they click the Action button. Perhaps consider removing the Action button and run a TI to consolidate the data every ten minutes, perhaps using a flag cube to show which items have changed. You can organise that via a DBS to set the flag value, so no need for a TI or Action button in the actual sheet.

However, my first take on this would be the single cube option. TM1 has been good at consolidation since version 1.

Regards

Paul Simon

[BariAbdul]

I forgot to say that YES, PI is turned ON

Bit more about PI:

https://www-304.ibm.com/support/docview ... wg21460595
http://www-01.ibm.com/support/knowledge ... ction.html

http://www.tm1forum.com/viewtopic.php?f=3&t=5147 Thanks

[tm123]

I just fired up my copy of 10.2 RP2 FP2 and I can't see your issue.

I created a dummy TI that just does this:

prolog

Code: Select all

CubeSetLogChanges('PriceCube',0);
sleep(10000);

epilog

Code: Select all

CubeSetLogChanges('PriceCube',1);

If I log in twice and run the same TI at the same time I can see the both run in parallel in tm1top. I suspect you have some other issue that's causing your TIs to serialize.

Yes, that exact code causes locking ( the only way it does not create locking is if Cube Logging for the cube }CubeProperties is ON, so in that case TM1 will not commit the change to the cube yet ). So just make sure that in the }CubeProperties cube, the setting for the cube }CubeProperties is NO and try it again, I would be surprised if you still can run your TI in parallel

[tm123]

Hi

I would be interested to know why rules behaved poorly on this. They generally only do this when you are feeding from one cell and have to target many cells. However, this looks like a classic case of changing levels. Therefore the feeders should be simple to write.

Another alternative would be to do everything in one cube.

Users input at the base level
TM1 naturally consolidates
At the consolidated level, the second set of users view the data, and if they want to make changes they enter the data into an _Input element eg if we have countries consolidating to continents, then the world, you would have eg United Kingdom going into Europe, and you would enter the adjustments against a Europe_Input element that consolidates into Europe.

In that way you wouldn't need any rules or TIs.

You mentioned the need to do some calculations, which I would guess you could handle via rules.

Another possibility to consider is whether it really is necessary for the user to see the data in the summary cube as soon as they click the Action button. Perhaps consider removing the Action button and run a TI to consolidate the data every ten minutes, perhaps using a flag cube to show which items have changed. You can organise that via a DBS to set the flag value, so no need for a TI or Action button in the actual sheet.

However, my first take on this would be the single cube option. TM1 has been good at consolidation since version 1.

Regards

Paul Simon

Users need real time reporting, it is a complicated model, with many data entry cubes and very sensitive data. The dimensions I gave in the example, are not the real dimensions, I was trying to just illustrate the scenario with an example.

Running a Report ( which integrates data from all the other Input and Calc cubes, was taking 25-30 minutes ). Also loading data from the source system, copying versions and other admin operations were taking forever. We had optimized the FEEDERS as much as we could, and also we have implemented many calculations by using the native consolidation approach that you mentioned, but our problem is that data has to be in different cubes, the Measures in Cube_A are totally different from Measures in Cube_B, but we have mapping cubes in the middle populated with Mapping Keys and we have TI process that parses the Mapping Cube in order to figure out which cell from Cube A goes to a certain Cell in Cube B.

Now, after Rules have been converted to TIs, reports run in 15 seconds, also all the other admin daily operations run in seconds, that was a huge benefit