CONSULTING
SOLUTIONS
PARTNERS
RESOURCES
CORPORATE
Twitter Facebook LinkedIn
Email [email protected]
Phone +1 713.457.7474

How to consolidate data in TM1

BLG

When loading data into a TM1 cube, the Turbo Integrator command used is CellPutN.  This sends a numeric value to a specific cell in your cube.   This command does not consolidate values, if the cells you are loading to have a value, it will be over written by the new value you send.  If you are loading two or more records to the same cell, each value will be over written by the next only the value from the last record from data load file will be retained.

For example, below is the data you want to send to the cube.

ACCOUNT STORE NUM COST CENTER CURRENCY LOCATION YEAR MONTH DATA
10050 C1 MKT USD NYC 2011 JAN 1000
10050 C1 MKT USD NYC 2011 JAN 500

 

Note that all cells (dimensions) for these two rows are the same but the data is different. By using the syntax CellPutN, the result for Account 10050 will show as 500 not the desired consolidated value of 1500.  To accumulate data as you load, you need to add the existing value from the cube to the data you are loading before loading the data to the cube.  Using the TI function CELLGETN to retrieve numeric data from a cell in the cube and then use CellPutN to send the consolidated value back to the cube.

TI Solution in two steps:

vDATA = vDATA + CELLGETN ( ‘FINANCE’,ACCOUNT,STORENUM,COSTCENTER,CURRENCY,LOCATION,YEAR,MONTH )

CELLPUTN( vDATA ,’FINANCE’, ACCOUNT,STORENUM, COSTCENTER, CURRENCY, LOCATION, YEAR , MONTH )

email [email protected]
phone +1 713.457.7474
Twitter Facebook LinkedIn
© PREDICTif Solutions. All rights reserved.