Performance issue: How to write feeders for ConsolidatedMax?

[abcuser]

Hi,
using TM1 v9.5.2 I have a requirement to use maximum of children problem for "reclaim_defect" dimension. I have been solving this problem for months now and I got to this solution from help on this forum thread How to consolidate dimension with maximum of children?.

Now I am opening a new problem (one thread per problem). I have a performance problem. Writting bellow code in simple sample works without a problem, but in production cube having the SAME LOGIC but more dimensions (10) and more measures (17) adding this formula to cube is producing horible performances - to calculate the cube it takes for hours.

Code: Select all

['QUANTITY'] = C:
if(
	ELLEV('reclaim_buyer', !reclaim_buyer)= 0 &
	ELLEV('reclaim_product', !reclaim_product)=0
	,
	ConsolidatedMax(0,'', !reclaim_buyer, !reclaim_defect, 'QUANTITY',!reclaim_product)
	,
	STET
  )
;

Note: ConsolidateMax is new function in TM1 v9.5.2.

In any other cube I have on production I always use SKIPCHECK and then write feeders to dramatically speed-up performance. I have tried to add SKIPCKECK definition (without feeders) and performance is dramatically better it takes a second to display a result, but it produces incorrect values. So most probably I need to write a feeders, but don't know how. Any idea how to write a feeder?
Regards

[Steve Rowe]

Assuming this isn't a specific quirk of the function you are using then.

1. Make sure that at least one of the Ns below the C level quantity you are calculating is populated / fed.
2. Make sure that all the Ns that are calculated by rules in the range that you are looking for the maximum are fed.

If you are happy that you are satisfying the above then I'm not sure where you go from there.

Cheers,

[abcuser]

Steve,
I am quite a newbie at writing feeders. Can you post some example?
Regards

[paulsimon]

Hi abcuser

A feeder is of the form ['A non-zero value in some element'] => ['A non-zero value in another element'] ;

However, without some knowledge of your application neither Steve nor I can tell you what feeder you need to write. You need to look for an element that implies that you will have a non-zero value at the base level, which can be consolidated.

eg if Sales = Units * Price then a non-zero value for Units implies that there will be a non-zero value for Sales.

['QUANTITY'] = C:
if(
ELLEV('reclaim_buyer', !reclaim_buyer)= 0 &
ELLEV('reclaim_product', !reclaim_product)=0
,
ConsolidatedMax(0,'', !reclaim_buyer, !reclaim_defect, 'QUANTITY',!reclaim_product)
,
STET
)
;

[abcuser]

Paul, I know what feeder is, I have read official product documentation. There are only simple sample described. The main problem is for 'quantity' measure I need to consolidate reclaim_defect dimension not by default "sum" of children, but maximum of children, so I trying to solve the problem by using consolidatemax function. What I am doing in above formula is consolidating QUANTITY measure alone. So is there anything like: quantity for consolidation levels=>quantity for N:?

[Steve Rowe]

Hi abcuser,
So first as Paul says it's difficult to give specifics as I've not used the function you are using and I know nothing of your model.

Code: Select all

['QUANTITY'] = C:
if(
   ELLEV('reclaim_buyer', !reclaim_buyer)= 0 &
   ELLEV('reclaim_product', !reclaim_product)=0
   ,
   ConsolidatedMax(0,'', !reclaim_buyer, !reclaim_defect, 'QUANTITY',!reclaim_product)
   ,
   STET
  )
;

My point 1, make sure that the Quantity cell itself is fed. It's not possible to feed a consolidation using a feeder statement, these only work at the N level. So in order to get C:Quantity to calculate with skipcheck on you need to make sure that at least 1 N:Quantity below C:Quantity is fed or directly populated. This won't be your problem if the C:Quantity cell displays with zero supression in your cube viewer.

My point 2 make sure that all the N level cells that the rule are looking at are fed even if they are indirectly reference by consolidations that the rule is looking at. If all your Ns are input rather than rule driven then this cannot be your problem. If the C's that you are referencing change value with skipcheck on/off then you have a feeder issue with the values on the right of your rule.

If all the Ns in the right hand side are values then IMO you are looking at a bug if the result changes with skipcheck on/off.

HTH

[abcuser]

Hi,
you are right, I should write more about my sample. I have created simple sample to demonstrate the problem (my production cube problem just has the same LOGIC, but just having more dimensions, more members and more measures).

I have a reclaim cube. Each buyer can have a complain about specific product about defects that are related to the product. Single product can have several defects like incorrect color, incorrect size, incorrect shape etc. But if single defect is recognized on particular product, buyer can return this product back to manufacturer. It is quite logical, it is not important if there is single or multiple defects on product, product having single or multiple defects is unusable.

The quantity of defected products returned to manufacturer is measured on intersection buyer and product. For example buyer_1 has complained that product_1 has problems, so buyer_1 returns 1000 products. So far no problem, consolidation of of quantity is done using default sum aggregation on product and on buyer dimension.

But now adding reclaim_defect dimension things get more complicated. For example: buyer_1 has reported two defects on product_1. Defects are: defect_1 and defect_3. In N level we import data 1000 quantity for intersection buyer_1 | product_1 | defect_1 and ALSO!!! 1000 quantity defected products for intersection buyer_1 | product_1 | defect_3. This quantity should NOT be summed on reclaim_defect consolidation levels, because it would produce incorrect values of 2000.

Now consolidation: on buyer and product there should be default sum consolidation. But on reclaim_defect dimension it should be a "maximum of children" consolidation. Having two types of consolidation (sum on buyer and product dimensions and max of children on defect dimension) produces two different values at consolidation levels, to avoid this problem "max of children" logic should only be applied to all of the defect dimension levels, but only if other dimensions like buyer and product are on N level (see purple rectangle on first picture).

Formula published in my first post of this thread solves this maximum of children consolidation problem.
Note: in order to formula work correctly the reclaim_defect dimension has to be reordered to the last position (In TM1 Architect right click on Re-order Dimensions | New order: reclaim_defect should be listed last).

So far so good, but adding this formula to production cube it gets terrible performances. So I have added SKIPCHECK in rules. But by adding SKIPCHECK I get incorrect values (see picture at bottom). It looks like consolidation on reclaim_defect overwrites buyer and product consolidation if SKIPCHECK is turned on.

So I am now stuck at the feeders. I have read several documents about feeders, but all of them are related to multiple measures, feeding one measure into another, but in my case I have only one measure.

How to write feeders for this sample?

P.S. I am attaching dim and cub files in zip file attachment for this sample, so you can look at my sample.

Any idea is greatly appreciated. I am struggling with this problem for months.
Regards

[paulsimon]

Hi abcuser

Sorry - I didn't realise that you already knew what a feeder looked like. You had mentioned that you had only just introduced skipcheck so I assumed that you didn't

I don't know the answer to your problem but I can tell you that it is not a feeder issue, as the value that you are consolidating is QUANTITY which I can see from your screenshots is in white cells and it must therefore be entered. As QUANTITY exists at the base level there should be no need for any feeders to get values at the consolidated level. Lack of feeders tends to result in a zero value at the top level, rather than incorrect values.

Are there no other rules involved?

I suggest that you strip out any other rules and feeders, and then see if you get the same effect, ie it works without skipcheck and fails to work with skipcheck. If so then I would report this to IBM as a bug.

I can post a method using old style rules rather than consolidatemax which will give you the max value - I fact I think I may have already posted this on the forum. That might be your only answer.

Regards

Paul Simon

[abcuser]

Lack of feeders tends to result in a zero value at the top level, rather than incorrect values.

From official "TM1 Rules" documentation: "However, 'underfeeding' (failing to feed cells that contain rules-derived
values) will result in incorrect values and must be avoided at all costs."
It really depends what incorrect values are? Are zeros only 'incorrect values' possible?

Are there no other rules involved?

No, that's it. The only change to my rules from my first post of this thread is adding 'skipcheck' command in the first line above existing code.

I suggest that you strip out any other rules and feeders, and then see if you get the same effect, ie it works without skipcheck and fails to work with skipcheck. If so then I would report this to IBM as a bug.

Yes, few minutes ago I reported a PMR.

I can post a method using old style rules rather than consolidatemax which will give you the max value - I fact I think I may have already posted this on the forum.

Is this the same suggestion as you wrote in thread How to consolidate dimension with maximum of children? I just don't understand all this complex formula and I don't really like to add so much code. The problem is I have 17 different quantity measures in production cube and I have to add all of the code for all of this 17 measures. It looks too complicated to me to admin this kind of huge code, that I don't understand at all.

[paulsimon]

Hi abcuser

In general if you under-feed you will see zeros at consol levels. However, there are certain cases where you will see incorrect values, so example if you browse values in a cube it can sometimes result in some values appearing at consol levels. However the way to check is to restart the server and then browse just the consol. Also if you view a calculated value even at base level and suppress zeros then it will disappear.

However, your problem cannot be a feeding issue because QUANTITY is an entered value, not a rule calculated value.

With regard to my alternate method - yes that is the one I meant. That was written before ConsolidatedMax was available. Obviously it would be preferable to do it with ConsolidatedMax. However, it would seem that this would rely on IBM getting it working correctly when SkipCheck is turned on.

Regards

Paul Simon

[abcuser]

However, your problem cannot be a feeding issue because QUANTITY is an entered value, not a rule calculated value.

I got response from IBM, he just copied text from manual where it is stated if feeders are not properly written then incorrect values are returned when skipcheck is turned on. He insists that feeders are needed and I need an tm1 expert to look at this issue. IMHO, he just copied the text and posted the message to get "rid" of me.

What I am now wondering is there a trick to tell TM1 to feed all cells in cube? I know this is not reasonable for production cube, but for this particular simple sample, I would just like to write 'feed all' and prove or disprove if this is really a feeder problem.

[mattgoff]

What I am now wondering is there a trick to tell TM1 to feed all cells in cube? I know this is not reasonable for production cube, but for this particular simple sample, I would just like to write 'feed all' and prove or disprove if this is really a feeder problem.

Sure, jut take SKIPCHECK out of your rules.

Matt

[abcuser]

Paul,
I got some 'feeders' document from IBM personal and now I understand what you are talking about. This really looks like not a feeder problem at all. Because all of the N: levels of quantity are populated it means all consolidated levels are AUTOMATICALLY fed. Also have checked with "suppress zeros" (values does not disappear) and "Check Feeders" on consolidated levels and non of the cell is "non-fed". So this implies that all consolidated cells are already automatically fed and there is no need for additional feeder statement!

I have tried to play around with the code and replacing STET statement from formula with ConsolidateChildren it does solve the problem. So it looks like there was a rule problem. On this simple sample cube I can comment out SKIPCHECK or having this command and get the same result. Also dimension order is not important anymore - I can sort dimension in any way I want to and rules works correctly each time.

Code: Select all

SKIPCHECK;

['QUANTITY'] = C:
IF(
	ELLEV('reclaim_buyer', !reclaim_buyer)= 0 &
	ELLEV('reclaim_product', !reclaim_product)=0
	,
	ConsolidatedMax(0,'', !reclaim_buyer, !reclaim_defect, 'QUANTITY',!reclaim_product)
	,
	ConsolidateChildren('reclaim_buyer','reclaim_product')
  )
;

At the moment it looks like this is THE solution, but I really need to test this rule on production cube to see if there is some glitch in there or not.

But still this does look strange:
1. it looks like default calculating each cells it respects dimension order
2. if skipcheck command is added then sparse calculation engine does not respects dimension order anymore.
Don't know if this is a bug or intentional, but it does look strange to me.

Regards

[abcuser]

Hi,
I have implemented this new rule in production cube. Testing the cube on lower levels and I think I get correct values.
But now performance has dropped from less then second (using skipcheck and STET in formula which produces incorrect values) to more then 45 minutes (I have stopped execution after this time). This new rule takes up 100% of CPU of the whole time. This cube is one of the smallest cubes. Total sum of memory used for all 10 cubes in production is 1.4 GB. Running default view from this rule consumes 30 GB of memory (10 GB of real memory and 20 GB of virtual memory). I have killed the process to prevent Windows crash. It appeared a cube explosion.

Any idea how to speed up performance? Maybe rewrite rule?

P.S. This is one of the smallest cube in our case, without rules I get result in less then second.
Regards

[abcuser]

Hi,
I have reported PMR couple of months ago and now getting a lot of mails from IBM now I understand that this is actually not a bug, but works as designed. My first post in this thread (with STET command) it works incorrectly in production like cube, because TM1 server is calculating the upper-levels by calculating leaf level. To this incorrect values problem I need to use ConsolidateChildren function which for laboratory cube produces correct values, but in real time production cube it just got teriable results - I killed TM1 server after 40 minutes and consuming all of my 10 GB RAM on server.

Now I am stuck again. STET like rule is not a bug, but works as designed (not useful in my case), ConsolidateChildren just not performing good.

Any other idea how to solve this maximum of children problem. Can't really believe this is such a pain using TM1 server. I see this problem like a basic problem in cubes, semi-additive dimensions in warehousing (they require to have some non-sum operation like last value, first value, average value, minimum value or MAXIMUM value). Any other idea how to solve this maximum of children problem?

Thanks a million times.

[Duncan P]

My understanding of the problem is this. Please correct me if I am wrong.

1. You need maximum up the defect hierarchy at leaf level on all other hierarchies.

2. You need summation of all other hierarchies from both the leaf and consolidated max values of the defect hierarchy.

I have a way of doing this that does not use ConsolidateChildren but it does involve a second cube with a second, flat, version of the defect dimension.

You have

MainCube - dimensions: reclaim_buyer, reclaim_product, reclaim_defect, measures
CalcCube - dimensions: reclaim_buyer, reclaim_product, reclaim_defect_flat, measures

reclaim_defect_flat should have all the members that reclaim_defect has but they should all be N level simple items.

The CalcCube should have the rule

Code: Select all

SKIPCHECK;

['Quantity'] = N: CONSOLIDATEDMAX(0, 'MainCube', !reclaim_buyer, !reclaim_product, !reclaim_defect_flat, 'Quantity' );

FEEDERS;

[] => DB('CalcCube', !reclaim_buyer, !reclaim_product, ELPAR('reclaim_buyer', !reclaim_buyer_flat, 1), 'Quantity' );

and the MainCube should have the rule

Code: Select all

SKIPCHECK;

['Quantity'] = C:DB('CalcCube', !reclaim_buyer,!reclaim_product,!reclaim_defect,'Quantity');

FEEDERS;

[] => DB('CalcCube',!relaim_buyer,!reclaim_product,!reclaim_defect,'Quantity');

The key is that the max consolidation is done in the main cube for the corresponding leaf items in the calc cube. As these are leaf level they are consolidated in the CalcCube and read into the C level of MainCube.

I haven't tested it at scale but the feeders should work ok. I hope. The feeder in the CalcCube is using the hierarchy in reclaim_defect (through ELPAR) to drive the feeding across the flat version. Thanks to Tom Shackell for this suggestion.