TI Process Simple Code Changes not working as expected - Requesting your kind help

[Arden]

Hello,

I am fairly new to TM1 as a technology and since there are no other folks who are proficient in my org, reaching out here for your kind help/pointers please...

We have a TI process named "Employee Details_Division Transfer" which in its earlier form was used to transfer an employee in the EMS_EMPLOYEE DEtails Cube i.e. change their STATUS to 1 under the Target Division (ToDivision) and change their STATUS to 0 under the Source Division (FromDivision).

Business wanted to write in higher STATUS Values i.e. >1 in the EMS_Employee Details cube which is being done without any issues. However, now owing to higher status values the above TI Process code needs to be changed -- NEW GOAL is to enable the transfer of Higher Status from Source Division to Target division and make STatus as 0 under Source Division for a particular employee.

I have been trying to make Code Changes to achieve the above Goal. I have managed to retrieve and transfer the higher STATUS values as required above. However, for writing in the STATUS as 0 under the Source Division (FromDivision) -- I have failed multiple times.

No matter what employee I test for, the code of writing in 0 does not work properly. What is really happening is -- it is writing in 0 for all the records for that employee for both SourceDivision and TargetDivision.

Could you please help me in correcting the code for the above problem in the DATA Tab? I am completely stuck and really losing hope/confidence in myself as I am not able to decipher where it's going wrong.

I am describing an example for you to get a clear understanding and have attached screenshots and code snippets of my TI Process which I had created.

Example:

Here trying to transfer Employee 010018 from SourceDivison 5410 to TargetDivison 7207 for the effective month of May (i.e. TI process will operate on data from May till Dec).
The issue is occurring because of the last line of code with CellPutN in the DATA Tab. (5th point below)

Info and Attachments:

1. DataSource is the cube itself

2. Variables
Variable Name | Type | Contents
vScenario | String | Other
vYear | String | Other
vMonth | String | Other
vDivision | String | Other
vEmployee | String | Other
vEmployeeDetails | String | Other
vValue | String | Other

3. Parameters
Parameter | Type | Entered Value
pScenario | String | Current Forecast
pYear |String | 2021
pMonth |String | May
pEmployeeName |String | 010018
pFromDivision |String | 5410
pToDivision |String | 7207
pDelimiter | String | +

4. Prolog Code

Code: Select all

#****Prolog****#



#****Begin: Generated Statements***
#****End: Generated Statements****


#======================================================================
#Process for transfer data fromEmployee Details  to Employee Details for Division Transfer
#======================================================================


cProcess = GetProcessName();
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.';
cViewName =  '}' | cProcess | '.' | NumberToString( ROUND( RAND( ) *100000 ) );
cSubsetName = cViewName;

sDebugFile = cDebugFile | 'Prolog.log';
cCubeName = 'EMS_Employee Details';



#=================
#source view creation
#=================
If( ViewExists( cCubeName, cViewName ) = 1 );
    ViewDestroy( cCubeName, cViewName );
EndIf;

ViewCreate( cCubeName, cViewName );

ViewExtractSkipCalcsSet( cCubeName, cViewName, 1 );
ViewExtractSkipZeroesSet( cCubeName, cViewName, 1 );
ViewExtractSkipRuleValuesSet( cCubeName, cViewName, 1 );

#=============================================
#Excluding the previous months from the selected month
#=============================================

vExclude='';
 
z = DIMSIZ('Month');
While(z>0);
vMonth= DIMNM('Month', z);
 
x=ELLEV('Month',vMonth);

IF(x=0);
IF(ATTRN('Month', vMonth, 'Month Number') < ATTRN('Month', pMonth, 'Month Number'));
vExclude=vExclude|vMonth|'&';
ENDIF;
ENDIF;
 
z = z - 1;
END;


vExcluded=SUBST(vExclude, 1,(Long(vExclude)-1));


#------------BEGIN Scenario------------
vDimName = 'Scenario';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
      SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pScenario, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------------END Scenario----------------

#------------BEGIN Year----------------
vDimName = 'Year';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
      SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pYear, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#----------END Year------------------

#---------BEGIN Employee-------
vDimName = 'Employee';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
      SubsetDestroy(vDimName, cSubsetName );
EndIf;
ExecuteProcess('Bedrock.Dim.Sub.Create.ByElement',
#        'pDimension', vDimName,    'pSubset', cSubsetName,  'pElements', pEmployeeName
        'pDimension', vDimName,    'pSubset', cSubsetName,  'pElements', pEmployeeName, 'pDelimiter',pDelimiter 
      );
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#-------------END Employee_____

#--------BEGIN Division-----
vDimName = 'Division';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
      SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pFromDivision, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------END Division____


#-----------BEGIN All N level elements for all other dimensions------------
vDimName = 'Employee Details';
      ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
        'pDimension', vDimName,    'pSubset', cSubsetName,   'pExclusions', ''
      );
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );

#------------END All N level elements for all other dimensions----------

vDimName = 'Month';
      ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
        'pDimension', vDimName,    'pSubset', cSubsetName,   'pExclusions', vExcluded
      );
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------------END All N level elements for all other dimensions----------



#====================
# Completed view creation 
#====================

#===============
#Assign Datasource
#===============
DataSourceType = 'VIEW';
DatasourceNameForServer = cCubeName;
DatasourceNameForClient = cCubeName;
DatasourceCubeView = cViewName;

5. DATA Tab -- the last segment of code for writing in STATUS as 0 is the ISSUE

Code: Select all

#****Data Tab Code****#


#****Begin: Generated Statements***
#****End: Generated Statements****


#==============================================
#Code for transfering the employee details within the division
#==============================================

if (VALUE_IS_STRING=1, CellPutS(SVALUE,'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,vEmployeeDetails),
 CellPutN(NVALUE, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,vEmployeeDetails));

#=======================================================
#Retriving the status of Source month and Source Divison 
#=======================================================

vStatus=CellGetN('EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');


#==============================================================================
#Writing the status of Successive month from the selected month till last month for the Target  
#==============================================================================

CellPutN(vStatus, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,'Status');

#=======================================================


#================================================
#Making the Status of Source Month as 0  -- ISSUE is here
#================================================


CellPutN(0, 'EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');


#======Trying to Debug the non-working code of above for Status being written to 0====================
vDebugPath='\\test\ems\data\Debug.csv';

IF(~((CellGetN('EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status')) = 0));
  
  ASCIIOUTPUT(vDebugPath,vScenario,vYear,vMonth,pFromDivision,vEmployee,vEmployeeDetails,vValue);
  
EndIF;

6. Epilog

Code: Select all

Epilog


#****Begin: Generated Statements***
#****End: Generated Statements****


#==========================
#BEGIN CLEAN VIEW/SUBSETS
#==========================
#cleanup/destroy view and subsets
ViewDestroy(cCubeName, cViewName);
i = 1;
WHILE (tabdim(cCubeName,i) @<> '');
  SubsetDestroy(tabdim(cCubeName,i), cSubsetName);
  i = i + 1;
END;

#==========================
#END CLEAN VIEW/SUBSETS
#=========================

I have tried multiple times to debug the issue by writing into a csv file but here I see that the correct STATUS values from Source Division is being written for the records.

7. Before Execution -- Employee Details cube


8. After Execution -- Employee Details cube (with keeping the problematic code for writing in STATUS as 0 commented)


9. After Execution -- Employee Details cube (executing the problematic code for writing in STATUS as 0)




Kindly help! I would be much grateful.

Thanks so much!
-Arden

[MarenC]

Hi,

I suspect your issue is that in your view you have some dimensions at n level,

but your cellputs' on your data tab are specific, e.g. CellPutN(vStatus, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,'Status');

So for a given employee, it first runs through and puts in the correct status, and makes the status to whatever it is
but the next time it runs through the data tab for the same employee it looks up the status and sees it is zero and puts zero in!

I think the answer is to separate out the status part of the code into another process and make the view specific to status measure,
or write some logic into the existing process to deal with it.

Maren.

[Arden]

Hi Maren,

Thanks so much for the reply!

I got what you said about the cube view being built at n level. However, I am utterly clueless to one important point - if you see the Prolog code, it creates the cube view using 'pFromDivision' for 'Division' Dimension.

I managed to bring up and see the temp view being created. It looks as below:


All the dimensions used are from parameters. And Division is specifically the 'pFromDivison'.

1. Thus, when it comes to Data tab, it should ideally operate on the cube view as above and consider only pFromDivision while doing the 'CellPutN' for Status as 0... isn't it ? But it's not doing that. It is writing in 0 for the intersections with pToDivision as well. I am really perplexed to how it is doing this ..thoughts?

Code: Select all

CellPutN(0, 'EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');

2. Also, another point I am failing to grasp is the flow of control in the Data Tab. I learnt that Data tab does operate on each record i.e. the control flows through each record coming in from the Prolog.
So, if that is correct, and given that in the DATA Tab, it is retrieving the vStatus , writing in that Status to pToDivision and then writing in 0 for pFromDIvision (in that order) -- how it is looping through again and finding Status as 0 and writing the same again...?

Code: Select all

vStatus=CellGetN('EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');


#==============================================================================
#Writing the status of Successive month from the selected month till last month for the Target  
#==============================================================================
CellPutN(vStatus, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,'Status');


#================================================
#Making the Status of Source Month as 0  -- ISSUE is here
#================================================
CellPutN(0, 'EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');

3. When you say "write some logic into the existing process to deal with it" -- did you mean like create the CubeVIew on just the 'Status' measure? (here the problem is the process needs to transfer data for all other measures like 'EmployeeName', 'JObTItle', etc.. under 'EmployeeDetails' dimension as well)
Sorry, I did not understand as I am really new to this.

Thanks again for helping!

[Wim Gielis]

I haven’t read all details of this topic but regarding point 3:
You could create a cube view based on 1 measure.
Then do a series of CellGetN and CellGetS to the other measures you need, in the Data tab.

[declanr]

TIs process the data tab for 1 cell at a time.

Assume the first record it runs for is the Job Level measure; at this point it moves job level over to the target division. It also transfers the status at the same time and sets the source status to 0.

Assume the second record it runs for is Job Type, it transfers the job type. It also tries to transfer status at the same time, but the source status is now 0 because that’s what it was set to when the first record was processed. As a result it now sends 0 to the target division.

The process that you have should be close enough, just needs a couple of small tweaks on the data tab. the first section will transfer the value from source to target division for any measure (including status.) As a result you don’t need the cellgetn/ cellputn that is specifically for status- just get rid of them.

You do however want to keep the bit that sets source status to 0… BUT you only want to run it AFTER the status has transferred. So wrap an if statement around that cellputn. The if statement would check if vEmployeeDetail = “status”, so it only runs for that 1 measure.

[MarenC]

Hi Declan,

Isn't the problem with adding the If as you suggest that there are 2 divisions in the example given (5410 and 7207), so it will run through
the status twice? And the second time it will put in the zero, therefore not solving the issue? And of course there may be 3 or 4 divisions for all we know or multiple of some other dimension.

Edit: The above will not be an issue because the subset for division is created from pDivisionFrom, so only one division in the view. Still not 100% sure if status will only ever run once though.

Maren

[declanr]

Hi Declan,

Isn't the problem with adding the If as you suggest that there are 2 divisions in the example given (5410 and 7207), so it will run through
the status twice? And the second time it will put in the zero, therefore not solving the issue? And of course there may be 3 or 4 divisions for all we know or multiple of some other dimension.

Edit: The above will not be an issue because the subset for division is created from pDivisionFrom, so only one division in the view. Still not 100% sure if status will only ever run once though.

Maren

Your edit beat me to it. Like you say the OP has a good source view on the process.

Why are you not sure status will only run once?



I meant to use code as per below.
In this case "Status" is transferred ONLY when it is the cell being handled by the TI and the standard CellPutS or CEllPutN handles that as well as it does all other measures.
The only addition is the IF statement then sets the source to zero after the fact.

Code: Select all

#==============================================
#Code for transfering the employee details within the division
#==============================================

if (VALUE_IS_STRING=1, CellPutS(SVALUE,'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,vEmployeeDetails),
 CellPutN(NVALUE, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,vEmployeeDetails));

#==============================================
# When measure is status, set source to 0 AFTER transferring to target.
#==============================================
If ( vEmployeeDetails @= 'Status' );
   CellPutN(0, 'EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');
EndIf;

[MarenC]

Hi Declan,

I was hedging my bets, but looking at how the source is built I don't think this will be an issue.

So, yes, your solution should do the trick.

Maren

[Arden]

Hi Declan,

Thanks so much - the solution worked! And I understood the flow of records through the DATA tab clearly now from your explanation.

Thanks again Maren, and Thank you Wim -- this community is great!

-Arden

[Wim Gielis]

Hi Declan,

Thanks so much - the solution worked! And I understood the flow of records through the DATA tab clearly now from your explanation.

Thanks again Maren, and Thank you Wim -- this community is great!

-Arden

My contribution was small this time. Credits to the others.
Not only the knowledge but also the spare time to read through all this.