Connecting Salesforce to TM1

IBM Planning Analytics (TM1) developers benefit from TM1py, in particular when source data resides in cloud systems such as Snowflake or Salesforce, where it can’t be reached easily with plain TM1.

This article explains how to create a dimension in TM1 from a Salesforce object with one Python script.

Salesforce supports many APIs (REST, SOAP, CURL…) and you can use any programming language such as Python or Golang. When thinking about writing a script in TM1, Python is a natural choice mainly thanks to TM1py. Once connected to Salesforce with Python, the data can then easily be sent to TM1 using the pre-built functions coming with TM1py.

Option 1 and 2 are very similar as they require two steps:

• One python script to extract Salesforce data to a relational database or CSV files

• One TM1 process to load the data into TM1

The third option requires only one Python script to request the data from salesforce, transform the data with Python and then send it to TM1.

This option is the only one that will work with TM1 on-premise and with the IBM Planning Analytics Cloud.

There are three main sections in the Python script we need:

1. Connect to Salesforce and request data from a SOQL query

2. Transform the data and create a new dimension object in Python

3. Create this dimension in TM1

The script is available to download at the end of the blog.

When working with Python, there is a popular Python package called simple_saleforce. With this package, you can connect to your Salesforce environment with one line of code:

sf = Salesforce(**config['salesforce'])

Then to retrieve data from Salesforce, you need to write a SOQL query. To help you write your query you can use dataloader.io as below:

The code to connect to Salesforce and to execute a SOQL query is only 5 lines of code:

At this stage, all the data is now stored in our Python object called records. The next step is to loop through the records to get the information we need to create a new dimension object.

A dimension object with TM1py requires the following information:

• List of elements

• List of edges (parent, child)

• List of attributes

• Attributes values

The code below creates a new hierarchy then loops through the fields to create one attribute per field and finally add the total consolidation.

Then in the same function, we loop through the records to create the edges (parent, child) and for each record, we loop through the values to add the attributes’ values:

Once the dimension object is created in our python script, then we just need three TM1py functions:

• TM1Service to connect to TM1

• dimension.update_or_create: to create the dimension

• cubes.cells.write: to update the attributes’ values

All of this with only 3 lines of code:

That’s it!

The python script is available to download below:

• Simplify MDX in TM1

• Save time testing TM1 security with impersonation

• Data science with TM1