TI process slows after reading 25,000 records.

[PavoGa]

We have a cube that maps allocation relationships between source pools and accounts and target pools and accounts. Its dimensions are:
Version (25 elements)
Scenario (1, it is a placeholder for future functionality)
Pool Source (27)
Account Source (132,668)
Account Target (5,269)
Pool Target (28)
Measure (3)

The Measure dimension's elements are. AllocationSourceFlag (0/1), AllocationTargetFlag (0/1) and Parent (Total Cost/Total Base). There are no rules or feeders for this cube. The dimension is populated entirely by a TI process, with the source data coming from Oracle with each record containing Pool Source, Account Source, Account Target, Pool Target, Parent. The Pool and Account dimensions all have top level consolidation element (Total Pools, Total Accounts). For each record read in, the intersection in the cube has AllocationSourceFlag set to 1. If the record is a target, then AllocationTargetFlag is set to 1. An intersection can be both a source and a target. Example: Pool 001 allocates to Pool 002, which in turn allocates to Pool 009.

The source SQL statement returns about 150,000 records. The data section of the TI runs rapidly until it hits about 25,000 records, generally a few seconds. Then it takes over three minutes to process the other 125,000 records. We have isolated the culprit line and it seems this may be a teaching moment to learn why we have this issue. First, the code:

Code: Select all

CELLPUTN(1, cubTarget, strVersion, strScenarioDefault, vSourcePool, strAcctOrgSourceMaster, strAcctOrgTarget, strTargetPool, 'AllocationSourceFlag');
CELLPUTS(strParentConsolidation, cubTarget, strVersion, strScenarioDefault, vSourcePool, strAcctOrgSourceMaster, strAcctOrgTarget, strTargetPool, 'Parent');
CELLPUTS(strParentConsolidation, cubTarget, strVersion, strScenarioDefault, vSourcePool, strAcctOrgSourceMaster, 'Total', 'Total Pools', 'Parent');

# If an allocation target, then populate the Allocation Target element.
# check by getting the value of the Total Source Pool, Total Source AcctOrg for a given target AcctOrg and Target Pool of Allocation Source
IF (DIMIX(dimAcctOrgTarget, strAcctOrgSourceMaster) > 0);
	nIsAllocationTarget = IF(CELLGETN(cubTarget, strVersion, strScenarioDefault, 'Total Pools', strTopLevelAcctOrg, strAcctOrgSourceMaster, vSourcePool,  'AllocationSourceFlag') = 0, 0, 1);
	CELLPUTN(nIsAllocationTarget, cubTarget, strVersion, strScenarioDefault, vSourcePool, strAcctOrgSourceMaster, strAcctOrgTarget, strTargetPool, 'Allocation Target');
ELSE;
	ASCIIOUTPUT(strDataErrLogFile, 'Source AcctOrg Not in Target DIM', strVersion, vSourcePool, vParentConsolidation, vAcct_ID, vOrg, vAllocationAcct, vTargetPool);
ENDIF;

The records are processed in pool order, so what we are doing here is loading each record read as a source at its particular intersection. Then we check to see if that Pool Source and Account Source in that record are the targets of other Pool and Account Sources by checking to see if the source dimension Total Pools and Total Accounts for the with the source values as targets in the record as targets (hope that makes sense!).

Record: 200, 501-150-001, No Account, No Pool looks at Total Pools (Source), Total Accounts (Source), 501-150-001, 200 and if the value of AllocationSourceFlag is > 0, then flag as a target (AccountAllocationTarget = 1)

We have isolated the problem to

Code: Select all

	nIsAllocationTarget = IF(CELLGETN(cubTarget, strVersion, strScenarioDefault, 'Total Pools', strTopLevelAcctOrg, strAcctOrgSourceMaster, vSourcePool,  'AllocationSourceFlag') = 0, 0, 1);

by removing it. It processes in seconds. We stripped out the IF condition. Takes four minutes. I tried this:

Code: Select all

	nIsAllocationTarget = CELLGETN(cubTarget, strVersion, strScenarioDefault, 'Total Pools', strTopLevelAcctOrg, strAcctOrgSourceMaster, vSourcePool,  'Allocation Source') \ 
		CELLGETN(cubTarget, strVersion, strScenarioDefault, 'Total Pools', strTopLevelAcctOrg, strAcctOrgSourceMaster, vSourcePool,  'Allocation Source');

Still takes four minutes to process. No other line removal, or changes improved processing time until that CELLGETN is removed. Just kind of guessing here, is the problem that as the AllocationSourceFlag become populated, the consolidations in the Source Pool and Account dimensions are taking just long enough to calculate to slow the CELLGETN down? Processing speed drops from over 4,500 records/sec to around 550.

Do I have a bad design idea here? Is the cube poorly designed?

Ty

[tomok]

As a general rule I never CellGet from the same cube I am CellPutting into inside the same TI process. It is almost always going to perform more slowly than just a clean process that does one or the other. I think you would do better to separate your task into two processes. One that loads your target cube from Oracle and a second that extracts out of the Target cube the relevant records for the ASCIIOutput.

[PavoGa]

Thanks, Tom, but some further experimentation took care of the problem. Reordered the dimensions and the processing time dropped down to what we were expecting.

I've seen previous posts of yours with that advice a number of times, but I've never really had an issue with it. Not saying it is not an issue, but that I've not experienced it.

Regardless, thank you for your response.

Ty