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

Post Reply
ChrisF79
Posts: 52
Joined: Mon Sep 20, 2010 2:20 pm
OLAP Product: IBM TM1
Version: 9.5.1
Excel Version: 2007 SP2

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

Post by ChrisF79 » Fri Mar 16, 2012 1:10 pm

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
MVP
Posts: 1663
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Is there an easy way to convert a dynamic subset to stat

Post by declanr » Fri Mar 16, 2012 1:23 pm

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.
Declan Rodger

Andy Key
MVP
Posts: 345
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Is there an easy way to convert a dynamic subset to stat

Post by Andy Key » Sat Mar 17, 2012 3:20 pm

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.
Andy Key

BariAbdul
Regular Participant
Posts: 420
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: TM1,Cognos
Version: 9.5.2
Excel Version: 2007

Re: Is there an easy way to convert a dynamic subset to stat

Post by BariAbdul » Sat Apr 18, 2015 3:22 pm

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
"You Never Fail Until You Stop Trying......"

pandinus
Posts: 78
Joined: Tue Mar 18, 2014 8:02 am
OLAP Product: TM1, Cognos Express
Version: 10.2.2
Excel Version: 2013

Re: Is there an easy way to convert a dynamic subset to stat

Post by pandinus » Sat Apr 18, 2015 7:07 pm

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
Posts: 38
Joined: Thu Oct 11, 2012 6:15 am
OLAP Product: TM1
Version: 10.2.2.4
Excel Version: 2010
Location: Melbourne, Australia

Re: Is there an easy way to convert a dynamic subset to stat

Post by upali » Sun Apr 19, 2015 8:48 pm

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.

User avatar
paulsimon
MVP
Posts: 755
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Is there an easy way to convert a dynamic subset to stat

Post by paulsimon » Tue Apr 21, 2015 10:27 pm

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
Regular Participant
Posts: 420
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: TM1,Cognos
Version: 9.5.2
Excel Version: 2007

Re: Is there an easy way to convert a dynamic subset to stat

Post by BariAbdul » Fri Apr 24, 2015 5:35 pm

Thanks a lot,Paul. :)
"You Never Fail Until You Stop Trying......"

Emixam
Posts: 63
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: North America

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

Post by Emixam » Mon Jun 22, 2020 11:58 am

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.

Image


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


Image

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 !
Sorry for my English :)

User avatar
gtonkin
MVP
Posts: 805
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

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

Post by gtonkin » Mon Jun 22, 2020 12:45 pm

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
Posts: 38
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

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

Post by scrumthing » Mon Jun 22, 2020 1:15 pm

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.
There is no OLAP database besides TM1!

lotsaram
MVP
Posts: 3412
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

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

Post by lotsaram » Mon Jun 22, 2020 1:58 pm

scrumthing wrote:
Mon Jun 22, 2020 1:15 pm
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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Emixam
Posts: 63
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: North America

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

Post by Emixam » Mon Jun 22, 2020 2:20 pm

Thanks a lot for the answer guys ! I'll update to the latest version.
Sorry for my English :)

Wim Gielis
MVP
Posts: 2467
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Mon Jun 22, 2020 3:06 pm

You can read about it here.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

scrumthing
Posts: 38
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

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

Post by scrumthing » Mon Jun 22, 2020 5:25 pm

lotsaram wrote:
Mon Jun 22, 2020 1:58 pm
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. :-P
Could you please shed some light on that. Now I am really curious.
There is no OLAP database besides TM1!

Emixam
Posts: 63
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: North America

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

Post by Emixam » Mon Jun 22, 2020 5:54 pm

scrumthing wrote:
Mon Jun 22, 2020 5:25 pm
That is probably why you are an MVP and I am just a regular guy. :-P
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.
Sorry for my English :)

Post Reply