Dynamic subset

[sbalaz]

Dear all,

is it possible to create a dynamic subset based on value stored in a cube. There are CellGetN() and CellGetS() working in TI. Is there something similar that works in dynamic subsets?

We use Parameters cube in which values defining actual year, month, etc are stored. In a reporting cube I need to copy data between ForecastVersion dimension elements. I have created TI process where datasource is a TM1 cube view. All the leaf elements are used (January, February, ... November, December) from Month dimension in the datasource view. I would like to reduce the datasource view by using the actual month only (e.g. July), which is stored in Parameters cube.

Have you got any ideas how to solve this issue?
Thank you in advance.

Stanislav

[Marcus Scherer]

well,
read out your parameter from your parameter cube and put it in subset. Then assign subset to your view:

like:

Code: Select all

# (3) Subset for month
#Monat auslesen
mon = CellGetS('z_param', 'Ben Allg', 'Para102', 'pardat_s2');

SubsetDestroy('Monat', 'z_mon');
SubsetCreate('Monat', 'z_mon');
SubsetElementInsert('Monat', 'z_mon', mon, 1);

# assign
ViewSubsetAssign('dbg1', 'qu3', 'Monat', 'z_mon');

HTH,

Marcus

[Oratia623]

Hi,

Totally agree with Marcus's solution, but to answer your question of whether it is possible to create a dynamic subset based on the values stored in a cube - Yes it is.

In the following example, We want to create a dynamic subset of the 'Months' dimension that will contain the 'Current Month' value held in the 'SystemParameters' cube.
The 'System Parameters' cube has three dimensions: 'Item', 'Parameters' and 'DataType'
The value we want in the dynamic subset is held in '1', 'Current Month', and 'Text' respectively.
Thus the MDX in the subset would be:
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Months] )},[SystemParameters].([Item].[1],[Parameters].[Current Month],[DataType].[Text]))}

Look here for the MDX Primer which has more cool MDX stuff

[sbalaz]

Hi,

Thanks a lot for your help. It is that easy if you know how to do things

I like the dynamic subset solution a bit more I think... I will change the subset and all the related views and processes are updated at once...

Do you think there is any weakness in this approach? Can you see a significant advantage in Marcus's solution?

Stanislav

[lotsaram]

Hi,

Thanks a lot for your help. It is that easy if you know how to do things

I like the dynamic subset solution a bit more I think... I will change the subset and all the related views and processes are updated at once...

Do you think there is any weakness in this approach? Can you see a significant advantage in Marcus's solution?

Stanislav

Another way to do it with MDX: use the StrToMember function to directly pick an element from a cube string value. For a single element selection like in this case this would be more efficient than subset all followed by filter.

Unless you have a requirement to dynamically change subsets on the fly I would also go with the suggestion of using an nightly TI to refresh static subsets. Static subsets give much better query performance than dynamic subsets, especially for large dimensions. That's the advantage and in a large scale model it can be significant. Is there anything wrong with Marcus's suggestion? Yes, but only on a technicality of the coding, if the subset is used in a view then SubsetDestroy will cause an error. To be safe SubsetDeleteAllElements is better if the subset might be being used in one of more views.

[sbalaz]

Another way to do it with MDX: use the StrToMember function to directly pick an element from a cube string value. For a single element selection like in this case this would be more efficient than subset all followed by filter.

Unless you have a requirement to dynamically change subsets on the fly I would also go with the suggestion of using an nightly TI to refresh static subsets. Static subsets give much better query performance than dynamic subsets, especially for large dimensions. That's the advantage and in a large scale model it can be significant. Is there anything wrong with Marcus's suggestion? Yes, but only on a technicality of the coding, if the subset is used in a view then SubsetDestroy will cause an error. To be safe SubsetDeleteAllElements is better if the subset might be being used in one of more views.

Thank you for explanation. I have tried the following:
1. Dynamic subset:
I have used subset all and filterbypattern - used for subsets in two small but dense dimensions (year: 6 elements, version: 13 elements). Very good performance compared to the previous view.
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Fiscal year] )}, [Parameter].([Parameter].[Year], [Parametervalue].[S]))}

2. Static subset defined within TI process - Marcus's solution:
I have replaced SubsetDestroy and SubsetCreate by SubsetDeleteAll Elements. Process has the same performance as with solution #1 above (used for small dimensions).

3. StrToMember:
I have not managed to get this working. I have tried {("StrToMember([Parameter].([Parameter].[Year], [Parametervalue].[S])")}. Also tried changes in syntax: " ", ' ', (), []. No success... I always got either Value Error or Syntax Error.

Thanks for help, now I can see the advanteage of static subset. I am going for solution #2.