Feed C level rules

Post Reply
Catherine
Posts: 110
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Feed C level rules

Post by Catherine »

Hi,

I have a problem of an instance RAM growing too much because of feeders.

In a few words:
-My main cube has 14 dimensions.
-Most of data are stored, a few calculation rules getting data from another cube.
-About 10 ratios and indexes calculations. Those calculations are the source of my problem. They are ratios so my rules do not have neither N: nor C:
The calculation works perfectly well, but my users want to be able to use suppress zero functionality. So I had to feed those rules. This makes the RAM grow from about 4 Go (without feeding the ratios) to 21 Go (feeding the 10 ratios).

Here is an exemple of one ratio and its feeders:
['U_WBrand']=['U_TOT']\['U_TOT','B1G']*100;
['U_TOT']=>['U_WBrand'];

Any idea why the RAM grows so much?

Thank you
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Feed C level rules

Post by jim wood »

Is U_TOT a consolidation?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Catherine
Posts: 110
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Re: Feed C level rules

Post by Catherine »

jim wood wrote:Is U_TOT a consolidation?
Yes, it is. But it only has two children.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Feed C level rules

Post by jim wood »

What about U_WBRand? Also are any of the chikdren calculated? How big is your cube dimension wise? Do you have any large dimensions?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feed C level rules

Post by tomok »

You can't feed a consolidation. Referencing a consolidated node in the feeder rule has the effect of feeding all the children underneath the consolidation. So, if you write your rule to calculate the ratio based on all nodes (bascially leaving out the N: or C: before) then the ratio will calculate correctly, but it won't be fed and a zero suppress will hide the ratio, if shown by itself. The only way I know around this is to structure your view so that the row or column that the ratio is in won't be suppressed because there are other values on the same row and/or column that have values and the ratio just comes along for the ride.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Feed C level rules

Post by Wim Gielis »

tomok wrote:The only way I know around this is to structure your view so that the row or column that the ratio is in won't be suppressed because there are other values on the same row and/or column that have values and the ratio just comes along for the ride.
There are better ways to do this.
For example, turn the ratio into a consolidation by adding a child that is fed correctly or contains base level data.
This will make sure the ratio is fed.
Basically, a component of the ratio could be a valid candidate to be the child. It could be a consolidated element in itself just as well.
Consumes no extra memory at all.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feed C level rules

Post by tomok »

Wim Gielis wrote:For example, turn the ratio into a consolidation by adding a child that is fed correctly or contains base level data.
This will make sure the ratio is fed.
If you make the ratio a consolidation and have other children underneath it that contain anything other than zero then your ratio won't be correct. A ratio of 20% plus another child that has a 1 is now equal to a consolidated ratio of 120%. Not the right answer. You would have to add a child with a zero balance and feed that child. Sorry, I just wouldn't do this.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Feed C level rules

Post by Wim Gielis »

Hello Tomok

Maybe I did not understand, or maybe I expressed myself in a not so clear way :-)
The ratio will change from N to C level but it will contain the exact same rule (this is, no qualifier for N as this is useless now).
The children of the ratio will just be denominator or numerator of the ratio, depends.

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Feed C level rules

Post by lotsaram »

tomok wrote:
Wim Gielis wrote:For example, turn the ratio into a consolidation by adding a child that is fed correctly or contains base level data.
This will make sure the ratio is fed.
If you make the ratio a consolidation and have other children underneath it that contain anything other than zero then your ratio won't be correct. A ratio of 20% plus another child that has a 1 is now equal to a consolidated ratio of 120%. Not the right answer. You would have to add a child with a zero balance and feed that child. Sorry, I just wouldn't do this.
I don't think Wim was suggesting to replace the rule with a consolidation but rather to keep the rule for U_WBrand and do away with the feeder as this would now be unnecessary. Provided the rule is written as generically as it is then there isn't any risk as such of the calculated value not being correct as the rule overrides the consolidation. I find this is frequently a very good approach, but in this instance with this kind of ratio calculation I don't really see a need to feed the calculation at all.
Catherine
Posts: 110
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Re: Feed C level rules

Post by Catherine »

Wim Gielis wrote:There are better ways to do this.
For example, turn the ratio into a consolidation by adding a child that is fed correctly or contains base level data.
This will make sure the ratio is fed.
Basically, a component of the ratio could be a valid candidate to be the child. It could be a consolidated element in itself just as well.
Consumes no extra memory at all.
Hi Wim,

You conclusion sounds very well but I did not understand the way of doing it :oops:
Could you detail it taking into account my context?

Let me just give some additional explanation on my context/rule.

My rule is the following:
['U_WBrand']=['U_TOT']\['U_TOT','B1G']*100;
U_TOT is a consolidation of two elements but I think it has nothing to do with my problematic. It could be as well a leaf element.
U_WBrand is a leaf level element, and is part of the same dimension as U_TOT.
B1G is a consolidation of all elements on my Brand dimension.
So in functional terms, U_WBrand is an element on which I can read the weight of a brand relatively to all brands, on all contexts of the cube (for a specific customer or for all, for a specific country or a group of countries, etc.).

As we have big numbers of elements on all dimensions, we need to be able to suppress zeros when looking at that ratio.

Thanks in advance
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Feed C level rules

Post by Wim Gielis »

You will want to try to add 'U_TOT' as child of the (now consolidated) element 'U_WBrand'.
Leave the rule as is, put the feeder(s) in commentary and restart the TM1 service (for the best test if this works or not).

But as Lotsaram suggested, please be sure it does not already work sans feeders. I.e. is ['U_TOT','B1G'] containing base level data or fed cells?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Catherine
Posts: 110
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Re: Feed C level rules

Post by Catherine »

Wim Gielis wrote:You will want to try to add 'U_TOT' as child of the (now consolidated) element 'U_WBrand'.
Leave the rule as is, put the feeder(s) in commentary and restart the TM1 service (for the best test if this works or not).
Ok, it works perfectly well (I put the two children of U_TOT as children of U_WBrand instead of U_TOT itself). I hadn't seen that tip before!
The only drawback is the structure of my dimension... Users will have difficulty to understand why U_WBrand has children. Any idea to avoid this?
Wim Gielis wrote:But as Lotsaram suggested, please be sure it does not already work sans feeders. I.e. is ['U_TOT','B1G'] containing base level data or fed cells?
When I had no feeders (and no children under U_WBrand), it didn't work. The suppress zero functionality was removing the element U_WBrand.
On most of the scenarios, ['U_TOT','B1G'] contains base level data.
But I don't see how it could work without feeders ?
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Feed C level rules

Post by jim wood »

The user problem:

Create 3 new elements under U_WBrand. Make sure any data enetered in to teh original elements also gets eneterd here. Change the rule to use teh 2 new elements. Then hide the new elements using security.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Catherine
Posts: 110
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Re: Feed C level rules

Post by Catherine »

jim wood wrote:The user problem:

Create 3 new elements under U_WBrand. Make sure any data enetered in to teh original elements also gets eneterd here. Change the rule to use teh 2 new elements. Then hide the new elements using security.
You mean I create a kind of U_TOT_Bis, child of U_WBrand which should receive the same data as U_TOT, and I hide it using security?
(U_TOT is used by users for some analysis.)
If so, my RAM will grow.
But as I have 4 ratios built on the same way, I can use that U_TOT_Bis for all of them, and at the end, I have a significant gain.
Is that it?
User avatar
Michel Zijlema
Site Admin
Posts: 712
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: Feed C level rules

Post by Michel Zijlema »

Catherine wrote:
Wim Gielis wrote:You will want to try to add 'U_TOT' as child of the (now consolidated) element 'U_WBrand'.
Leave the rule as is, put the feeder(s) in commentary and restart the TM1 service (for the best test if this works or not).
Ok, it works perfectly well (I put the two children of U_TOT as children of U_WBrand instead of U_TOT itself). I hadn't seen that tip before!
The only drawback is the structure of my dimension... Users will have difficulty to understand why U_WBrand has children. Any idea to avoid this?
Hi catherine,

I haven't tested this, but maybe you could move a dummy consolidation between the ratio element and the U_TOT element and hide this dummy consolidation per Jim's suggestion. I assume this will 'break' the drill-down on the ratio element (although it will still appear as a C-element).

Michel
Catherine
Posts: 110
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Re: Feed C level rules

Post by Catherine »

Hi Michel,

Good idea !
It works perfectly well.

Thank you all for your help. I am happy to have reduced the RAM from 22 Go to 5 Go !

Catherine
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Feed C level rules

Post by Wim Gielis »

Catherine wrote:Hi Michel,

Good idea !
It works perfectly well.

Thank you all for your help. I am happy to have reduced the RAM from 22 Go to 5 Go !

Catherine
You're welcome, glad it helps!
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Feed C level rules

Post by jim wood »

Catherine wrote:
jim wood wrote:The user problem:

Create 3 new elements under U_WBrand. Make sure any data enetered in to teh original elements also gets eneterd here. Change the rule to use teh 2 new elements. Then hide the new elements using security.
You mean I create a kind of U_TOT_Bis, child of U_WBrand which should receive the same data as U_TOT, and I hide it using security?
(U_TOT is used by users for some analysis.)
If so, my RAM will grow.
But as I have 4 ratios built on the same way, I can use that U_TOT_Bis for all of them, and at the end, I have a significant gain.
Is that it?
Hi Catherine,

I'm glad that you've got a result. Apolgies for not explaining myself a little better but this was my second attempt at entering my suggestion. The first longer attempt went when my browser crashed. I kept my second shorter just in case,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Feed C level rules

Post by lotsaram »

Catherine wrote:But I don't see how it could work without feeders ?
Welcome to the world of lateral thinking! The sole purpose of feeders is to allow rule calculated values to consolidate as for an N rule there is no underlying data, so you can think of a feeder as "fake data". It is just there as a flag to enable consolidation with the sparse consolidation algorithm that generally makes TM1 so efficient. When you structure a dimension like this and place the element that would usually be the feeder for a calculation as a child what you are doing is using actual data to do the "feeding". So yes many rules can in fact work without feeders providing your dimensions are appropriately designed to support this.

The only drawback of this solution is potential confusion from users as the rule calculated element will now display as a consolidation. The usual way I work around this is the method which Michel and Jim have already alluded to by not add the "feeding elements" directly to the calc element but to an intermediate node with a name like "NA" or "Invisible" that you hide from users with element security. The calculated element then still displays as a consolidation but users are unable to drill down on it.

The other real benefit of this method it you can have virtually as many rule calculated members as you please with no impact on growth in RAM or rule save time as there is no additional real or virtual cube data required.
Post Reply