Dinamic Creation of Subsets based on attribute data

[jviegas@bi4all.pt]

Hello,

My client has a product dimension where each element has an attribute regarding market segment.

Ex:

Product category Product Attribute Segment
Cat A Prod A Market 1
Cat A Prod B Market 2
Cat B Prod C Market 1

In a TI process to create the dimension I need to create automatically some subsets for the dimension (based on each existing attribute).

In this example:

A subset for Market 1
other for Market 2
(mantaining the default subset)

Can anyone give me some help?

Thank you

[qml]

Here's MDX syntax for your two dynamic subsets:

Code: Select all

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Market 1")}

Code: Select all

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Market 2")}

Just paste the code in the expression window in Subset Editor and save the subset with the expression attached.

[jviegas@bi4all.pt]

Hello qml,

Thanks for the feedback.

My problem is that I want to do this dinamically with TI after executing a TI process to update the DIM Products. And I need to create a subset for each data existing in the Segment attribute.

I haven't been able to do it but also I can't determine if this is possible (by the way, I'm using a csv file as a data source in the dim products TI process).

Thanks again for the time and help,

Here's MDX syntax for your two dynamic subsets:

Code: Select all

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Market 1")}

Code: Select all

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Market 2")}

Just paste the code in the expression window in Subset Editor and save the subset with the expression attached.

[qml]

These dynamic subsets will work after your dimension gets updated with TI.
However, if you really insist on creating them as static subsets using TI (which might have som pros, I admit), then my advice is to first create dynamic subsets using SubsetCreateByMDX() and then use them as data sources for another (sub)process that will pick up all elements in them and add them to another static subset using SubsetElementInsert(). Then you can delete the dynamic subsets.
It would be really useful if your list of possible unique values of the Segment attribute was known beforehand, so that you don't have to build a separate logic to get that list.

[jviegas@bi4all.pt]

Would it be possible to do something like:

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Variable X" )}

being "Variable X" a variable to get the data in the attribute "Segment" ?

I'm trying to make this the most possible "bulletproof" solution so that the power users don't forget each time a new segment is loaded from the ERP.

These dynamic subsets will work after your dimension gets updated with TI.
However, if you really insist on creating them as static subsets using TI (which might have som pros, I admit), then my advice is to first create dynamic subsets using SubsetCreateByMDX() and then use them as data sources for another (sub)process that will pick up all elements in them and add them to another static subset using SubsetElementInsert(). Then you can delete the dynamic subsets.

[Duncan P]

If you are using SubsetCreateByMDX you will need to make sure that the dimension has at least one element that satisfies the MDX condition. If the MDX returns an empty set then the call fails. If you are only creating subsets for the values of the attribute that appear in the current members then you will be OK. If there are other attribute values for which you need subsets you will have to create them interactively in Architect or, if it must be done in TI, add a dummy item that has the correct attribute value, create the subset then delete the item. Alternatively modify the attribute value for any existing item and then change it back afterwards. To create a subset in Architect for a non-existent attribute value you will have to modify the MDX manually as the UI for filtering by attribute values only offers current values.

[qml]

Would it be possible to do something like:

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Variable X" )}

being "Variable X" a variable to get the data in the attribute "Segment" ?

I'm trying to make this the most possible "bulletproof" solution so that the power users don't forget each time a new segment is loaded from the ERP.

What I would suggest is to create a purely technical dimension Segment that would be updated automatically from ERP with any new segment created there. You could have a nightly chore to run this update. Then you could have a second TI, using a dynamic subset containing all segments as its data source:

Code: Select all

{TM1SUBSETALL( [Segment] )}

For each element of this subset it would (re)create a corresponding dynamic subset using the following code logic on the Metadata or Data tab:

Code: Select all

sSubsetName = 'Dynamic_Segment_' |vSegment;
If (SubsetExists ('Product', sSubsetName) = 1);
  SubsetDestroy ('Product', sSubsetName);
EndIf;
sMDX = '{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "' | vSegment |'" )}';
SubsetCreatebyMDX (sSubsetName, sMDX);

As Duncan P rightly pointed out, you need to ensure that the subset definition returns at least one element at the time of creation. Otherwise this TI will throw an error.

[jviegas@bi4all.pt]

Hello to all,

thanks for the feedbacks and sorry my late reply.

I was able to do this with a second TI Process after creating the dim (the dim has an attribute with the product segment previously loaded with a file)

In this 2nd TI process i've placed this code:

Code: Select all

################################ SUBSET <Subset name>################################
	i = 1;
	While (i <= DimSiz('<Dim_Name>'));

### Determina Nome do elemento da dimensão ###
		dimelement=DimNm('<Dim_Name>', i);

### Determina o nome do <Subset name>para o elemento ###
		subex=Attrs('<Dim_Name>',dimelement,'<Attribute_name>');

## Update do subset existir e apenas para elementos que não existam ##
		If(SubsetExists('<Dim_Name>',Attrs('<Dim_Name>', dimelement, '<Attribute_name>'))=1);
			top=SubsetGetSize('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'));
			count=1;
			SubElemExists=0;
			while ((count<=top)%(top=0));
				if(SubsetGetElementName('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'), count)@=dimelement);
					SubElemExists=1;
				endif;
				count=count+1;
			end;
			if(SubElemExists=0);
				numelement=SubsetGetSize('<Dim_Name>',Attrs('<Dim_Name>',dimelement,'<Attribute_name>'));
				SubsetElementInsert('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'), dimelement, numelement);
			ENDIF;
			SubElemExists=0;

### Criação de subset e colocação do artigo ##
		ELSEIF ((SubsetExists('<Dim_Name>',Attrs('<Dim_Name>', dimelement, '<Attribute_name>'))=0)&(Attrs('<Dim_Name>',dimelement,'<Attribute_name>')@<>''));

## Cria o subset ##
			SubsetCreate('<Dim_Name>',Attrs('<Dim_Name>',dimelement,'<Attribute_name>'));

## Insere elemento no subset ##
			numelement=SubsetGetSize('<Dim_Name>',Attrs('<Dim_Name>',dimelement,'<Attribute_name>'));
			SubsetElementInsert('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'), dimelement, numelement);
		Endif;
	i = i + 1;
	End;

Hope it helps if someone needs something like this.

Thanks again for the ideas