Hi Experts,
i am having data in following format.
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.
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.
Load Data in TM1 Cube from CSV
-
- Posts: 26
- Joined: Tue Feb 03, 2015 10:22 am
- OLAP Product: IBM Cognos BI
- Version: 10.2
- Excel Version: Excel 2010
-
- 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
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:
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.
Code: Select all
CellPutN(v20174, Employee, Project, '20174');
CellPutN(v20175, Employee, Project, '20175');
CellPutN(v20176, Employee, Project, '20176');
CellPutN,,,,,,,,,,,,,
-
- 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
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.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:
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.Code: Select all
CellPutN(v20174, Employee, Project, '20174'); CellPutN(v20175, Employee, Project, '20175'); CellPutN(v20176, Employee, Project, '20176'); CellPutN,,,,,,,,,,,,,
- 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
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.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.
Ty
Cleveland, TN
Cleveland, TN
-
- 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
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.
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
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