Locking out new logins during TI Processes

[PavoGa]

We are having a problem with users being locked out when processes are running and the processes include metadata updates. Here is the scenario:

A control process runs a number of subprocesses. Some of those, including several of the first ones are updating dimensions and attributes. While this process is running, regular clients are unable to login in. Admins can. We are using CAM.

I've read through the Forums and a number of links dealing with locking, but if anything is dealing with this specifically, I missed it. What is the best solution for this? Set the metadata updates to perform separately, then set a flag in a cube with a chore looking for that flag to execute the rest of the processes which mainly perform data loads?

Or should we simple execute the thing through a chore with multi-commit? That can be a little aggravating because the control process has several parameters the users set at runtime to control what actually occurs during execution. I guess we could put that in a cube and have the processes get their parameter values from there. I am also a little leery of depending on multi-commit because of the issues in 10.1 (we are on 10.2.2).

Would TM1RunTI provide a possible solution? I've not used it before so am not that familiar with it.

[ellissj3]

One option might be to use the Enable Bulk Load Mode.

http://www-01.ibm.com/support/knowledge ... RN_BkLdMc

[macsir]

There is no good option when you try to run many processes for a long time during business hours. I think better to schedule it to non-business hours if you need huge data loading?

[lotsaram]

One option might be to use the Enable Bulk Load Mode.

Seriously? The OP's issue is that a TI process is preventing user logins and you suggest something which by design prevents further access by any other thread.

[PavoGa]

Thank you very much for the responses.

The process takes about 5-6 minutes to run, but the business may run it several times a day when they are heads down on final adjustments to their numbers.

[George Regateiro]

I would investigate using a batch script to replace your control process and using RunTI to call your sub processes. This should release the locks caused by the metadata updates between process calls. This method would also allow you to pass in parameters while achieving the same results as the multi commit mode of a chore.

[Edward Stuart]

I would monitor TM1Top during the process run to identify what is actually locking the model, if it is metadata updates as you suspect then I would explore DIMENSIONELEMENTINSERTDIRECT to see if this alleviates the issue if you haven't already

What datasource are you using? Are you able to setup a trigger to run metadata updates independently of data loads? A quick look at the official documentation gives you a very solid start with TM1RUNTI

[PavoGa]

I would investigate using a batch script to replace your control process and using RunTI to call your sub processes. This should release the locks caused by the metadata updates between process calls. This method would also allow you to pass in parameters while achieving the same results as the multi commit mode of a chore.

That is what I was wondering, but I did not want to spend time on it if it was leading down a blind alley. Is this something you've used yourself?

[PavoGa]

I would monitor TM1Top during the process run to identify what is actually locking the model, if it is metadata updates as you suspect then I would explore DIMENSIONELEMENTINSERTDIRECT to see if this alleviates the issue if you haven't already

What datasource are you using? Are you able to setup a trigger to run metadata updates independently of data loads? A quick look at the official documentation gives you a very solid start with TM1RUNTI

Thanks, Edward. We have multiple datasources: an Oracle table and four cubes among the processes that are executed when the master process runs. I actually thought about that, but some of the dimension seem rather large with one having over 160K members and I've read that the ...INSERTDIRECT functions are best for slowly changing dimensions or performance could suffer. I've not had time to perform any investigation on it.

We may try the TM1TIRun. I've been busy on something else and thought putting this on the forum would get some informative feedback.

Thank you, again.

[George Regateiro]

That is what I was wondering, but I did not want to spend time on it if it was leading down a blind alley. Is this something you've used yourself?

RunTI is a tool we utilize all the time. It is a great way to break up large process and we have used it extensively to take advantage of parallel processing. It does add complexity since it puts you in charge of managing the process calls to ensure any dependent calls are completed before moving on but it can yield to some pretty great results. You can be creative with this part though. You can do something like have your meta data updates in one process and have that process kick off the RunTI script for the data loads from the epilog tab, so that way you can ensure that the metadata updates have completed.

We have multiple datasources: an Oracle table and four cubes among the processes that are executed when the master process runs.

Once you break out the metadata updates and isolate those from the data loading you can possibly leverage run TI to run the 4 cube loads in parallel and this could get you some good performance gains, but I would leave that for a phase 2 once you have gotten around the metadata.


Depending on the amount of updates you are doing the insert directs might be an option as mentioned by another poster, but I have not used them enough to weigh in on how they work in a large scale load process.

[PavoGa]

RunTI is a tool we utilize all the time. It is a great way to break up large process and we have used it extensively to take advantage of parallel processing. It does add complexity since it puts you in charge of managing the process calls to ensure any dependent calls are completed before moving on but it can yield to some pretty great results. You can be creative with this part though. You can do something like have your meta data updates in one process and have that process kick off the RunTI script for the data loads from the epilog tab, so that way you can ensure that the metadata updates have completed.

I'm going to dive on in with this. It sounds pretty much like the solution to at least minimize the impact on the users. Great explanation by the way, kind of helped some other thoughts and pieces of other puzzles fall into place. Thank you.

Ty

[BrianL]

I would monitor TM1Top during the process run to identify what is actually locking the model

^^This. You can also enable the log4j.logger.TM1.Lock.Exception=DEBUG logger to potentially get more information on what objects are causing contention. Usually for blocked logins it's the }Clients or }Groups dimension or something like that. I've also seen cases where it's not all logins that are blocked, but only new users who are logging into TM1 for the first time. When using CAM, at the first login TM1 will pull all the group membership information from CAM and add/modify group membership information as necessary. This can cause issues when other long running operations are even reading from a cube that uses the }Clients or }Groups dimensions.

[Alan Kirk]

That is what I was wondering, but I did not want to spend time on it if it was leading down a blind alley. Is this something you've used yourself?

RunTI is a tool we utilize all the time. It is a great way to break up large process and we have used it extensively to take advantage of parallel processing. It does add complexity since it puts you in charge of managing the process calls to ensure any dependent calls are completed before moving on but it can yield to some pretty great results. You can be creative with this part though. You can do something like have your meta data updates in one process and have that process kick off the RunTI script for the data loads from the epilog tab, so that way you can ensure that the metadata updates have completed.

I've found that Cubewise's free Hustle utility (link in this thread is an excellent way to handle multiple TI calls. It's one of those "Why didn't I think of that?" tools.

[paulsimon]

Hi

A quick note about DimensionElementInsertDirect - You say that you have dimensions with 160,000 elements, however, the Dimension Direct functions are normally used in Data Loading on the Data Tab with an IF( Dimix( ) = 0 ) test so that the element only needs to be inserted if it doesn't already exist, therefore you don't actually re-insert 160,000 elements, since I would guess that the vast majority will already exist in the dimension. The locking will only start from the point at which an element needs to be inserted. If no elements need to be inserted then there will be no locking.

One approach to reduce locking during data loading is to write all records that have elements that don't already exist to a file, and then process this file at the end which delays the point at which locking occurs and reduces the time duration of the locking.

I would also check that you have identified all Cube Dependencies from the Server Log and that you explicitly add these using a Process and a Startup Chore. If TM1 has to add these at run time, it can cause cube locking.

Another thing to mention is that if you are reading from Oracle, then the time taken there can be what causes the long duration. Consider pre-summarising in Oracle with a Group By and reading from the summarised table. Consider writing the data to a file from Oracle and then reading in the file.

For Dimension Updates on systems with large dimensions, I use a scheme whereby I update a copy of the dimension. I then compare the copy with the main dimension and write out a file of the operations required such as breaking links, adding links, inserting elements, etc. I then use TM1RunTI to trigger another process which reads the file and updates the main dimension, using the Direct functions. Updating the Copy of the dimension should generally not cause any locking as it is not used in any cubes. The time to update the main dimension can be kept to a minimum since it usually only needs to process a small file of changes, and it uses the Direct functions so there isn't a full re-write of the dimension. As mentioned before the use of TM1RunTI confines any locking to just this process.

A handy by-product of this approach is that you also get an audit trail of the changes that have been made to the dimension.

Regards

Paul Simon

[paulsimon]

Hi

Sorry a further thing, which I believe is still the case. View creates and Subset creates are meta data operations that cause locking. Instead of destroying Views and Subsets in the Epilog, it is better to retain Views and Subsets and to just amend the Subset using SubsetDeleteAllElements and then re-insert which effectively amends the View. Hopefully once IBM sort out temporary Subsets and Views this will be less of an issue.

Regards

Paul Simon

[PavoGa]

Thanks, Paul.

Sorry a further thing, which I believe is still the case. View creates and Subset creates are meta data operations that cause locking. Instead of destroying Views and Subsets in the Epilog, it is better to retain Views and Subsets and to just amend the Subset using SubsetDeleteAllElements and then re-insert which effectively amends the View. Hopefully once IBM sort out temporary Subsets and Views this will be less of an issue.

Actually we are not seeing a problem with this. The first two subprocesses are doing the metadata updates, if any, and the last three are moving data. Almost all build subsets and views for the data sources, but only the ones updating dimensions are causing the locks preventing logins.

We are experiencing performance issues, but now have the window down to 5-6 minutes vs 30+ when actually loading data.

TM1RunTI has resolved the locking by reducing the locking window to a few seconds. Interestingly, we had some problems with the data loads running through TM1runTI, so those are still executed through ExecuteProcess.

Thanks again for the tips. They are appreciated.