TM1 Forum

My first post - please be gentle!

I have a TI process called from VBA in an Excel Report.
The user specifies a date range, say 1 May to 15 May the process creates a subset by MDX containing all the individual dates, these elements are then assigned to the consolidation 'Date_Range' so the report can be run for this specific date range.

Now users are asking that more reports be created (for some reason users at this site don't query cubes themselves) with the facility to select a date range. This raises the possibility that the process will be executed simultaneously with different parameters. Is it possible to ensure the process can't be run if it is in use by another user? I've read what I can on locking but I'm still unsure as to what happens.

The TI runs quickly and I have had success running the TI simultaneously from 2 PCs but I need to know if this was just a fluke.

As your TI is acting on meta data and changing a subset and a consolidation point this action will envoke locking and cause any other thread (be it user action, another process or the same process called by another user) which attempts to modify the same objects to wait until the 1st process has completed and released its lock. So in that sense you don't need to do anything the TI will always execute with the parameters given by the user, you can't get a hybrid.

However if the report that I'm assuming the VBA then builds refrences either the subset or the date_range consolidation point and the definitions of these have changed in the meantime since the report was created due to the process being run with fifferent params by another user then the report could verry well return incorrect or unexpected results from the user's perspective. If this is likely or even a possibility then you should look at options like concatenating the user ID into the object names that get created to ensure each user sees what they expect.

Thanks for that - it's exactly what I needed to know!

After creating the consolidation the next, and final line, in the VBA is simply a TM1REFRESH so I don't think the issue would arise.

I did consider using the usernames but this wasn't workable due to another process that runs over the dimension.

Thanks again

marboy wrote:Thanks for that - it's exactly what I needed to know!

After creating the consolidation the next, and final line, in the VBA is simply a TM1REFRESH so I don't think the issue would arise.

I did consider using the usernames but this wasn't workable due to another process that runs over the dimension.

Thanks again

No. This is precisly where the issue might arise! Unless the report is also being hardcoded at this point and all TM1 references removed then it the user subsequently does a refresh themselves in Excel (Alt + F9 or click rebuild sheet button) then the report will be refreshed according to the current definition of the subset / consolidation point and it could be quite confusing for users to see their report change on them ...

I think I've got that covered.
The users are accustomed to recalculating worksheets by pressing a 'Calculate' button on the report. The code to call the TI and build the subset is in this macro.