There are plenty of options when it comes to choosing how to integrate with D365 F&O. But rather like a extensive menu at a restaurant, too much choice can make it difficult to find the right solution for the task at hand!
The purpose of this post is to present an overview of the options available and help direct your decision-making process or, even if you’re not at the level yet to make these sorts of decisions, hopefully it helps you to begin to understand the art of the possible.
At-a-glance comparison
Our initial comparison chart shows our primary integration patterns and helps to highlight what each one is suitable for:
Key:
- Real-time: Indicates whether the integration pattern supports real-time data exchange.
- Batch: Indicates whether the integration pattern supports batch data exchange.
- Complex ETLETL (Extract, Transform, Load) is a process that extracts data from sources, transforms it for analysis, and loads it into a database.: Indicates whether the integration pattern supports complex data transformations.
- High Volume: Indicates whether the integration pattern is suitable for high volume data exchange.
A bit more detail
An uninformed eye could be tempted by the Custom service line in the first table, with its four gleaming ticks standing out from the competition!
Let’s go back to basics and describe each method in a bit more detail:
- Power Platform Integration: Using Microsoft Power Platform allows the creation of custom apps and workflows, that can connect data across the Microsoft eco-system and beyond, using either one of 1,000+ standard connectors, or a custom build.
- ODataOpen Data Protocol is an open protocol that allows the creation and consumption of queryable and interoperable Web service APIs in a standard way.: The Open Data Protocol (ODataOpen Data Protocol is an open protocol that allows the creation and consumption of queryable and interoperable Web service APIs in a standard way.) serves as a standardised way to expose and consume data via RESTful APIsAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data.. It allows developers to query, filter, and manipulate data using familiar HTTP methods, to facilitate data exchange with external applications and services.
- Batch Data APIAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data.: This APIAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data. groups records into batches, instead of handling individual records one by one, thus improving performance and reducing overhead, and efficiently transferring bulk data without overwhelming resources. It also allows asynchronous processing, making it suitable for scenarios like scheduled data imports or exports.
- Data Management Package: Data management packages encapsulate common import and export tasks, ensuring consistency and adherence to business rules, whether migrating data, updating records, or integrating with other applications. They provide a structured approach, promoting data governance and reducing the risk of errors.
- Custom Service: Sometimes, none of the out-of-the-box solutions cut it and custom APIAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data. services need to be developed, offering complete flexibility that align align precisely with requirements. Inevitably, this comes at a much higher cost of design, implementation, and maintenance.
- Excel Integration: Excel remains a ubiquitous tool for data manipulation and reporting (as any CIO battling shadow IT is well aware!). However, D365 F&O allows seamless integration with Excel spreadsheets, allowing users to import/export data, perform calculations, and create insightful reports – all within the more familiar Excel environment.
- Consumption of External Web Services: D365 F&O can extend its reach by consuming external web services, tapping into third-party APIsAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data. such as real-time currency exchange rates, address validation, or payment gateways.
The final showdown
With our new perspective, let’s take a deeper look at the for and against of each method, so here is a more detailed comparison:
| Integration Method | Pros | Cons |
|---|---|---|
| Power Platform Integration | Seamless integration with Microsoft’s Power Platform which includes Power Apps, Power AutomatePower Automate is Microsoft’s service for creating automated workflows between apps, streamlining repetitive tasks., and Power BIPower BI is a cloud-based suite of business intelligence and analytics tools that lets anyone connect to, visualise, and analyse data with greater speed, efficiency, and understanding.. Enables rapid development and deployment of apps, flows, and reports without the need for extensive coding. | Limited to the functionalities and connectors available within the Power Platform. May require additional licensing. Not suitable for large volumes of data. |
| ODataOpen Data Protocol is an open protocol that allows the creation and consumption of queryable and interoperable Web service APIs in a standard way. (Open Data Protocol) | Enables real-time integrations, suitable for scenarios requiring immediate data reflection. Supports standard CRUD (Create, Read, Update, Delete) operations, making it straightforward for basic data operations. | Not ideal for handling large data volumes. Discouraged for use with Power BIPower BI is a cloud-based suite of business intelligence and analytics tools that lets anyone connect to, visualise, and analyse data with greater speed, efficiency, and understanding. reports in favour of using the Entity Store. |
| Batch Data APIAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data. | Suitable for handling large data volumes by processing data in batches. Asynchronous processing allows for scheduling and managing data transfers efficiently. | Not suited for real-time data integration requirements. May require additional setup and configuration for batch jobs. |
| Data Management Package REST APIAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data. | Enables on-premise deployments, providing a solution for environments not fully migrated to the cloud. Supports batch processing of data, aiding in managing large data transfers. | Limited to the functionalities provided by the Data Management framework. May require additional setup and expertise to utilize effectively. |
| Custom Service | Offers a high degree of customisation and control over the integration process. Can be tailored to meet complex or unique business requirements. | Requires development expertise and may lengthen the integration timeline. Maintenance and support may be more demanding as compared to standard integration methods. |
| Excel Integration | Familiar interface for users, enabling data import/export using Excel. Supports data validation and basic data transformation within Excel before importing into D365 FO. | May not be suitable for complex data integration scenarios. Dependent on Excel’s capabilities and may require additional setup for automated integrations. |
| Consumption of External Web Services | Allows for integration with a wide range of external services and APIsAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data.. Provides flexibility in connecting with various third-party platforms. | May require custom development and additional security considerations. The ease of integration may vary depending on the external service’s compatibility and APIAn Application Programming Interface is a set of rules enabling two or more applications to communicate and exchange data. design. |
Conclusion
There’s a good reason for so many integration methods in D365 F&O. Some integrations require speed, or ease of user adaption. Others need to move large volumes of data, or perhaps need to reflect in (near) real time.
When architecting your solution, be sure to give due consideration to what you’re trying to achieve – and definitely don’t try to use one method for all of your interfaces!
F&O integration should be always done using DMF Export /Import project. OData can be used to query F&O ( not high frequency) but it is not for extensive real time integration use case.
Batch Data API and DMF have similar feature. Batch API have scheduling inside F&O and DMF can be triggered from Outside F&O ( which is preferred as noone want to change F&O settings post golive).
Custom services are good but expensive. Do we really need those ? Are they going to provide some real £ benefit in business process. We should have some cost benefit analysis before build custom services.
F&O consuming external API directly will be introduce tight coupling, and potential break point in case third party API is not working or changed. There should always be Azure Integration Services layer on top of F&O to secure and decouple F&O from outside world.
Thanks for your thoughts, Tarun, it’s genuinely appreciated. That’s a great point regarding AIS layers for third party APIs. Pretty bold to suggest DMF for all integrations though! I think it’s really important to consider the scale of any integration, as well as budget, volume, frequency, etc. before going straight for the bug guns; there are plenty of low-volume functional use cases that can be easily implemented with Power Platform or managed semi-automatically via Excel. I think you make a similar point (in the other direction) against custom services.
AIS-DMF based integration should be desired state but other solutions can be used based on business requirements and cost.