error handling of SubsetCreateByMDX when 0 elements returned

[stex2727]

Is there a way to handle or trap the error when you attempt a SubsetCreateByMDX and it returns 0 elements. In my case its a simple filter where none of the elements meet the criteria. I'm after a test of likely number of elements created so I can skip these occurances, (rather than a dirty fat error).

Error details

Error: Prolog procedure line (52): Could not create dynamic subset:
expression:
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [ABC_AllocationFrom] )}, "*922129*")}

Cheers in advance
Steve

[Olivier]

The only way i can think of would be to test earlier in the process on the components of the mdx statement.

In some cases you migth find more reliable to build dynamicaly the subset without using mdx as well.
( i.e. using a loop to collect elements and then test on subset size at the end).

Not sure how far you can go in mdx subset error handling.

Hope this helps,

[lotsaram]

Is there a way to handle or trap the error when you attempt a SubsetCreateByMDX and it returns 0 elements. In my case its a simple filter where none of the elements meet the criteria. I'm after a test of likely number of elements created so I can skip these occurances, (rather than a dirty fat error).

Error details

Error: Prolog procedure line (52): Could not create dynamic subset:
expression:
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [ABC_AllocationFrom] )}, "*922129*")}

Cheers in advance
Steve

Steve, this lack of functionality with CreateBxMDX has always irked me. You would think a test of whether an expression evaluates as a null set would not be too difficult to add in but unfortunately it has never been done. I have requested this as an enhancement a number of times but never got any respose other than the ticket was closed as it had been "moved to development". The only solution is to first loop through your dimension and do the relevant test. The code below would do the trick.

Code: Select all

## Set up variables
sDim = 'ABC_AllocationFrom';
sExpr = '922129';
sSub = 'Elements containing ' | sExpr;
sMDX = '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [' | sDim | '] )}, "*' | sExpr | '*")}';
nMax = DimSiz(sDim);
nCount = 1;
nTest = 0;

## Test whether any elements pass the test
While(nCount <= nMax);
  sEle = DimNm(sDim, nCount);
  IF( Scan(sExpr, sEle) > 0 );
    nTest = nTest + 1;
  EndIF;
  nCount = nCount + 1;
End;

## Create the dynamic subset
IF( SubsetExists(sDim, sSub) = 0 & nCount >= 1 );
  SubsetCreatebyMDX(sSub, sMDX);
EndIF;

[qml]

My approach is to always make sure the MDX does not return an empty set by always glueing a "dummy" element at the end of the MDX statement. This dummy could be a consolidation that you can later filter out or just an unused element, whatever works in that particular case. Then once the subset is created it's easy to test whether the subset contains only this one element (which means that for practical purposes it's empty), or more elements.

[lotsaram]

My approach is to always make sure the MDX does not return an empty set by always glueing a "dummy" element at the end of the MDX statement. This dummy could be a consolidation that you can later filter out or just an unused element, whatever works in that particular case. Then once the subset is created it's easy to test whether the subset contains only this one element (which means that for practical purposes it's empty), or more elements.

I like that. I might use it in future.

Steve - this approach might look something like this ...

Code: Select all

## Set up variables
sDim = 'ABC_AllocationFrom';
sExpr = '922129';
sSub = 'Elements containing ' | sExpr;
sMDX = '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [' | sDim | '] )}, "*' | sExpr | '*")}';
sTempEle = 'zDummy_' | sExpr;

## Create the dynamic subset
DimensionElementInsert(sDim, '', sTempEle, 'N');
IF( SubsetExists(sDim, sSub) = 0 );
  SubsetCreatebyMDX(sSub, sMDX);
EndIF;
DimensionElementDelete(sDim, sTempEle);

(warning, completely untested ....)

[Steve Rowe]

Nice tip qml, am also hitting this issue and will see if I can apply it to my TIs.
Cheers

[stex2727]

Thanks guys, got it working well now. I'd recommend this approach to others as well

Regards
Steve

[buncikp]

My approach is to always make sure the MDX does not return an empty set by always glueing a "dummy" element at the end of the MDX statement. This dummy could be a consolidation that you can later filter out or just an unused element, whatever works in that particular case. Then once the subset is created it's easy to test whether the subset contains only this one element (which means that for practical purposes it's empty), or more elements.

I like that. I might use it in future.

Steve - this approach might look something like this ...

Code: Select all

## Set up variables
sDim = 'ABC_AllocationFrom';
sExpr = '922129';
sSub = 'Elements containing ' | sExpr;
sMDX = '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [' | sDim | '] )}, "*' | sExpr | '*")}';
sTempEle = 'zDummy_' | sExpr;

## Create the dynamic subset
DimensionElementInsert(sDim, '', sTempEle, 'N');
IF( SubsetExists(sDim, sSub) = 0 );
  SubsetCreatebyMDX(sSub, sMDX);
EndIF;
DimensionElementDelete(sDim, sTempEle);

(warning, completely untested ....)

This approach works well, however when parallel interaction is considered the code will sync processes on the dimension being altered by DimensionElementInsert.

[Duncan P]

There was a feature put in that SubsetCreateByMDX could take an additional argument of the dimension name to apply the subset definition to if the MDX didn't return any elements.

It was put in about 2 to three years ago and I think it's in 10.1.? but definitely in 10.2.

It hadn't been documented when I left IBM a year ago and I'm guessing that it still hasn't but I bet they haven't taken it out. Try it.

[buncikp]

There was a feature put in that SubsetCreateByMDX could take an additional argument of the dimension name to apply the subset definition to if the MDX didn't return any elements.

It was put in about 2 to three years ago and I think it's in 10.1.? but definitely in 10.2.

It hadn't been documented when I left IBM a year ago and I'm guessing that it still hasn't but I bet they haven't taken it out. Try it.

My observation is that combining both SubsetCreateByMDX(SubName, MDX_Expr, DimensionName) [with 3rd undocumented parameter DimensionName] and SubsetMDXSet(SubName, DimensionName, '') yields empty subset SubName. If you omit the DimensionName from SubsetCreateByMDX, the dynamic subset will become static and will be populated correctly. Is it a bug or feature? It's shame that the two undocumented features don't like to coexist. I have tried to put SubsetMDXSet to epilog of the process with no success. Trying to put SubsetCreateByMDX to another child process doesn't solve the problem as well.
Compare:
Case 1) Result = empty static subset SubName, empty subset will not lead to process error
SubsetCreateByMDX(SubName, MDX_Expr, DimensionName);
SubsetMDXSet(SubName, DimensionName, '');

Case 2) Result = correctly populated static subset, empty subset will raise process error
SubsetCreateByMDX(SubName, MDX_Expr);
SubsetMDXSet(SubName, DimensionName, '');

[declanr]

I still haven't played to much with MDXGet and MDXSet but I still use the old method that I always have and it's never presented a problem.
The optional parameter in SubsetCreateByMDX has been used every day pretty much by me since DuncanP mentioned it a couple of years ago.

Code: Select all

If ( SubsetExists ( sDimName, sSubsetName ) = 1 );
	SubsetDestroy ( sDimName, sSubsetName );
EndIf;
SubsetCreateByMDX ( sSubsetName, sMDX, sDimName );

nLast = SubsetGetSize ( sDimName, sSubsetName );
If ( nLast > 0 );
	sLast = SubsetGetElementName ( sDimName, sSubsetName, nLast );
	SubsetElementDelete ( sDimName, sSubsetName, nLast );
	SubsetElementInsert ( sDimName, sSubsetName, sLast, nLast );
EndIf;