Store values VS. Accumulate values

Post Reply
sunlight
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

Post by sunlight »

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:
store values cube.jpg
store values cube.jpg (92.76 KiB) Viewed 10016 times
here is the value 429.208,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select accumulate values:
accumulate values cube.jpg
accumulate values cube.jpg (98.5 KiB) Viewed 10016 times
I checked the source excel file and got how the values sum up to 429.208 for accumulate values:
accumulate values excel source.jpg
accumulate values excel source.jpg (284.52 KiB) Viewed 10016 times
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.
sunlight
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

Post by sunlight »

Link to the Yandex Drive with the Excel file "sales_plan2.xls":
https://yadi.sk/i/DRwYM6An3Lm4cS
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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
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: Store values VS. Accumulate values

Post by Wim Gielis »

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
Good explanation from George.

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
sunlight
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

Post by sunlight »

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.
Last edited by sunlight on Wed Jan 13, 2021 9:41 am, edited 1 time in total.
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: Store values VS. Accumulate values

Post by tomok »

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/
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? :roll:
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Sighurd
Posts: 30
Joined: Mon Oct 03, 2011 9:18 pm
OLAP Product: TM1
Version: 10.2.2fp6
Excel Version: 2016

Re: Store values VS. Accumulate values

Post by Sighurd »

tomok wrote: Mon Aug 07, 2017 5:08 pm 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? :roll:
Ambitious, right? :mrgreen:
sunlight
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

Post by sunlight »

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".
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: Store values VS. Accumulate values

Post by Wim Gielis »

sunlight wrote: Tue Aug 08, 2017 9:08 pm 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".
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
sunlight
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

Post by sunlight »

Hi Wim Gielis,

here are the scripts generated by Wizard. Store Values, as you wrote it is CellPutN( ):
script store values.jpg
script store values.jpg (42.91 KiB) Viewed 9682 times
Accumulate Values, as you wrote it is CellPutN( CellGetN( ) ):
script accumulate values.jpg
script accumulate values.jpg (66.59 KiB) Viewed 9682 times
My question is actually already solved, thank you and @gtonkin for your help!
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: Store values VS. Accumulate values

Post by Wim Gielis »

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
Post Reply