TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Writing a TI Process to Create Dynamic Subsets

https://www.tm1forum.com/viewtopic.php?t=1204

Page 1 of 1

Writing a TI Process to Create Dynamic Subsets

Posted: Wed Jul 01, 2009 5:26 pm
by DNA_DC
I promise to contribute to the discussion when I am more knowledgable about this but how would I write a TI process to create dynamic subsets for every change in values of a particular attribute. Lets use the example I used earlier:

For every new "Sponsor" (attribute in the Organizations function), create a new dynamic subset that shows only the elements that have that particular "Sponsor" as an attribute value.

Help is definitely appreciated!

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Wed Jul 01, 2009 6:05 pm
by Martin Ryan
Not sure off the top of my head, but have a look in the manual under TI functions, there's something in there about creating subsets based on MDX expressions.

To get the right MDX expression you might have to play around with Tools -> Record Expression in Subset Editor to record the MDX query. You'll need to have View -> Expression Window checked.

Martin

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Wed Jul 01, 2009 6:16 pm
by DNA_DC
I get that part... the thing thats difficult is creating new subsets "automatically" as new "Sponsors" are loaded from data.

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Wed Jul 01, 2009 6:24 pm
by Martin Ryan
I'd do one of two things

1) As new sponsors are loaded up, create a new dimension called 'Sponsors' (actually, I think you already have this?) then once the load process is created call another process that cycles through the 'Sponsors' dimension and uses the elements in this dimension to create the subsets

2) Instead of cycling through a specially created dimension, cycle through the 'Organizations' dimension and retrieve the sponsor attributes and use them to create the subsets. Again this will be in a separate process.

Martin

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Wed Jul 01, 2009 6:29 pm
by George Regateiro
Since it sounds like your MDX might get a little complex for the Subset Editor you also might look into the MDX Primer that is in this forum. It has a pretty good overview of MDX if you have not worked in it before.

But like Martin stated there is no "automatic" way to do, processes are your only option.

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Wed Jul 01, 2009 9:50 pm
by DNA_DC
The part of the process I am having trouble with is "cycling' through the attributes to find unique attributes and create a subset of them. I tried a "while ( index > dimsiz('dimensionname') ) loop but to no available.

-DN

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Thu Jul 02, 2009 12:04 am
by DNA_DC
the code below does create the first subset (albeit not dynamic) but does not create the subsequent subsets:

Code: Select all

#declaring variables

Index = 1;
FunctionSize = dimsiz('Functions');
SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');

#while loop to cycle through all of the Resource Sponsors

While (index < FunctionSize );
If( SubsetExists('Functions',SponsorName) = 0);

SubsetCreate('Functions', SponsorName);
Index = Index + 1;

ElseIf( SubsetExists('Functions',SponsorName) = 1);

index = index + 1;

ENDIF;

END;
If I use the following "SubsetCreateByMDX" code, it doesn't recognize "SponsorName":

Code: Select all

#declaring variables

Index = 1;
FunctionSize = dimsiz('Functions');
SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');

#while loop to cycle through all of the Resource Sponsors

While (index < FunctionSize );
If( SubsetExists('Functions',SponsorName) = 0);

SubsetCreatebyMDX(SponsorName,'{FILTER( {TM1SUBSETALL( [Functions] )}, [Functions].[Resource Sponsor] = SponsorName)}');
Index = Index + 1;

ElseIf( SubsetExists('Functions',SponsorName) = 1);

index = index + 1;

ENDIF;

END;
However, if I run this code, it creates the first dynamic subset:

Code: Select all

#declaring variables

Index = 1;
FunctionSize = dimsiz('Functions');
SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');

#while loop to cycle through all of the Resource Sponsors

While (index < FunctionSize );
If( SubsetExists('Functions',SponsorName) = 0);

SubsetCreatebyMDX(SponsorName,'{FILTER( {TM1SUBSETALL( [Functions] )}, [Functions].[Resource Sponsor] = "A")}');
Index = Index + 1;

ElseIf( SubsetExists('Functions',SponsorName) = 1);

index = index + 1;

ENDIF;

END;
So in the end, there are two problems:

- The While statement doesn't work as needed
- The MDX statement doesnt recognize the variable.

What do you guys recommend?

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Thu Jul 02, 2009 12:29 am
by mattgoff
You need to move:

Code: Select all

SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');
inside your while. As written, it never changes after index 1.

Matt

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Thu Jul 02, 2009 12:57 am
by DNA_DC
mattgoff wrote:You need to move:

Code: Select all

SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');
inside your while. As written, it never changes after index 1.

Matt
As you suggested, I changed the code (below):

Code: Select all

Index = 1;
FunctionSize = dimsiz('Functions');

#while loop to cycle through all of the Resource Sponsors

While (index < FunctionSize );

SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');

If( SubsetExists('Functions',SponsorName) = 0);

SubsetCreate('Functions', SponsorName);

ENDIF;

Index = Index + 1;

END;
However, now while it does create the subsets, it gives me the following error:
Error: Epilog procedure line (16): Name "" contains an invalid character

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Thu Jul 02, 2009 1:25 am
by DNA_DC
Nevermind! Got it to work!!! (See Code Below):

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

Index = 1;
FunctionSize = dimsiz('Functions');

#while loop to cycle through all of the Resource Sponsors

While (index < FunctionSize );

SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');

If( SponsorName @<>'' );

If( SubsetExists('Functions',SponsorName) = 0);

SubsetCreate('Functions', SponsorName);

ENDIF;

ENDIF;

Index = Index + 1;

END;

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Thu Jul 02, 2009 1:43 am
by DNA_DC
But now I need to figure out how to get the MDX statement to accept the SponsorName variable... any pointers?

I am also trying an alternative approach (which is still giving me errors):

Code: Select all

Index = 1;
SubsetIndex = 1;
FunctionSize = dimsiz('Functions');

#while loop to cycle through all of the Resource Sponsors

While (index < FunctionSize );

SponsorName = attrs('Functions',DIMNM('Functions',Index),'Resource Sponsor');

If( SponsorName @<>'' );

If( SubsetExists('Functions',SponsorName) = 0);

SubsetCreate('Functions', SponsorName);

SubsetIndex = 1;

SubsetElementInsert('Functions', SponsorName, SUBNM('Functions', 'All', Index), SubsetIndex);

SubsetIndex = SubsetIndex + 1;

Else;

SubsetElementInsert('Functions', SponsorName, SUBNM('Functions', 'All', Index), SubsetIndex);
SubsetIndex = SubsetIndex + 1;

ENDIF;

ENDIF;

Index = Index + 1;

END;
Basically, when I run it, it says:

Code: Select all

Error: Epilog procedure line (24): Subset not found
I'm trying to figure out why... if anyone can resolve that, it would be spectacular!

Re: Writing a TI Process to Create Dynamic Subsets

Posted: Thu Jul 02, 2009 11:32 pm
by DNA_DC
As an update, I solved it... in case someone wants to use it, here's the generalized code:

Code: Select all

Index = 1;

Reference_Dim_Size  = dimsiz('Reference_Dim');

#while loop to cycle through all of the Resource Sponsors and create the subsets

While (index < Reference_Dim_Size );

Attribute_Name = attrs('Reference_Dim',DIMNM('Reference_Dim',Index),'MyAttribute');

SubsetPre = INSRT('My Attribute Subset ', '', 1);

If( Attribute_Name @<>'' );

SubsetName = SubsetPre | Attribute_Name;

If( SubsetExists('Reference_Dim',SubsetName) = 0);

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( {TM1SUBSETALL( [Reference_Dim] )}, [Reference_Dim].[MyAttribute] = "%Attribute_Name%")}'));

ENDIF;

ENDIF;

Index = Index + 1;

END;
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited