Updating Element Attributes in TI Process

Post Reply
Thurston Howell III
Posts: 31
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Updating Element Attributes in TI Process

Post by Thurston Howell III » Thu Mar 16, 2017 4:52 pm

Hi All,

I am creating a TI process to update an element attribute with the name of the element if that field is not already populated. I ran the process and it worked for most of the elements, but produced an unspecified error with reference to line 42 (which begins "Elem = SUBSETGETELEMENTNAME...." below).

Does anyone have any insight on what might cause this? It would be much appreciated.

Code: Select all

#Attributes
BonusSubAccountAttribute = 'BonusSubAccount';


# Other
TimeStamp = TIMST( NOW, '\Y\m\d\h\i\s');

vDoubleQuote = CHAR(34);
vSingleQuote = CHAR(39);
vPlusChar = CHAR(43);

strLogPath = 'C:\Tm1Suite\BPMS\TM1Server\Logs\';
strErrorFile = ProcessName | '_' | TimeStamp  | '.txt';
strError = strLogPath | strErrorFile;

# Subsets
SubsetName = 'z' | ProcessName | '_' | TimeStamp ;

SubAccountRollup = 'all pc' | vSingleQuote | 's and cc' | vSingleQuote | 's' ;

SUBSETCREATEBYMDX( SubsetName , '{FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [bpmSubAccount] )}, "' | SubAccountRollup | '")}, ALL, RECURSIVE )}, 0)}, [bpmSubAccount].[BonusSubAccount] = "")}'); 


i = SUBSETGETSIZE( SourceDim , SubsetName );
n = 1;
WHILE ( n <= i );
Elem = SUBSETGETELEMENTNAME( SourceDim , SubsetName , n );

##Test Bonus Subaccount
# Use CELLGETS on attribute cube instead of ATTRS
IF (1 = 0 );
CurrentBonusSubAccount = ATTRS ( SourceDim , elem ,  BonusSubAccountAttribute );
  IF ( CurrentBonusSubAccount @= '');
    ATTRPUTS ( elem , SourceDim , elem ,  BonusSubAccountAttribute );
ENDIF;
ENDIF;

# Use CELLGETS
CurrentBonusSubAccount = CELLGETS ( '}ElementAttributes_' | SourceDim , elem ,  BonusSubAccountAttribute );
IF ( 1 = 1 );
  IF ( CurrentBonusSubAccount @= '');
    CELLPUTS ( elem , '}ElementAttributes_' | SourceDim , elem ,  BonusSubAccountAttribute );
ENDIF;
ENDIF;

       

n = n+1;


END;

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Updating Element Attributes in TI Process

Post by tomok » Thu Mar 16, 2017 6:03 pm

Did it produce the error for all elements in your subset, or just a few? Did you check the subset to see if it actually results in any elements?

Sincerely,
The Professor
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
mattgoff
MVP
Posts: 514
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016
Location: Florida, USA
Contact:

Re: Updating Element Attributes in TI Process

Post by mattgoff » Thu Mar 16, 2017 6:38 pm

I would be concerned about using a dynamic subset in this way since you're altering the results mid-process. Have you considered using SubsetMDXSet to create a static subset instead?

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.

Thurston Howell III
Posts: 31
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Re: Updating Element Attributes in TI Process

Post by Thurston Howell III » Thu Mar 16, 2017 7:15 pm

Tom: It updated some of the elements, but only gave the error once and aborted the process.

Matt: Thanks for the suggestion. I am not familiar with that function and do not see it in the Reference Guide. Is it available in 10.2.2?

I tried to process again and the specific error is "Element not found".

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Updating Element Attributes in TI Process

Post by tomok » Thu Mar 16, 2017 8:42 pm

I think, as Matt has suggested, your problem is because your MDX query is based on the BonusSubAccount being empty and then you are populating it, which in turn changes the subset because that account would no longer in the subset because it's BonusSubAccount attribute is not empty. You need to make the subset static, not dynamic, so the elements don't change while the process is running.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
mattgoff
MVP
Posts: 514
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016
Location: Florida, USA
Contact:

Re: Updating Element Attributes in TI Process

Post by mattgoff » Thu Mar 16, 2017 9:02 pm

Thurston Howell III wrote:Matt: Thanks for the suggestion. I am not familiar with that function and do not see it in the Reference Guide. Is it available in 10.2.2?
It's available in 10.2.2 but undocumented until PAx 2.0. http://www-01.ibm.com/support/docview.w ... wg27042401
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.

Thurston Howell III
Posts: 31
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Re: Updating Element Attributes in TI Process

Post by Thurston Howell III » Thu Mar 16, 2017 9:29 pm

That function sounds like it would be very useful, then. Would I be best off inserting it after the SUBSETCREATEBYMDX statement?

Edward Stuart
Community Contributor
Posts: 226
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Updating Element Attributes in TI Process

Post by Edward Stuart » Thu Mar 16, 2017 10:14 pm

Yes, as the function describes from the link:
SubsetMDXSet
This function removes the MDX expression from a dynamic subset and converts the dynamic subset to a static subset without locking the underlying dimension.

Thurston Howell III
Posts: 31
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Re: Updating Element Attributes in TI Process

Post by Thurston Howell III » Fri Mar 17, 2017 2:49 pm

Alright the function SubsetMDXSet works, but I am still getting the "Element not found" error with reference to the line Elem = SUBSETGETELEMENTNAME( SourceDim , SubsetName , n )

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Updating Element Attributes in TI Process

Post by tomok » Fri Mar 17, 2017 2:59 pm

Thurston Howell III wrote:Alright the function SubsetMDXSet works, but I am still getting the "Element not found" error with reference to the line Elem = SUBSETGETELEMENTNAME( SourceDim , SubsetName , n )
it doesn't look like your code is deleting the subset when it is done. Since this is the case, can you manually open the subset in Server Explore and verify that it is indeed now static?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Thurston Howell III
Posts: 31
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Re: Updating Element Attributes in TI Process

Post by Thurston Howell III » Fri Mar 17, 2017 3:19 pm

It is still a dynamic subset.

I had my code to delete the subset in the epilog.

IF ( SUBSETEXISTS ( SourceDim , SubsetName ) = 1 ) ;
SUBSETDESTROY ( SourceDim , SubsetName );
ENDIF;

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Updating Element Attributes in TI Process

Post by tomok » Fri Mar 17, 2017 3:22 pm

Thurston Howell III wrote:It is still a dynamic subset.
Then your usage of the SubsetMDXSet function did not work.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Updating Element Attributes in TI Process

Post by tomok » Fri Mar 17, 2017 3:24 pm

Thurston Howell III wrote:It is still a dynamic subset.
Then your usage of the SubsetMDXSet function did not work. There is also the old method of manually adding and then deleting an element to an MDX-based subset, which will convert it to a static subset.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Thurston Howell III
Posts: 31
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Re: Updating Element Attributes in TI Process

Post by Thurston Howell III » Fri Mar 17, 2017 3:43 pm

I used SUBSETELEMENTINSERT after my MDX expression to turn it static and now it works perfectly. Thanks for alerting me to the issue being the dynamic subset!

User avatar
Alan Kirk
Site Admin
Posts: 5928
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Updating Element Attributes in TI Process

Post by Alan Kirk » Tue Jul 09, 2019 12:12 am

I know I'm very late to the party on this, but today is the first time I'd had cause to use this function.

It appears that if you feed an MDX expression into the third argument to update the subset:

Code: Select all

SubsetMDXSet(dimension, subsetname, [MDX_expression]);
Then the function just leaves it as a dynamic subset with the new subset definition.

This is... far from clear from the definition of that argument, making me suspect that this is in fact "unintended behaviour". Or, as we called it in the olden days, a bug:
MDX_expression: Optionally, the MDX expression that you want to populate the subset with. All elements are deleted and the subset is populated with the elements as defined by the new MDX expression. If the MDX expression is invalid, TurboIntegrator processing stops, the contents of the subset is unchanged and an error is logged. If you do not use this parameter, include an empty string as shown in the example.
OK, it says that the elements in the subset are replaced (as one would expect and desire) but it does NOT say that they remain dynamic. And indeed since the entire purpose of the function is stated to be:
This function removes the MDX expression from a dynamic subset
then in my view it would be kinda hard to argue that this behaviour is "by design".

Anyway, there is another way around this beyond the "insert and delete a dummy element" one; just call the function again with an empty string as the third argument. The first call will update the subset as required, the second call will render it static.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

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

Re: Updating Element Attributes in TI Process

Post by lotsaram » Tue Jul 09, 2019 6:22 am

SubsetMDXSet
This function removes the MDX expression from a dynamic subset and converts the dynamic subset to a static subset without locking the underlying dimension.
This is just an example of really bad documentation (written no doubt by a professional in this area but someone who has never actually used the product and knows deeply about what they are writing). You need to take it in context of the paragraph that comes next and the example which is given.
All of the old elements at the time of the call are saved as elements of the new static subset. The function returns the number of elements that the new subset contains. Optionally, you can also populate the subset with a new MDX expression.
And the example being of passing a blank string as the MDX member set expression.
SubsetMDXSet('Cities', 'testsubset', '');

Reading between the lines you can infer the true intent of the function is as the name implies, to change the MDX of an existing dynamic subset or to convert an existing static subset to dynamic. Only when the MDX expression is passed as a blank string does the function convert a dynamic subset to a static one with the same members.

However, I have raised the same bug in a few versions of PA 2.0.x that passing a blank string with SubsetMDXSet can convert the subset to EMPTY rather than same set of members just static. For this reason I have stuck to the old method of adding and deleting an element to do the static conversion.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Mark RMBC
Regular Participant
Posts: 181
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Updating Element Attributes in TI Process

Post by Mark RMBC » Wed Jul 10, 2019 12:36 pm

Hi,

I am sure one of experts on here used the following, which I now use:

Create a substet by mdx first, so:

subsetcreatebymdx( sSub, sMDX);

Then I use SubsetMDXGet, so:

vMDX = SubsetMDXGet( vDim , sSub ) ;

Finally use SubsetMDXSet, so:

IF( vMDX @<> '' ) ;
vNumElems = SubsetMDXSet( vDim, sSub , '' ) ;
ENDIF ;

Anyway this is how I have used it and this method seems to work. Or should I say I haven't see it fail yet!

cheers, Mark

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

Re: Updating Element Attributes in TI Process

Post by Wim Gielis » Wed Jul 10, 2019 1:00 pm

If you are interested, please refer to:
http://www.wimgielis.com/tm1_subsetcrea ... set_EN.htm
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 service with AutoHotKey

Post Reply