TM1 Forum

In an effor to reduce locking during dimension updates Im wondering if it's possible to simply add a textoutput prior to any dimensional functions.

The idea is to only have TM1 create the lock once data is streaming back from the ODBC connection. Currently if the datawarehouse is busy or we get delayed there can be locks for a significant period of time which can cause issue for users.

Any thoughts?

Sounds like you know the answer already, if I couldn't be sure of the performance of my ODBC connection then I'd be looking for a text output too.
Cheers,

I guess in more detail, will adding a non locking function as the first function to be executed that needs data from the ODBC allow the system to stay lock free untill the query begins returning data? If that does work then what would I do in the case of a dimension recreate where the function is in the prolog? Use the same textoutput in the prolog? It doesn't really matter what gets output as long as TM1 thinks it's waiting to perform a textoutput first instead of a metadata update.

I'm kind of confused here. Could you explain in more detail what exactly is the problem you are trying to solve and what your proposed solution is, in particular what code is currently in each of the tabs and what you intend to add where?

Sorry to be a bit dim and all that.

What we are experiencing is a locking of data while the SQL is being submitted to the database. In the case when the database delays our query or takes an inordinate amount of time to respond, we end up with locking for the whole time. Im looking to only have the metadata lock occur while the database is responding rather than while we are waiting for it.

What is missing for me is what TI calls are doing the locking, on what tabs they are written, where you are putting the TextOutput and how it's going to change things.

Sorry if it's obvious, but it's not a problem I've had to solve before and so for me it needs a bit of spelling out. Sorry to be a pain.

The SQL for your data source will only get executed at the end of the prolog. This has to be the case as you can change the SQL that the process will use within the prolog itself. If you have any other code in your prolog that could cause a lock on the server then this will already have done so before your SQL gets executed, so the lock isn't anything to do with the SQL itself, but with the other code.

A couple of alternatives:

You could create a process that tests the connection to the ODBC source, and only run your existing process if the source is available. This won't stop your SQL running slowly if there is load on the source.

You could move most, if not all, of your code from the prolog to the metadata, certainly move everything that would cause a lock on the server. Initialise a counter in the prolog, then have a test in the metadata so that the code from your prolog only runs once. In this way the lock will only get established when the code on your metadata is run for the first time.

Third, and what Steve was alluding to, if you can't guarantee good response times from your source, change your process so that it creates a text file first, then use a second process to read that text file. This way the lock only gets created when you start reading the text file.

Or borrow a slightly more relational technique and don't update your target dimension directly, but do your updates from your source on a shadow dimension, where it doesn't matter if this object is locked, as there will be no cubes using it, then once this shadow has been updated use this as the source of the updates to your main dimension.

Reading between the lines it sounds like you are trying to use PI and the speed of the data feed is making the MD run slow and hence extending cube lock. This is made worse by having the dimension maintenance and the data update in the same TI and needing to run the query twice.

If you write the SQL query results to a text file then you only need to run the query once (we are already winning). You can then have two TIs, one to update the dimensions and another the data. You probably don't need two TIs since you are writing data and updating the dimensions in the same cube so it will be locked during both the data and the MD updates.

Used well PI is a real game changer, we've currently got a TM1 instance that generates pre calculated results for a static cube for BI to read. We export the heavily rule driven values (~45 mins, 1 month) to a text file and then read it in (~5 mins). We do get a performance dip during the 45 mins in BI but no lock out and then a short one while the data is read in. With no text file in between we were locked for the full 50 mins. Dim maintenance also done separately too.

Andy's approach of using a mirror dimension to build from the data feed (slow and masses of redundant info) and then building the main dimension from the mirror is also a good one if you really want to squeeze every bit of performance. An alternative of this is to process the data file and just write out the changes (using a dimix test) to another text file. Then use this to populate the master dimension. I suggest this because the code to copy one dimension into another is surprisingly complex for the general case, so it ought to be quicker to develop.

Cheers!

So here is a little more detail but basically its simpler than you think.

1. I have the ability to split the TIs that update the metadata and data so that the metadata updates are serial and the data updates are kicked off by TM1RunTI.exe so the lock isnt held through the entire update.
2. The TI's I have don't mix loading metadata and data.
3. The simple answer here is that a basic dimension update could hold a lock for a very long time if the database puts my query on hold from its end.
4. What Im basically thinking is that if TM1 reads from Left->Right and Top->Bottom including Left->Right from Prolog to Epilog than the way it determines there is a lock is when it hits a function such as dimensionelementinsert or dimensionelement componentadd. In a dimensional udpate TI these would be the first functions it might run into.
5. My theory is that by placing a dummy textoutput function that references a variable from the query, TM1 will be stopped at that function which doesn't create server locking. This in essence means that the TI is only locking during the actual streaming of the data rather than any wait time that may be incurred on the database side.


Does that make a little more sense? Basically Im trying to eliminate locking of objects during the wait time of the database query to respond.

Hi

It depends on whether the issue is in the resolution of the SQL Query or whether there is also an issue in the speed with which data is fed. You cannot necessarily rely of SQL using the same execution plan every time. How the query will get executed will depend on things like available memory, other jobs, how frequently statistics have been updated, etc. Therefore you may get a big delay and then streaming data, or you may get periodic blips followed by small bursts of data.

A way to mitigate the locking is as follows:

a) Have no code on the Metadata tab
b) On the Data Tab test each element that makes up the CellPut that might potentially not exist to see whether it exists or not using DIMIX( Dim, Elem ) = 0
c) As soon as you find an element that doesn't exist, asciioutput the entire record to a file and set a missing elements flag
d) In the Epilog, if the missing elements flag is set, call another process to read the file that you wrote. That Process should use a Metadata tab to add the elements and then a Data Tab to store the values in the conventional way.

The advantage of this approach is that, if none of the elements are new, then there is no need to call the auxiliary process and there is not locking Metadata processing at all. If there are any new elements then you will only load those records that have a new element through the Metadata tab. In the worst case you will make meta data calls for other elements in the same record that already exist while you add the element in the dimension that did not exist. However, as the number of new elements is generally small, this should be minor. As your metadata update is driven from a small text file, there is no waiting for a SQL query to be resolved.

I think that this approach minimises the locking. However, it was implemented before the arrival of version 10 which has the immediate Dimension update statements, that you can use in the Data Tab. You might want to try an approach using those to see which is faster.

Regards

Paul Simon

I have thought about the write to file and read from file methodology. In this case once the data is actually returning Im ok with the lock. Its the wait time for the query to respond that Im trying to avoid. Rather than overdesign something I was just wondering if I could "trick" TM1 into thinking it was going to be performing a TextOutput rather than a dimensional update. Once the data is coming back it would move on past the TextOutput and the acceptable lock would begin.

Its more a question of does this provide the desired result rather than actually looking for a solution. We have thought up a few different solutions to this but simple in most cases is better!