Aggregating "As At" value

[beek]

Hi there,

I have a question. Currently I have a cube in TM1 which stores the inventory information. It has dimensions below
1. Day (maintain structures in week, Month, Year)
2. ProductCode (maintains Product Structures, console node = Profit Center)
3. Measures

This cube stores inventory status daily, it keeps track of each Product Stock On Hand, Selling, Purchase, etc. For Selling, I have no problem in aggregating it. Assuming I sell 100 qty everyday, in 1 week, I should be seeing 700 (at week level, day dimension)

But for Stock On Hand, I cant just aggregate it, assuming, I have on hand stock value of 50, for this week. If I see this at week level, I will be seeing 50*7, which will be 350, this would be incorrect. Hence, we have put a rule, for C: it should be last loaded value.

However, I'm having problem in viewing it from a higher level of Product Code. we group product codes into a few profit center. Stock on Hand is not aggregating to the Profit Center level. Assuming in Profit Center 1, I have 2 product, 1 product I have on hand value of 100, another product I have on hand value of 200. I should be seeing 300 under this profit center. But I'm not seeing it.. It does not aggregate up. Can someone advise me how should I put the rule ?

Below is my current rules

[] = N:STET ; C: IF ( ATTRN('Measure',!Measure,'fAsAtValue' )=0 , STET ,
IF ( SUBST(!Day,7,1)@='w'
,DB('Production',!Product
, STR ( DB('SysLogByDetail',ATTRS('Measure',!Measure,'SysLogMap'),!Product,!Day,'LastestDayLoad') , 8,0)
,!Measure), 0 ) ) ;

Cheers,
Beek

[beek]

Just to update. I have found a way. After I use formula below, it is aggregating..
['StockOnHandValue'] = ConsolidateChildren('Product');

[Duncan P]

If you are using this in a test environment with cut-down data you should really test this at full scale before using this solution.

ConsolidateChildren has very different performance characteristics from the natural consolidation that you get with Stet. For large sparse cubes it is many times slower and it appears not to take account of feeders, which is probably because of the way it is defined.

[lotsaram]

If you are using this in a test environment with cut-down data you should really test this at full scale before using this solution.

ConsolidateChildren has very different performance characteristics from the natural consolidation that you get with Stet. For large sparse cubes it is many times slower and it appears not to take account of feeders, which is probably because of the way it is defined.

I agree with Duncan, using ConsolidateChildren('Product') may solve your immediate issue of StockOnHandValue not consolidating but there are likely better and more efficient ways of achieving the result. If ConsolidateChildren is returning the correct value but natural consolidation isn't then this points to a feeder issue since ConsolidateChildren ignores the SkipCheck algorithm and visits each cell exhaustively, which is why it is much slower (and also why it can return a correct consolidated result even if feeders are not correct.)

[beek]

Yes, you are right. I have just checked.. even ConsolidateChildren('Product') is not solving my problem.. as ConlidateChildren adds up everything.. but it should not be adding all days.. it should be taking only the last day of the node of the console..
I suppose this should be a common scenario, whereby there do have other companies want to see their total inventory at monthly level. Is there any other way in achieving it? except for loading the last day value into a seperate cube ..

[lotsaram]

There's no need for a separate cube. Usually this would just be done via a rule that affects only consolidated time periods and looking up the last balance value either via an attribute or combination of ELCOMP & ELCOMPN.

e.g.

Code: Select all

['stock balance'] = C:
IF( ELLEV('Time', !Time) = 0,
  Continue,
  DB('Inventory Cube', !BU, !Location, !Product, ELCOMP('Time', !Time, ENCOMPN('Time, !Time)), !Measure)
);

[Duncan P]

Alternatively, if all the non-rule based measures in the cube aggregate the same way - last period in this case - then you could use a special version of the time dimension in which for each aggregate time period the weight of the last child period is 1 and all the others are 0.

If some of your measures need to have normal aggregation (or first period - "opening balance") you can't use this technique as it applies the same weighting for all measures. However your C level rule for the "fAsAtValue" measures would still work on those measures to which it applied.

A disadvantage is that you need to maintain two time dimensions, but you could create a TI script to create one from the other.

[beek]

Thanks lotsaram. That is a brilliant idea of combining ELCOMP & ELCOMPN. However, I noted 1 problem. If I do not have value at the last day, then I will not be able to get the value.. eg. Time dimension, my element is at day level. It is possible sometimes I'm refering to the cube in the middle of the month, then I will not be able to see the inventory value, as the last day inventory is not yet loaded..
my current rule is checking the other cube, SysLogByDetail which stores the latest load day..
Thanks Duncan.. I supposed your proposed idea by using different weight, I will be facing the same problem? which is when I'm checking the inventory in the middle of the month, I will not know the value ...

[lotsaram]

Thanks lotsaram. That is a brilliant idea of combining ELCOMP & ELCOMPN. However, I noted 1 problem. If I do not have value at the last day, then I will not be able to get the value.. eg. Time dimension, my element is at day level. It is possible sometimes I'm refering to the cube in the middle of the month, then I will not be able to see the inventory value, as the last day inventory is not yet loaded..
my current rule is checking the other cube, SysLogByDetail which stores the latest load day..
Thanks Duncan.. I supposed your proposed idea by using different weight, I will be facing the same problem? which is when I'm checking the inventory in the middle of the month, I will not know the value ...

That's why I also mentioned the option of using an attribute in which case the rule would look something like:

Code: Select all

['stock balance'] = C:
IF( ELLEV('Time', !Time) = 0,
  Continue,
  DB('Inventory Cube', !BU, !Location, !Product, AttrS('Time', !Time, 'Last Period Ref'), !Measure)
);

Using this method you have the ability to overcome the problem of a balance not being available for a consolidated time period that is still in progress, ... but at the cost of having to maintain the attribute.

[beek]

Now I understand.. yes .. currently I'm maintaining the last load day in seperate cube.. Because I'm not maintaining it at attribute level, hence it does not aggregate to the level I'm looking for..and the cost of maintaining it at attrs would be rather high, I think..
Thank you so much for your suggestions .. it really helps a lot..