SAP BW and Dynamics 365 integration

Question

What is the best practice to build an integration between Dynamics 365 and SAP BW (dynamics data should be imported to SAP BW on every update/change event).

Thanks.

Answer 1

Question is quite generic but let me add my 2 cents. Someone with architecture level knowledge could add even more relevant answer.

I would prefer such integration between 2 systems using Azuer service bus with azure functions. Now why azure service bus? If in case at some data cases integration fails you have error messages/records which could be analyzed and retried for integration.

I would also propose webhook with azure functions. Here we move integration outside crm and avoid 2 minutes times out from crm.

I would also propose power automate. But integration logic could get complicated here and power automate might consume more API calls than needed.

There can be azure data factory but I have limited knowledge about it.

You could also look into 3rd party softwares like kingswaysoft and scribe which are specifically meant to perform integration.

My inclination is towards azure service bus with azure functions.

Answer 2

As a first step, in order to work with Dynamics 365 data, we have dedicated components for connecting, reading and writing to your instance, which you can find as a part of our SSIS Integration Toolkit for Microsoft Dynamics 365.

Since we do not have dedicated components for SAP BW, if the API offers a Rest API, then you could use our HTTP Connection Manager to build a Restful connection with the API referring to the documentation, and then use our JSON components to read or write data to it. You can check out the Online Help Manuals for each of the above-mentioned components.

As for incremental loads, we have our Integration Gateway solution that offers real-time integration capabilities using webhooks. Webhook is a web callback URL that is generally triggered by an event or action in the system and delivers data to the target when it happens. To use this, you would need to obtain, register and configure webhooks for your specific instances. The configuration would depend on how SAP BW authentication works, and depending on that you may try and use the custom webhook option that we have to configure and set it up. We have a blog post that shows how this is done between HubSpot and Dynamics 365. A similar approach can be used for SAP BW.

Another way to perform an incremental load is by designing the package within SSIS in order to get the last run time and then fetch the records as per the timestamp and create a bidirectional integration. With Dynamics 365, if you meant CDS/CRM, then we have options to use the change tracking feature in CRM and get Entity Changes. We have a blog post on that as well with a sample package.

You would need to use the above-mentioned JSON components to create a bidirectional sync logic between SAP BW and Dynamics 365. Then you can deploy the packages to SSISDB catalog on your integration server, and schedule it using a SQL Server agent job. Schedule it to run at as close intervals as possible, so that all changes (inserts, updates, deletions) are captured and fully synced to both systems. You can also take a look at some other general incremental load blog posts we have.

• Incremental Load in SSIS Made Easy
• Incremental Load in SSIS with Diff Detector