Planning Analytics

Hi,

We have a PA Cloud client who is looking to switch their ERP system to Microsoft Business Central.

Has any set up such a connection?  Did you need to write code to connect through the API or were you able to create a connection through Secure Gateway?

I appreciate any insight as this datasource is new to me :)

Hi, I don't believe Microsoft Business Central has an ODBC connector but it does have a rest API endpoint. Have done a couple of integrations where a Turbo Integrator Process would execute a python script (packaged as an exe using pyinstaller). The python script would log into business Central and execute the required rest API query and export the results to a CSV file for Planning Analytics to pick up. (I used the python library pandas to facilitate this).

Instead of extracting to CSV it would also be possible to write back to Planning Analytics via Rest API. 

Warning business Central rest API get requests are limited to 20,000 rows so you'll need to loop to the next get request to pick up the remaining results.

The standard API for business Central are also fairly limited, you'll likely need to write a query in business Central and publish the query as an API and use that instead to pull data from.

------------------------------
Simon Saul

Original Message:
Sent: Mon August 26, 2024 09:57 AM
From: Jennifer Hoffman
Subject: Connection to Microsoft Business Central

Hi,

We have a PA Cloud client who is looking to switch their ERP system to Microsoft Business Central.

Has any set up such a connection?  Did you need to write code to connect through the API or were you able to create a connection through Secure Gateway?

I appreciate any insight as this datasource is new to me :)

This sounds like a good use case for the new ExecuteHTTPRequest TI function in TM1 v12 (https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-executehttprequest).  ExecuteHTTPRequest can be used to make REST API calls that issue a SQL query (or other form or query) to data sources that support SQL over REST API.  ExecuteHTTPRequest can be used to pull data in CSV or JSON format to the TM1 databases in the prolog section of the TI process so it can be processed as a data source.   The advantage of this approach is that it avoids adding Python code or other services to access the data source.  It also avoids the need for management of ODBC drivers and DSNs outside of the Planning Analytics product.  

Note that TM1 v12 is currently only available in the Planning Analytics SaaS offering and as part of Cloud Pak for Data Planning Analytics. 

Hi, I don't believe Microsoft Business Central has an ODBC connector but it does have a rest API endpoint. Have done a couple of integrations where a Turbo Integrator Process would execute a python script (packaged as an exe using pyinstaller). The python script would log into business Central and execute the required rest API query and export the results to a CSV file for Planning Analytics to pick up. (I used the python library pandas to facilitate this).

Instead of extracting to CSV it would also be possible to write back to Planning Analytics via Rest API. 

Warning business Central rest API get requests are limited to 20,000 rows so you'll need to loop to the next get request to pick up the remaining results.

The standard API for business Central are also fairly limited, you'll likely need to write a query in business Central and publish the query as an API and use that instead to pull data from.

------------------------------
Simon Saul

Original Message:
Sent: Mon August 26, 2024 09:57 AM
From: Jennifer Hoffman
Subject: Connection to Microsoft Business Central

Hi,

We have a PA Cloud client who is looking to switch their ERP system to Microsoft Business Central.

Has any set up such a connection?  Did you need to write code to connect through the API or were you able to create a connection through Secure Gateway?

I appreciate any insight as this datasource is new to me :)

Indeed Stuart. This is a perfect use case for ExecuteHttpRequest, unfortunately the clients I'm working with are on v11 so have to resort to python or other tools for the time being.

This sounds like a good use case for the new ExecuteHTTPRequest TI function in TM1 v12 (https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-executehttprequest).  ExecuteHTTPRequest can be used to make REST API calls that issue a SQL query (or other form or query) to data sources that support SQL over REST API.  ExecuteHTTPRequest can be used to pull data in CSV or JSON format to the TM1 databases in the prolog section of the TI process so it can be processed as a data source.   The advantage of this approach is that it avoids adding Python code or other services to access the data source.  It also avoids the need for management of ODBC drivers and DSNs outside of the Planning Analytics product.  

Note that TM1 v12 is currently only available in the Planning Analytics SaaS offering and as part of Cloud Pak for Data Planning Analytics. 

Hi, I don't believe Microsoft Business Central has an ODBC connector but it does have a rest API endpoint. Have done a couple of integrations where a Turbo Integrator Process would execute a python script (packaged as an exe using pyinstaller). The python script would log into business Central and execute the required rest API query and export the results to a CSV file for Planning Analytics to pick up. (I used the python library pandas to facilitate this).

Instead of extracting to CSV it would also be possible to write back to Planning Analytics via Rest API. 

Warning business Central rest API get requests are limited to 20,000 rows so you'll need to loop to the next get request to pick up the remaining results.

The standard API for business Central are also fairly limited, you'll likely need to write a query in business Central and publish the query as an API and use that instead to pull data from.

------------------------------
Simon Saul

Original Message:
Sent: Mon August 26, 2024 09:57 AM
From: Jennifer Hoffman
Subject: Connection to Microsoft Business Central

Hi,

We have a PA Cloud client who is looking to switch their ERP system to Microsoft Business Central.

Has any set up such a connection?  Did you need to write code to connect through the API or were you able to create a connection through Secure Gateway?

I appreciate any insight as this datasource is new to me :)

Indeed Stuart. This is a perfect use case for ExecuteHttpRequest, unfortunately the clients I'm working with are on v11 so have to resort to python or other tools for the time being.

This sounds like a good use case for the new ExecuteHTTPRequest TI function in TM1 v12 (https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-executehttprequest).  ExecuteHTTPRequest can be used to make REST API calls that issue a SQL query (or other form or query) to data sources that support SQL over REST API.  ExecuteHTTPRequest can be used to pull data in CSV or JSON format to the TM1 databases in the prolog section of the TI process so it can be processed as a data source.   The advantage of this approach is that it avoids adding Python code or other services to access the data source.  It also avoids the need for management of ODBC drivers and DSNs outside of the Planning Analytics product.  

Note that TM1 v12 is currently only available in the Planning Analytics SaaS offering and as part of Cloud Pak for Data Planning Analytics. 

Hi, I don't believe Microsoft Business Central has an ODBC connector but it does have a rest API endpoint. Have done a couple of integrations where a Turbo Integrator Process would execute a python script (packaged as an exe using pyinstaller). The python script would log into business Central and execute the required rest API query and export the results to a CSV file for Planning Analytics to pick up. (I used the python library pandas to facilitate this).

Instead of extracting to CSV it would also be possible to write back to Planning Analytics via Rest API. 

Warning business Central rest API get requests are limited to 20,000 rows so you'll need to loop to the next get request to pick up the remaining results.

The standard API for business Central are also fairly limited, you'll likely need to write a query in business Central and publish the query as an API and use that instead to pull data from.

------------------------------
Simon Saul

Original Message:
Sent: Mon August 26, 2024 09:57 AM
From: Jennifer Hoffman
Subject: Connection to Microsoft Business Central

Hi,

We have a PA Cloud client who is looking to switch their ERP system to Microsoft Business Central.

Has any set up such a connection?  Did you need to write code to connect through the API or were you able to create a connection through Secure Gateway?

I appreciate any insight as this datasource is new to me :)

Indeed Stuart. This is a perfect use case for ExecuteHttpRequest, unfortunately the clients I'm working with are on v11 so have to resort to python or other tools for the time being.

This sounds like a good use case for the new ExecuteHTTPRequest TI function in TM1 v12 (https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-executehttprequest).  ExecuteHTTPRequest can be used to make REST API calls that issue a SQL query (or other form or query) to data sources that support SQL over REST API.  ExecuteHTTPRequest can be used to pull data in CSV or JSON format to the TM1 databases in the prolog section of the TI process so it can be processed as a data source.   The advantage of this approach is that it avoids adding Python code or other services to access the data source.  It also avoids the need for management of ODBC drivers and DSNs outside of the Planning Analytics product.  

Note that TM1 v12 is currently only available in the Planning Analytics SaaS offering and as part of Cloud Pak for Data Planning Analytics. 

Hi, I don't believe Microsoft Business Central has an ODBC connector but it does have a rest API endpoint. Have done a couple of integrations where a Turbo Integrator Process would execute a python script (packaged as an exe using pyinstaller). The python script would log into business Central and execute the required rest API query and export the results to a CSV file for Planning Analytics to pick up. (I used the python library pandas to facilitate this).

Instead of extracting to CSV it would also be possible to write back to Planning Analytics via Rest API. 

Warning business Central rest API get requests are limited to 20,000 rows so you'll need to loop to the next get request to pick up the remaining results.

The standard API for business Central are also fairly limited, you'll likely need to write a query in business Central and publish the query as an API and use that instead to pull data from.

------------------------------
Simon Saul

Original Message:
Sent: Mon August 26, 2024 09:57 AM
From: Jennifer Hoffman
Subject: Connection to Microsoft Business Central

Hi,

We have a PA Cloud client who is looking to switch their ERP system to Microsoft Business Central.

Has any set up such a connection?  Did you need to write code to connect through the API or were you able to create a connection through Secure Gateway?

I appreciate any insight as this datasource is new to me :)