Page 1 of 1
Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 4:52 pm
by Thurston Howell III
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;
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 6:03 pm
by tomok
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
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 6:38 pm
by mattgoff
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
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 7:15 pm
by Thurston Howell III
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".
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 8:42 pm
by tomok
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.
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 9:02 pm
by mattgoff
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
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 9:29 pm
by Thurston Howell III
That function sounds like it would be very useful, then. Would I be best off inserting it after the SUBSETCREATEBYMDX statement?
Re: Updating Element Attributes in TI Process
Posted: Thu Mar 16, 2017 10:14 pm
by Edward Stuart
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.
Re: Updating Element Attributes in TI Process
Posted: Fri Mar 17, 2017 2:49 pm
by Thurston Howell III
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 )
Re: Updating Element Attributes in TI Process
Posted: Fri Mar 17, 2017 2:59 pm
by tomok
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?
Re: Updating Element Attributes in TI Process
Posted: Fri Mar 17, 2017 3:19 pm
by Thurston Howell III
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;
Re: Updating Element Attributes in TI Process
Posted: Fri Mar 17, 2017 3:22 pm
by tomok
Thurston Howell III wrote:It is still a dynamic subset.
Then your usage of the SubsetMDXSet function did not work.
Re: Updating Element Attributes in TI Process
Posted: Fri Mar 17, 2017 3:24 pm
by tomok
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.
Re: Updating Element Attributes in TI Process
Posted: Fri Mar 17, 2017 3:43 pm
by Thurston Howell III
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!
Re: Updating Element Attributes in TI Process
Posted: Tue Jul 09, 2019 12:12 am
by Alan Kirk
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]);
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.
Re: Updating Element Attributes in TI Process
Posted: Tue Jul 09, 2019 6:22 am
by lotsaram
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.
Re: Updating Element Attributes in TI Process
Posted: Wed Jul 10, 2019 12:36 pm
by Mark RMBC
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
Re: Updating Element Attributes in TI Process
Posted: Wed Jul 10, 2019 1:00 pm
by Wim Gielis