Unassign Subset from View - Back to "All"

[John Hammond]

Code: Select all

#ViewCreate('cbsMMRTrialBalance','dynView') ;

#ViewSubsetAssign('cbsMMRTrialBalance','dynView', 'dimChartOfAccounts', 'TrialBalanceAll');
#ViewSubsetAssign('cbsMMRTrialBalance','dynView', 'dimChartOfAccounts', 'All');
#ViewSubsetAssign('cbsMMRTrialBalance','dynView', 'dimChartOfAccounts', 'dimChartOfAccounts');
ViewSubsetAssign('cbsMMRTrialBalance','dynView', 'dimChartOfAccounts', '');

This code creates a view and then assigns a subset to one of the dimensions. I then want the view to return back to the state it was before I assigned the subset.

I tried this by using the most obvious arguments but all got the message

Error: Prolog procedure line (10): Subset not found in dimension

I suppose the answer could be to dynamically create the view and dispose of it once finished but I would like some way to return my view to a pristine condition since even the hidden view designer in the view datasource in TI can't achieve this!

As always any suggestions acknowledged and appreciated.

[Martin Ryan]

You could set up a one off view called "zTIAll" that you always use as the source for the TI process and then create the actual view that is to be used in the Prolog of the TI process then using DataSourceView='NewView'; and delete the view when you're finished with it. This won't affect the "zTIAll" view at all.

The only other way to get around this would be to delete and rebuild the view you wish to leave as it was originally, using the Epilog.

Martin

[John Hammond]

Thanks Martin - the problem I have is that when you use a view to specify input to a TI proc then that view needs to exist prior to start of the TI proc and cannot be created on the fly.

Code: Select all

389,100
370,0
361,1
362,1
363,0.
364,0.
365,
366,
367,0
376,0
375,c:0.00
374,6
7,dimChartOfAccounts
6,DynSubset
7,dimCounterParty
6,ALL
274,
281,0
282,
7,dimFunction
6,ALL
274,
281,0
282,
7,dimOrganisation
6,ALL
274,
281,0
282,
7,dimPeriod
6,ALL
274,
281,0
282,
7,dimProject
6,ALL
274,
281,0
282,
360,1
7,dimYear
6,ALL
274,
281,0
282,
371,1
7,dimTier
6,ALL
274,
281,0
282,
373,6
1
1
1
1
1
1
372,0
372,00
384,0
385,0
377,4
0
0
0
0
378,0
382,255
379,8
0
0
0
0
0
0
0
0
11,20091120171805
381,0

The crazy thing is that the structure on disk of a view is very simple being what subset applies to each dimension. I tried 'ALL' as an argument but that did not work. What if I create a dummy subset called ALL?

[John Hammond]

This works !

SubsetCreate( 'dimChartOfAccounts','ALL') ;
ViewSubsetAssign('cbsMMRTrialBalance','dynView', 'dimChartOfAccounts','ALL');
# DestroySubset( 'dimChartOfAccounts','ALL') ;

If you do this you get a Subset called ALL that is undeletable or editable. Although no members have been added it has All of the dimension in there. It is not an MDX based subset but it dynamically updates once you change the underlying dimension. It looks like it has been intentionally designed.

What you would do is create a dimension and then use TI to create subset ALL which you can then use to return views to their pristine state both programatically and using the view designer in TI and in cube viewer. This subset would be very useful where you have a dimension that has multiple or incomplete hierarchies and you want to make sure you have everything.

The question is: Useful undocumented feature or dangerous fiddling with TM1 internals?

Please can some of the more experienced users let me know what they think.

[Martin Ryan]

I've noticed that if you select subset as the source for a TI process then 'All' is an option. I've not tried the method you've used though and I wouldn't trust it.

Thanks Martin - the problem I have is that when you use a view to specify input to a TI proc then that view needs to exist prior to start of the TI proc and cannot be created on the fly.

A view needs to exist prior to start, not necessarily the view. You can set up a dummy view, like zTIAll, that is the source for the view including the number and names of the variables, then you can set up the actual view that you want to work with in the Prolog programatically, use DataSourceView='NewView'; to reassign the source view that is actually what you want to work with. Then in the Epilog you can delete the view and any subsets you created programatically.

Cheers,
Martin

[John Hammond]

Got this from the manual which is pretty close to what you are talking about.

DatasourceCubeview
This TurboIntegrator local variable sets the view to process if the DatasourceType is 'VIEW'.
Syntax
DatasourceCubeview='ViewName';
Arguments
ViewName The name of the view to be processed. This must be an existing view of the cube specified by the DataSourceNameForServer variable.

I do get what you are saying and it is a very neat way of handling the problem. By not using transient views we are cluttering up the user experience with loads of TI views.

Do you know if there is any way of making these views local so only our TI service account user id can see them?

Mucho Thanks for this.

[Alan Kirk]

I've noticed that if you select subset as the source for a TI process then 'All' is an option. I've not tried the method you've used though and I wouldn't trust it.

Thanks Martin - the problem I have is that when you use a view to specify input to a TI proc then that view needs to exist prior to start of the TI proc and cannot be created on the fly.

A view needs to exist prior to start, not necessarily the view.

That's not strictly necessary either. You need to have the view in existence at the time that you develop the process so that you can get the variable names, but having done that you can delete the view and have it created completely by code, and destroyed completely by code, at run time. You can even subsequently edit the process without the view existing though it's best to stay away from the Variables tab when doing so. There's no real need to leave the theoretical zTIAll in existence after the initial development is done.

You can set up a dummy view, like zTIAll, that is the source for the view including the number and names of the variables, then you can set up the actual view that you want to work with in the Prolog programatically, use DataSourceView='NewView'; to reassign the source view that is actually what you want to work with. Then in the Epilog you can delete the view and any subsets you created programatically.

I prefer doing my subset and view creation and more particularly my view and subset deletion (in that order) in separate processes within the chore. Primarily this is because if the code in the data tab crashes out for any reason, the latter processes can still run to delete the runtime view(s) and subset(s). We also append an execution number (derived from a control cube) to the dynamically created subset names to ensure that if for some reason the subset is locked and can't be deleted (and therefore recreated at the next runtime), the chore can still run.

[Alan Kirk]

Got this from the manual which is pretty close to what you are talking about.

DatasourceCubeview
This TurboIntegrator local variable sets the view to process if the DatasourceType is 'VIEW'.
Syntax
DatasourceCubeview='ViewName';
Arguments
ViewName The name of the view to be processed. This must be an existing view of the cube specified by the DataSourceNameForServer variable.

I do get what you are saying and it is a very neat way of handling the problem. By not using transient views we are cluttering up the user experience with loads of TI views.

Do you know if there is any way of making these views local so only our TI service account user id can see them?

There's no need, and views which are used for TI need to be public, not private, anyway.

If you:
- Create any necessary subsets using SubsetCreate and SubsetElementInsert;
- Create the view using ViewCreate and ViewSubsetAssign (any dimensions which don't have a subset explicitly assigned will use the implicit "ALL" subset);
- Use the relevant functions to suppress zeroes, consolidations and rule values (according to taste) of that view;
- In the prolog of your data processing process, assign the newly created view name as the data source using the DatasourceCubeview function;
- Process your data;
- Either in the Epilog or, as is my preference, in subsequent processes use ViewDestroy to delete the view and SubsetDestroy to delete the subsets,

then the view and subsets will only exist for as long as they're needed and the users will probably never see them.

[lotsaram]

SubsetCreate( 'dimChartOfAccounts','ALL') ;
....
If you do this you get a Subset called ALL that is undeletable or editable. Although no members have been added it has All of the dimension in there. It is not an MDX based subset but it dynamically updates once you change the underlying dimension. It looks like it has been intentionally designed.

Hi John,

Looks like Martin & Alan have already fully covered off the point that the view datasource can (and should!) be created on the fly and does not need to exist prior to the TI being run.

The other point worth making is that subset ALL is implicit and exists for all dimensions, you do not need to create it or declare it. In fact you also do not need to assign it to a TI datasource view either as this subset is implicitly assigned to each dimension on ViewCreate, you only need to use ViewSubsetAssign if you wish to use a subset other than subset ALL for the dimension in question.