RunProcess explanation

[MarenC]

Hi,

I have read the explanation for RunProcess which, "lets you run TurboIntegrator processes in parallel, each on its own thread that is managed by TM1 Server".

What I can't figure out is how to actually use it!

So would I put a number of RunProcess statements in one TI master process and just run the master process. So would they all run at the same time? For example on the prolog of the master process,

RunProcess('ProcessA', 'Month',1)
RunProcess('ProcessA', 'Month',2)
RunProcess('ProcessA', 'Month',3)
RunProcess('ProcessA', 'Month',4)


Or would I create a chore for each process and set them to run at the same time?

Maren

[tomok]

It's up to you how you use it but I think the general concept is that you would have a master process that has the RunProcess commands in it. You could call those from the Prolog but I think the real power is in calling them from the Data tab. For example, we have a number of Oracle ledgers we have to load on a daily basis and I use a subset of ledger list as a data source for the TI process and in the data tab I call the RunProcess, passing the specific ledger I want to load. It will cycle through all the ledgers pretty quickly and launch a separate thread for each ledger so that effectively they are all loading at the same time.

If I load each ledger serially it takes about an hour to run. Using RunProcess it takes about 15 minutes. The only thing missing is I can't specify which or how many cores TM1 is going to use but for the most part it works well. It also is only really effective if your "batches" are somewhat homogenous. If one ledger had 80% of the records and all the other ones combined had 20% then using RunProcess really wouldn't offer much in time savings.

[lotsaram]

So would I put a number of RunProcess statements in one TI master process and just run the master process. So would they all run at the same time? For example on the prolog of the master process,

RunProcess('ProcessA', 'Month',1)
RunProcess('ProcessA', 'Month',2)
RunProcess('ProcessA', 'Month',3)
RunProcess('ProcessA', 'Month',4)


Or would I create a chore for each process and set them to run at the same time?

You would do the former.
You can already create multiple chores whihc execute at the same time. You don't need RunProcess for that!

...the general concept is that you would have a master process that has the RunProcess commands in it. You could call those from the Prolog but I think the real power is in calling them from the Data tab.

I would caution, and I mean really caution using RunProcess on the Data or Metadata tabs. It's OK for Tom becasue he really knows what he's doing. But this is only suitable where you know you have only a handful of records which will be processed on the Data tab. Otherwise it's a very easy, even surefire way of swamping the CPU and freezing your server if there are more RunProcess calls than you have CPUs.

[MarenC]

Many thanks for the explanations.

I will defer using it on the data tab until I know what I am doing!

Maren

[blackhawk]

The biggest problem with this new command is there is no way to know when all of them are done so that you can do the next sequential action. For example, after 12 months are loaded, how do you then kick of an allocation script, or a email saying everything is fine. You have to end up jumping through some synchronization hoops (i.e. updating a cube) to get this all to work properly. What happens when one fails? How do you stop subsequent processes based on a condition? How do you control the size of the queue to keep it within CPU limits? etc. etc.

In my opinion, it really isn't any better than TM1RunTI, other than it taking up a single connection.

We ended up having to write our own external command lines to really do what you need to for parallel processing. Hopefully IBM will add other control functions to make this truly usable outside of simplistic use cases.

[MarenC]

Hi,

I decided I would try using RunProcess on the data tab, in test I hasten to add.

So I have a master process which calls another process with RunProcess included on the data tab of the called process.

In the master view I build an mdx created view which basically returns 12 employee records, so 12 rows.

When I run the process only 9 records are updated. If I change the mdx so only 9 rows are returned I still get 9 records updated. Basically it appears that no matter how many records/rows the MDX creates it only updates 9.

I also notice that the CPU spikes to 100% but it still completes

So why would it be only updating 9 records and a question to Tomok if he is listening, have you come up against this problem with your ledger process and if so how do you get around it?



Maren

[tomok]

So why would it be only updating 9 records and a question to Tomok if he is listening, have you come up against this problem with your ledger process and if so how do you get around it?

No, I have not come across any issues with using RunProcess except that once the cores are all tied up it seems to no longer run the processes in parallel, but rather stacks them up and runs the remaining in succession. I haven't done enough digging into it to find out the exact cause.

[EvgenyT]

You can use RushTi to mitigate the risk of blowing up your cores

[paulsimon]

Hi

Are all the processes operating on separate views with separate subsets? If not then one process may be destroying the view used by another part way through. Using temporary subsets and views may help overcome that.

However, I still tend to agree with others here, that experimenting with parallel processing is all very well, but first I would try running a single process to see if there is actually any problem in loading data quickly enough.

Regards

Paul Simon

[EvgenyT]

The biggest problem with this new command is there is no way to know when all of them are done so that you can do the next sequential action. For example, after 12 months are loaded, how do you then kick of an allocation script, or a email saying everything is fine. You have to end up jumping through some synchronization hoops (i.e. updating a cube) to get this all to work properly. What happens when one fails? How do you stop subsequent processes based on a condition? How do you control the size of the queue to keep it within CPU limits? etc. etc.

In my opinion, it really isn't any better than TM1RunTI, other than it taking up a single connection.

We ended up having to write our own external command lines to really do what you need to for parallel processing. Hopefully IBM will add other control functions to make this truly usable outside of simplistic use cases.

Well, you can control the size of the queue with RushTI. I use it very extensively and works great. As for the kick of an allocation script, you could use Syncronized () to queue your allocation script while Master Process of RushTI is executing.

In my opinion, it really isn't any better than TM1RunTI, other than it taking up a single connection.

I would disagree with you here, RunProcess takes away all the complexities of executing RunTI i.e. server, credentials etc. Also, RunProcess leverages Rest API to make a connection therefore you can use the same connection to kick off multiple threads without incurring a lock (a known issue with CAM and RunTI).

[MarenC]

Hi,

I decided to test this further. I built some new processes just to simply things.

I built a Master Process and another process which is called by the master process.

The Called Process does the following:

Prolog:
Builds a view based on mdx. For the employee dimension the mdx is for a single employee and passes in a pEmp parameter, so mdx is
Expand('{[Employee].[%pEmp%]}').
The data source is defined on the prolog as follows:
DatasourceType = 'VIEW';
DatasourceNameForServer = 'Job Info Cube';
DatasourcecubeView = vName;

(vName = ‘Admin Process_' | pEmpID) – so the view name = the pEmp parameter and the subsets also include the pEmp in their name, ensuring each time the process is run the view and susbsets have a unique name.

Data Tab:
I simply asciioutput some data, as follows:
Asciioutput(sFile, vVersion, vCostCode, vEmployee, vJob, numbertostring(vValue));

So each time the master runs the called process a file is created which is basically the name of the employee.

The Master Process uses a TM1 employee dimension leaf level subset as its data source. I then use the following code on the data tab, I basically want to just test using 200 employees but I know that only 47 of the 200 have data so the upshot should be that 47 asciioutput files are created):

Code: Select all

If(Stringtonumber(vEmpindx) < 200);
  RunProcess('Called Process', 'pEmp', vEmp, 'pDeleteView', 'Yes');
 Endif;

(vEmpIndx is derived as follows: Numbertostring(Dimix('Employee', vEmp))

The upshot of the above is only between 4 and 7 files get created. However if I adjust the code to include the sleep function, then it appears to work, i.e. 47 files are created. So the code on the data tab of the master process is as follows

Code: Select all

If(Stringtonumber(vEmpindx) < 200);
 RunProcess('Called Process', 'pEmp', vEmp, 'pDeleteView', 'Yes');
  Sleep(500);
Endif;

I then tried increasing the <200 to <300 and this should have returned 76 files, and that is how many were created. So at the moment I am thinking adding the sleep function resolved this issue.

So my conclusion is that the reason the process wasn’t working is that it was running too fast and had to be slowed down in order to bring some order to things.

So I am puzzled about the technicalities of all this! Hoping you can throw any light on this?

Hope all the above makes sense.

Maren

[gtonkin]

I would be curious to know what sFile is defined as - sounds like you may be using a date and time stamp and because the process is executing quickly, you end up over-writing the previously created file.
You say name of the employee but anything else being done to it? Share the code perhaps?

[MarenC]

Hi Gtonkin,

sFile is as follows: sFile = Expand('C:\TM1 Servers\Test\Exports\Test_%pEmp%.csv');

So the file is unqiue because it includes the name of the Employee. I also tried cellput 1 to a 2 dimension cube instead of asciioutputs and the same issue occurs. Unless I insert the sleep function the results are unpredictable.

I will try some time later to provide the full code for both processes.

Maren

[MarenC]

Hi,

the code is as follows:

The following is the Master Process Code, which is all on the Data Tab.

The Data Source is a leaf level subset of the employee dimension. Code is:

Code: Select all

#****Begin: Generated Statements***
vEmpIndx=
Numbertostring(Dimix('Employee', vEmp));
#****End: Generated Statements****

If(Stringtonumber(vEmpindx) < 300);

RunProcess('CalledProcess', 'pEmpID', vEmp, 'pDeleteView', 'Yes');

Endif;

##########################################################

The Following is the Called Process.

Prolog:

Code: Select all

sFile = Expand('C:\TM1 Servers\Test\Exports\Test_%pEmp%.csv');

vView = 'Admin - Create View';


### Create view of Job Info Cube. Define values to pass to Admin - Create View parameters.

	vCube = 'Job Info';
	vName = 'Admin - TempView' | pEmpID;
	vSubsetID = 'TI' | pEmpID;
	vSkipZero = 'Yes';
	vSkipC = 'Yes';
	vDim1 = '{[Versions].[Actuals]}';
	vDim2 = '{[Years].[2019/2020]}';
	vDim3 = '0';
	vDim4 = '0';
	vDim5 = '{Except(TM1FILTERBYLEVEL({TM1SUBSETALL( [Cost Centre])}, 0), TM1FILTERBYLEVEL( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Cost Centre] )}, "*Temp*")}, 0))}';
	vDim6 = Expand('{[Employee].[%pEmpid%]}');
	vDim7 = '0';
	vDim8 = '{[Job Lines].[Actual Pay]}';
	vDim9 = '0';		
	vDim10 = '0';
	vDim11 = '0';
	vDim12 = '0';
	vDim13 = '0';
	vDim14 = '0';
	vDim15 = '0';
	vDim16 = '0';
	vDim17 = '0';
	vDim18 = '0';
	vDim19 = '0';
	vDim20 = '0';
	vDestroy = '0';
	vZero = 'No';
	vOverride = 'No';
	vProcess = '';
	vParam1 = '';
	vParam2 = '';
	vParam3 = '';
	vParam4 = '';
	vParam5 = '';

# Execute the process to create the view
	
	ExecuteProcess(vView,
		'pCube', vCube,
		'pName', vName,
		'pSubsetID', vSubsetID,
		'pSkipZero', vSkipZero,
		'pSkipC', vSkipC,
		'pDim1', vDim1,
		'pDim2', vDim2,
		'pDim3', vDim3,
		'pDim4', vDim4,
		'pDim5', vDim5,
		'pDim6', vDim6,
		'pDim7', vDim7,
		'pDim8', vDim8,
		'pDim9', vDim9,
		'pDim10', vDim10,
		'pDim11', vDim11,
		'pDim12', vDim12,
		'pDim13', vDim13,
		'pDim14', vDim14,
		'pDim15', vDim15,
		'pDim16', vDim16,
		'pDim17', vDim17,
		'pDim18', vDim18,
		'pDim19', vDim19,
		'pDim20', vDim20,
		'pDestroy', vDestroy,
		'pZero', vZero,
		'pOverride', vOverride,
		'pProcess', vProcess);

DatasourceType = 'VIEW';
DatasourceNameForServer = 'Job Info';
DatasourcecubeView = vName;

Data tab:

Code: Select all

Asciioutput(sFile, vVersion, vArea, vDept, vCC, vEmp, vJob, numbertostring(vValue));

Epilog:

Code: Select all

## Delete the views and subsets

If ( pDelete @= 'Yes');

	####Destroy the View

	Viewdestroy ( vCube,  vName  );

	#####Destroy the Subsets

	###Set Loop Count Variable

	X=1;

	###Reference Dimensions in Cube

	cDim = TABDIM(vCube, X);

	###Loop over those Dimensions

	While (cDim @<> '');

		vSubset=cDim|'_'|vSubsetID;

		subsetdestroy( cDim  , vSubset  );

		X=X+1;

		cDim = TABDIM(vCube, X);

	End;
Endif;

Maren

[gtonkin]

You are creating sFile as follows:

Code: Select all

sFile = Expand('C:\TM1 Servers\Test\Exports\Test_%pEmp%.csv');

Where are you getting pEmp from?

Parameters passed look like pEmpID and pDeleteView:

Code: Select all

RunProcess('CalledProcess', 'pEmpID', vEmp, 'pDeleteView', 'Yes');

Have you tried:

Code: Select all

sFile = Expand('C:\TM1 Servers\Test\Exports\Test_%pEmpID%.csv');

[MarenC]

Hi,

the pEmp was a typo, it is pEmpID. So it was correct. Sorry.

I have simplified the process to take out the view creation completely. So now I RunProcess on the master prolog and call it 47 times. The Called Process now simplycellputn 1 into a 2 dimensional cube. Details as follows:

So now on the Master process prolog I have the following (that is all the code that is in the master process):

Code: Select all

RunProcess('CalledProcess', 'pEmpID', 'Employee1');
RunProcess('CalledProcess', 'pEmpID', 'Employee2');
RunProcess('CalledProcess', 'pEmpID', 'Employee3');
RunProcess('CalledProcess', 'pEmpID', 'Employee4');
RunProcess('CalledProcess', 'pEmpID', 'Employee5');
RunProcess('CalledProcess', 'pEmpID', 'Employee6');
RunProcess('CalledProcess', 'pEmpID', 'Employee7');
RunProcess('CalledProcess', 'pEmpID', 'Employee8');
RunProcess('CalledProcess', 'pEmpID', 'Employee9');
RunProcess('CalledProcess', 'pEmpID', 'Employee10');
RunProcess('CalledProcess', 'pEmpID', 'Employee11');
RunProcess('CalledProcess', 'pEmpID', 'Employee12');
RunProcess('CalledProcess', 'pEmpID', 'Employee13');
RunProcess('CalledProcess', 'pEmpID', 'Employee14');
RunProcess('CalledProcess', 'pEmpID', 'Employee15');
RunProcess('CalledProcess', 'pEmpID', 'Employee16');
RunProcess('CalledProcess', 'pEmpID', 'Employee17');
RunProcess('CalledProcess', 'pEmpID', 'Employee18');
RunProcess('CalledProcess', 'pEmpID', 'Employee19');
RunProcess('CalledProcess', 'pEmpID', 'Employee20');
RunProcess('CalledProcess', 'pEmpID', 'Employee21');
RunProcess('CalledProcess', 'pEmpID', 'Employee22');
RunProcess('CalledProcess', 'pEmpID', 'Employee23');
RunProcess('CalledProcess', 'pEmpID', 'Employee24');
RunProcess('CalledProcess', 'pEmpID', 'Employee25');
RunProcess('CalledProcess', 'pEmpID', 'Employee26');
RunProcess('CalledProcess', 'pEmpID', 'Employee27');
RunProcess('CalledProcess', 'pEmpID', 'Employee28');
RunProcess('CalledProcess', 'pEmpID', 'Employee29');
RunProcess('CalledProcess', 'pEmpID', 'Employee30');
RunProcess('CalledProcess', 'pEmpID', 'Employee31');
RunProcess('CalledProcess', 'pEmpID', 'Employee32');
RunProcess('CalledProcess', 'pEmpID', 'Employee33');
RunProcess('CalledProcess', 'pEmpID', 'Employee34');
RunProcess('CalledProcess', 'pEmpID', 'Employee35');
RunProcess('CalledProcess', 'pEmpID', 'Employee36');
RunProcess('CalledProcess', 'pEmpID', 'Employee37');
RunProcess('CalledProcess', 'pEmpID', 'Employee38');
RunProcess('CalledProcess', 'pEmpID', 'Employee39');
RunProcess('CalledProcess', 'pEmpID', 'Employee40');
RunProcess('CalledProcess', 'pEmpID', 'Employee41');
RunProcess('CalledProcess', 'pEmpID', 'Employee42');
RunProcess('CalledProcess', 'pEmpID', 'Employee43');
RunProcess('CalledProcess', 'pEmpID', 'Employee44');
RunProcess('CalledProcess', 'pEmpID', 'Employee45');
RunProcess('CalledProcess', 'pEmpID', 'Employee46');
RunProcess('CalledProcess', 'pEmpID', 'Employee47');

On the called process I now simply have the following on the prolog:

Code: Select all

Cellputn(1, 'zTestRunPro', pEmpId, 'Value');

There is no other code whatsoever. Yet I still get the issue, it only updates 9 records, or maybe 11 if I rerun the process.

If I put sleep(100) between each RunProcess statement everything works ok. I.e. it updates 47 employees.

Interestingly if I put everything on the epilog it nearly works, it returns 45 or 46 employees, just 1 or 2 short!
Actually when I then put the RunProcess back onto the prolog it updated 45 or 46 too. Then it suddenly started updated 47. But now it is back to updating 9! The only sure fire way to guarantee it will be correct is to use the sleep function.

I don't know if this is relevant but I am running the TM1server as an application not a service. So don't know if this is a quirk of runprocess or something to do with my computer!

Maren

[PavoGa]

Have you looked in the log file and determined how many of the multi-threaded processes are finishing after the main process finishes?

If the master process is executing a view or cube zero out, then executing the RunProcesses, that may be the problem. The master process's "transaction" is not committed until it finishes. So the subprocesses are merrily doing their writes to the cube and completing their transactions, but when the master process finishes and commits its clearing transaction, that is what the cube winds up with, overwriting the previously completed subprocesses. The few values you see written are the ones that are completing and committing after the master process finishes.

I do not understand why the file outputs are failing, but I would use LogOutput on the subProcess to try and determine what is going on there.

[Wim Gielis]

Have you looked in the log file and determined how many of the multi-threaded processes are finishing after the main process finishes?

Indeed. Even though the main process stops, the "RunProcess processes" can still be running and finish somewhat later.
I've had the same case when copying over lowest-level numeric cells from 1 cube to another, very similar, cube. No rules involved.
I managed to copy 150,000 cells in 0.6 seconds for 12 months, with 12 calls to the same copy process. But it appears that the last process stopped shortly after the mother process gave the 'Completed successfully" message.

[MarenC]

Hi,

I simplified the process so it doesn't use any View creation whatsoever. It simply does a cellputN for each employee passed into the parameter as per my previous comment - the code in my previous comment is all the code there is.

I literally have 47 runprocesses in the master process and a cellput on the prolog of the called process and I still get the issue.

When you say look at the logs, you mean the server log?

regards,
Maren

[PavoGa]

When you say look at the logs, you mean the server log?

regards,
Maren

Yes, the server log.