Microsoft D365 – Entity Explorer, Resources and Learnings

Recently working upon integrating Microsoft D365 platform to an existing data warehouse,  and the challenges it posed were just one after the another.

So to simplify any ones journey on a similar road, I aim to put this blog of resources and techniques to simplify the life easier and more productive for any one reading the same.

ERP is such a diverse and deeply integrated system into all aspects of business lines, that overall migration is a massive project and it encapsulates the reporting and data warehousing challenges of its own.

As I write blog now, the following are the means of data in-out methodologies available to one in the D365 universe

1. OData API : This is a real time data surfacing technique in-out of D365 to any external systems. This API is OAuth authenticated so one needs to the relevant Azure AD app registrations created with the necessary credentials
2. Export to Data Lake (Azure): This is Microsoft configured and developed components for D365 which in the backend enables the MSSQL Server CDC mechanism on the tables which allows them to replicate the data movement in them as CSV in the selected Azure data lake. There is a limit of tables that can be exported using this method (Currently 350 tables)
3. Azure Data Lake Export CSV to On-Prem DB, Custom Development: Now Microsoft has also released additional means to browse the data exported in the Azure data lake to make the developers life easy. Because the data set that is exported to data lake is in a custom format termed as CDM – Common Data Model which is nothing but JSON files illustrating the entity structures, and to interpret the same they have provided guiding fast track implementation assets (via Github).

   Leveraging the above we have extended the code base to build an application to Sync the Azure Data Lake files to our On-Prem SQL database which is fully automated.

   So now any one enabling the tables to be exported to Azure data lake:

   It directly starts appearing in our target On-Prem database with the regular data updates
   
   

   

   Note: The above is a custom C# Azure Function (Scheduled) we have developed and deployed in Azure cloud connected to On-Prem DB.

4. Excel Add-in: This is user frendly plugin aviable in Excel Add-in menu where the users can export the entities from the D365 and publish back also the data changes. This is a very useful tool for the business advanced users to manage the D365 meta data and customary reporting.
   A great article highlighting the use cases for excel add-in for D365 FO
   How to use Excel Add-in for D365 FO
5. Recurring Integrations: This is a customary tool available from Microsoft (via Github) in which the scheduled export/integration jobs which run on the cloud D365 the outputs are collected and imported in the On-Prem systems for integration and vice-versa
6. Custom Development: Now in all the above case of data integration, various methods provides us the means to tap into D365, but for the warehouse integration, one has to not just observer/study data entity by entity in isolations, but they have to look at the overall picture of data structure laid out to evaluate the right joins, and relationships between entities.In order to achieve the above we came up with the following OData, multi-threaded  driven application which imports the all available entities from the OData API calls and with the (top=1) entity we were able to extract the fields available for the each entity thus providing us with a wholistic view of the data structures available in D365In the above you can see the most important fields we obtain and persist are the
   
   DataCount: Row count for that entity
   FieldList: Multiple columns available for that entity
   
   The above greatly helped us to put the picture in place for ER mapping exercise.
7. Chrome Tools for Table/Entity Browser: To my much surprise chrome has got few add-ins which surface out the D365 list of tables and entities, this is close to we wanted but still it lacked the field list.
   

So from the above mentioned points and methods explained, I have tried to assimilate the multiple methods of interacting with D365 implementation of Cloud and its integration mechanism from the view point of any large scale integrations as points to consider.

Happy integrating, and look forward to share thoughts and experiences with readers.