Page 1 of 1
error handling of SubsetCreateByMDX when 0 elements returned
Posted: Thu May 05, 2011 4:02 am
by 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
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu May 05, 2011 5:39 am
by 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,
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu May 05, 2011 6:30 am
by lotsaram
stex2727 wrote: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;
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu May 05, 2011 8:32 am
by 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.
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu May 05, 2011 8:54 am
by lotsaram
qml wrote: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 ....)
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu May 05, 2011 8:01 pm
by Steve Rowe
Nice tip qml, am also hitting this issue and will see if I can apply it to my TIs.
Cheers
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Fri May 06, 2011 3:57 am
by stex2727
Thanks guys, got it working well now. I'd recommend this approach to others as well
Regards
Steve
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Tue Feb 17, 2015 11:41 am
by buncikp
lotsaram wrote:qml wrote: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.
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Tue Feb 17, 2015 9:07 pm
by 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.
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu Feb 19, 2015 11:48 am
by buncikp
Duncan P wrote: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, '');
Re: error handling of SubsetCreateByMDX when 0 elements retu
Posted: Thu Feb 19, 2015 12:14 pm
by 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;