9.5.1 TM1 TI allocation process

[rollo19]

Hi guys - I am having a bit of trouble with my TI process.. I can't seem to get the element name I want into the cellputN statement in my process. Or maybe it's that I can't actually retrieve the element name using DIMNM and an index. Does anything stand out (other than the big red arrows thanks to JING screen capture) in the attached?

The process is an allocation. The target cube has two dimensions not in the source cube. The allocation split for these dimensions comes from two assumption cubes retreived using a cellgetN.

Rules? Overfeeding on these two dimensions consumed way to much ram if I try and rule the allocation.

[Gregor Koch]

Hi
What stands out is that both your loops count down to zero by using

While(x >= 0);
and
While(y >=0);

In that case you will get errors because you are trying to retrieve/use the element with index '0' which does not exist.
So if your concern are the errors that should be fixed by using

While(x > 0);
and
While(y > 0);

Not sure what the problem is if you don't get any names/values at all.
You could try to have an AsciiOutPut below each of the lines (to different text files) which do a CellGet and the CellPut to see where it is trying to read and write.

Cheers

[rollo19]

Oops - fixed that and it's running. Thanks very much!

.. and it's still running... This is a large but sparse target cube - source has some 68,000 records by some 3,500 in allocation target dim 1 by 50 in allocation target dim 2 = 11,900,000,000 possible allocation targets the DIMNM loops must trawl through and test if the cellisupdateable, calc allocation value, cellget & cellput.. So far processing ~1 record per second so perhaps 18hrs.

Does anyone have any examples of such an allocation process or approach which might be more efficient/faster than this?

I also found it was quicker to skip the IF(Allocation Split >0) check and just run through all elements in the target allocation dimensions.

#---------- Example allocation data script:
# Loop through all the target elements
y = DIMSIZ('Alloc Dim');
While(y >0);

#Get the Dim 1 Split
vAllocDim = DIMNM('Alloc Dim', y);

IF(CellIsUpdateable('Target cube',... vAllocDim, ...'Measure');

vAllocSplit = CellGetN('Alloc Split Cube', ... vAllocDim.. '% Split');
AllocValue = Value * vAllocSplit;

CurrentVal = CellGetN('Target cube',... vAllocDim, ...'Measure');
CellPutN(AllocValue + CurrentVal, 'Target cube',... vAllocDim, ...'Measure');

ENDIF;

ENDIF;
y = y - 1;
END;

[tomok]

Use the CellPutProportional spread TI function. Have one TI process that puts the percent values to be allocated into the leaf elements of the target cube and then a second process (using a view on the source cube as the data source) that comes back through and cellputproportionalspreads to the top level parent of the two dimensions that don't exist in the source cube and each of the leafs in the target cube. This should replace your percent values with a proportional amount of the target values. The only caveats are 1) you have to make sure the percents you put in the source cube in step 1 have a corresponding value in the target cube (for the matching dimensions) or you'll be left with percent values for those that don't and 2) you have to make sure the source values you are allocating to the matching elements have been given a percent to receive (you can't allocate 0 percent) or you'll get an error.

[rollo19]

With this data (sparse), that would require populating target cells with fractions which would not receive a number to spread. So to do that I would need to grab every single record including zeros from source (ViewExtractSkipZeroesSet(sSourceCube, vViewName, 0). That would be 2,642,421,600 records in this case which might be faster than the process above. But to populate the target cells with the spread fractions in the first place I would need to rip through even more records with a cellput. Might be worth a shot. Thanks.

[rollo19]

Also trying this approach of using the target view as source (including zero's, excl consol & rule calc values). Many more records to process but far fewer conditionals. So far it is running very fast, but a lot of records to get though (46,242,378,000,000) - data process looks like:

AllocValue = Value * CellGetN('Alloc Split Cube',...... '% Split');
AccumValue = CellGetN('Source cube', ... Measures) * ;
CellPutN(AllocValue + AccumValue , 'Target Cube',.. Alloc Dim, .. Measures);

[rollo19]

The all in one 68,000 source cube records x 3,500 target dim 1 elements x 50 target dim 2 elements = 11,900,000,000 proved to be too many permutations on a single thread. Pretty clearly it is not sensible to attempt the allocation across two or more alloc target dimensions at once, but to split it into separate stages (or more depending on the allocation):
Stage one:
68,000 populated cells in source cube x 3,500 elements in alloc target Dim 1 = a paltry 238,000,000 iterations.
Stage one could be into a special allocation cube OR what I did was create a an element in the alloc target Dim 2 called 'Pre Allocation' and loaded all records into that to save creating yet another cube.
Stage two:
Then the second stage uses either the allocation cube or in my case I used the Target cube, with only the 'Pre Allocation' element as a source view.
As the Source view excludes zero's, there are far fewer than the 238M x 50 (target Alloc Dim 2 elements) 11,900M records to process. In this case 1.5M pre allocation cells x 50 = 75M which ran in a few minutes.
Rules
The same would apply to a rule based allocation (swap records above for cells to feed). But rules would require separate cubes for each allocation stage to avoid overfeeding a massive number of cells (and blowing up the server as I did) by attempting to do it all in one. Using a process as above we can keep the number of cubes down and it reduces the amount of RAM used.