Methods for Inputting Data into TM1 Cube

[rrizwansayed10]

Hello All,

What are the different method to input the data into tm1 Cube?

I'm particularly interested in understanding if it's possible to input data into a TM1 Cube directly from an Excel file(not .csv and not .cma) using a process. Has anyone attempted this?

Thanks!!!

[tomok]

Hello All,

What are the different method to input the data into tm1 Cube?

I'm particularly interested in understanding if it's possible to input data into a TM1 Cube directly from an Excel file(not .csv and not .cma) using a process. Has anyone attempted this?

Thanks!!!

TM1 cannot natively read the data in an Excel file so you would have to use another mechanism. TM1 supports ODBC and there is a driver from Microsoft that allows you to access the Excel file as if it were a database table. The key here is that the Excel file has to be structured just like an SQL table with rows representing records and columns as fields. It can't just randomly look into the Excel file and grab random cells or named ranges. Good luck. I did this exact thing about fifteen years ago and it worked fine. Better option would be a .csv file.

[David Usherwood]

As an alternative, you could open the file in Excel and add DBSW formulae down the rows to send the data to the cube. Not the neatest, but TM1 had this in version 6 before Turbo Integrator was a gleam in Manny Perez' eye.

[rrizwansayed10]

Thank you so much for your reply

but can we take excel and convert it into csv by TI process, and then can we upload csv into tm1?

Is there anything similar we can do?

[Adam]

If you have access to Excel and access to TI, why not “save as” in Excel to CSV? If multiple files, I suggest writing a VBA that automates this within a chosen folder. You may be able to build a VBScript (or other solution) and then trigger the .xlsx to .csv within TI using ExecuteProcess.

[Wim Gielis]

Yes, e.g. what Adam describes: in the past I wrote PowerShell to do just that.

[rrizwansayed10]

Thanks Adam and Wim,

I really like this idea, can both of you tell or share a step by step process or a link to do this, I totally dont know VBS and Powershell, so please guide me to do this. I only know Cognos.

[Adam]

Google (or ChatGPT) will help as .xlsx to .csv isn’t exactly a tm1-specific problem to solve.

[rrizwansayed10]

OK, Thanks a lot.

[WilliamSmith]

I've had success with three methods:

1.) Use VBA to re-calculate DBSS formulas, to push data into cube cells. (Slow)
2.) Use VBA to send JSON payloads to the TM1 Rest API (Slower) (Could only figure out how to send one cell at a time)
3.) Use VBA to export data to a flat .CSV file, in a location the TM1 server can access. Send Rest API command to trigger Turbo Integrator to import .CSV file. (Fast)

Happy to discuss further.

[Wim Gielis]

Python and tm1py (hence REST API) would also be an option.

[ardi]

I have created a Python Script to do that. I pass the excel file to a python script, create a dataframe, I clean up the dataframe and at the end I store the Dataframe to a CSV file, which is consumed by a TI Process. The TI process can be executed from within Python Script (TM1Py), or it can be executed from the same TI process that called the Python script (with Wait=1)
But you can also update the cube directly from Pandas Dataframe with TM1Py

[konstantin-spb]

Excel + VBA:
1) First button on the sheet = write the range (or the desired cells and rows) to CSV (the directory and file must be accessible from tm1);
2) Second button on the sheet = run “tm1runti.exe” with parameters and call the process to read the CSV and write data to the cube.