Dimension Copier?

Ideas and tips for enhancing your TM1 application
Post Reply
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Dimension Copier?

Post by John Hammond »

Code: Select all

rmdir /q /s c:\dimcopy
mkdir c:\dimcopy
set tm1directory=\\xxxxxxxxxxx\c$\Program Files\Cognos\TM1\Custom\TM1Data\TM1 Server - DevA backup
echo copying from  %tm1directory%
echo dimension 
xcopy "%tm1directory%\%1.dim" c:\dimcopy
echo excel version of dimension
xcopy "%tm1directory%\%1.xdi" c:\dimcopy
echo subsets 
xcopy "%tm1directory%\%1}subs" c:\dimcopy\%1}subs /s /i
echo attributes cube...
xcopy "%tm1directory%\}ElementAttributes_%1.cub" c:\dimcopy
echo views on the attributes cube...
xcopy "%tm1directory%\}ElementAttributes_%1}vues" c:\dimcopy\}ElementAttributes_%1}vues /s /i 
echo ... and dimension
xcopy "%tm1directory%\}ElementAttributes_%1.dim" c:\dimcopy
echo security 
xcopy "%tm1directory%\}ElementSecurity_%1.cub" c:\dimcopy
echo locking
xcopy "%tm1directory%\}ElementProperties_%1.cub" c:\dimcopy
dir c:\dimcopy
This bit of code takes (hopefully) all of the files associated with a dimension and then copies them into dimcopy directory which then should allow you to easily move them into a new TM1 environment. It seems to work ok.

I tried using .mdi but there does not seem to be a way that you can create an .mdi from an existing .dim.

I assume this will work ok where you have a test environment created from the live in September and then you need the dimension from live as at October. This assumes that the way the numeric array reference is generated remains the same. If elements have been deleted between September and October then I would guess they are not loaded into memory when the disk version of the cube is read in. If elements were added in any cube the cells are zeroed out for the new elements.

One thing I could never find was how TM1 translates a dimension element into some sort of numeric array reference. On the applix site there used to be an internals doco but that seems to have been removed.

Any comments gratefully appreciated.
Last edited by John Hammond on Wed Apr 07, 2010 5:52 pm, edited 1 time in total.
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Dimension Copier?

Post by Andy Key »

How about adding copy statements for %1.rux and .blb for both the Attributes and Security cubes.
Andy Key
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: Dimension Copier?

Post by Wim Gielis »

Hello John

Last week, I took a variation on the theme of copying a dimension. The text is an article that I will put on my website once I have a number of articles ready.

Duplicate a dimension

Ever wanted an easy way to duplicate a dimension? I mean, have another dimension with exactly the same elements and hierarchical structures as a chosen dimension. Here is a bunch of TI code to do it for you.

Create a new TI process with data source None. Create a parameter in the Advanced > Parameters tab. Call this parameter pDimensionOld, it's a String, and the Prompt Question could be something along the lines of: Which dimension do you want to duplicate ? Create a second parameter called pDimensionNew, again a String, and the Prompt Question could be: How do you want to name the new dimension (leave empty for a generic name) ?

Here is the code in the Prolog tab:

Code: Select all

############################
# Wim Gielis
# Aexis NV Belgium
# wim.gielis@gmail.com
# 10/31/2009
# TI Code to duplicate a dimension, attributes are copied too
# Also on http://www.wimgielis.be
##############################

IF(LONG(pDimensionNew)=0);
     pDimensionNew=pDimensionOld | '_Copy';
ENDIF;

IF(DIMENSIONEXISTS(pDimensionOld)=0 % 
    DIMENSIONEXISTS(pDimensionNew)=1);
     PROCESSQUIT;
ENDIF;

DIMENSIONCREATE(pDimensionNew);


# loop through the elements of the original dimension
# to copy to the new dimension
i=1;

WHILE(i<=DIMSIZ(pDimensionOld));

     # CHILD
     vChildElement=DIMNM(pDimensionOld,i);
     vChildType=DTYPE(pDimensionOld,vChildElement);
     DIMENSIONELEMENTINSERT(pDimensionNew,'',
                            vChildElement,vChildType);

     # PARENT (loop through them)
     j=1;

     WHILE(j<=ELPARN(pDimensionOld,vChildElement));
          vParentElement=ELPAR(pDimensionOld,vChildElement,j);
          vChildWeight=ELWEIGHT(pDimensionOld,vParentElement,
                                vChildElement);
          DIMENSIONELEMENTINSERT(pDimensionNew,'',
                                 vParentElement,'C');
          DIMENSIONELEMENTCOMPONENTADD(pDimensionNew,
                   vParentElement,vChildElement,vChildWeight);
          j=j+1;
     END;

     i=i+1;

END;
Here is the code in the Epilog tab:

Code: Select all

# loop through the elements of the new dimension
# and fill in the attributes
i=1;

WHILE(i<=DIMSIZ(pDimensionNew));

     vElement=DIMNM(pDimensionNew,i);

     # attributes, if any
     IF(DIMENSIONEXISTS('}ElementAttributes_'|pDimensionOld)=1);

          # there are attributes, so loop through them
          j=1;

          WHILE(j<=DIMSIZ('}ElementAttributes_'|pDimensionOld));

               vAttributeName=DIMNM('}ElementAttributes_' | 
                                    pDimensionOld,j);
               vAttributeType=SUBST(DTYPE(pDimensionOld,
                                    vAttributeName),2,1);

               # insert attribute itself
               IF(i=1);
                    ATTRINSERT(pDimensionNew,'',vAttributeName,
                               'A' | vAttributeType);
               ENDIF;

               # update attribute values for the element
               IF(vAttributeType@='N');
                    ATTRPUTN(ATTRN(pDimensionOld,vElement,
                        vAttributeName),pDimensionNew,vElement,
                        vAttributeName);
               ELSE;
                    ATTRPUTS(ATTRS(pDimensionOld,vElement,
                        vAttributeName),pDimensionNew,vElement,
                        vAttributeName);
               ENDIF;

               j=j+1;

          END;

     ENDIF;

     i=i+1;

END;
The Prolog makes a loop through all elements in the Old dimension, inserting them (in the correct structure) in the new dimension. The Epilog tab mainly loops through all the attributes for that dimension and sets the attributes, if any.

As far as I know, in TI there is not an easy way to loop through subsets, except for copying folders in the TM1 database directory on the server. With the TM1 API, it will be possible too.


Wim
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
Kyro
Community Contributor
Posts: 126
Joined: Tue Nov 03, 2009 7:46 pm
OLAP Product: MODLR - The CPM Cloud
Version: Always the latest.
Excel Version: 365
Location: Sydney, Australia
Contact:

Re: Dimension Copier?

Post by Kyro »

Wim,
That looks great, do you think it would be possibly to use BATCH commands via EXECUTECOMMAND in TI to move the subsets?

Shameless Plug: do you think you could post that tutorial/article on www.tm1tutorials.com? I'd approve it right away. if not could I post it?
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: Dimension Copier?

Post by Wim Gielis »

Hi Ben

I will post up my articles that I already have on my website and I will let you know when I did that. I have 10 articles at the moment, all TI-related.

By the way, you also have interesting articles on the website, also the one above is useful.

I *think* the subsets can be moved as you propose, but I did not try it.

Wim
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
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: Dimension Copier?

Post by Wim Gielis »

Wim Gielis wrote:Hi Ben

I will post up my articles that I already have on my website and I will let you know when I did that. I have 10 articles at the moment, all TI-related.
Done ;)

Visit http://www.wimgielis.be, go to TM1 (on top) and then go to the articles. 12 articles have been put on the my site.

Have fun! Comments are always welcome.

Wim
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
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: Dimension Copier?

Post by John Hammond »

Andy
How about adding copy statements for %1.rux and .blb for both the Attributes and Security cubes.
You'll have to give me a bit more info on this one as I thought a .rux was a rules file associated with a cube.

Wim

Great code - I have learnt a great deal from following your code. Here's stuff to pump out a dimension to a sequel server table.

Code: Select all

If (1 = 1) ;

outputstring = 'insert into '  ;

outputstring = dimChartofAccounts ;

outputstring = ATTRS('dimChartofAccounts',dimChartofAccounts, 'Account Code') ;
outputstring = SUBST(outputstring,1,6);

y = DTYPE('dimChartofAccounts',dimChartofAccounts) ;
x = 5 ;

s_StringWithQuotes =dimChartofAccounts ;

l_LocNext = Scan ( Char(39), s_StringWithQuotes  );

While ( l_LocNext > 0 );


   asciioutput('c:\test.csv',s_StringWithQuotes );
    s_StringWithQuotes  = Subst ( s_StringWithQuotes , 1, l_LocNext - 1) | Char(34) | Subst ( s_StringWithQuotes , l_LocNext+1, Long (s_StringWithQuot
es ) - l_LocNext );
   asciioutput('c:\test.csv',s_StringWithQuotes );

     l_LocNext = Scan ( Char(39), s_StringWithQuotes  );

End;

dimChartofAccounts = s_StringWithQuotes ;

If (y@<>'C');





odbcoutput('TM1 Data Manager',expand('INSERT INTO [dmcuser].[tbl_CoA_TM1] ([code] ,[description]) VALUES (''%outputstring%'', ''%dimChartofAccounts%''
)')) ;

EndIf ;

EndIf ;
Thanks for all your replies...
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Dimension Copier?

Post by Andy Key »

John,
You'll have to give me a bit more info on this one as I thought a .rux was a rules file associated with a cube.
Absolutely correct. And one of the things that you are already copying is the }ElementAttributes cube. This is just another TM1 cube, so there is no reason why you can't have a rule associated with it. In the same vein, if you define security on the dimension you will get an }ElementSecurity cube and can have a rule on that as well.
Andy Key
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Dimension Copier?

Post by Martin Ryan »

Trap for young players (and older players too, as I learnt this the hard way not three weeks ago). If you copy an attributes cube in the back end so that it is supposed to reference a new dimension it won't work, as the dimensions that make up a dimension are written inside the .cub file itself, which is not readily editable.

E.g. if you have Entity.dim and }ElementAttributes_Entity.cub you cannot simply create Entityv1.dim and }ElementAttributes_Entityv1.cub by a simple back end copy and paste and expect the attributes cube to function correctly because the underlying .cub file will still reference Entity.dim, not your new Entityv1.dim. Best way would be to create a TI process that builds the attributes cube from scratch and populates it too.

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dimension Copier?

Post by lotsaram »

Martin Ryan wrote:Trap for young players (and older players too, as I learnt this the hard way not three weeks ago). If you copy an attributes cube in the back end so that it is supposed to reference a new dimension it won't work, as the dimensions that make up a dimension are written inside the .cub file itself, which is not readily editable.
Hi Martin,

I believe this was discussed recently here.
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Dimension Copier?

Post by Martin Ryan »

Quite right. I've been away for two weeks, didn't read all the posts that had been made in that time, so missed that one.
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
image2x
Posts: 125
Joined: Tue Jun 02, 2009 7:05 pm
OLAP Product: TM1, PAX, PAW, SPSS
Version: 2.0.916.10 on RHEL
Excel Version: 2016
Location: Minneapolis, MN

Re: Dimension Copier?

Post by image2x »

Wim,

Thanks for sharing your dimension copier code.

For some reason, I ran into problems with it under 9.4.1 HF16.

In the Epilog tab, I had to change two lines:

Code: Select all

FROM:              vAttributeType=SUBST(DTYPE(pDimensionOld, vAttributeName),2,1);
TO:                  vAttributeType=SUBST(DTYPE('}ElementAttributes_'|pDimensionOld, vAttributeName),2,1);

FROM:              ATTRINSERT(pDimensionNew,'',vAttributeName, 'A' | vAttributeType);
TO:                  ATTRINSERT(pDimensionNew,'',vAttributeName, vAttributeType);
DTYPE would return null unless I fully qualifed the attribute dimension and ATTRINSERT wouldn't accept "AS/AN/AA".

Note sure why this happening unless something really changed under the hood between releases.

-- John
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: Dimension Copier?

Post by Wim Gielis »

Hi John,

Nothing will have changed under the hood, I saw the same behavior.

Also, it works smoothly with your suggestions.

I guess something went serieously wrong during my tests, or I might have copied an earlier version of the code.

Thanks for sorting it out,

Wim
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
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: Dimension Copier?

Post by John Hammond »

Wim

Here's a bit more towards a Dimension Copier . To copy the subsets.

Code: Select all


# copies subsets of Dimension - will only copy MDX contents not the expression.

Pathname =  pDimensionOld | '}subs\*.sub' ;
PriorFileName = '' ;

SubsetNameOld = WildcardFileSearch( Pathname, PriorFilename);
PriorFileName = SubsetNameOld ;
i = 1 ;
WHILE (PriorFileName @<> '' ) ;
     #
     IF (SCAN('$',SubsetNameOld)  =  LONG(  SubsetNameOld)  ) ;
          asciioutput('c:\debug\prolog.txt','+++ partially saved subset skipped  [' | SubsetNameOld  | ']') ;
     ELSE ;
          SubsetOld =  SUBST(SubsetNameOld,1,LONG(  SubsetNameOld) - 4 ) ;
          asciioutput('c:\debug\prolog.txt','+++  [' | SubsetOld  | ']') ;
           SubsetOldSize =   SubsetGetSize(pDimensionOld, SubsetOld);

          SubsetDestroy(pDimensionNew, SubsetOld);
          SubsetCreate(pDimensionNew, SubsetOld);
           j = 1 ;
          WHILE ( j <= SubsetOldSize ) ;
                SubsetOldElement =    SubsetGetElementName(pDimensionOld, SubsetOld,  j);
                asciioutput('c:\debug\prolog.txt','Element ' | SubsetOldElement ) ;
                SubsetElementInsert(pDimensionNew, SubsetOld, SubsetOldElement,  j);

                j = j + 1 ;
                IF ( j > 10000 ) ;
                    PROCESSQUIT ;
                ENDIF ;

           END ;
     #
     ENDIF ;
     SubsetNameOld = WildcardFileSearch( Pathname, PriorFilename);
     PriorFileName = SubsetNameOld ;
    # safety feature
     i = i + 1 ;
    IF ( i > 1000 ) ;
        PROCESSQUIT ;
    ENDIF ;
END ;

Feel free to distribute without any warranty.
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: Dimension Copier?

Post by John Hammond »

Here is code to copy security on the dimension.

Code: Select all

# copies security from one dimension to another

i=1;

WHILE( i <=  DIMSIZ('}Groups'));

     vElement = DIMNM('}Groups',i);
     Permission = CELLGETS('}DimensionSecurity',pDimensionOld,vElement) ;
     Eltype = DTYPE('}Groups',vElement) ;
     Eltype2 = DTYPE('}Dimensions',pDimensionNew) ;
     IF ( Eltype @= 'S'  & vElement @<> 'ADMIN' & vElement @<> 'SecurityAdmin' & vElement @<> 'DataAdmin'  ) ;
              #  ASCIIOUTPUT('C:\DEBUG\JPH_prolog.txt',vElement | ' ' | Permission | ' ' | Eltype  | ' ' | Eltype2) ;
              CELLPUTS(Permission,'}DimensionSecurity',pDimensionNew,vElement) ;
     ENDIF ;
     i = i + 1 ;

END ;


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: Dimension Copier?

Post by Wim Gielis »

Thanks for the update John.

@Admins: this topic might be moved to the "Useful code, tips and tricks" subforum?

[AK: Moved accordingly.]
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
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: Dimension Copier?

Post by Wim Gielis »

Another variant along the same principle, can be found here:

http://forums.olapforums.com/viewtopic.php?f=3&t=3023

The code allows to split a dimension in several others, with no manual work (other than generalizing the code a bit).

Wim
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
Post Reply