How to Copy Subsets between Dimensions ??
-
- 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 ??
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 ?
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 ?
-
- 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 ??
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.
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)
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
-
- 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 ??
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.
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
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
-
- 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 ??
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: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.
Code: Select all
{SOME STUFF{[OriginalDimension].Some Stuff)}
Declan Rodger
-
- 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 ??
@declanr
Thank you for the code, but this code is the first possibility i mentioned in my first post:
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?
Thank you for the code, but this code is the first possibility i mentioned in my first post:
And this is on my server very slow.Go through all subsets and elements and create them in the new dimension.
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?
-
- 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 ??
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.McSash wrote:Any ideas?
-
- Posts: 6
- Joined: Mon Jun 20, 2016 11:22 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010 2013
"
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.
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.
-
- 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 ??
That's true Tomok and also Declanr a few posts up.tomok wrote: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.McSash wrote:Any ideas?
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
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
- 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 ??
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.
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
Cleveland, TN
Re: How to Copy Subsets between Dimensions ??
Hi Pavoga
Do you have the code available to copy Subsets between dimensions. Just checked out your 2013 post.
Many thx
Chris
Do you have the code available to copy Subsets between dimensions. Just checked out your 2013 post.
Many thx
Chris