No matter which tool your company uses for planning, if finance users have to choose one tool to input lots of data, they will choose Excel.

With IBM TM1/Planning Analytics, you can give your users the freedom to do all their input into Excel and then upload the Excel file into the TM1 cubes. This article explains the different methods that you can implement in your TM1 application to upload data from an Excel file into your IBM TM1/Planning Analytics cubes.

Option 1: DBS or PAx

The most common way to send data from Excel to TM1 is to use the DBS formula. The DBS takes a value from a cell and sends it to the TM1 cube. With PAx, the new Excel Add-in, a simple copy paste can send the value into the TM1 cube. 

The advantage of this method is that it is straight forward, you just need to refresh Excel or press the commit button in PAx to upload the data into the TM1 cube. But the challenging part is that you rely on Excel and this is not the best option to upload lots of data. The other main challenge is that there is no governance or transparency. Any TM1 user could upload any data from any Excel spreadsheets.

Option 2: TM1 processes

The second option is to use a TM1 process. TM1 processes are very powerful, they can load data from many types of data sources, albeit Excel is unfortunately not one of them. In order to load data from an Excel file, you will need first to convert the file into a CSV or text format.

To upload data using this method you will need to follow these steps:

1. User copy their Excel file into a specific folder.
2. A script converts Excel file into CSV.
3. TM1 process uploads the CSV file into TM1.

The advantage of this method is that users do not need to log in to TM1 and they can work offline. But this method is not straight forward, in theory it works, but in real life applications there are lots of moving parts.

Option 3: Canvas

The third option is to use the TM1 REST API, in just two steps you can load lots of data from your web browser into TM1:

Canvas includes the SheetJS library which helps working with spreadsheets on the web. With a simple button you can upload an Excel file and then display the data into your browser.

This step allows you to do validations before sending the data into TM1:

Once the data is in the browser and after clicking on the Save button. Canvas will batch the query and send the data into the TM1 cube using the TM1 REST API.

This method gives you an extra step where you can validate automatically your users input and add greater governance, controls and transparency so you know who has updated which cells and when.

Once the Excel file is uploaded, all the work is happening on your web browser. You can then continue working on Excel while your data is uploading.

• Find the biggest impact on your budget in an instant.
• Analyse your data with Google Map.
• 5 simple ways to be more productive as a TM1 Administrator.