Store values VS. Accumulate values
-
- Posts: 16
- Joined: Thu May 12, 2016 10:29 am
- OLAP Product: TM1
- Version: PA 2.0
- Excel Version: Office 365
- Location: Frankfurt am Main, Germany
Store values VS. Accumulate values
Hi all,
I got the basic concept how store values vs. accumulate values works, but I cannot explain the values from the 6 dimensions cube from the recipe from TM1 Cookbook (Ankit Garg).
Here is the value 55.704,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select store values: here is the value 429.208,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select accumulate values: I checked the source excel file and got how the values sum up to 429.208 for accumulate values: But I cannot get how the values sum up to 55.704 for store values, I marked these values yellow.
I would appreciate any idea on my example!
Excel file I am uploading is attached.
I got the basic concept how store values vs. accumulate values works, but I cannot explain the values from the 6 dimensions cube from the recipe from TM1 Cookbook (Ankit Garg).
Here is the value 55.704,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select store values: here is the value 429.208,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select accumulate values: I checked the source excel file and got how the values sum up to 429.208 for accumulate values: But I cannot get how the values sum up to 55.704 for store values, I marked these values yellow.
I would appreciate any idea on my example!
Excel file I am uploading is attached.
Last edited by sunlight on Sun Aug 06, 2017 8:11 pm, edited 2 times in total.
-
- Posts: 16
- Joined: Thu May 12, 2016 10:29 am
- OLAP Product: TM1
- Version: PA 2.0
- Excel Version: Office 365
- Location: Frankfurt am Main, Germany
Re: Store values VS. Accumulate values
Link to the Yandex Drive with the Excel file "sales_plan2.xls":
https://yadi.sk/i/DRwYM6An3Lm4cS
https://yadi.sk/i/DRwYM6An3Lm4cS
- gtonkin
- MVP
- Posts: 1198
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Store values VS. Accumulate values
Hi sunlight,
Accumulating is basically getting the value in the cube then adding to that the value in the incoming record. The result is then written back to the relevant cell (based on the data in the record e.g. Version, month, product etc.) in the cube.
Store is simply taking the value from the incoming record and writing it to the relevant cell. If your file has multiple values for the same comibinations, based on your dimensions, values will be over-written each time a value for the same cell in the cube is encountered.
In short, the last value encountered in your source file for a particular cell is written to the cube.
HTH
Accumulating is basically getting the value in the cube then adding to that the value in the incoming record. The result is then written back to the relevant cell (based on the data in the record e.g. Version, month, product etc.) in the cube.
Store is simply taking the value from the incoming record and writing it to the relevant cell. If your file has multiple values for the same comibinations, based on your dimensions, values will be over-written each time a value for the same cell in the cube is encountered.
In short, the last value encountered in your source file for a particular cell is written to the cube.
HTH
-
- MVP
- Posts: 3113
- 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: Store values VS. Accumulate values
Good explanation from George.gtonkin wrote: ↑Mon Aug 07, 2017 4:26 am Hi sunlight,
Accumulating is basically getting the value in the cube then adding to that the value in the incoming record. The result is then written back to the relevant cell (based on the data in the record e.g. Version, month, product etc.) in the cube.
Store is simply taking the value from the incoming record and writing it to the relevant cell. If your file has multiple values for the same comibinations, based on your dimensions, values will be over-written each time a value for the same cell in the cube is encountered.
In short, the last value encountered in your source file for a particular cell is written to the cube.
HTH
Let me add to this regarding the codes that we use in Turbo Integrator.
You can use CellGetN( ) to get a value from a cube. Then you add a value, usually stored in a variable, representing the value in the incoming record.
After that you do a CellPutN( ) to write the value to the cube, overwriting whatever value is in the cube at that intersection.
This was the traditional approach before TM1 version 10.0 (but still works perfectly). Since version 10.0 we use the function CellIncrementN( ) which does the same task in 1 go. This means less code, less places in the code where fixed or hard-coded values are. I don't think it will matter with respect to performance.
For anyone using the Turbo Integrator wizard, this has not been updated with CellIncrementN( ) and still uses CellPutN( CellGetN( ) )
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: 16
- Joined: Thu May 12, 2016 10:29 am
- OLAP Product: TM1
- Version: PA 2.0
- Excel Version: Office 365
- Location: Frankfurt am Main, Germany
Re: Store values VS. Accumulate values
Hi gtonkin,
thank you a lot for your reply!
After posting the question on forum it seems I found the answer. I just needed to sort the dimensions in "A to Z" order starting from the last one in the cube, then it becomes really obvious which values TM1 takes from this excel file in case of Store Values.
thank you a lot for your reply!
After posting the question on forum it seems I found the answer. I just needed to sort the dimensions in "A to Z" order starting from the last one in the cube, then it becomes really obvious which values TM1 takes from this excel file in case of Store Values.
Last edited by sunlight on Wed Jan 13, 2021 9:41 am, edited 1 time in total.
-
- 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: Store values VS. Accumulate values
You don't know the difference between accumulating and storing values in a TI process and you're writing a blog about how to use TM1?sunlight wrote: ↑Mon Aug 07, 2017 10:21 am Hi gtonkin,
thank you a lot for your reply!
After posting the question on forum it seems I found the answer. I just needed to sort the dimensions in "A to Z" order starting from the last one in the cube, then it becomes really obvious which values TM1 takes from this excel file in case of Store Values. Here is a short post with a little bit more details in my TM1 blog: https://tm1notes.wordpress.com/2017/08/ ... te-values/
-
- Posts: 16
- Joined: Thu May 12, 2016 10:29 am
- OLAP Product: TM1
- Version: PA 2.0
- Excel Version: Office 365
- Location: Frankfurt am Main, Germany
Re: Store values VS. Accumulate values
Hi Wim Gielis,
thank you a lot for your reply and information on CellIncrementN( ) !
I also compared TM1 scripts generated by wizard CellPutN( CellGetN( ) ), but was still curious how the calculation looks like on this real example "sales_plan2.xls".
thank you a lot for your reply and information on CellIncrementN( ) !
I also compared TM1 scripts generated by wizard CellPutN( CellGetN( ) ), but was still curious how the calculation looks like on this real example "sales_plan2.xls".
-
- MVP
- Posts: 3113
- 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: Store values VS. Accumulate values
Then it would be helpful if you could post your actual code. Also have a look at the help files for the arguments of that function.
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: 16
- Joined: Thu May 12, 2016 10:29 am
- OLAP Product: TM1
- Version: PA 2.0
- Excel Version: Office 365
- Location: Frankfurt am Main, Germany
Re: Store values VS. Accumulate values
Hi Wim Gielis,
here are the scripts generated by Wizard. Store Values, as you wrote it is CellPutN( ): Accumulate Values, as you wrote it is CellPutN( CellGetN( ) ): My question is actually already solved, thank you and @gtonkin for your help!
here are the scripts generated by Wizard. Store Values, as you wrote it is CellPutN( ): Accumulate Values, as you wrote it is CellPutN( CellGetN( ) ): My question is actually already solved, thank you and @gtonkin for your help!
-
- MVP
- Posts: 3113
- 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: Store values VS. Accumulate values
Okay, so if you bypass the wizard code of CellPutN and CellGetN, it would be a CellIncrementN function.
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