Feed C level rules
-
- 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
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
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
- 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
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
Yes, it is. But it only has two children.jim wood wrote:Is U_TOT a consolidation?
- 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
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
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.
-
- 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
There are better ways to do this.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.
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
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
-
- 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
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.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.
-
- 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
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
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
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
-
- 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
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.tomok wrote: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.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.
-
- 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
Hi Wim,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.
You conclusion sounds very well but I did not understand the way of doing it
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
-
- 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
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?
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
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
-
- 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
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!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).
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?
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.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?
On most of the scenarios, ['U_TOT','B1G'] contains base level data.
But I don't see how it could work without feeders ?
- 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
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.
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
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?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.
(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?
- 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
Hi catherine,Catherine wrote: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!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).
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?
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
-
- 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
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
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
-
- 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
You're welcome, glad it helps!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
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
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
- 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
Hi Catherine,Catherine wrote: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?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.
(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?
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
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.Catherine wrote:But I don't see how it could work without feeders ?
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.