Calculated Zero Cells and Feeders

Post Reply
nicola531
Posts: 43
Joined: Thu Oct 22, 2009 7:58 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007

Calculated Zero Cells and Feeders

Post by nicola531 » Thu Jul 07, 2011 10:28 pm

Hello everybody,

I've got a big doubt.
Do Calculated cell, when their value is zero, feeders other cells if they are feed?

I've understood properly, a cell which value is zero should not feed other cells when it is covered by feeders itself. This in order to not waste time to aggregate zeros.
But I've got the doubt that when a cell is covered by feeder and is zero it actually feeders other cells.

Thanks in advance for you help,

Nicola

User avatar
Michel Zijlema
Site Admin
Posts: 705
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Calculated Zero Cells and Feeders

Post by Michel Zijlema » Fri Jul 08, 2011 5:27 am

nicola531 wrote:Hello everybody,

I've got a big doubt.
Do Calculated cell, when their value is zero, feeders other cells if they are feed?

I've understood properly, a cell which value is zero should not feed other cells when it is covered by feeders itself. This in order to not waste time to aggregate zeros.
But I've got the doubt that when a cell is covered by feeder and is zero it actually feeders other cells.

Thanks in advance for you help,

Nicola
Hi Nicola,

If you have a rule that calculates only on the consolidated level and have feeders feeding the leaf cell underneath the consolidation (where the value of the leaf cells stay zero), you will see the calculated consolidated value in a non zero-surpressed view, but in a zero-surpressed view the regarding row and/or column can disappear as if the consolidated value was zero.
So in other words a fed cell is treated as a zero when this cell has value zero.

Michel

jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Calculated Zero Cells and Feeders

Post by jstrygner » Fri Jul 08, 2011 9:03 pm

nicola531 wrote: Do Calculated cell, when their value is zero, feeders other cells if they are feed?
Nicola,

I found your question very interesting and to answer it I decided... to check it :D

I think attached picture shows, that fed cells that calculate to zero still feed other cells - the Q2 row shows that.

HTH
Attachments
FeedFromCalculatedZero.JPG
FeedFromCalculatedZero.JPG (65.17 KiB) Viewed 4717 times

User avatar
Michel Zijlema
Site Admin
Posts: 705
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Calculated Zero Cells and Feeders

Post by Michel Zijlema » Sat Jul 09, 2011 8:46 am

jstrygner wrote:I think attached picture shows, that fed cells that calculate to zero still feed other cells - the Q2 row shows that.
Is zero surpression activated in the view in your screenshot?

Michel

User avatar
Michel Zijlema
Site Admin
Posts: 705
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Calculated Zero Cells and Feeders

Post by Michel Zijlema » Sat Jul 09, 2011 9:12 am

After having a second look I couldn't understand the results in the 'L00003' column - these are not correct. After looking a bit better I see you have an error in your feeder statement regarding the amount of zeroes in the element names.
I reproduced your example with rules and feeder labels aligned and in this case the Q2 element is non-zero. Only he Q2 and Q3 elements are feeding the 'L00004' element, hence the result of 2 in the 'All Quarters' element. So at this moment I don't see what your test exactly is proofing.

Michel

User avatar
Michel Zijlema
Site Admin
Posts: 705
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Calculated Zero Cells and Feeders

Post by Michel Zijlema » Sat Jul 09, 2011 12:02 pm

The attached picture illustrates the behaviour that I was referring to in my first post in this thread.
Left is non zero-surpressed view, right view is one with zero-surpression. Although all leaf cells of the 'L2' element are fed, the column disappears when zero-surpression is activated - the zero cells are treated just as zero cells, fed or not.

Michel
Attachments
Feeder_Zero_test.jpg
Feeder_Zero_test.jpg (62.83 KiB) Viewed 4704 times

jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Calculated Zero Cells and Feeders

Post by jstrygner » Sat Jul 09, 2011 12:35 pm

Michel Zijlema wrote:So at this moment I don't see what your test exactly is proofing.
Michel,

I think we interpreted Nicola's question different and that is why we gave totally different responses.

My interpretation of the question was:
- Let's assume we have a leaf cell that is calculated.
- Let's assume this cell feeds another leaf cell.
- If the first cell value is calculated to non zero it is obvious that it should feed this other leaf cell.
- But if the first cell value is calculated to zero will it still feed this other cell?

And IMHO my example shows that yes, the feeder will still fire, and I think that was Nicola's question about.
Michel Zijlema wrote:Is zero surpression activated in the view in your screenshot?
No.

lotsaram
MVP
Posts: 3251
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Calculated Zero Cells and Feeders

Post by lotsaram » Sat Jul 09, 2011 7:34 pm

jstrygner wrote:
Michel Zijlema wrote:So at this moment I don't see what your test exactly is proofing.
Michel,

I think we interpreted Nicola's question different and that is why we gave totally different responses.

My interpretation of the question was:
- Let's assume we have a leaf cell that is calculated.
- Let's assume this cell feeds another leaf cell.
- If the first cell value is calculated to non zero it is obvious that it should feed this other leaf cell.
- But if the first cell value is calculated to zero will it still feed this other cell?

And IMHO my example shows that yes, the feeder will still fire, and I think that was Nicola's question about.
Michel Zijlema wrote:Is zero surpression activated in the view in your screenshot?
No.
My interpretation of Nicola's question is the same as yours. That is if a fed cell has a zero value will it in turn feed other cells?

However, your analysis is not correct, as indicated by your response to Michel's question of whether the view in your image was zero suppressed. Whether a leaf cell calculates the correct value has nothing to do with whether it is fed or not. If the cell value is directly queried then the rule will still be evaluated. To check whether the cell is fed, right-click the cell and select check feeders. If you did this on the intersection of Q2 and L000004 the resulting window should indicate that the cell is not fed. To refine your example create a view containing only the intersection of Q2 and L000004, zero suppress the view and there should be no value, this indicates the cell is not fed (although it evaluated to 1). The purpose of feeders is not to assist leaf cells to be correctly evaluated but rather to mark leaf cells as having a value so that consolidated cells will evaluate to the correct value.

Expected behaviours is that if a rule calculated cell evaluates to zero then it will not feed any further cells. At least that's what I have always observed.

jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Calculated Zero Cells and Feeders

Post by jstrygner » Sun Jul 10, 2011 12:45 am

lotsaram, I'm affraid have to disagree with some of your statements.
lotsaram wrote:Whether a leaf cell calculates the correct value has nothing to do with whether it is fed or not. If the cell value is directly queried then the rule will still be evaluated.
(...)
The purpose of feeders is not to assist leaf cells to be correctly evaluated but rather to mark leaf cells as having a value so that consolidated cells will evaluate to the correct value.
100% agree :)
There was nothing in my post that would suggest something different.
lotsaram wrote:To check whether the cell is fed, right-click the cell and select check feeders. If you did this on the intersection of Q2 and L000004 the resulting window should indicate that the cell is not fed.
To check if cell ['Q2', 'L000004'] is fed I did select the check feeders operation. But I did not do it on the leaf cell, as you suggest (so not on the ['Q2', 'L000004']), but I did it on a consolidation (where is the right place to do it, so in my example it would be ['All Quarters', 'L000004']).
And the screenshot of the check feeders is there on the picture I attached to my first post of this thread and it shows that there are two cells that are not fed: ['Q1', 'L000004'] and ['Q4', 'L000004'], which indicates, that the remaining two (['Q2', 'L000004'] and ['Q3', 'L000004']) are fed.
lotsaram wrote:To refine your example create a view containing only the intersection of Q2 and L000004, zero suppress the view and there should be no value, this indicates the cell is not fed (although it evaluated to 1).
The screenshot of a view is attached below (I also attach view where whole quarter dimension is shown to confirm what I showed in my previous post). As we can see ['Q2', 'L000004'] is there!
lotsaram wrote:Expected behaviours is that if a rule calculated cell evaluates to zero then it will not feed any further cells. At least that's what I have always observed.
I was expecting exactly the opposite, but decided to make an example to make sure I give Nicola the right answer.
My test just assured me in my thoughts.

As we know TM1 tends to behave different between versions without mentioning about it in documentation, so it is possible you could observe different behavior.
My test was done on 9.5.1 HF17, Windows 32 bit.

Regards.
Attachments
ViewsWithZeroSuppression.JPG
ViewsWithZeroSuppression.JPG (32.41 KiB) Viewed 4687 times

User avatar
qml
MVP
Posts: 1073
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Calculated Zero Cells and Feeders

Post by qml » Mon Jul 11, 2011 9:11 am

I will have to agree with jstrygner on this one. Whether a cell calculates to 0 or not does not seem to influence its ability to feed further cells. If it is fed itself then it feeds.
My test was with 9.5.1, no hotfixes. In my example Chicago is always calculated to 0, but it does not stop it from feeding London. The view is zero-supressed and so Chicago is not shown, but London, fed from Chicago, is shown with no problem and fed, which Check Feeders confirms (not shown in my screenshot).
Attachments
yetanotherfeedercheck.JPG
yetanotherfeedercheck.JPG (43.81 KiB) Viewed 4655 times
Kamil Arendt

User avatar
Michel Zijlema
Site Admin
Posts: 705
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Calculated Zero Cells and Feeders

Post by Michel Zijlema » Mon Jul 11, 2011 9:48 am

I would like to add I agree with jstrygner too. I was thinking in a entirely different direction :geek: .
AFAIK this feeder behaviour has always been like this (at least for all v9 versions).

Michel

lotsaram
MVP
Posts: 3251
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Calculated Zero Cells and Feeders

Post by lotsaram » Mon Jul 11, 2011 10:22 am

Weight of evidence is that a fed rule cell will on-feed regardless whether it's calculated value is zero or not. This isn't the behaviour I would have expected. I'll try and do my own quick analysis and have a serve of humble pie on the side this afternoon.

Tested in 9.5.1 and calculated value of zero still propagates feeders:
FeederCheck.jpg
FeederCheck.jpg (111.1 KiB) Viewed 4634 times
I always try to avoid conditional feeders wherever possible, but this would be a case in point where it is absolutely necessary. Groan and d'uh.
Last edited by lotsaram on Mon Jul 11, 2011 3:55 pm, edited 1 time in total.

User avatar
qml
MVP
Posts: 1073
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Calculated Zero Cells and Feeders

Post by qml » Mon Jul 11, 2011 11:58 am

Weight of evidence is that a fed rule cell will on-feed regardless whether it's calculated value is zero or not.
I daresay (after some further testing to make sure I'm not dreaming this) that a cell will feed on even if there is no feeder statement currently attached to it and at least one of the following is true:

a) there is a resident feeder "flag" because of a feeder statement that has been turned on, but isn't anymore (as we know TM1 does not unload feeders between sessions unless the cube itself is unloaded);

b) there has never been a feeder statement, but there has been a non-zero value input straight in the cell; this cell will feed on even if the value goes back to zero (during that server session, of course).
Kamil Arendt

User avatar
Steve Rowe
Site Admin
Posts: 1920
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Calculated Zero Cells and Feeders

Post by Steve Rowe » Mon Jul 11, 2011 8:59 pm

I'd always assumed that a zero ruled cell would not trigger a feeder but can't recall spending time testing this.

It sort of makes sense that it doesn't work like this though since it implies that the feeder engine would have to fully evaluate all rule driven cells on server start-up in order to work in this way.

Not sure what the implications are though, does it imply that in a sparse ruled cube that the feeders should be written

['Source']=>DB ( If ['Source']=0, '', 'CubeName', etc );

Sounds pretty horrid.

I seem to remember that some aspects of the trace feeder functionality isn't entirely accurate in that when you request this it reports what is in the rule file irrespective of cube values. The cube values in the test cases don't support this though. What is reported in the performance cubes in the test cases in terms of fed cells?
Cheers

RCO
Posts: 40
Joined: Mon Sep 09, 2013 7:16 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Calculated Zero Cells and Feeders

Post by RCO » Fri Jul 17, 2015 12:50 am

Hi everyone,
I came across with this post as I am having exactly the same problem as Nicola.
I am not sure if I misread all your previous replies but I don't think the issue of having the respective columns and/or rows disappearing when zero suppression is activated in a cube view was actually answered. I apologise if it indeed was :?

Below example is for a given Company code where a ratio formula is " Y / X = Ratio A " . This is only calculated at a Company level and not at the cost centre level basically due to the fact that variable "X" does not apply by cost centre hence it is allocated to a "NA" element as shown below. Variable "Y" applies to individual cost centres and the aggregated result is then used to workout "Ratio A" for a company.
Capture.PNG
Capture.PNG (5.46 KiB) Viewed 3542 times
I would think feeders will be something like: ['X' , 'Y' ]=> [ 'Ratio A' ] , however when zero suppression is activated in a cube view Ratio A column/row disappears. Did I miss anything in your replies?
I would really appreciate any help :)

Post Reply