TI process locking cube from reading

[kpradeep25]

hi,

One of our cube is getting locked whilst a BI process is running , not letting any user to refresh excel report and browse cube from perspective.

The TM1 top shows status as IX:<cube name against the process name.

I noticed that when I remove feeder statements but keep skipcheck only then it does not lock the cube. below are the complete rule and feeder statement. Do you notice anything wrong here? can it be further optimised?

skipcheck;
['Actual PY']=N:DB('PSP',!PSP Provider,!Domain,'Actual',ATTRS('Time',!Time,'PY'),!PSP Accounts,!Measure PSP);
['Actual PY-1']=N:DB('PSP',!PSP Provider,!Domain,'Actual',ATTRS('Time',!Time,'PY-1'),!PSP Accounts,!Measure PSP);
['Actual to Current Period']=N:IF(ATTRN('Time',!Time,'IndexNumber')<=ATTRN('Time',DB('zSystem Control','Current Month','sValue'),'IndexNumber'),
['Actual'],STET);

[{'Actual PY','Actual PY-1','Actual to Current Period'}]=N:STET;


FEEDERS;
['Actual'] => DB('PSP',!PSP Provider,!Domain,'Actual PY',ATTRS('Time',!Time,'NY'),!PSP Accounts,!Measure PSP);
['Actual'] => DB('PSP',!PSP Provider,!Domain,'Actual PY-1',ATTRS('Time',!Time,'NY+1'),!PSP Accounts,!Measure PSP);
['Actual'] => DB(IF(ATTRN('Time',!Time,'IndexNumber')<=ATTRN('Time',DB('zSystem Control','Current Month','sValue'),'IndexNumber'),'PSP',''),!PSP Provider,!Domain,'Actual to Current Period',!Time,!PSP Accounts,!Measure PSP);

[tomok]

whilst a BI process is running

That's a pretty broad statement. Just what exactly is this "BI Process" doing? Sounds like you need to look here instead of your rules, which IMO look reasonable.

[kpradeep25]

whilst a BI process is running

That's a pretty broad statement. Just what exactly is this "BI Process" doing? Sounds like you need to look here instead of your rules, which IMO look reasonable.

Sorry it's TI process;TI process is basically loading data from data warehouse, It also add new dimension element to two dimensions. But not sure why only these two cubes are being affected as the same dimension ( which are being modified) is used in other cubes as well?

[PlanningDev]

Updating metadata will cause locking. Are you positive that your other cubes aren't affected? Any object related to the dimension you are updating should be locked untill you complete the update.

[tomok]

Sorry it's TI process;TI process is basically loading data from data warehouse, It also add new dimension element to two dimensions. But not sure why only these two cubes are being affected as the same dimension ( which are being modified) is used in other cubes as well?

So, you are loading data into a cube and users are trying to read from the same cube while this is happening? That's not going to work unless you are on 9.5.2 or higher and have parallel interaction enabled.

[PlanningDev]

Even with parallel interaction, if you are running metadata updates as part of the data update then your cubes will get locked while the process is running.

[mvaspal]

if you are running metadata updates

I was wondering which of the following events fall into this category as they are quite commonly used in the Prolog tab even if you do not manipulate dimension structures; I have never had the patience to test it one by one:
SubsetDestroy, SubsetCreate, SubsetCreateByMDX
SubsetDeleteAllElements, SubsetElementInsert
ViewDestroy, ViewCreate
ViewSubsetAssign, ViewExtractSkip...

[whitej_d]

I was wondering which of the following events fall into this category as they are quite commonly used in the Prolog tab even if you do not manipulate dimension structures; I have never had the patience to test it one by one:
SubsetDestroy, SubsetCreate, SubsetCreateByMDX
SubsetDeleteAllElements, SubsetElementInsert
ViewDestroy, ViewCreate
ViewSubsetAssign, ViewExtractSkip...

I'm sure someone will correct me if I'm wrong on this, but I believe that SubsetDestroy, SubsetCreate, SubsetCreateByMDX, ViewDestroy, ViewCreate will cause a system wide lock for the duration of the TI (even if they are in the prologue). This is because they are creating or destroying objects. IBM actually recommend not using these functions if possible in TIs if locking is likely to be an issue in systems. Instead, it's better to just manipulate an existing subset. You can see this if you try to manipulate a subset in a sub-process which you created in a parent process.

SubsetDeleteAllElements, SubsetElementInsert will lock only dependent objects - ie cubes (and dependent cubes) which use the dimensions they are associated with.

I would imagine that the ViewSubsetAssign and ViewExtractSkip would lock the cube and dependent objects, although I'm not 100% on this one.

[PlanningDev]

whilst a BI process is running

That's a pretty broad statement. Just what exactly is this "BI Process" doing? Sounds like you need to look here instead of your rules, which IMO look reasonable.

Sorry it's TI process;TI process is basically loading data from data warehouse, It also add new dimension element to two dimensions. But not sure why only these two cubes are being affected as the same dimension ( which are being modified) is used in other cubes as well?

I wasn't aware that editing subsets in a TI would cause locking although I can see where it's possible. I was speaking more to this statement which says you are add new elements to two dimensions. If you have DimensionElementInsert anywhere in your TI then you are for sure going to have locking.

[kpradeep25]

Apologies for the delay.

I am using 9.5.2 with Parallel Interaction ON. I also run viewcontruct and add dependecncy before the process run. But still, only these two cubes gets locked from read operation. The processes are run from TM1 chore.

I am not sure if TM1 is effective in real-time environment. if yes, what is the best way to minimize locking issue?

[whitej_d]

Hi,

In your case, there's a couple of options I'd suggest to minimize the locking:

1. Put the metadata updates in a separate TI in a separate chore. Make sure the metadata chore runs before the dataload chore. You'll still get locking, but it will be for much shorter as it won't hold the lock for the duration of the data load as well.

2. If this is still too much, due to size of data source or logic involved in creating the elements or otherwise, then have your metadata TI build a dummy dimension which is not part of any cube, then run a separate TI which updates the main dimension from the dummy dimension. Again, you'd have to control the order of execution of these chores to ensure that they run in the correct sequence. If it's only a couple of elements you're adding, then this should be fast enough so that the locking isn't an issue.

[mvaspal]

Hi Joe,

SubsetDeleteAllElements, SubsetElementInsert will lock only dependent objects - ie cubes (and dependent cubes) which use the dimensions they are associated with.

I was just testing this with 10.1.1 FP1 and it was better than I expected: I put SubsetDeletAllElements and SubsetElementInsert in the Prolog of a TI and then a long WHILE so that I can monitor it in Top. Then I ran another TI with SubsetDeleteAllElements and SubsetElementInsert on the same dimension but for different subset and it worked well. It seems that IX locks were put only to the subsets but not the whole dimension.

[whitej_d]

Hi Joe,

SubsetDeleteAllElements, SubsetElementInsert will lock only dependent objects - ie cubes (and dependent cubes) which use the dimensions they are associated with.

I was just testing this with 10.1.1 FP1 and it was better than I expected: I put SubsetDeletAllElements and SubsetElementInsert in the Prolog of a TI and then a long WHILE so that I can monitor it in Top. Then I ran another TI with SubsetDeleteAllElements and SubsetElementInsert on the same dimension but for different subset and it worked well. It seems that IX locks were put only to the subsets but not the whole dimension.

That's interesting and good to know.

Did you try it with ParallelInteraction enabled vs disabled? I'd be interested to know if it's something to do with it.

[mvaspal]

I tried only with PI on

[Steve Rowe]

With PI on lock are triggered by changing dimensions. This includes structure / elements and the bit that gets missed, attributes. (Not sure if it is all attributes or just alias, untested)

I'm not aware of anything else that triggers locks, certainly all the standard data load stuff like building views and subsets doesn't impact.

Cheers,

[ndivine]

With PI on lock are triggered by changing dimensions. This includes structure / elements and the bit that gets missed, attributes. (Not sure if it is all attributes or just alias, untested)

I'm not aware of anything else that triggers locks, certainly all the standard data load stuff like building views and subsets doesn't impact.

Cheers,

Creating views, no. Creating subsets will cause locks.

[mvaspal]

Hi

Creating views, no. Creating subsets will cause locks.

I tried it now with ViewDestroy - ViewCreate - ViewSubsetAssign lines in the Prolog, then the long WHILE in the first TI.

Then in the Prolog of another TI, ViewDestroy and ViewCreate on the same cube but different view name. The lock is there, the second TI waits with IXC.