View seems to lock cube for write access

[holger_b]

Dear all,

in a pretty complex environment with quite a number of rules which usually involve queries of one or more other cubes, we experience that creating and using a view in a TI process (with ViewConstruct) seems to lock the cubes involved, which we do not really understand since it is only about read access. In other words, while the process executes and queries cells which are filled by a rule that points to another cube, users cannot write data into that cube before the process has ended. I wonder why that happens as the view just reads.

This is TM1 version 9.5.2, ParallelInteraction is set to T. Any hints?

Thank you
Holger

[qml]

If you think about it, precalculating a view is not a read only operation. The server needs to write the calculated values to memory. From my experience, this, too, puts a lock on the affected cube. I haven't tested if PI changes this particular behaviour. One way around it would be to switch caching off altogether (probably the opposite of what you want to do), another one to run any ViewContructs when the server is least accessed.

[George Regateiro]

Disclaimer: I have not tested PI with View Construct, I am going under the assumption that it is not a special case the would break PI.

Places I would look first

1) Are there any cube dependencies being generated while the view construct is running? These are located in the tm1server log if you have your properties file set up like http://www-01.ibm.com/support/docview.w ... wg21460595. If there are you should be predefining your dependencies.

2) Are there any rule generated attributes involved? We encountered an issue with unexpected locking if there was a rule file attached to a referenced attribute cube.

[holger_b]

Thank you George. Actually, both are executed in two seperate chores. So would you suggest to run the AddCubeDependency commands right before the ViewConstruct, in the same chore?

Regards
Holger

After following the document link:

ViewConstruct in a TI Process – Customers use a ViewConstruct(cubename,viewname) function in Turbo Integrator (TI) Processes to pre-cache a view to improve performance for subsequent View execution (assuming the cache has not been invalidated). This function will block reads and writes to the View’s cube for the duration of the TI.

This was an important piece of information, thanks a lot for this! We could try creating the views in question and looping through them with "itemskip" in the data tab instead, maybe that improves the situation.

Regards
Holger

[George Regateiro]

Wow. I have looked at that document a few times and never remembered the quoted section (though I probably tuned out at the time since I was not using view construct). If that is the case then no amount of dependencies will help you. It sounds like it will always block.

[holger_b]

Just to complete this for further reference: I tried that, looping through a view to cache the data without locking the cube, but this is hopelessly slow. We decided to let the Viewconstruct run during the night (takes around four hours to cache the major slices), but you lose everything as soon as someone chooses to modify just one figure. And we are still trying to find out how to avoid cube locking - actually seems like rocket science to me. I keep coming across situations where in TM1Top I can see tasks blocking others where I would not expect it. When I grow up, I want to compile a little one pager "10 steps to avoid data locks"

Wish me luck
Holger

[lotsaram]

Just to complete this for further reference: I tried that, looping through a view to cache the data without locking the cube, but this is hopelessly slow. We decided to let the Viewconstruct run during the night (takes around four hours to cache the major slices), but you lose everything as soon as someone chooses to modify just one figure. And we are still trying to find out how to avoid cube locking - actually seems like rocket science to me. I keep coming across situations where in TM1Top I can see tasks blocking others where I would not expect it. When I grow up, I want to compile a little one pager "10 steps to avoid data locks"

Wish me luck
Holger

This is indeed a tricky topic. It does get a whole lot easier though with 9.5.2!

[holger_b]

Hi lotsaram,

this is 9.5.2 (sorry I need to update my profile). Can you provide a list of performance relevant parameters to check (apart from architecture of course), like cfg entries, }Cube properties settings and the like? I feel the longer I browse the internet the more contradictious opinions I find. Just to be curious, here is two of them: Would you turn hyperthreading on or off on a 64 bit system? How about LockPagesInMemory, T or F?

Regards
Holger

[lotsaram]

Would you turn hyperthreading on or off on a 64 bit system? How about LockPagesInMemory, T or F?

Both of these won't do a darn thing for locking. Turning hyperthreading off (if you have a newer Wintel server with a chipset using hypertheading by default) will make TM1 run faster but nothing else. LockPagesInMemory will stop RAM being paged to disk in Windows x64 if the server is in a dormant state for "a long period of time" (whatever a long period of time is I don't know).

In terms of managing locking in 9.5.2 concurrent reads and writes and multiple concurrent writes on the same cube now seem entirely possible. The main thing which we have observed is that metadata object creation will cause a lock for the user (that is user opens view for the first time, any subsequent refresh will be OK). A view being a view in server explorer or from a VIEW formula. So in other words you may still have some locking for users using perspectives but if you were using EV or Contributor then you could probably have an entirely lock free system (which I am guessing at as I don't have such a system to test on.)

So in terms of what to do for any TI updates just make sure views and subsets are not created fresh - reusing and redefining seems OK from what i have observed. Avoid dynamic subsets these still cause locks so any public views and subsets used commonly by users should avoid these (but it looks like there are new config params in 10.1 to finally address this.) Dimension updates will still lock whole server so need to be avoided.

[holger_b]

Now this is interesting, thank you. We try to educate ourselves to delete any subsets and views which we use in TI processes (for zeroout views for example) in the epilog - does that mean you would rather not do that but just empty them before you use them again?

[lotsaram]

For anything running in "the overnight batch window" we create and destroy processing views and subsets. For transfers that occur while users are on the system views and subsets are created if not existing otherwise left with no cleanup on the epilog. For subsets delete all elements and reput members the next time the subset is needed in case the subset should be different. We prefiix such "processing" views and subsets with } so users don't see them. Bedrock process can be used to daily or periodically cleanse the system of such views and subsets based on naming convention.

[holger_b]

Thanks a lot. This is quite close to what we do, but we can sure improve on that here and there.

Regards
Holger