How to Copy Subsets between Dimensions ??

Post Reply
McSash
Posts: 6
Joined: Mon Jun 20, 2016 11:22 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010 2013

How to Copy Subsets between Dimensions ??

Post by McSash »

Hello,

I want to copy about 2,400 subsets with each 30,000 elements between two dimensions with a TI-Process.
They both have the same elements, one is the archive dimension, the other one is used normally.

Possibilities:
1. Go through all subsets and elements and create them in the new dimension.
Advantage: no server restart needed
Disadvantage: slow as hell :?
2. Copy all .sub files of subs-folder with cmd execution.
Advantage: VERY fast and accurate
Disadvantage: Server restart needed

Do you guys have any idea how to copy these subsets fast and without a server restart ?
declanr
MVP
Posts: 1815
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: How to Copy Subsets between Dimensions ??

Post by declanr »

The below code would allow you to cycle the source directory .sub files and then create them in the target; it then cycle the elements in the source dim and populate them to the target dim subset.
Note that it will only create them as Static subsets - any MDX will be missed. You could alter the below code with the MDX GET function and then use subsetcreatebymdx etc.

Code: Select all

sFileName = WildCardFileSearch ( cDirectory | '*', '' );
While ( sFileName @<> '' );
	sSubsetName = SubSt ( sFileName, 1, Long ( sFileName ) - 4 );
	If ( SubsetExists ( cDimSource, sSubsetName ) = 1 );
		If ( SubsetExists ( cDimtarget, sSubsetName ) = 1 );
			SubsetDeleteAllElements ( cDimTarget, sSubsetName );
		Else;
			SubsetCreate ( cDimTarget, sSubsetName );
		EndIf;
		iCount = 1;
		iMax = SubsetGetSize ( cDimSource, sSubsetName );
		While ( iCount <= iMax );
			sElementName = SubsetGetElementName ( cDimSource, sSubsetName, iCount );
			If ( Dimix ( cDimTarget, sElementName ) = 0 );
				AsciiOutput ( cErrorFile, sElementName | ' not found in dimension ' | cDimtarget );
			Else;
				SubsetElementInsert ( cDimTarget, sSubsetName, sElementName, SubsetGetSize ( cDimTarget, sSubsetName ) + 1 );
			EndIf;
			iCount = iCount + 1;
		End;
	EndIf;
	sFileName = WildCardFileSearch ( cDirectory | '*', sFileName );
End;

Caveat - I wrote the code in the forum window; it has not been checked so you must validate it for typos. It is intended to give an idea rather than be a finished solution.

You will need to specify variables/constants for:
cDimTarget and cDimSource (your to and from dimensions)
cErrorFile (a text file name to output issues to )
cDirectory (the }.....Subs folder for the source dim)
Declan Rodger
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: How to Copy Subsets between Dimensions ??

Post by Wim Gielis »

While Declan provided a nice solution, I would go for a solution with a server restart.
Just copy the subsets to the target dimension and use a batch find/replace or the dimension name.
Restart the server and done.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
declanr
MVP
Posts: 1815
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: How to Copy Subsets between Dimensions ??

Post by declanr »

Wim Gielis wrote:While Declan provided a nice solution, I would go for a solution with a server restart.
Just copy the subsets to the target dimension and use a batch find/replace or the dimension name.
Restart the server and done.
It is most likely if you do this that the MDX subsets won't come across to the new dimension; they will be something like:

Code: Select all

{SOME STUFF{[OriginalDimension].Some Stuff)}
So you will need to do something to sort out the dimension name part of any MDX.
Declan Rodger
McSash
Posts: 6
Joined: Mon Jun 20, 2016 11:22 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010 2013

Re: How to Copy Subsets between Dimensions ??

Post by McSash »

@declanr
Thank you for the code, but this code is the first possibility i mentioned in my first post:
Go through all subsets and elements and create them in the new dimension.
And this is on my server very slow.
EDIT:
You are right, I also got some dynamic subsets and they're all empty when using the file copy method.

@Wim Gielis
...thats the problem: I dont want to restart the server because there are users working on it.
And as declanr mentioned are the dynamic subets empty after using this method.

So im searching for a solution to copy the subsets with
1. all dynamic and static subsets
2. not having to run through all of them (as @declanr's code does)
3. and not having to restart the server (as @Wim Gielis said)
Any ideas?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to Copy Subsets between Dimensions ??

Post by tomok »

McSash wrote:Any ideas?
Yes. Listen to what the experts are telling you. These are your ONLY two options. Copying subset (.sub) files is by far the easiest and less risky, but requires a restart. Why can't you schedule the restart at off-peak times, like overnight. Surely you don't have users working in TM1 24/7.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
McSash
Posts: 6
Joined: Mon Jun 20, 2016 11:22 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010 2013

"

Post by McSash »

I also have to copy dynamic subsets.
By using the file copy method they won't get copied right, because the .sub files store the MDX with a hard dimension name.
So the file copy method won't work for me.

So there is only the "run through every subset and element" method left, which needs more than 2 hours in my case.

That's the reason I'm asking the members for another method.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: How to Copy Subsets between Dimensions ??

Post by Wim Gielis »

tomok wrote:
McSash wrote:Any ideas?
Yes. Listen to what the experts are telling you. These are your ONLY two options. Copying subset (.sub) files is by far the easiest and less risky, but requires a restart. Why can't you schedule the restart at off-peak times, like overnight. Surely you don't have users working in TM1 24/7.
That's true Tomok and also Declanr a few posts up.

I would do this:
- use Notepad++ (or similar tools) to find/replace the dimension names in bulk, like all*.sub files in the particular folder. There is a probably that not everything will be fine after the bulk replace, for example, the old dimension name is part of a MDX FILTER expressoin on a cube containing that old dimension. If the new dimension not part of the cube the expression will be wrong/invalid.
- copy the *.sub files to the subsets folder of the other dimension
- restart the server:
* either force users out of TM1
* either the server has a scheduled reboot every once in a while
* either you reboot yourself at night or any other time when you will not disturb users or other data loads

The other option would work too, but needs some TI coding and takes a while to run. If it takes too long, why don't you do it in batches, like 100 subsets at a time ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: How to Copy Subsets between Dimensions ??

Post by PavoGa »

For what it is worth, I have a TI process that rebuilds subsets from the *.sub files including those based on MDX. A little tweaking and you can substitute in your target dimension name. We use it to rebuild subsets on some of the control dimensions after a server restart.

Just copy the sub files into the target dimensions sub folder, tweak the code and it should work. Let me know if you're interested.
Ty
Cleveland, TN
chewza
Posts: 146
Joined: Tue Aug 17, 2010 11:51 am
OLAP Product: TM1
Version: 9.5
Excel Version: 7

Re: How to Copy Subsets between Dimensions ??

Post by chewza »

Hi Pavoga

Do you have the code available to copy Subsets between dimensions. Just checked out your 2013 post.

Many thx
Chris
Post Reply