Load Data in TM1 Cube from CSV

Post Reply
escape18in
Posts: 26
Joined: Tue Feb 03, 2015 10:22 am
OLAP Product: IBM Cognos BI
Version: 10.2
Excel Version: Excel 2010

Load Data in TM1 Cube from CSV

Post by escape18in »

Hi Experts,

i am having data in following format.
Capture1.JPG
Capture1.JPG (34.48 KiB) Viewed 4222 times
the columns in yyyymm format is measure i.e. employee working hours. these columns are dynamic means data is available for 8,9,12 up-to 24 month.

my cube have following dimension Employee,Project,Month,Measure dimension(hours) because of uncertainty of month columns i need to convert this file in below format and then load.
Capture2.JPG
Capture2.JPG (28.69 KiB) Viewed 4222 times
is there any way that i can directly use the original file to load the data? one way is to involve ETL team to load these files in database and then load it to TM1 but i want to achieve this in TM1 any .vbs or .bat utility can also work who can transpose the file in desire format.
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: Load Data in TM1 Cube from CSV

Post by tomok »

Just create a "sample" file, that has all 24 months in it, and use that to create your code. You can leave the column names as you have them as the variable names (put a "v" in front as TM1 doesn't like numeric only variable names). Then you write 24 CellPutN statements in the Data tab:

Code: Select all

CellPutN(v20174, Employee, Project, '20174');
CellPutN(v20175, Employee, Project, '20175');
CellPutN(v20176, Employee, Project, '20176');
CellPutN,,,,,,,,,,,,,
If a row in the file has less than the 24 months those variables will be empty, which should be OK. If not then just wrap each CellPutN in an IF statement that only puts when the data is not zero.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
escape18in
Posts: 26
Joined: Tue Feb 03, 2015 10:22 am
OLAP Product: IBM Cognos BI
Version: 10.2
Excel Version: Excel 2010

Re: Load Data in TM1 Cube from CSV

Post by escape18in »

tomok wrote:Just create a "sample" file, that has all 24 months in it, and use that to create your code. You can leave the column names as you have them as the variable names (put a "v" in front as TM1 doesn't like numeric only variable names). Then you write 24 CellPutN statements in the Data tab:

Code: Select all

CellPutN(v20174, Employee, Project, '20174');
CellPutN(v20175, Employee, Project, '20175');
CellPutN(v20176, Employee, Project, '20176');
CellPutN,,,,,,,,,,,,,
If a row in the file has less than the 24 months those variables will be empty, which should be OK. If not then just wrap each CellPutN in an IF statement that only puts when the data is not zero.
Thanks for the suggestion , one more thing i forgot to mention these months are dynamic it will change as we move ahead so coding variable name as month will give data error in future.
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Load Data in TM1 Cube from CSV

Post by PavoGa »

escape18in wrote: Thanks for the suggestion , one more thing i forgot to mention these months are dynamic it will change as we move ahead so coding variable name as month will give data error in future.
If your period date always within a given time frame? In other words, the source data will never contain data outside of a 24-month period during a given fiscal year/etc? If so, structure your time dimension as 1-24 (or 36, or whatever) and use an alias for the actual periods you are dealing with at the time (201701, 201702, ...) and that way you do not have to modify code, just the aliases when appropriate.
Ty
Cleveland, TN
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: Load Data in TM1 Cube from CSV

Post by Wim Gielis »

I would use a sample file as Tom wrote.
Execute the process with a parameter for year and period.
Depending on these parameters determine the real calendar periods and do a zero out and cellincrentn.
If you use V1 to V24 you could make it all dynamic in a loop and use Expand.
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