Use cases of Microsoft Dynamics CRM Adapter in a S/4HANA Implementation project might sound commonplace especially when we mention them in the same breath as Salesforce and SugarCRM adapters. However, it wouldn’t be a stretch of imagination to call them a rarity. In this article, I will elucidate the scenario of implementing the MSD CRM adapter with BTP and cover the challenges I had encountered. At the end, I will wrap up with the solution that we ended up utilizing.
Adapter Details:
Microsoft Dynamics CRM Adapter can be used to connect To MS Dynamics CRM Online. It is included with the SAP Integration Suite license. Documentation is included in the ZIP file downloaded from SAP Software Download Center including the Installation and Configuration guide. Details on the features and where to download can be found in link below:
https://api.sap.com/package/MicrosoftDynamicsCRMAdapter/overview
Scenario Description:
The scenario here encompasses the extraction of the customer master data from MSD and sending to the HANA Database on BTP. The complexity is that the extract includes quite a few fields that don’t exist in S/4HANA Cloud.
One way to overcome the challenge would be creating them as custom fields in S/4HANA.However, there are too many of them and creating them as custom fields would soon exceed the limits of custom fields in S/4HANA. So we ended up using HANA Database tables on BTP for uploading the data using the Cloud Integration.
The standard Fiori apps could be extended to use the new fields and create new apps for specific cases.
In the architecture of Microsoft Dynamics 365 the data gets stored in multiple entities. For example, the customer entity stores the ID of the country where the customer is located. In order to get the country name, it is necessary to query the country entity along with the ID.
This meant the requirement was to bring all the information of scattered entities together in one place, to combine the multiple secondary entities to the main entities. One way would be querying the entities one by one and then combining the data in CPI – which would be highly cumbersome. FetchXML Query provided me with the perfect leeway. It allows bringing in the data combined and ready to be mapped to the database.
The processes will be elucidated in the scenarios below.
Adapter configuration and Eclipse set up:
The adapter and Eclipse Plug-in installation steps are described on the official documentation of the adapter in the folder that can be downloaded at SAP Launchpad.
Use the Dynamics CRM Eclipse Plug-in to generate XSDs representing the entities as shown on the image below:
The Challenge with the Integration:
The processes required multiple fields from Microsoft Dynamics that do not exist in S/4 HANA Cloud. Due to S/4 limitation regarding the custom fields quantity we created tables in the HANA Cloud on BTP to store the additional fields.
Pull data from multiple entities and merge them before sending it to S/4 HANA Cloud and HANA DB Tables.
Scenario 1 – The interface will run once to pull all the customers from Microsoft Dynamics Accounts Business Object and insert into customers table in SAP HANA for BTP.
Design Solution in Cloud Integration:
1 – Looping Process Call
Use the looping process call to pull the customers using pagination once this interface will handle a huge amount of data.
2 – Request Reply (MicrosoftDynamicsCRM)
On this step, configure the connection and the request details to get the customers from MSD.
The Credential Name and Client ID Alias need to be set at Overview -> Manage Security -> Security Material.
- Processing Mode – Single or Batch. Batch mode is not used on this scenario
- Operation – The operations supported by this adapter are Query, Get (by ID), Create, Update, Delete, Associate, Disassociate. This scenario uses Query because it is pulling multiple lines from the same B.O.
- Business Object – Indicate the business object that the data will be pulled. If you are not sure which B.O. to use, you can use the Eclipse Plug-in to look up business objects.
- Next Page Link – Inform the property which stores the link of the next request for pagination. This property will be handled on step 3.
- Query Type – Options available are Basic, Advanced or FetchXML. Basic is enough for this scenario, for more complex scenarios you should use Advance and inform OData Queries to filter the data or FetchXML Query.
- FetchXML is an interesting way to pull data from multiple entities, this type of query will be handled in the second scenario.
- FetchXML is an interesting way to pull data from multiple entities, this type of query will be handled in the second scenario.
- Filters – Add a simple filter to make sure the request won’t pull invalid customers.
3 – Content Modifier
As mentioned above, store the next link for the pagination request. In this case the page size is 500, the first request will bring the first 500 lines and return the next link to get the 501-1000.
4 – Message Mapping (MSD result to JDBC request)
Generate the response XSD using the Eclipse Plug-in, after finding the corresponding Business Object for Customers, select the tab ‘Response XSD’, Save it local and upload it to the integration design.
Upload and map the data to a JDBC request format like the example below:
<root>
<StatementName>
<dbTableName action="INSERT" table="TB_MSD_CUSTOMERS">
<access>
<CUSTOMERID>10055</CUSTOMERID>
<CUSTOMERNAME>INTEGRATION TEST</CUSTOMERNAME>
</access>
</dbTableName>
</StatementName>
</root>4 – Request Reply (JDBC)
- JDBC Data Source Alias – The database where the data will be sent. This needs to be configured at Overview -> Manage Security -> JDBC Material
Scenario 2 – The interface will run once to pull all the customers from Microsoft Dynamics and their related addresses using fetchXML to query and insert into customers table in SAP HANA for BTP.
This scenario adds the fields Country and State to the customer’s table.
Below is the data relationship model in MSD.
Use the Advanced Find in MSD to create the FetchXML Query for the data model above.
Use the functionality ‘Edit Columns’(1) to get just the relevant columns for this scenario, add a filter(2) to get just the customers that have Country and State and then download the FetchXML Query(3) that will be used in Cloud Integration.
Below is the XML downloaded
Important Note: The FetchXML query in the adapter is formatted differently. The text of the FetchXML cannot contain spaces. The Eclipse Plug-in has a feature that can help you convert your FetchXML query to a format that is accepted by the Microsoft Dynamics Adapter
Remove the attributes version, output-format and distinct from the tag ‘Fetch’ and change the alias of the link-entities. After these changes use the Eclipse Plug-in to Format the Query as expected by the adapter.
Design Solution in Cloud Integration:
This design is simpler than the other because FetchXML doesn’t support Pagination (the response does not provide the nextLink tag, but you still can use it to limit the number of lines), so all the steps related to pagination were removed.
1 – Request Reply (MicrosoftDynamicsCRM)
On the image below is the result of this query
The other steps are the same as they were in the previous scenario, I just added the mapping for Country and State.
