SubsetCreateByMDX and SubsetMDXSet do not always play nice together
- Oct. 29, 2017
Update
If, before we make the dimension static, we ask for the number of elements, it works !
# Result: it WORKS ;-) No idea why this is needed though vDim = 'Department'; vSubset = 'test'; SubsetDestroy( vDim, vSubset ); SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}', vDim ); # THIS LINE DOES THE MAGIC n = SubsetGetSize( vDim, vSubset ); # END OF THE CHANGE n = SubsetMDXSet( vDim, vSubset, '' ); AsciiOutput( 'test MDX.txt', NumberToString( n ));
Introduction
Here is an oddity/bug, so please read this if you tend to use the functions SubsetCreateByMDX and SubsetMDXSet. In fact, the 2 functions work nicely but a particular combination of them, does not.
That particular combination, that I still like to use in my coding, is the following. Suppose you want to make use of the hidden / undocumented third (optional) parameter in the function SubsetCreateByMDX. Usually you specify:
- the subset name
- the MDX expression
If you then add the name of the dimension of the MDX, then the TI process will not error out in case the MDX expression would not return at least 1 element. With only the 2 arguments mentioned about, the process would bomb and stop immediately. Often I prefer that the process continues and I will test on the number of returned elements using SubsetGetSize. So that is one thing. The other is the relatively new function, SubsetMDXSet. Usually you specify:
- the dimension name
- the subset name
- an MDX expression or empty quotes ('')
With empty quotes ('') you ask TM1 to convert the dynamic subset to a static subset, with exactly the same elements as of the time of doing this.
That's it, these are our 2 ingredients in the mix. We want a static subset that does not error out in the TI process in case no element survives the MDX expression. That does not work, unfortunately and you end up with a static, EMPTY subset !
Code examples
Below I show you examples of what works and what does not work. Change the dimension name to your own situation to test it with me:
# Result: subset 'test' exists as a static subset and contains a list of elements. Good !
vDim = 'Department';
vSubset = 'test';
SubsetDestroy( vDim, vSubset );
SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}' );
SubsetMDXSet( vDim, vSubset, '' );
# Result: subset 'test' exists as a dynamic subset and contains a list of elements. Good ! vDim = 'Department'; vSubset = 'test'; SubsetDestroy( vDim, vSubset ); SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}' ); # SubsetMDXSet( vDim, vSubset, '' );
# Result: subset 'test' exists as a dynamic subset and contains a list of elements. Good ! vDim = 'Department'; vSubset = 'test'; SubsetDestroy( vDim, vSubset ); SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}', vDim ); # SubsetMDXSet( vDim, vSubset, '' );
# Result: subset 'test' exists as a static subset BUT IS EMPTY. Yuk !
vDim = 'Department';
vSubset = 'test';
SubsetDestroy( vDim, vSubset );
SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}', vDim );
SubsetMDXSet( vDim, vSubset, '' );
As you can observe, case 4 is not really the intended purpose… Does anyone have a clue ?
Observe that TM1 still applies the MDX expression well:
# Result: the file 'test MDX.txt' contains: "{TM1SubsetAll( [ Department ] )}"
vDim = 'Department';
vSubset = 'test';
SubsetDestroy( vDim, vSubset );
SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}', vDim );
AsciiOutput( 'test MDX.txt', SubsetMDXGet( vDim, vSubset ));
But it goes wrong with SubsetMDXSet:
# Result: the file 'test MDX.txt' contains: 0. That is too little ;-(
vDim = 'Department';
vSubset = 'test';
SubsetDestroy( vDim, vSubset );
SubsetCreateByMDX( vSubset, '{TM1SubsetAll( [ ' | vDim | ' ] )}', vDim );
n = SubsetMDXSet( vDim, vSubset, '' );
AsciiOutput( 'test MDX.txt', NumberToString( n ));
I experimented with permanent vs. temporary subsets in the function SubsetMDXSet, but that did not make it clearer. See: this document.
My tests were done with TM1 10.2.2 FP5 and again with PAL 2.0.2 (build 998). SubsetMDXGet/Set was introduced with TM1 10.2.2 FP1 and temporary subsets/views were introduced with TM1 10.2.2 FP4. I did not test this behaviour with earlier versions of TM1, notably not between FP1 and FP4 of TM1 10.2.2. It is possible that this behaviour is different there. If anyone can test this as well, or in other TM1 versions, please let me know the results.
Anyway, a warned person counts for two !
