Handling empty dynamic subsets in TI's

Post Reply
Nick_Blommaerts
Posts: 17
Joined: Tue May 24, 2011 4:39 pm
OLAP Product: Cognos TM1
Version: 9.x + 10.x
Excel Version: 2003+2007

Handling empty dynamic subsets in TI's

Post by Nick_Blommaerts » Mon Jun 18, 2012 3:26 pm

Hello all.

I was wondering if anyone knew a good way to handle the problem of having an empty subset on creation by MDX in order to create a view on a cube (for copying purposes, for example). Long story short, I am creating the subset based on a certain value in one of the dimension's attributes, because the data in a cube for those specific elements need to be manipulated by my TI. Needless to say, if no such elements exist, the TI should not be running and I would definitely not want to be getting an error to say the subset I tried to use is empty.

The things I am trying to avoid are:
- Inserting a dummy in the subset on top of the dynamically chosen ones
- Going through all elements with an extra TI prior to the TI that creates the subset to see if at least one element has the attribute value I am looking for

Both options above should work but I find them a bit impractical (I don't want to be creating a dummy just for this/my dimension is quite big so looping through is something I'd rather avoid). Since proper error handling in TM1 is more or less out of the question, I was wondering if there is a special function I am unaware of, or anything simple I am overlooking for one reason or another.

Thanks in advance.

lotsaram
MVP
Posts: 3307
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Handling empty dynamic subsets in TI's

Post by lotsaram » Mon Jun 18, 2012 4:07 pm

Nick_Blommaerts wrote:The things I am trying to avoid are:
- Inserting a dummy in the subset on top of the dynamically chosen ones
- Going through all elements with an extra TI prior to the TI that creates the subset to see if at least one element has the attribute value I am looking for

Both options above should work but I find them a bit impractical (I don't want to be creating a dummy just for this/my dimension is quite big so looping through is something I'd rather avoid). Since proper error handling in TM1 is more or less out of the question, I was wondering if there is a special function I am unaware of, or anything simple I am overlooking for one reason or another.

Thanks in advance.
No unfortunately the things you are trying to avoid are the only way to do it.
(maybe this is what you meant but other approach is to insert the dummy BEFORE the subset create by MDX so that the MDX cannot fail as it will return at least one member, then you could just delete all such dummys at the very end, it is a not too messy a workaround as far as workarounds go).

declanr
MVP
Posts: 1626
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Handling empty dynamic subsets in TI's

Post by declanr » Mon Jun 18, 2012 4:22 pm

Could you not just add an if function into the TI:

If ( SubsetGetSize ( Dim, Subset ) =0 );
ProcessQuit;
EndIf;

Unless a subset with 0 elements cause the SubsetGetSize to fail somehow?
Declan Rodger

Nick_Blommaerts
Posts: 17
Joined: Tue May 24, 2011 4:39 pm
OLAP Product: Cognos TM1
Version: 9.x + 10.x
Excel Version: 2003+2007

Re: Handling empty dynamic subsets in TI's

Post by Nick_Blommaerts » Mon Jun 18, 2012 4:36 pm

Lotsaram, that is indeed one of the methods I tried to describe, it's a shame that it seems there is no other way to do this though.

Declan, the problem is that I am creating this subset solely for the purpose of manipulating some data, the view and subsets are delete by the TI after doing so. The reason I do this is to avoid the possibility of any users altering my dimension subsets for one reason or another (e.g. accidentally making them static by saving them). Problem is that if you create a dynamic subset in a TI and assign it to a view, it will cause the TI to throw an error (which surprise surprise, you cannot handle in TM1 :lol: ).

Thanks for the confirmation though guys, well appreciated. If anyone has any other suggestion I'd be interested to hear them, never hurts to learn something ;)

User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Handling empty dynamic subsets in TI's

Post by rkaif » Mon Jun 18, 2012 5:37 pm

Nick_Blommaerts wrote:Problem is that if you create a dynamic subset in a TI and assign it to a view, it will cause the TI to throw an error (which surprise surprise, you cannot handle in TM1
You can create the dynamic subset in a separate TI and then use that subset to build a View in another TI.
Cheers!
Rizwan Kaif

Wim Gielis
MVP
Posts: 2213
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Handling empty dynamic subsets in TI's

Post by Wim Gielis » Mon Jun 18, 2012 6:43 pm

Hello there

You could also have a dynamic subset starting with the character }
thereby doing as if it was a control object.

Users will probably be much less likely to mess with these.

Or use an MDX to get a dynamic subset of part of the dimension, through which a simple loop can be done.
In general, I find that looping is quite fast in TM1 (unless you have a double loop:
for example for every element in a large dimension, loop the same or a different large dimension).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Duncan P
MVP
Posts: 588
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Handling empty dynamic subsets in TI's

Post by Duncan P » Mon Jun 18, 2012 7:21 pm

You can catch the error if you have the SubsetCreateByMDX in a separate process that you call with ExecuteProcess. This sub-process will bomb but the calling process will continue. You can query the system global variable ProcessReturnCode ( but be aware that you have to declare it first with NumericGlobalVariable( 'ProcessReturnCode' ) ) and compare it to the result of ProcessExitSeriousError(). If they match then your MDX failed and you can just skip over the rest of your process.

The downside of this is that at the end of the process there will be a message box saying the process completed with minor errors. I am not aware of any way of avoiding this. Any suggestions welcome.

Nick_Blommaerts
Posts: 17
Joined: Tue May 24, 2011 4:39 pm
OLAP Product: Cognos TM1
Version: 9.x + 10.x
Excel Version: 2003+2007

Re: Handling empty dynamic subsets in TI's

Post by Nick_Blommaerts » Tue Jun 19, 2012 7:20 am

Duncan P wrote:You can catch the error if you have the SubsetCreateByMDX in a separate process that you call with ExecuteProcess. This sub-process will bomb but the calling process will continue. You can query the system global variable ProcessReturnCode ( but be aware that you have to declare it first with NumericGlobalVariable( 'ProcessReturnCode' ) ) and compare it to the result of ProcessExitSeriousError(). If they match then your MDX failed and you can just skip over the rest of your process.
This is something new for me actually, I'll look into it because it may come in handy at some point.
Duncan P wrote: The downside of this is that at the end of the process there will be a message box saying the process completed with minor errors. I am not aware of any way of avoiding this. Any suggestions welcome.
Indeed, and that is what I would like to avoid. In my specific case, the TI that has to do the data manipulation is a subprocess of a bigger string of TI's, so everything indeed just continues until at the end you get the process completed with minor errors. At that point, you get the users to complain that the process does not work, while in fact nothing is wrong. I could simply change the Failure Message on my action button to be similar to the Success Message, but that is just not a proper way of working :lol:

Looking forward to suggestions in that regard as well though.

jyoung66
Posts: 24
Joined: Fri Aug 13, 2010 2:14 am
OLAP Product: TM1
Version: PAW 2 PAX
Excel Version: 2016

Re: Handling empty dynamic subsets in TI's

Post by jyoung66 » Thu Nov 07, 2013 5:03 am

lotsaram wrote:
Nick_Blommaerts wrote:The things I am trying to avoid are:
- Inserting a dummy in the subset on top of the dynamically chosen ones
- Going through all elements with an extra TI prior to the TI that creates the subset to see if at least one element has the attribute value I am looking for

Both options above should work but I find them a bit impractical (I don't want to be creating a dummy just for this/my dimension is quite big so looping through is something I'd rather avoid). Since proper error handling in TM1 is more or less out of the question, I was wondering if there is a special function I am unaware of, or anything simple I am overlooking for one reason or another.

Thanks in advance.
No unfortunately the things you are trying to avoid are the only way to do it.
(maybe this is what you meant but other approach is to insert the dummy BEFORE the subset create by MDX so that the MDX cannot fail as it will return at least one member, then you could just delete all such dummys at the very end, it is a not too messy a workaround as far as workarounds go).

I used this method just now, or a variation of it at least and it works a treat.

I had my MDX and then inserted a UNION with an element that always exists, then created the MDX, and it works every time, then you just need to delete your dummy element.

Good solution

Post Reply