Managing TI Usage with Multiple People

[Steve Vincent]

Some questions on how / if you can manage the running of a TI by people in different locations where the concurrent running could cause issues with the data they are loading;

Scenario:
One TI used to load data to one cube by 15 people spread out all over the place.
Data is loaded per user to seperate elements in a "versions" dim.
Times that users wish to update are quite likely to clash.

Options i have thought of so far;
Do nothing and hope for the best!
Expect users to check with everyone else that has access to load the data and ensure the coast is clear (unworkable).
Create seperate TIs for every user (maintenance minefield).
Launch the TI from Excel and use Excel/VBA to allow / ban running the TI if someone else is already in the file.
Tell the customer that a key benefit they were aiming to get from TM1 is actually not workable in reality and to continue loading data centrally...

Issues with various ideas so far;
I cannot create subsets / views on the fly as the version of TM1 we are currently using has a bug related to doing so.
As a result i am loathed to create individual subsets / views manually for individual TIs for each user.
I'm not sure if i can pass a data source to a TI process via Excel.
If running via Excel the user looses the "this process ran with minor errors" type of feedback. This happens a lot and is an important thing to be missing.

Has anyone had this kind of process issue before and found workable alternatives i'm yet to think of? All ideas welcome!

[tomok]

if you want to keep people from bumping into each other with the TI you can create a "Control" cube, if you don't already have one, and have an element called "TI Running". You could put code in the Prolog to check for a 1 or "Y", or something like that, and if found abort process. If not found, set flag and continue process. In Epilog you can clear the flag. If you are worried about TM1 locks you can employ the same logic in a SQL server table too. I have used both.

If you want to get fancy you could have an Excel sheet for kicking off the user load, with an Action Button, that has a DBRW into the Control cube that conditionally formats the cell green when it's OK to run the TI or red when they shouldn't. Tell the users it's OK to run the TI when the cell is green. If it's red come back later or recalculate every few minutes until it turns green.

[Steve Vincent]

Sadly i don't have action buttons in my version of TM1. I am looking forward to improving various things with 9.5 when we get it but for now that is a no go.

Server locking via a control cube might be an option. It kicks off other processes at the end so that would help avoid a clash. Cheers, another idea for the toolbox.

[Martin Ryan]

At a previous site I had a zLock cube that just had one cell in it. Every single process would write a random value to the zLock cube as the first thing it did in the Prolog. That would lock that cube and prevent any subsequent process from writing to it. That meant the subsequent processes had to wait for the first process to finish and release the lock on the zLock cube, before they could write to it, and then continue from there. The end result is that all the processes executed sequentially rather than simultaneously.

It's similar to tomok's suggestion, but will mean the process just waits in a queue instead of having to abort and try again later.

Martin

[Steve Vincent]

Some interesting things are coming out of this bit of testing;

• locking is no use as i'm on 9.0, but again it might be handy when we migrate to a later version.
• My TI which also calls other TIs in epilog actually crashes the server when 2 users try to run it at the same time.
• When a TI is run, TM1 automatically saves it when changes (eg. different data source) have been made.
• If the user that opened the TI first has it left open while another user opens it and changes a data source, once the 2nd user saves / runs the process it also affects the 1st user. Although the screen will not refresh, unless the 1st user makes another change to the TI or presses save, when it is run it will do so in the state the 2nd user left it, thus possibly loading incorrect data.

For now at least i'm stuck with quitting the process based on a locking value. It would be nice if i could find someway to provide feedback to the user to say "process quit: please wait 5 mins and try again" but although we have ItemReject for data, i can't find a similar ProcessReject call.

[tomok]

If the user that opened the TI first has it left open while another user opens it and changes a data source, once the 2nd user saves / runs the process it also affects the 1st user.

You're letting users change the data source in a Ti process? Interesting. Have you explored using an input parameter for the data source. I use this to make SQL queries dynamic (enclosing the parameter with ?Parameter? in data source tab). I haven't tested it but it might work for ASCII files too.

[garry cook]

Hey matey, just a thought - why not use DatasourceNameForServer=x in the Prolog tab hooked up to a parameter that's populated by passing it through the API code for running a process in Excel? I've done similar here in ladies underwear world and works fine (assume you guys are still on 9.0SP2).

Also, why not just write out to a control cube holding the }Clients dim the appropriate string message if the process was a success or failure and use environ(username) or WhoAmI MDX to query the cube directly after the load as part of the VBA code and return the value to a cell? Even if the username doesn't match the windows logon, it's a useful alias to have regardless. Quick counter that starts on Prolog and ends in Epilog should be able to tell what's happened in the TI.

Should work in theory.

Hope all is well in plane land

[jim wood]

I don't know if this is any help.....

What we have done is use SQL 2000 to act as processing front end. There is a table in SQL with one cell. (This is very similar to the control cube option offered above but doesn't need action buttons) The user kicks off a dts from an excel (Macro button) spreadsheet. The macro button firstly checks in the control table whether a process is currently running. If so it stops. If not it kicks off the dts. The first thing the dts does is write to the control table. (Locking things down) It then runs a chore via the nice DOS execute code provided by guys on this forum. Once the schedule is finished it writes back to the table to effectively unlocking everything again.

I hope this helps,

Jim.

[Steve Vincent]

Plane Land is, well, much the same to be honest. Just less planes

All great ideas, thanks. Tomok - the data source is an ascii file and as the users are loading their own parts of the business, yes they need to change the datasource. I know there are ways the filepath can be passed but in the time i have left before i end up chained to my desk until "IT" is done, i just don't have the resources to do that much work on it. I was mainly looking for enough evidence to say to the customer YES or NO as to its possibility, rather than have something done for them right now.

I should be able to hold them off until after the next block of work is complete, but at least the customer now has that warm feeling of it won't be a problem rather than the current situation of "don't or you'll kill it!".

[dubs]

if you had loads of time and didn't mind writing convoluted TI code you could always temporarily deny access to all other users to the TI until it has finished running- this would stop the concurrency clash that is causing things to crash

I wouldn't do it personally and it wouldn't necessarily help from the user feedback point of view

When a TI is run, TM1 automatically saves it when changes (eg. different data source) have been made.

is this true when you override the data source in the prolog tab using a variable? I would have thought that as you pass it a variable it wouldn't change the actual code in the saved version of the proc:

SELECT * FROM %VARIABLE%

this actually leads me to a question of my own - new thread i think

[lotsaram]

if you had loads of time and didn't mind writing convoluted TI code you could always temporarily deny access to all other users to the TI until it has finished running- this would stop the concurrency clash that is causing things to crash

If the version is 9.1 that has stability issues with running the same processes concurrently then I wouldn't advise this at all. A security refresh would be necessary and this would dump all cached calculations which would usually be undesirable. Also in a large model with lots of users (which you have to assume this is to be encountering concurrency issues) then the security model might be quite large. In very big systems with hundreds to thousands of users and hundreds of groups a security refresh of tens of seconds to a couple of minutes is not uncommon so is best restricted to outside business hours. The approach already suggested of a single cell "TIQueue" cube (semaphore cube in IBM terminology) is the standard solution to this problem and the best one to take.

When a TI is run, TM1 automatically saves it when changes (eg. different data source) have been made.

is this true when you override the data source in the prolog tab using a variable? I would have thought that as you pass it a variable it wouldn't change the actual code in the saved version of the proc:

When a TI is being executed as opposed to edited this is most definitely not true. A run time assignment of a new parameter value or a variable such as the datasource categorically does not change the underlying saved code. The statement is only true in the exact context which Steve was referring to of changing the data source in the TI editor and then running the TI from the editor.

All great ideas, thanks. Tomok - the data source is an ascii file and as the users are loading their own parts of the business, yes they need to change the datasource. I know there are ways the filepath can be passed but in the time i have left before i end up chained to my desk until "IT" is done, i just don't have the resources to do that much work on it. I was mainly looking for enough evidence to say to the customer YES or NO as to its possibility, rather than have something done for them right now.

I should be able to hold them off until after the next block of work is complete, but at least the customer now has that warm feeling of it won't be a problem rather than the current situation of "don't or you'll kill it!".

Steve I have to say I really don't follow your logic. Assigning DataSourceNameForServer on the prolog is very simple and allows the data source to be changed on the hop at process runtime. The implementation time is negligible. Versus allowing users to change the data source in the TI editor is very risky, for a start this is only possible if the users have full Admin or DataAdmin access, fraught with danger.

[dubs]

I agree I wouldn't go with my suggestion either

When a TI is run, TM1 automatically saves it when changes (eg. different data source) have been made.

is this true when you override the data source in the prolog tab using a variable? I would have thought that as you pass it a variable it wouldn't change the actual code in the saved version of the proc:

When a TI is being executed as opposed to edited this is most definitely not true. A run time assignment of a new parameter value or a variable such as the datasource categorically does not change the underlying saved code. The statement is only true in the exact context which Steve was referring to of changing the data source in the TI editor and then running the TI from the editor.

thought so, certainly how I would handle a dynamic data source would be to override the data source in prolog using parameters or even automate it by matching a tm1 user id to a data source string held in a cube, it wouldn't matter if the proc somehow got saved as the code would override the data source the next time it was run and saving it would only save down a version which was the same in code as the original.

[Steve Vincent]

Steve I have to say I really don't follow your logic. Assigning DataSourceNameForServer on the prolog is very simple and allows the data source to be changed on the hop at process runtime. The implementation time is negligible. Versus allowing users to change the data source in the TI editor is very risky, for a start this is only possible if the users have full Admin or DataAdmin access, fraught with danger.

It's not the time it takes TM1 to start running the TI, its the time it takes for the user to enter the other parameters needed for the TI that is my problem. I need to ensure that each user has exclusive access and TM1 cannot provide that ability. Not without the help of an external GUI such as Excel anyway. It's that part i don't have the time for at present, but knowing it can be done means i don't have to think about it while i'm doing all the other stuff that is more important right now

[Martin Ryan]

It's not the time it takes TM1 to start running the TI, its the time it takes for the user to enter the other parameters needed for the TI that is my problem. I need to ensure that each user has exclusive access and TM1 cannot provide that ability. Not without the help of an external GUI such as Excel anyway. It's that part i don't have the time for at present, but knowing it can be done means i don't have to think about it while i'm doing all the other stuff that is more important right now

Some more to not think about: if you set up a parameters cube you could add in the }Clients dimension. Then you can specify the file name by individual user and pick it up at run time from TI via something like fileName=cellgets('ParmCube', tm1user(), 'FileName);

That should speed things up and also reduce the likelihood of users mistyping things into the far-too-small parameters input cell.

Martin

[dubs]

another thing to not think about maybe using session variables

[BigG]

Hi just working through this post and we have a similar control cube as tomok describes for a user run TI process.

if you want to keep people from bumping into each other with the TI you can create a "Control" cube, if you don't already have one, and have an element called "TI Running". You could put code in the Prolog to check for a 1 or "Y", or something like that, and if found abort process. If not found, set flag and continue process. In Epilog you can clear the flag. If you are worried about TM1 locks you can employ the same logic in a SQL server table too. I have used both.
quote]

but we would like to wait for the first process to finish and release the lock so the second run (by another user at same time) of the TI process, can anyone expand on how this would be set up, we want the process to continue once the other process is released.

At a previous site I had a zLock cube that just had one cell in it. Every single process would write a random value to the zLock cube as the first thing it did in the Prolog. That would lock that cube and prevent any subsequent process from writing to it. That meant the subsequent processes had to wait for the first process to finish and release the lock on the zLock cube, before they could write to it, and then continue from there. The end result is that all the processes executed sequentially rather than simultaneously.

[/quote]