Single TI process for many cube copies?

[BigG]

Hi There,

my TI Process prolog creates a datasource, source and target views, zeros target and uses parameter 'pTargetVersion' and user variable 'CubeVariable' from a control TI Process. This part is resuable for all cubes with Budget_Version.

I want to create a reusable data section. But because we have to choose a datasource and view then define its variables I cannot see how to do this dynamically with the data. I have an issue with the variables and especially the vValue variable I would normally define in the variables section of the TI process.

Does anyone have any suggestions. See my attempt (not tested ) below...cheers

Code: Select all

mycubename = CubeVariable;
myTarget = pTargetVersion;


#maximum 8 dimensions in a cube
    DimMax = 8;
    DimLast = TabDim ( mycubename , DimMax );
    While ( DimLast @= '' );
        DimMax = DimMax - 1;
    End;
#Reuse once recieved all Dim variables below


  counter = 1;

  While( TABDIM(mycubename , counter) @<> '' );

#repeat 8 times as this is the max assumed  for Budget Version cubes. If Budget_Version dim then make target version.
If(counter = 1);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim1 = myTarget;
ELSE;
Dim1 =  TABDIM(mycubename , counter);
ENDIF;

elseIf(counter = 2);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim2 = myTarget;
ELSE;
Dim2 =  TABDIM(mycubename , counter);
ENDIF;

elseIf(counter = 3);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim3 = myTarget;
ELSE;
Dim3 =  TABDIM(mycubename , counter);
ENDIF;

elseIf(counter = 4);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim4 = myTarget;
ELSE;
Dim4 =  TABDIM(mycubename , counter);
ENDIF;

elseIf(counter = 5);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim5 = myTarget;
ELSE;
Dim5 =  TABDIM(mycubename , counter);
ENDIF;

elseIf(counter = 6);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim6 = myTarget;
ELSE;
Dim6 =  TABDIM(mycubename , counter);
ENDIF;


elseIf(counter = 7);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim7 = myTarget;
ELSE;
Dim7 =  TABDIM(mycubename , counter);
ENDIF;

elseIf(counter = 8);
If(TABDIM(mycubename , counter) @= 'Budget_Version');
Dim8 = myTarget;
ELSE;
Dim8 =  TABDIM(mycubename , counter);
endif;

ENDIF;

    counter = counter + 1;

     End;

#now use dimmax again
If(DimMax = 3);

CellPutN(vValue,mycubename,Dim1,Dim2,Dim3);

elseIf(DimMax = 4);

CellPutN(vValue,mycubename,Dim1,Dim2,Dim3,Dim4);

elseIf(DimMax = 5);

CellPutN(vValue,mycubename,Dim1,Dim2,Dim3,Dim4,Dim5);

elseIf(DimMax = 6);

CellPutN(vValue,mycubename,Dim1,Dim2,Dim3,Dim4,Dim5,Dim6);

elseIf(DimMax = 7);

CellPutN(vValue,mycubename,Dim1,Dim2,Dim3,Dim4,Dim5,Dim6,Dim7);

elseIf(DimMax = 8);

CellPutN(vValue,mycubename,Dim1,Dim2,Dim3,Dim4,Dim5,Dim6,Dim7,Dim8);

endif;

[lotsaram]

Actually you are very close.

Testing the number of dimensions in a cube via a while loop on an incremented counter and breaking when TABDIM returns a blank string is the standard approach. This code should be on the prolog by the way as the value of a variable set on the prolog will be retained for the duration of the process, there is no need to test for each record once the data source has been assigned (also it seems to me you could simplify this section ...)

If statements on the data tab with different CellGetN and CellPutN depending on the number of dimensions is the only way to have a process with flexibility to work for multiple cubes so you are on the right track here also.

I'm assuming you already have code to set DataSourceNameForServer based on a pTargetCube parameter?

The other trick is that when you are setting up the process make sure that you have a cube as the source selected with the maximum possible number of dimensions as the number of data source variables can't be changed at runtime so you need to make sure that you have more than you need if you want your process to truly be flexible.

[Andy Key]

You should also have a test to check the data type of the cell you want to copy, and do a CellPutN or CellPutS depending on the result. As you are going to have n+1 variables defined (one for the maximum number of dimensions you have, as lotsa suggests, plus 1 for your value) you are going to have to have them all defined as String so that when you are copying a cube with less dimensions the variable that holds your value will change, and will become one of the variables that you have previously used for a dimension.

You can check the record variable VALUE_IS_STRING (I think that's what it is, I don't have a TM1 server running, and it is definitely five past beer o'clock) and then do either a CellPutN(StringToNumber(Value), dims) or CellPutS(Value, dims) as appropriate.

[BigG]

thanks to you both for quick response, I will find some time to compile my processes based on your suggestions and will post my results once tested (or failed )

[BigG]

Ok second attempt, I still have an issue with how to figure which dimension is my ‘Budget_Version’ dimension. If I remove the orginal post loop from data tab, how do I know what Dim is the Budget Version in the order? (as it changes every cube).

[Andy Key]

In your prolog, turn your TabDim checks round, so you start at MaxDim=1, check TabDim(MaxDim) and increment until TabDim returns a blank. Within the loop include a check to see if the name of the dimension matches the name of your Version dimension. If it does, set a variable, say VersionDimPosition, to the current value of MaxDim. When TabDim returns a blank, don't forget to take one off of MaxDim to get the number of dimensions in the cube. Then at the start of your Data tab, include a set of checks along the lines of

Code: Select all

If( VersionDimPosition = 1);
   vDim1 = myTarget;
ElseIf( VersionDimPosition = 2);
   vDim2 = myTarget;
etc

[BigG]

Ok final attempt, seems to work

As prior setup I have a number of cubes that are relevant to the process

• LU_View_Control cube which defines the current version (essentially the source version)
• ZZ_Dimension_Use for reference to see where dimensions are used based on http://cubewise.co.uk/tech-archive-46.p ... 8b7d125327

I also have a control cube that utilises the a parameter TI rule to pass the cube name run the generic process then loop through again . The versioncopy_control is a separate TI process with prolog:

Code: Select all

 

#pTargetVersion target version copy control cube
#Current version is always source for all version copy
#Executes TI process zUpdateCube_ZZ_Dimension_Use to  update 'ZZ_Dimension_Use' cube
#Loops through all cubes (based on control dim }Cubes)
#Looks for cubes with Budget _Version dimension based on 'ZZ_Dimension_Use' cube
#If cube with Budget_Version dim then executes VersionCopy_BU_ALL


DimUseCube = 'ZZ_Dimension_Use';
dimname = 'Budget_Version';
TIProcess = 'VersionCopy_BU_TEST';

#Update  'ZZ_Dimension_Use' cube
ExecuteProcess('zUpdateCube_ZZ_Dimension_Use');

i = 1;
While (i<= DIMSIZ('}Cubes'));
mycubename= DIMNM('}Cubes', i);

#IGNORE control cubes
IF(subst(mycubename,1,1) @<> '}');

         #Values are strings as both dims in cube are referenced as STRING
         IF(stringtonumber(CellGetS(DimUseCube,mycubename,dimname)) >=1);
              ExecuteProcess(TIProcess, 'pCubename', mycubename, 'pTargetVersion', pTargetVersion);
        ENDIF;

ENDIF;
        i=i+1;
END;
 

The main generic VersionCopy_BU_Test

Have maximum 6 dims for a cube. So use one of the cubes with 6 dims for ‘Datasource’ and ‘Variables’. Call all variables vDim1, vDim2…vDim6 and make sure the ‘Value’ is a string.
PROLOG

Code: Select all

 


myTarget = pTargetVersion;
mycubename = pCubeName;
mydimVer = 'Budget_Version';
mysourceview = 'ztmp_Source';
mytargetview = 'zTmp_Target';
mysourceversion =CellGetS('LU_View_Control','String','Current Version');
mysourcesub = 'zTmp_src'|SUBST(mycubename, 3,LONG(mycubename)-3 );
mytargetsub ='zTmp_tgt'|SUBST(mycubename, 3,LONG(mycubename)-3 );

#maximum 6 dimensions in a cube
#Find DimMax for current cube

###1. Get the Budget Version dimension's position in this cube #############
Budget_Version_Pos=0;
DimMax = 1;
DimLast = TabDim ( mycubename , 1 );
While ( DimLast @<> '' );
      IF(DimLast @= 'Budget_Version');
          Budget_Version_Pos=DimMax;
           ENDIF;
    DimMax = DimMax +1 ;
    DimLast = TabDim( mycubename , DimMax);
End;
DimMax = DimMax -1;

###1. check all the versions exist################
#source version
IF(DIMIX(mydimVer,mysourceversion)=0);
PROCESSQUIT;
ENDIF;

#Target Version
IF(DIMIX(mydimVer,pTargetVersion)=0 % DTYPE(mydimVer,pTargetVersion)@='C');
     PROCESSQUIT;
ENDIF;

###2. Create SOURCE view and ensure all SOURCE data is in view######################

If(ViewExists(mycubename, mysourceview)=1);
   ViewDestroy(mycubename, mysourceview);
endif;

VIEWCREATE(mycubename, mysourceview);

DataSourceType = 'VIEW';
DatasourceNameForServer = mycubename;
DatasourceCubeview = mysourceview;

VIEWSETSKIPCALCS(myCubeName,mysourceview,1);
VIEWSETSKIPRULEVALUES(myCubeName,mysourceview,1);

IF (subsetexists(myDimVer,mysourcesub )=1);
subsetdestroy(myDimVer,mysourcesub );
endif;

SUBSETCREATE(myDimVer,mysourcesub);

SubsetElementInsert(myDimVer, mysourcesub, mysourceversion, 1);

VIEWSUBSETASSIGN(mycubename, mysourceview, myDimVer, mysourcesub);

###3. Create TARGET view######################
If(ViewExists(mycubename, mytargetview)=1);
ViewDestroy(mycubename, mytargetview);
endif;
VIEWCREATE(mycubename, mytargetview);

IF (subsetexists(myDimVer,mytargetsub )=1);
subsetdestroy(myDimVer,mytargetsub);
endif;

SUBSETCREATE(myDimVer,mytargetsub);

SubsetElementInsert(myDimVer, mytargetsub, pTargetVersion, 1);

VIEWSUBSETASSIGN(mycubename, mytargetview, myDimVer, mytargetsub);

###4 .Zero TARGET view ######################
###Turn logging off, zero N level values in View, Turn logging on##
CubeSetLogChanges( mycubename, 0);
ViewExtractSkipCalcsSet ( mycubename, mytargetview, 1);
ViewZeroOut( mycubename, mytargetview);
CubeSetLogChanges(mycubename, 1);

###Will want to delete VIEW and TEMP SUBSETS in Epilog#########

DATA

Code: Select all

 
#now use dimmax again

         IF(Budget_Version_Pos = 1);
             vDim1 = myTarget;
          elseif(Budget_Version_Pos = 2);
             vDim2 = myTarget;
          elseif(Budget_Version_Pos = 3);
             vDim3 = myTarget;
          elseif(Budget_Version_Pos = 4);
             vDim4 = myTarget;
          elseif(Budget_Version_Pos = 5);
             vDim5 = myTarget;
          elseif(Budget_Version_Pos = 6);
             vDim6 = myTarget;
        ENDIF;

IF(DimMax = 3);

   IF( VALUE_IS_STRING = 1 );
     CellPutS( vDim4, mycubename, vDim1, vDim2, vDim3);
   ELSE;
     CellPutN( StringToNumber( vDim4), mycubename, vDim1, vDim2, vDim3);
   ENDIF;

ELSEIF(DimMax = 4);

   IF( VALUE_IS_STRING = 1 );
     CellPutS( vDim5, mycubename, vDim1, vDim2, vDim3, vDim4);
   ELSE;
     CellPutN( StringToNumber( vDim5), mycubename, vDim1, vDim2, vDim3, vDim4 );
   ENDIF;

ELSEIF(DimMax = 5);

   IF( VALUE_IS_STRING = 1 );
     CellPutS( vDim6, mycubename, vDim1, vDim2, vDim3, vDim4, vDim5 );
   ELSE;
     CellPutN(StringToNumber( vDim6), mycubename, vDim1, vDim2, vDim3, vDim4, vDim5 );
   ENDIF;

ELSEIF(DimMax = 6);

   IF( VALUE_IS_STRING = 1 );
     CellPutS( vDim7, mycubename, vDim1, vDim2, vDim3, vDim4, vDim5,vDim6 );
   ELSE;
     CellPutN( StringToNumber( vDim7), mycubename, vDim1, vDim2, vDim3, vDim4, vDim5,vDim6 );
   ENDIF;

ENDIF;

EPILOG

Code: Select all

 ######Destroy zTMP Views and SUBSETS############

mycubename =  pCubeName;
mydimVer = 'Budget_Version';
mysourceview = 'ztmp_Source';
mytargetview = 'zTmp_Target';
mysourcesub = 'zTmp_src'|SUBST(mycubename, 3,LONG(mycubename)-3 );
mytargetsub ='zTmp_tgt'|SUBST(mycubename, 3,LONG(mycubename)-3 );

If(ViewExists(mycubename, mysourceview)=1);
ViewDestroy(mycubename, mysourceview);
endif;


IF (subsetexists(myDimVer,mysourcesub )=1);
subsetdestroy(myDimVer,mysourcesub );
endif;

If(ViewExists(mycubename, mytargetview)=1);
ViewDestroy(mycubename, mytargetview);
endif;


IF (subsetexists(myDimVer,mytargetsub )=1);
subsetdestroy(myDimVer,mytargetsub);
endif;