How to modify TM1 data using TI script

Check list of all posts

Introduction

Further to ex post (How to create a TM1 view using TI script), we want to leverage the idea to modify TM1 data. As known, the only way to modify TM1 data is to use CellPutN  and CellPutS. However, these two functions need to have exact dimension sequence. It presents a very big challenge to have a generic approach to modify TM1 data. The goal is to generic approach to modify TM1 data. A few thoughts are shared below.

Make TM1 Data Source dynamic

# Step 1: create dynamic view

# Step 2: Assign this View to become TI data source view

DataSourceType = 'VIEW';

DatasourceNameForServer = pCube;

DatasourceNameForClient = pCube;

DatasourceCubeView = sView;

Use case statement to cover all cases

# Step 1: Determine number of dimensions

nDimensionCount = 0;

sDimension = TabDim( pCube, nDimensionCount + 1 );

nDimensionIndex = 0;

While( sDimension @<> '' );

 nDimensionCount = nDimensionCount + 1;

 If( sDimension @= pDimension );

   nDimensionIndex = nDimensionCount;

 EndIf;

 sDimension = TabDim( pCube, nDimensionCount + 1 );

End;

# Step 2: Use CellPutN (or CellPutS) to cover all cases

 If( nDimensionCount = 2 );

        CellPutN( Numbr( v4 )+CellGetN( pCube, v1, v2 ), pCube, v2, v3 );

     ENDIF;

 ElseIf( nDimensionCount = 3 );

       CellPutN( Numbr( v4 )+CellGetN( pCube, v1,v2, v3 ), pCube, v1, v2, v3 );

     ENDIF;

 ElseIf( nDimensionCount = 4 );

       CellPutN( Numbr( v5 )+CellGetN( pCube, v1,v2, v3,v4 ), pCube, v1, v2, v3, v4 );

     ENDIF;

 ElseIf( nDimensionCount = 5 );

       CellPutN( Numbr( v6 )+CellGetN( pCube, v1,v2, v3,v4, v5), pCube, v1, v2, v3, v4, v5 );

     ENDIF;

 ElseIf( nDimensionCount = 6 );

       CellPutN( Numbr( v7 )+CellGetN( pCube, v1,v2, v3,v4 ,v5,v6), pCube, v1, v2, v3, v4, v5, v6 );

     EndIf;

………………………..

Please note that when there is nDimensionCount dimensions, the will be a variable with nDimensionCount + 1, as it represents Value.

Map variable between source and target

Note that source view is created by data source. What we need to resolve is to figure out the position of target variable.

# Step 1: Determine position of pDimension in the cube

nDimensionCount = 0;

sDimension = TabDim( pCube, nDimensionCount + 1 );

nDimensionIndex = 0;

While( sDimension @<> '' );

 nDimensionCount = nDimensionCount + 1;

 If( sDimension @= pDimension );

   nDimensionIndex = nDimensionCount;

 EndIf;

 sDimension = TabDim( pCube, nDimensionCount + 1 );

End;

# Step 2: Map variable to a specified element pTargetElement

If( nDimensionIndex = 1 );

 v1 = pTargetElement;

ElseIf( nDimensionIndex = 2 );

 v2 = pTargetElement;

ElseIf( nDimensionIndex = 3 );

 v3 = pTargetElement;

ElseIf( nDimensionIndex = 4 );

 v4 = pTargetElement;

ElseIf( nDimensionIndex = 5 );

 v5 = pTargetElement;

ElseIf( nDimensionIndex = 6 );

 v6 = pTargetElement;

……………….

Result

Cube

Request to copy data

pDimension:Product

pSourceElement:Product 1

pTargetElement:Product 2

pDimension2:Customers

pSourceElement2:Customer 1

pTargetElement2:Customer 2

pDimension3:Measure

pSourceElement3:Sales

pTargetElement3:Cost

Source view

Target view

Data changed