Is there an easy way to convert a dynamic subset to static?

[ChrisF79]

We have some problems going on due to dynamic subsets causing table locks, etc. That's not the point of the post though. The point is, how can we easily convert dynamic subsets to static ones? I've read about the SubsetCreateByMDX function but that creates the dynamic subset. Any way of making it static after the fact?

[declanr]

You could have a TI that uses the MDX generated subset as its datasource and simply adds the elements to a new subset.


If you need the subset to have the same name as the MDX generated one you will just need to think about the order of your actions... e.g. when to delete the subset, when to recreate the subset.

It should be pretty simple for you though and if you need to do this with a few things you could have it driven by parameters (e.g. one asks the name of the MDX Subset, another asks the name of what you'd like the hardcoded to be called.... if they are the same an IF function would change the way in which its processed.)



And if its a one off, just "Save As" with the subset.

[Andy Key]

You can do a SubsetCreateByMDX in a TI, then just add and then remove an element to the end of the subset and it will become static.

If you are using contributor have a look at the TIs that come with it and you'll see one of them does precisely this. I can't remember what it is called, but it is something along the lines of }tp_util_convert_dynamic_subset_to_static, or something similarly obvious.

[BariAbdul]

From 10.2.2 you can directly convert dynamic subset to static subset,Most of you may be already aware of this,Just in case someone misses out:
w-01.ibm.com/support/docview.wss?uid=swg27042401 Thanks

[pandinus]

You ever got the SubsetMDXSet to work? On my TM1 10.2.2 FP2 environment this did not work, so I had to revert to the SubsetCreateByMDX, SubsetElementInsert and SubsetElementDelete to make the subset static.

[upali]

I use these two lines...

Code: Select all

SubsetCreatebyMDX(cSubset, '{{[' | vDim | '].MEMBERS.ITEM(0)}, {TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | vDim | '] )}, 0)}, ASC)}}');
SubsetElementDelete(vDim, cSubset, 1);

vDim is the Dimension and cSubset is the subset you create.

Code: Select all

{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | vDim | '] )}, 0)}, ASC)}

Can be replaced by your MDX statement, while the one before that is used to ensure that the final subset is not empty.

[paulsimon]

Hi

The following bit of code does the trick

Code: Select all

# ##############################################################

# Convert the MDX Subset to a Static Subset
# first checking that it does have an MDX Expression

vMDX = SubsetMDXGet( vDim , vSub ) ;

IF( vMDX @<> '' ) ;
  vNumElems = SubsetMDXSet( vDim, vSub , '' ) ;    
ENDIF ;

# ##############################################################

The description on the IBM Web Site is misleading

This function removes the MDX expression from a dynamic subset and converts the dynamic subset to a static subset without locking the underlying dimension.

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.

It implies that this will always return a static subset. In practice you actually have to use SubsetMDXSet to set the MDX expression to ''. (You must first have a Subset based on an MDX Expression. If you want to create a Subset with an MDX Expression then just use the usual SubsetCreateByMDX, and then use the above to convert the result of the MDX expression to a Static subset.

Regards

Paul Simon

[BariAbdul]

Thanks a lot,Paul.

[Emixam]

Hello everyone,

I start working for a new client today and there is a strange behaviour with the combination of SubsetCreateByMDX/SubseyMDXSet to make a dynamic subset static.

The following code return a subset with a MDX, everything is good.




However, when I uncomment the last line (to make it static), subset '__Test1' is now empty...




Any idea what happened ? Is there a magic unknown parameter that i'm not aware of ? Am I too tired and I have to go back to bed and review my code after ?

They are using TM1 11.4.0.21/PA 2.0.6

Thanks and have a nice day !

[gtonkin]

I have been doing mine like this for a while:

Code: Select all

IF(SubsetExists(sDim,sSubset)=0);
SubsetCreate(sDim,sSubset);
ENDIF;
sMDX='TM1SUBSETALL( ['|sDim|'] )}, ['|sDim|']}';
SubsetMDXSet(sDim.Measures,sSubset,sMDX);
SubsetMDXSet(sDim.Measures,sSubset,'');

Create a blank subset if it does not already exist, apply MDX to it then remove the MDX.

[scrumthing]

Ever since SubsetMDXSet was introduced I only use that one to create dynamic subsets. It seems more robust. For example you do not get an error if your MDX returns an empty set. If you use SubsetCreateByMDX with such a subset an error will occur. But sometimes it is okay to have an empty set if you use it to check for example for users without password or similar things.

And then do it like gtonkin said. Just convert the dynamic one into a static one.

[lotsaram]

Ever since SubsetMDXSet was introduced I only use that one to create dynamic subsets. It seems more robust. For example you do not get an error if your MDX returns an empty set. If you use SubsetCreateByMDX with such a subset an error will occur. But sometimes it is okay to have an empty set if you use it to check for example for users without password or similar things.

And then do it like gtonkin said. Just convert the dynamic one into a static one.

You have always been able to use SubsetCreateByMDX to create an empty set without error (you just need to use the undocumented 3rd argument of the dimension name to do it )

Depending on version in PA be careful of using SubsetMDXSet to set blank string as the MDX to convert to static subset. In some versions there is a bug where this will empty the subset (in addition to emptying the MDX). This will be the issue that Emixam has. (PH06270 TI FUNCTION SUBSETMDXSET DOESN'T WORK AS EXPECTED). This issue is marked as fixed in the 2.0.8 release notes.

Adding (then removing) a member is the safe way to do it for all versions. You can use SubsetMDXSet safely for 2.0.8 & 2.0.9

[Emixam]

Thanks a lot for the answer guys ! I'll update to the latest version.

[Wim Gielis]

You can read about it here.

[scrumthing]

You have always been able to use SubsetCreateByMDX to create an empty set without error (you just need to use the undocumented 3rd argument of the dimension name to do it )

That is probably why you are an MVP and I am just a regular guy.
Could you please shed some light on that. Now I am really curious.

[Emixam]

That is probably why you are an MVP and I am just a regular guy.
Could you please shed some light on that. Now I am really curious.

Based on IBM documentation:

Code: Select all

SubsetCreatebyMDX(SubName, MDX_Expression);

However, if you add the 'undocumented 3rd argument' which is the Dimension Name, you won't have any error if your MDX return an empty set.

Code: Select all

SubsetCreatebyMDX(SubName, MDX_Expression, DimName);

Wim Gielis made a very good post about MDX statement : https://www.wimgielis.com/tm1_mdxstatements_EN.htm . You'll find more informations on the 3rd parameter in the caveats section.