TI View / Subset Creation Error

[Steve Vincent]

I've started to use some of Alan K's ideas within my own models ( ) and begun writing TIs that create views and subsets, do what they need to then delete them all afterwards. Not had a chance to double check yet but i think v9.0 SP3 has a bug in it, relating to subsets used in a view and a manual abort of the TI.

My process was running fine a few times, but i'd made a mistake in which view i was using as it's datasource. One such run, i realised the error and manually cancelled the TI using the "cancel" button that appears when it's running. I fixed the error, re-ran it and the TI failed complaining that i couldn't delete a subset that's already used in a view. My code explicitly deletes the temp view first before meddling with the subsets, and had it been my code it would have failed 2nd time around, not the 5th or 6th. Upon checking and rechecking i was sure it wasn't being used by anything, but still it wouldn't run. I resorted to restarting the service and surprise surprise it's fine again. I'm going to try and prove the error later this afternoon, but does anyone else have that issue in other versions, or is it specific to 9.0? It's not worth logging if it doesn't happen in 9.1, but worth thinking about if anyone else does something similar in the TIs. My code is below if it's of any help (all from the prolog);

Code: Select all

### PARAMETERS - USER DEFINED ###
Dim1 = 'Version';
Dim2 = 'Demand Measures';
ElName1 = 'Current';
ElName2 = 'Man Hours';
ElNameZero = 'Demand Headcount';
Cube = 'Demand';

### PARAMETERS - SYSTEM DEFINED ###
ViewName = 'zTemp';
SubName = 'zTemp';
ViewZero = 'zTempZero';
SubZero = 'zTempZero';

### DON'T LOG ANY CHANGES ###
CubeSetLogChanges(Cube, 0);


### CREATE TEMP VIEW ###
IF ( ViewExists ( Cube , ViewName ) = 1);
    ViewDestroy ( Cube , ViewName );
ENDIF;
IF ( ViewExists ( Cube , ViewZero ) = 1);
    ViewDestroy ( Cube , ViewZero );
ENDIF;

ViewCreate ( Cube, ViewName );
ViewCreate ( Cube, ViewZero );

ViewExtractSkipZeroesSet ( Cube , ViewName , 1 );
ViewExtractSkipCalcsSet ( Cube , ViewName , 1 );
ViewExtractSkipRuleValuesSet ( Cube, ViewName, 1 );

ViewExtractSkipZeroesSet ( Cube , ViewZero , 1 );
ViewExtractSkipCalcsSet ( Cube , ViewZero , 1 );
ViewExtractSkipRuleValuesSet ( Cube, ViewZero, 1 );

### CREATE TEMP SUBSETS ###
### delete if already there ###
IF ( SubsetExists ( Dim1 , SubName) = 1);
    SubsetDestroy ( Dim1, SubName );
EndIF;
IF ( SubsetExists ( Dim2 , SubName ) = 1);
    SubsetDestroy ( Dim2, SubName );
EndIF;

#IF ( SubsetExists ( Dim1 , SubZero) = 1);
#    SubsetDestroy ( Dim1, SubZero );
#EndIF;
IF ( SubsetExists ( Dim2 , SubZero ) = 1);
    SubsetDestroy ( Dim2, SubZero );
EndIF;


### create new ones ###
#SubsetCreatebyMDX('zTemp', '{TM1SUBSETALL( [' | Dim1 | '] )}' );
#SubsetCreatebyMDX('zTemp', '{TM1SUBSETALL( [' | Dim2 | '] )}' );
SubsetCreate ( Dim1, SubName );
SubsetCreate ( Dim2, SubName );

#SubsetCreate ( Dim1, SubZero );
SubsetCreate ( Dim2, SubZero );

### fill new ones ###
SubsetElementInsert ( Dim1, SubName , ElName1, 1);
SubsetElementInsert ( Dim2, SubName , ElName2, 1);
SubsetElementInsert ( Dim2, SubZero , ElNameZero, 1);


### ASSIGN TEMP SUBSETS TO TEMP VIEW ###
ViewSubsetAssign ( Cube , ViewName , Dim1, SubName );
ViewSubsetAssign ( Cube , ViewName , Dim2, SubName );

ViewSubsetAssign ( Cube , ViewZero , Dim1, SubName );
ViewSubsetAssign ( Cube , ViewZero , Dim2, SubZero );

#ProcessQuit;

#Zero a view if required
ViewZeroOut ( Cube , ViewZero );

[TomBr]

Hi Steve,

One way of getting around this happening in the future might be to timestamp the View and Subset names to make them unique.

Add the line

sNow= TIMST( now(), '\Y\m\d\h\i\s');

to the beginning of the Prolog and then append to the View and Subset Names i.e.

### PARAMETERS - SYSTEM DEFINED ###
ViewName = 'zTemp'|sNow;
SubName = 'zTemp'|sNow;
ViewZero = 'zTempZero'|sNow;
SubZero = 'zTempZero'|sNow;

Hope this helps,

Tom

[ScottW]

You could do this minor tweak to your code to avoid the error.

Code: Select all

###~~ PROLOG  ~~###
IF ( SubsetExists ( Dim1 , SubName ) = 1);
    SubsetDeleteAllElements ( Dim1, SubName );
Else;
    SubsetCreate ( Dim1, SubName );
EndIF;

###~~ EPILOG  ~~###
IF ( ViewExists ( Cube , ViewName ) = 1);
    ViewDestroy ( Cube , ViewName );
ENDIF;
IF ( SubsetExists ( Dim1 , SubName) = 1);
    SubsetDestroy ( Dim1, SubName );
EndIF;

[Steve Vincent]

Thanks for the ideas, but neither is going to solve the issue

Timestamping would just leave me with loads of unnecessary clutter if the TI was cancelled and run again. Deleting all elements wouldn't solve the problem that TM1 thinks the subset is used in a view when it's not. My code is fine unless the TI is inturrupted, then TM1 gets confused and thinks a subset is in use when its blatently not and only a restart seemed to solve it. I even proved the fact by opening the view with notepad - it made no mention of the subset yet the TI wouldn't re-run correctly.

I'm going to try and get the Cognos Community site to work and allow me to download the latest 9.1, put my models in it and try the same thing again. If that does the same then it looks like a fault and i'll log it, cheers for the help.

[Alan Kirk]

I've started to use some of Alan K's ideas within my own models

Hey compadre, you ain't using MY ideas there! There are two fundamental things that aren't compatible with the way we do it:
(a) I never create or delete views and subsets in the same process. This way can (potentially, not always but usually at the worst possible time) lie locking madness.
(b) My subsets (not my views) are always uniquely named to avoid this situation. We don't use a timestamp, we use an execution number (which is incremented in our control cube each time the chore is run), but the principle is the same. The other advantage of using an execution number is that it's possible to create a process which will loop through the subsets and "clean up" any which are left over from past executions if anything goes catastrophically wrong.

I have to admit, we never manually cancel executions. (To be honest I'm not even sure whether the Cancel button works in 8.2.12.) I'm also not sure whether it would nuke the chore or just the current process if we did so; if it stopped the chore, then my standard code wouldn't clean up after itself because the processes which delete the view(s) and subset(s) wouldn't execute. In such a case we'd need to either clean up manually or run a "sweeper" process as alluded to above.

However, this is all bye-the-bye... for there is something that you should probably try first.

What I've noticed is that if I'm executing a chore manually (i.e. process by process) to test it out, if I so much as manually look at how one of the views is defined then the subsets in that view will somehow become locked even after I've deleted the view. And somehow that locking is done by my client session. If I try to delete the subset, it will tell me that it's in use and I can't. However if I shut down Excel and restart it (I think in one case I had to reboot the PC too, though that may have been from another issue), then log on to TM1 again, I can delete the subset. Obviously what you did with restarting the service would have a similar effect, but trust me on this... next time it happens, reboot your Excel session and try to delete the subset again. If that doesn't work, reboot your PC and try to delete the subset again. My bet is that on one or the other occasion, it'll do it.

Other than that, definitely go for the "subsets named by execution number" method, and use sweeper processes (which in our case are essentially just the same processes that delete the subsets normally, except that they loop from 1 to the highest execution number) to tidy up should you need to.

[ScottW]

Deleting all elements wouldn't solve the problem that TM1 thinks the subset is used in a view when it's not

Actually Steve I think this will solve your problem, without resorting to uniquely named subsets for each time the TI is run. (I would still advise to "uniquely" name the subset by some concatenation of view, dimension, TI name or purpose etc). The problem is that TM1 (erroneously) thinks that the subset is being used by a view when it isn't and therefore won't let you destroy the subset. Rather than destroying the subset if it exists if you delete all elements instead this will achieve the same result as a subsetdestroy + subsetcreate with the same name combination and the code will run without errors even if interrupted and TM1 thinks the subset is in use when it isn't.

[Alan Kirk]

Rather than destroying the subset if it exists if you delete all elements instead this will achieve the same result as a subsetdestroy + subsetcreate with the same name combination and the code will run without errors even if interrupted and TM1 thinks the subset is in use when it isn't.

It should in theory, but there's a potential gotcha to watch for there. I'm not sure whether this still happens in later versions (since I've put code in mine to explicitly check that a subset has at least 1 element on creation), but I recall that in 7.1.4 accessing a TI-created subset which didn't have any elements (or a view containing such a subset) could make your server go bye-byes.

It wouldn't be a problem if both the depopulate and repopulate components of the code were executed, but if all of the elements were removed then the process stopped before new elements could be added in... you MAY have a ticking time bomb sitting there.

[ScottW]

Is anyone still using 7.1?

[Alan Kirk]

Is anyone still using 7.1?

That question came up in the old forum once... I recall that much to everyone's surprise, one user admitted to still being on version 6.

As I mentioned, the bug that I referred to MAY have been fixed in later versions... but I find it unsafe to put money on it, particularly when crashes are triggered by "but it wasn't designed to do that" events (such as the presence of an empty subset).

[ScottW]

In any case I'm fairly certain that ViewExists and SubsetExists were only introduced around 8.3 ...

[Alan Kirk]

In any case I'm fairly certain that ViewExists and SubsetExists were only introduced around 8.3 ...

That's possible; I can't find them in 8.2.12.

It doesn't matter if you're just destroying a view or subset; it's possible to use the relevant destroy command on a non-existent object without TI being unduly upset by it.

But I agree that if you can't test for the existence of a subset before attempting to add elements to it the results could be more... unfortunate.

(SubsetGetSize is present in 8.2.12, but errors if the subset doesn't exist so that's not a substitute.)

[Steve Vincent]

Thanks guys, will have a further dabble with all this later on today if i get a chance. I'm sure i've had issues with views based on subsets with no elements before, but i can't remember the context of that one or what happened, hence my reluctance to try again.