wimgielis.com

Copy all data from one element to another

• Feb. 14, 2010

	Example files with this article:
	Copy all data on an element

Introduction

Often a TM1 consultant or Admin person needs to copy all data on an element to another element. Examples may include a budget version iteration that is duplicated to ease data entry, or an element that is used as a backup. Also, in some cases, a TI process might temporarily store data on another element.

Options

You can of course do this manually. Open the cube in a Cube viewer, create a view containing source and target element, and list all elements to the lowest level in rows and/or columns. Don't forget to turn zero suppression on or your view is very likely to be too big. Then, either use data spreading or copy-paste the data. Yet another approach is to slice the data on the source element to Excel, and use a bunch of DBSW functions to upload the values on the target element. But over at TM1 forum, Appleglaze28 asks for a generic TI-based approach. Here is how.

A TI approach

To obtain all the below code in a process, right-click the file on top of the page and Save it. Note that you will certainly need to adjust the process a bit to match your TM1 server and cube.

Create a view on the cube in which you need to copy data. It can be a really trivial view with only 1 element selected in each of the dimensions, that is not important. But you will find it handy to layout the dimensions in the cube to match the order of the dimensions in the cube: stack the last dimension of the cube in the columns, the last-but-one dimension in the rows, and the other dimensions (in the right order) in the titles. Save the view with the name SourceView.

After that, create a new process. As the Datasource Type, choose TM1 > Cube view. In the Data Source Name, navigate to the cube and the view you just created. Your view is an easy one and does not contain the real source data to be copied, but hold on… TI code in the Prolog tab of the process will recreate the view on the fly depending on the element whose data you need to copy! All that you need to do now is make sure it exists to help you setup the process.

Then turn to the Advanced > Parameters tab and create 3 parameters:

Before diving into the code, a few words on the benefits of this process. The process allows you to copy text data. In addition, it allows you to copy data from consolidated levels. Also, pay attention to the 7 Preliminary checks that are done prior to advancing to the real stuff. That boils down to creating a view for the target data and zero it out, and creating a view for the source data to loop through in the Data tab. The source view potentially includes consolidated values if you ask so based on pSourceElement.

TI code

Here is the code in the Prolog tab:

# Wim Gielis
# https://www.wimgielis.com
#####
# TI code to copy data from 1 element to another
# (elements must be in the same dimension)
# 11/12/09
#####
## A. Explicit constants

vCubeName='MYCUBE_CHANGEOVERHERE';
vSourceViewName='SourceView';
vTargetViewName='TargetView';
vSourceSubsetName='SourceSubset';
vTargetSubsetName='TargetSubset';


## B. Preliminary checks

# dimension-related
IF(DIMENSIONEXISTS(pDimensionName)=0);
     PROCESSQUIT;
ENDIF;

# elements-related
IF(DIMIX(pDimensionName,pSourceElement)=0);
     PROCESSQUIT;
ENDIF;

IF(DIMIX(pDimensionName,pTargetElement)=0 %
   DTYPE(pDimensionName,pTargetElement)@='C');
     PROCESSQUIT;
ENDIF;

IF(TRIM(pSourceElement)@=TRIM(pTargetElement));
     PROCESSQUIT;
ENDIF;

# process-related
IF(DATASOURCETYPE@<>'VIEW');
     PROCESSQUIT;
ENDIF;

IF(DATASOURCENAMEFORSERVER@<>vCubeName);
     PROCESSQUIT;
ENDIF;

IF(DATASOURCECUBEVIEW@<>vSourceViewName);
     PROCESSQUIT;
ENDIF;


## C. Create a view containing all data for the Source element

# 1. Tidy up the Source view
VIEWDESTROY(vCubeName,vSourceViewName);
SUBSETDESTROY(pDimensionName,vSourceSubsetName);


# 2. Create subset
SUBSETCREATE(pDimensionName,vSourceSubsetName);
SUBSETELEMENTINSERT(pDimensionName,vSourceSubsetName,pSourceElement,1);


# 3. Create view & assign subset
VIEWCREATE(vCubeName,vSourceViewName);
VIEWSUBSETASSIGN(vCubeName,vSourceViewName,pDimensionName,vSourceSubsetName);

IF(DTYPE(pDimensionName,pSourceElement)@='C');
     VIEWSETSKIPCALCS(vCubeName,vSourceViewName,0);
ENDIF;

VIEWSETSKIPRULEVALUES(vCubeName,vSourceViewName,0);


## D. Zero out a view containing all data for the Target element

# 1. Tidy up
VIEWDESTROY(vCubeName,vTargetViewName);
SUBSETDESTROY(pDimensionName,vTargetSubsetName);


# 2. Create subset
SUBSETCREATE(pDimensionName,vTargetSubsetName);
SUBSETELEMENTINSERT(pDimensionName,vTargetSubsetName,pTargetElement,1);


# 3. Create view & assign subset
VIEWCREATE(vCubeName,vTargetViewName);
VIEWSUBSETASSIGN(vCubeName,vTargetViewName,pDimensionName,vTargetSubsetName);


# 4. Zero out the view
VIEWZEROOUT(vCubeName,vTargetViewName);


# 5. Tidy up
VIEWDESTROY(vCubeName,vTargetViewName);
SUBSETDESTROY(pDimensionName,vTargetSubsetName);

Here is the code in the Data tab:

IF(CELLISUPDATEABLE(vCubeName,
       MY_DIMENSION_VARIABLES_CHANGE_OVER_HERE,
       pTargetElement,OTHER_DIMENSION_VARIABLES)=1);
     IF(VALUE_IS_STRING=0);
          CELLPUTN(NValue,vCubeName,
          MY_DIMENSION_VARIABLES_CHANGE_OVER_HERE,
          pTargetElement,OTHER_DIMENSION_VARIABLES);
     ELSE;
          CELLPUTS(SValue,vCubeName,
          MY_DIMENSION_VARIABLES_CHANGE_OVER_HERE,
          pTargetElement,OTHER_DIMENSION_VARIABLES);
     ENDIF;
ENDIF;

Here is the code in the Epilog tab:

# 4. Tidy up the Source view
VIEWDESTROY(vCubeName,vSourceViewName);
SUBSETDESTROY(pDimensionName,vSourceSubsetName);

Caveats

As you can tell from inspecting the code, the code in the Data tab is specific to your cube at hand. Therefore, you will certainly have to adjust the code in the Data tab. Together with the top statements in the Prolog tab, these are the only changes you need to make to the process. For instance, the variable containing the name of the view for the source data (in the Prolog tab) should match your choice in the source tab. Specifically for the Data tab, add the correct variable names representing the dimensions, in the correct order! If you do this correctly the process should run smoothly. If you prefer to, turn off the logging of changes to this cube whilst executing it. Instead of using this process you could make a totally generic script instead. Hereby you allow for cubes containing up to 15-20 dimensions. Keep everything in the TI process as generic as possible, and call that TI process from another process or command button, passing appropriate parameter values.

• « Previous TM1 article
• Next TM1 article »