Writing a TI Process to Create Dynamic Subsets

[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!

[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

[DNA_DC]

I get that part... the thing thats difficult is creating new subsets "automatically" as new "Sponsors" are loaded from data.

[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

[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.

[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

[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?

[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

[DNA_DC]

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

[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;

[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!

[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;