There are many cases where we want to change element, such as rename the element, realign data and correct wrong input. It is very normal action as SQL update in database. Unfortunately, there is no such existing TM1 function to perform. This document is intended to find a generic approach to update a element or a batch update elements.
Design approach
There is no functionality in TM1 that you can dynamically generate command, and then execute it. Therefore, the only approach left is to use write script based on number of dimensions.
The good news is that TM1 does have local variables, which can be used to dynamically specify data source. This functionality enable us to dynamically get view without manually create view for each cube.
In order to update element, we must use CellGetN and CellPutN. Such function needs to have exact dimension sequence. In addition, you must know which element is in which dimension. There is no way that we can use combination of number of dimension and number of position to get script. We must hard code each case.
Implementation
Parameters
pCube
|
String
|
Specify cube name
|
pOldElement1
|
String
|
Specify element 1 to be changed from
|
pOldElement2
|
String
|
Specify element 2 to be changed from
|
pNewElement1
|
String
|
Specify element 1 to be changed to
|
pNewElement2
|
String
|
Specify element 2 to be changed to
|
pCase
|
numeric
|
Specify case, which is needed to be defined in Data tab
|
Sample
ExecuteProcess('UpdateElement','pCube','Sales','pOldElement1','Customer 1','pOldElement2','Product 1','pNewElement1','Customer 1','pNewElement2','Product 2','pCase',1);
ExecuteProcess('UpdateElement','pCube','Plan_BudgetPlan','pOldElement1','FY 2003 Budget','pOldElement2','Input','pNewElement1','FY 2003 Budget','pNewElement2','line input','pCase',2);
Variable
In order to get TI process setup, create a dummy cube and dummy view to start
Add 10 variables
TI Script for Prolog
#****Begin: Generated Statements***
#****End: Generated Statements****
### Constants ###
cProcess = 'UpdateElement';
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
sRandomInt = NumberToString( INT( RAND( ) * 1000 ));
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.' | sRandomInt ;
### Initialize Debug ###
sDebugFile = cDebugFile | 'Prolog.debug';
# Log start time
AsciiOutput( sDebugFile, 'Process Started: ' | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
# Log parameters
AsciiOutput( sDebugFile, 'Parameters: pCube: ' | pCube );
AsciiOutput( sDebugFile, ' pOldElement1: ' | pOldElement1 );
AsciiOutput( sDebugFile, ' pOldElement2: ' | pOldElement2 );
AsciiOutput( sDebugFile, ' pNewElement1: ' |pNewElement1 );
AsciiOutput( sDebugFile, ' pNewElement2: ' |pNewElement2 );
AsciiOutput( sDebugFile, ' pCase: ' | NumberToString(pCase) );
### Validate Parameters ###
nErrors = 0;
# Validate source cube
If( pCube @= '' % CubeExists( pCube ) = 0 );
nErrors = 1;
sMessage = 'Invalid source cube specified: ' | pCube;
AsciiOutput( sDebugFile, sMessage );
DataSourceType = 'NULL';
ItemReject( sMessage );
EndIf;
### Determine number of dims in cube ###
nDimensionCount = 0;
While( TabDim( pCube, nDimensionCount + 1 ) @<> '' );
nDimensionCount = nDimensionCount + 1;
End;
If( nDimensionCount > 10 );
nErrors = 1;
sMessage = 'Cube has too many dimensions: ' | pCube | ' max 10 dims catered for, TI must be altered to accomodate.';
AsciiOutput( sDebugFile, sMessage );
DataSourceType = 'NULL';
ItemReject( sMessage );
EndIf;
### Assign Datasource ###
DataSourceType = 'VIEW';
DatasourceNameForServer = pCube;
DatasourceNameForClient = pCube;
NValue = 0;
SValue = '0';
Value_is_String = 0;
sView = 'TMP';
If( ViewExists( pCube, sView ) = 1 );
ViewDestroy( pCube, sView );
EndIf;
ViewCreate( pCube, sView );
ViewExtractSkipCalcsSet( pCube, sView, 1 );
ViewExtractSkipRuleValuesSet( pCube, sView, 1);
ViewExtractSkipZeroesSet( pCube, sView, 1 );
DatasourceCubeView = sView;
### Get cube info ###
sDim1 = TabDim( pCube, 1 );
sDim2 = TabDim( pCube, 2 );
sDim3 = TabDim( pCube, 3 );
sDim4 = TabDim( pCube, 4 );
sDim5 = TabDim( pCube, 5 );
sDim6 = TabDim( pCube, 6 );
sDim7 = TabDim( pCube, 7 );
sDim8 = TabDim( pCube, 8 );
sDim9 = TabDim( pCube, 9 );
sDim10 = TabDim( pCube, 10 );
AsciiOutput( sDebugFile,'1',sDim1,'2',sDim2,'3',sDim3,'4',sDim4,'5',sDim5,'6',sDim6,'7',sDim7,'8',sDim8,'9',sDim9,'10',sDim10 );
### End Prolog ###
TI Script for Data
#****Begin: Generated Statements***
#****End: Generated Statements****
### Initialise Debug ###
# Set debug file name
sDebugFile = cDebugFile | 'data.debug';
### Check for error in prolog ###
If( nErrors > 0 );
ProcessBreak;
EndIf;
### Write data from source cube to target cube ###
If( pCase = 1 );
if (v1 @=pOldElement1 & v2 @=pOldElement2);
AsciiOutput( sDebugFile, pCube,v1,v2,v3,v4);
vValue = Numbr( v4 );
CellPutN(0 ,pCube,v1,v2,v3);
CellPutN(vValue + CellGetN(pCube,pNewElement1,pNewElement2,v3), pCube,pNewElement1,pNewElement2,v3);
endif;
endif;
If( pCase = 2 );
#AsciiOutput( sDebugFile, pCube,v1,v2,v3,v4,v5,v6,v7,v8);
if (v1 @=pOldElement1 & v6 @=pOldElement2);
AsciiOutput( sDebugFile, pCube,v1,v2,v3,v4,v5,v6,v7,v8);
vValue = Numbr( v8 );
CellPutN(0 ,pCube,v1,v2,v3,v4,v5,v6,v7);
CellPutN(vValue + CellGetN(pCube,pNewElement1,v2,v3,v4,v5,pNewElement2,v7), pCube,pNewElement1,v2,v3,v4,v5,pNewElement2,v7);
endif;
endif;
### End Data ###
TI Script for epilog
#****Begin: Generated Statements***
#****End: Generated Statements****
### Initialise Debug ###
sDebugFile = cDebugFile | 'Epilog.debug';
### Finalise Debug ###
# Log errors
If( nErrors <> 0 );
AsciiOutput( sDebugFile, 'Errors Occurred' );
EndIf;
# Log finish time
AsciiOutput( sDebugFile, 'Process Finished: ' | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
### If errors occurred terminate process with a major error status ###
If( nErrors <> 0 );
ProcessQuit;
EndIf;
### End Epilog ###
No comments:
Post a Comment