Automated Upload of XLS Flat Files into IBM Planning Analytics Cloud

Uploading large amounts of data from Excel into IBM Planning Analytics Cloud environments can be a cumbersome task. Although IBM PA’s Excel integration is second to none in the EPM space, there is no way to easily upload a flat file directly from Excel.

Some of the common solutions to the problem are:

• DBSW formulas – These are great for static formats and smaller data volumes; however, they can be difficult to maintain with changing data sets. They can be slow and don’t allow for any advanced processing that is possible with TI
• Using VBA and SFTP – This is a useful solution to manipulate a file, convert to a flat file and transfer the file and can seamlessly execute a TI process. However, it does require the client machine to have an SFTP client installed and requires some work to pass the File Share credentials. Additionally, SFTP is not support in the PA SaaS (AWS and Azure environments)

Although IBM has added some rich functionality for uploading files via Planning Analytics Workspace, the ability to do so from Excel has never really been available.

ACG has come up with a simple solution that leverages basic VBA, the PAfE API, and TI processes to seamlessly massage an Excel file and load into a cube. To summarize what needs to be done:

• Setup your Excel book to use the PAfE API. See https://ibm.github.io/paxapi/#set-up
• Using VBA, convert your Excel to a CSV file. This can include some data scrubbing, but at a minimum it should save your Excel file as a new CSV file
• Optional: Create an application folder using the MakeFolderTM1 You can also use an existing folder
• Using the PublishTM1 function, push the newly created Excel file to the folder
• Use the ExecuteFunction function to kick off the TI process to load the file
• At a minimum, 2 TI processes will be needed that can be wrapped together in a single task:
  • Parse the application BLOB (PAoC) or JSON (PA SaaS) file to get the exact location of the CSV file in the Externals directory.
  • Using the file determined in the first process, load the CSV file

Of course, the underlying detail and customization of these steps will vary from implementation to implementation. However, the framework will hold up and allows for great flexibility without leaving the friendly confines of Excel.

Please reach out to info@acgi.com if you would like to discuss further.