CellPutProportionalSpread

[BigDSter]

Hi all,

Haven't had to ask any questions for quite a while, but am doing some extensive spreading for the first time in a while.

What I'm trying to do is build up a Customer and Product Profitability cube, which extends our normal trading P&L (normally goes to TCAM - Total Contribution After Marketing) down to EBT (Earning Before Tax).

Now we have a customer and product split for the normal trading P&L but what we then need to do is apportion the lower half of our P&L down to the same level. This includes such things as Factory Overheads, Logistics, Interest, Depreciation etc.

I've managed to do most of these by using CellPutProportionalSpread after seeding the row with a base data set, ie Factory Overheads would have a base data set of hours (which we can get as we know the standard output rate). The Factory Overheads are then spread down directly over this giving us the number we first thought of, but at Customer/Product level.

Now my query is this (eventually)

I have some costs which maintained at various levels of the product hierarchy, ie there will be some costs which are purely down to one product, and other costs which are generic across a sector. eg we have marketing costs for Tesco Cookies, but also marketing costs for Own Label Cookies, both held separately.

What I need to do is spread both down, and add up the results, but CellPutProportionalSpread will just blitz the number that is in there originally. I've got round this on other calcs by having a couple of fields rolled up into a subtotal, and seeding/spreading the data using more TI's. This is okay when there are only a couple of definite calcs, but when it gets to 10+ then I don't really want to be doing all that extra work. What it really needs is a running total I guess.

So how do other people approach this, I know most of you use spreading quite a lot.

Ta for any help.

David

[John Hobson]

David

Would a 2 d table or attribute that you could look up, holding the marketing cost type to apportion, to each biscuit type work?

J

[paulsimon]

David

I am not sure that I fully understand what you are after. However, would the following help?

1) Do the initial spread for the wider hierarchy eg Own label
2) Get the result of what was spread for the sub-hierarchy eg Tesco
3) Spread the Tesco result + the Tesco value to spread - This is a bit like an Additive load with CellPutN CellGetN.

Another alternative would be to use rules. When combined with attributes or lookup cubes this can be quite flexible.

Regards

Paul Simon

[BigDSter]

Thanks Paul, thats pretty much what I'm trying to do. I finally managed to work out the logic on Friday and have it running, it's just not very efficient at the minute, and I had to kill the last attempt after 30 minutes

What I have set up in a mapping field is a group I want each line to map to.

I loop through the product structure and work out which one has a mapping
Work out which products belong to that group
Fill in the base data for that group (ie sales data to be used as a base for spreading)
Get the Consumer Marketing figure from our main trading model for that group
Spread it down over the base data
Move the calc field to the final field (additive)
Clear calc field and start again

This works, but because I have to go through periods/products/customers I have to continually loop, which I think is a bit time consuming. Looking at trying to speed the process up today if I can, but may take some time

TI
--------------

version=CellGetS('CPP Control', 'version', 'Value')|' '|CellGetS('CPP Control', 'Year', 'Value');
tyear=CellGetS('CPP Control', 'Year', 'Value');
tversion=CellGetS('CPP Control', 'version', 'Value');
adjustment='Total';
measure='NSV';

# first thing we need to do is clear out the consumer marketing and consumer marketing calc fields in CPP

OldCubeLogChanges = CUBEGETLOGCHANGES('CPP');
CUBESETLOGCHANGES('CPP', 0);
VIEWZEROOUT('CPP','ZCPP_ConsumerMarketing');


count=1;
while (count <= DIMSIZ('TRD Product'));



elname=DIMNM('TRD Product', count);

if(ellev('TRD Product',elname)=0);

# check if consm_map attribute is set

#if(AttrS('TRD Product,elname, 'consm_map')<>'';

grpname=AttrS('TRD Product',elname, 'consm_map');

if(grpname@='');
grpname2=grpname;
else;

VIEWZEROOUT('CPP','ZCPP_ConsumerMarketingCalc');

# if this is set then we need to check what products belong to that group

gcount=1;
while (gcount <= DIMSIZ('TRD Product'));

gelname=DIMNM('TRD Product', gcount);

if(ellev('TRD Product',gelname)=0);

if (ELISANC('TRD Product',grpname,gelname)=1);

# now we need to loop through period and customers
# lets try customer first

icount=1;

while (icount <= DIMSIZ('TRD Customer'));

ielname=DIMNM('TRD Customer',icount);

if (ellev('TRD Customer',ielname)=0);

# now loop through periods P01 - P12

kcount=1;

while (kcount <= DIMSIZ('CPP Period'));

kelname=DIMNM('CPP Period',kcount);

if (ellev('CPP Period',kelname)=0);

# at this point we need to start getting the data filled in
tdata=CellGetN('Trading',ielname,gelname,tyear,adjustment,tversion,kelname,measure);
if(tdata<>0);
CellPutN(tdata, 'CPP', gelname,ielname,'Consumer Marketing Calc',kelname,version);
endif;

endif;

kcount=kcount+1;

end;

endif;

icount=icount+1;

end;



endif;

endif;

gcount=gcount+1;

end;

# at this point we have the components of that group all filled in with lovely data from Trading
# so we need to spread data down across it, then move it to Consumer Marketing, making sure you do additive
# before then blanking out the calc field to start again

#firstly we need to get the value we are going to be spreading

kcount=1;

while (kcount <= DIMSIZ('CPP Period'));

kelname=DIMNM('CPP Period',kcount);

if (ellev('CPP Period',kelname)=0);

cdata=CellGetN('Trading','HT Adjustment',elname,tyear,adjustment,tversion,kelname,'Total Consumer Marketing');

CellPutProportionalSpread(cdata, 'CPP', grpname,'Home Trade - CPP','Consumer Marketing Calc',kelname,version);

cdata=CellGetN('Trading','Export',elname,tyear,adjustment,tversion,kelname,'Total Consumer Marketing');

CellPutProportionalSpread(cdata, 'CPP', grpname,'Export','Consumer Marketing Calc',kelname,version);

endif;

kcount=kcount+1;

end;

# now we need to take the results of this spread and add it to the Consumer Marketing field

hcount=1;
while (hcount <= DIMSIZ('TRD Product'));

helname=DIMNM('TRD Product', hcount);

if(ellev('TRD Product',helname)=0);

# and loop through customer again

icount=1;

while (icount <= DIMSIZ('TRD Customer'));

ielname=DIMNM('TRD Customer',icount);

if (ellev('TRD Customer',ielname)=0);

# now loop through periods P01 - P12

kcount=1;

while (kcount <= DIMSIZ('CPP Period'));

kelname=DIMNM('CPP Period',kcount);

if (ellev('CPP Period',kelname)=0);

ndata=CellGetN('CPP', helname,ielname,'Consumer Marketing Calc',kelname,version);
odata=CellGetN('CPP', helname,ielname,'Consumer Marketing',kelname,version);
xdata=ndata+odata;
CellPutN(xdata, 'CPP', helname,ielname,'Consumer Marketing',kelname,version);
endif;

kcount=kcount+1;

end;

endif;

icount=icount+1;

end;

endif;

hcount=hcount+1;

end;

endif;

endif;

count=count+1;

end;

VIEWZEROOUT('CPP','ZCPP_ConsumerMarketingCalc');

[Steve Rowe]

Blimey!
Whats worked for me instead of looping through subsets looking for a particular set of elelements is to set up MDX subsets based on a particular set of conditions that I'm looking for. If I can't get to the exact element I need then I can least produce a much shorter subset to loop through.

Not sure if thsi will be any help to you or not....

Cheers,
Steve

[BigDSter]

Ta Steve, not really figured out MDX yet, despite using tm1 for over 3 years Guess I generally stick to what I know works. Anyway, I've had another thought, the Trading cube that I am getting the data off is quite large ~800mb and I am hitting it a lot of times with reads, think the performance would improve if I dumped a subset of the cube into a summary one before I started and then used that?

[Steve Rowe]

Not sure, I think if you have to produce the summary cube you are going to be calculating the same data anyway so in theory there should not be much difference. Actually this doesn't really hold since if you retrieveing a high level value and then changing the data in the cube then the high level value will get discarded. If your summary cube contains the high level values as data rather than a consolidation then you could see a performance inprovement.

I don't really do MDX either but I do find that the subset recorder thing usually gets me 90% of the way. The other thing that can speed things up is to do more VZOs but on smaller bits of the cube, not sure how practical this is for you...

I don't know much about the way you work but its seems to me that rules would be more practical for doing this type of "allocation" exercise?

Cheers,

[BigDSter]

Well I've managed to get this working quite nicely now, down from 92 mins (cancelled) - 20 minutes - 2 1/2 minutes, which is completely adequate

What really sped it up was the removal of the two data loops, one to loop through products/customers/periods to get the base data, then another loop to put the data into the final location.

Mangaged to do these via another process which is then called from within the main one. So much quicker Thanks for all the help

TI
-----------------------

version=CellGetS('CPP Control', 'version', 'Value')|' '|CellGetS('CPP Control', 'Year', 'Value');
tyear=CellGetS('CPP Control', 'Year', 'Value');
tversion=CellGetS('CPP Control', 'version', 'Value');
adjustment='Total';
measure='NSV';

# first thing we need to do is clear out the consumer marketing and consumer marketing calc fields in CPP

OldCubeLogChanges = CUBEGETLOGCHANGES('CPP');
CUBESETLOGCHANGES('CPP', 0);
VIEWZEROOUT('CPP','ZCPP_ConsumerMarketing');


count=1;
while (count <= DIMSIZ('TRD Product'));

elname=DIMNM('TRD Product', count);

if(ellev('TRD Product',elname)=0);

# check if consm_map attribute is set

grpname=AttrS('TRD Product',elname, 'consm_map');

if(grpname@='');
grpname2=grpname;
else;

VIEWZEROOUT('CPP','ZCPP_ConsumerMarketingCalc');

# clear out the ZCPPProduct subset that we will use in the load

SubsetDeleteAllElements('TRD Product','ZCPPProduct');

# if this is set then we need to check what products belong to that group

gcount=1;
while (gcount <= DIMSIZ('TRD Product'));

gelname=DIMNM('TRD Product', gcount);

if(ellev('TRD Product',gelname)=0);

if (ELISANC('TRD Product',grpname,gelname)=1);

# we also need a check here to make sure that the product isn't a 25000 or 26000 series as we don't want to spread back
# across adjustment lines

if (SUBST(gelname,1,3)@='l_2');


else;

SubsetElementInsert('TRD Product', 'ZCPPProduct',gelname, 1);

endif;

endif;

endif;



gcount=gcount+1;

end;

# now we have the subset filled in with the right products, run another TI which uses this subset to blitz the data in.

ExecuteProcess('Load Cube CPP - NSV to Consumer Marketing Calc');

# at this point we have the components of that group all filled in with lovely data from Trading
# so we need to spread data down across it, then move it to Consumer Marketing, making sure you do additive
# before then blanking out the calc field to start again

#firstly we need to get the value we are going to be spreading

kcount=1;

while (kcount <= DIMSIZ('CPP Period'));

kelname=DIMNM('CPP Period',kcount);

if (ellev('CPP Period',kelname)=0);

cdata=CellGetN('Trading','HT Adjustment',elname,tyear,adjustment,tversion,kelname,'Total Consumer Marketing');

CellPutProportionalSpread(cdata, 'CPP', grpname,'Home Trade - CPP','Consumer Marketing Calc',kelname,version);

cdata=CellGetN('Trading','Export',elname,tyear,adjustment,tversion,kelname,'Total Consumer Marketing');

CellPutProportionalSpread(cdata, 'CPP', grpname,'Export','Consumer Marketing Calc',kelname,version);

endif;

kcount=kcount+1;

end;

ExecuteProcess('Load Cube CPP - Consumer Marketing Calc to Consumer Marketing');

endif;

endif;

count=count+1;

end;

VIEWZEROOUT('CPP','ZCPP_ConsumerMarketingCalc');