Store values VS. Accumulate values

Post Reply
sunlight
Posts: 8
Joined: Thu May 12, 2016 10:29 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Germany

Store values VS. Accumulate values

Post by sunlight » Sun Aug 06, 2017 7:46 pm

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 606 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 606 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 606 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: 8
Joined: Thu May 12, 2016 10:29 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Germany

Re: Store values VS. Accumulate values

Post by sunlight » Sun Aug 06, 2017 7:53 pm

Link to the Yandex Drive with the Excel file "sales_plan2.xls":
https://yadi.sk/i/DRwYM6An3Lm4cS

User avatar
gtonkin
MVP
Posts: 511
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Store values VS. Accumulate values

Post by gtonkin » 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

Wim Gielis
MVP
Posts: 1538
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Store values VS. Accumulate values

Post by Wim Gielis » Mon Aug 07, 2017 8:23 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

sunlight
Posts: 8
Joined: Thu May 12, 2016 10:29 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Germany

Re: Store values VS. Accumulate values

Post by sunlight » 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/

User avatar
tomok
MVP
Posts: 2335
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 » Mon Aug 07, 2017 5:08 pm

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:

Sighurd
Posts: 29
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 » Mon Aug 07, 2017 9:11 pm

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: 8
Joined: Thu May 12, 2016 10:29 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Germany

Re: Store values VS. Accumulate values

Post by sunlight » Tue Aug 08, 2017 9:08 pm

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: 1538
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Store values VS. Accumulate values

Post by Wim Gielis » Tue Aug 08, 2017 11:17 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

sunlight
Posts: 8
Joined: Thu May 12, 2016 10:29 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Germany

Re: Store values VS. Accumulate values

Post by sunlight » Fri Aug 11, 2017 7:16 pm

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 272 times
Accumulate Values, as you wrote it is CellPutN( CellGetN( ) ):
script accumulate values.jpg
script accumulate values.jpg (66.59 KiB) Viewed 272 times
My question is actually already solved, thank you and @gtonkin for your help!

Wim Gielis
MVP
Posts: 1538
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Store values VS. Accumulate values

Post by Wim Gielis » Sat Aug 12, 2017 3:31 pm

Okay, so if you bypass the wizard code of CellPutN and CellGetN, it would be a CellIncrementN function.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

Post Reply