Allocation best practice advice and help with rules needed
-
- Posts: 30
- Joined: Mon Oct 03, 2011 9:18 pm
- OLAP Product: TM1
- Version: 10.2.2fp6
- Excel Version: 2016
Allocation best practice advice and help with rules needed
I have a history of sales by product types and product lines (dimension 't-l'), by product managers ('pm'). I have a forecast from marketing department on a summary level - by product types. I need to allocate it to both 't-l' and 'pm'. I can not use actual names of products in rules, as there are too many of them and they change dynamically. Same applies to product managers.
I summed up the situation on the screenshot. I need a set of rules to fill the lower third of the table. Obviously, all numbers for all product lines of product type 1 will have to be doubled, for product type 2 - tripled. How do I do it for a large cube?
I summed up the situation on the screenshot. I need a set of rules to fill the lower third of the table. Obviously, all numbers for all product lines of product type 1 will have to be doubled, for product type 2 - tripled. How do I do it for a large cube?
-
- MVP
- Posts: 3120
- 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: Allocation best practice advice and help with rules need
For example:
['calculation']=N:['history'] * DB('forecast calc','forecast','PM Total',Elpar('t-l',!t-l,1)) \ DB('forecast calc','history','PM Total',Elpar('t-l',!t-l,1));
The associated feeder is simple and is left for you.
['calculation']=N:['history'] * DB('forecast calc','forecast','PM Total',Elpar('t-l',!t-l,1)) \ DB('forecast calc','history','PM Total',Elpar('t-l',!t-l,1));
The associated feeder is simple and is left for you.
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: 30
- Joined: Mon Oct 03, 2011 9:18 pm
- OLAP Product: TM1
- Version: 10.2.2fp6
- Excel Version: 2016
Re: Allocation best practice advice and help with rules need
Thank you very much!
I compared it to what I wrote trying to solve this problem and found my mistake. I used Elpar('t-l','!t-l',1) instead of Elpar('t-l',!t-l,1) and got all zeroes instead of right values.
I compared it to what I wrote trying to solve this problem and found my mistake. I used Elpar('t-l','!t-l',1) instead of Elpar('t-l',!t-l,1) and got all zeroes instead of right values.
-
- Posts: 30
- Joined: Mon Oct 03, 2011 9:18 pm
- OLAP Product: TM1
- Version: 10.2.2fp6
- Excel Version: 2016
Re: Allocation best practice advice and help with rules need
BTW, regarding feeders... Two top thirds of the cube on screenshot are imported from external sources. The last third is exported to another cube. I need only detailed values, I don't need any consolidations. Am I right in assuming that I can leave feeders section of the rules empty for this cube?
-
- MVP
- Posts: 3120
- 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: Allocation best practice advice and help with rules need
If you're only interested at level 0 in the cube (all white cells), you can omit the feeders.
Note that consolidations will not take place (isn't that the core business of TM1?).
Also, zero suppression will not work as intended.
But if you can live with that, you can skip the Feeders part.
Note that consolidations will not take place (isn't that the core business of TM1?).
Also, zero suppression will not work as intended.
But if you can live with that, you can skip the Feeders part.
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: 30
- Joined: Mon Oct 03, 2011 9:18 pm
- OLAP Product: TM1
- Version: 10.2.2fp6
- Excel Version: 2016
Re: Allocation best practice advice and help with rules need
This is a "service cube", users won't ever see it. They will see the result in other cube and there will be consolidations. It is kinda special case, it does not even need to be loaded all the time, it's once-a-month-allocation. So yes, I can live with it. Thanks again.
-
- MVP
- Posts: 2832
- 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: Allocation best practice advice and help with rules need
If you are using a view as your data source in the export you won't be able to use zero-suppression to limit the number of records.Sighurd wrote:The last third is exported to another cube.
-
- Posts: 30
- Joined: Mon Oct 03, 2011 9:18 pm
- OLAP Product: TM1
- Version: 10.2.2fp6
- Excel Version: 2016
Re: Allocation best practice advice and help with rules need
Out of professional curiosity and for the sake of experience I decided to finish set of rules and feeders for the above cube. The result is on attached screenshot. Seems to work correctly, but I wonder: is it optimal?
-
- MVP
- Posts: 3120
- 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: Allocation best practice advice and help with rules need
You are overfeeding.
Feed:
['history']=>['calculation'];
Or change calculation into a consolidation and add history as a child (the only one). Don't know if this does not compromise the rest of your TM1 model.
Or use TI to compute the allocation.
Feed:
['history']=>['calculation'];
Or change calculation into a consolidation and add history as a child (the only one). Don't know if this does not compromise the rest of your TM1 model.
Or use TI to compute the allocation.
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: 30
- Joined: Mon Oct 03, 2011 9:18 pm
- OLAP Product: TM1
- Version: 10.2.2fp6
- Excel Version: 2016
Re: Allocation best practice advice and help with rules need
Thank you, Wim. All good and valid ideas to think about.