Create a subset by MDX based on another subset

[tosca1978]

TM1 v10.1.0

Hi all,

I have created a TI with the following script in the Metadata tab:

Code: Select all

IF(SubsetExists('T3',vBLI_DRILL)=0);
SubsetCreate('T3', vBLI_DRILL);
ENDIF;

SubsetElementInsert('T3', vBLI_DRILL, vPROJECT,1);

SubsetCreatebyMDX(vBLI_nLevel, '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[T3].['|vBLI_DRILL|']}, ALL, RECURSIVE )}, 0)}');

In the variable I have the following:

vBLI (a budget line item code)
vBLI_DRILL (has the following expression:

Code: Select all

vBLI_DRILL=vBLI|' (DRILL)';

vBLI_nLevel (has the following expression:

Code: Select all

vBLI_nLevel=vBLI|' (nLevel)';

The first part of the code creates an empty susbet for each vBLI with the name of the BLI followed by " (Drill)"

The second part inserts vProject to the subset. vProject is the lowest level of detail in the source data, but is a consolidated element that already exists in the T3 dimension. Therefore all of it's children gets added to the subset too.

The last part of the code attempts to create a subset by mdx based on the static subset that has been created and names it the BLI code followed by " (nLevel)". I am trying to get to a list of n level elements under the vProjects and save as a subset.

The mdx code works fine in subset editor when you substitute the ['|vBLI_DRILL|'] for the subset name. However the TI fails with the following error message:

Error: MetaData procedure line (32): Unable to register subset

.

Any advice would be much appreciated.

[rmackenzie]

The subset you are trying to use in the SubsetCreateByMdx command has not yet been registered on the TM1 server. SubsetCreate has created an unregistered subset which cannot be accessed by other commands.

One option is to create all the vBLI_DRILL type subsets in one pass of the datasource and then in another pass you can then create the vBLI_nLevel subsets based of the vBLI_DRILL ones.

[tosca1978]

Hi rmackenzie,

thanks for your reply. That makes perfect sense.

I have now tried the following:

1. Moved

Code: Select all

SubsetCreatebyMDX(vBLI_nLevel, '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[T3].['|vBLI_DRILL|']}, ALL, RECURSIVE )}, 0)}');

to the epilogue tab of the same TI.

This results in the same error message "Unable to register subset". However, one (and only one) of the nLevel subsets does get created (and it contains the correct list of nLevel elements).

2. I removed the SubsetCreatebyMDX code from this TI all together. I created another TI with the same data source and variables and added the SubsetCreatebyMDX code to the MetaData tab. I then added ExecuteProcess('DIM_UPDATE_T3_SUBSETS'); to the epilogue tab of the original TI.

The results are the same - same error message and one subset gets created (I deleted this before testing to see if it was being created).

Any ideas?

Many thanks.

[rmackenzie]

1. Moved

Code: Select all

SubsetCreatebyMDX(vBLI_nLevel, '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[T3].['|vBLI_DRILL|']}, ALL, RECURSIVE )}, 0)}');

to the epilogue tab of the same TI.

This results in the same error message "Unable to register subset". However, one (and only one) of the nLevel subsets does get created (and it contains the correct list of nLevel elements).

Presumably, the one subset that does get created is because vBLI_nLevel and vBLI_DRILL still retain values going into the Epilog, per the last row in the data source. As you found out, this is doing nothing for you.

2. I removed the SubsetCreatebyMDX code from this TI all together. I created another TI with the same data source and variables and added the SubsetCreatebyMDX code to the MetaData tab. I then added ExecuteProcess('DIM_UPDATE_T3_SUBSETS'); to the epilogue tab of the original TI.

The results are the same - same error message and one subset gets created (I deleted this before testing to see if it was being created).

This is what I was thinking of, but obviously you still have an issue which is hard to pinpoint with seeing the code etc (too difficult for the forum). One thing to note is that, IIRC, SubsetCreateByMdx will error when you try and issue that statement against an existing subset.

[tosca1978]

Thanks rmackenzie.

One thing to note is that, IIRC, SubsetCreateByMdx will error when you try and issue that statement against an existing subset.

So you cannot use and existing subset as the basis of a new subset if creating by MDX? Is there any other way? I know that I could create a subset based on another subset if it was the data source but this is no good to me as I would need a TI per subset. Do you think I could use the a view of the }ElementAttributes_T3 cube as the data source and build the subsets that way? Or am I clutching at straws!

Thanks again.

[rmackenzie]

Thanks rmackenzie.

One thing to note is that, IIRC, SubsetCreateByMdx will error when you try and issue that statement against an existing subset.

So you cannot use and existing subset as the basis of a new subset if creating by MDX? Is there any other way?

Sorry, I wrote that badly. What I meant was that if you already have a subset called 'SomeNewSubset' then this will error:

Code: Select all

SubsetCreateByMdx ( 'SomeNewSubset', '{[SomeDimension].[SomeSubset]}' );

If SomeSubset exists already then that is fine (and actually a precondition to this doing what you want). I was thinking your errors might be because your dimension already has subsets with the same names as the ones you are trying to create with your SubsetCreateByMdx statements. Because it is all variable driven it is hard to work out what the problem is...