Dynamics 365 FinOps: Batch import automation with Azure Functions, Business Events and PowerBI

Design automation for real-time/synchronous interfaces in Dynamics 365 for Finance has its challenges but it’s quite often less complex than do the same for batch/asynchronous interfaces, for a couple reasons. On batch interfaces we need to manipulate files across servers, be aware of network traffic, quotas, operation timeouts, storage limits and costs, and so on.

While consuming OData endpoints are relatively simple – get a token, call the service, receive the response, with Data Management Framework APIs isn’t that straight forward and requires additional steps to set your project, upload/enqueue the file, execute, wait, and if necessary request execution status in loop until it’s finished, so we can send a response/acknowledgement to the source system.

There are two APIs available to automate the batch integrations using Data Management Framework, and these are they key differences:

Choosing the best API, patterns and tools depends of each scenario and can be confusing with so many options available.

In this post I’ll describe how to use Data Package API, Business Events and Azure Functions to build an event-based package import process automation.

Why Azure Functions over Logic Apps?

Both are great options and the choice depends of how much flexibility and control you want, versus how much code you’re willing to write. But a key difference made me choose Azure Functions for this particular task: its event-based triggers, while Logic Apps works with polling-based triggers.

For simple integration scenarios it does not make a big difference, however, as the complexity increases and we add in the picture multiple legacy systems generating data in a non-linear recurrence to be loaded near real-time, every execution counts as it can be quite costly and inefficient design you middleware to poll multiple sources in a pre-defined scheduling.

Another point to consider – as the communication between the middleware and Data Package API requires many calls in sequence, having control over each step and the possibility to apply different patterns – like throttling, circuit breaker, retry patterns, and others – is essential.

Solution overview

For the sake of simplicity, we’ll skip the data extraction and transformation steps and I’ll assume you have a DMF import project in place and a sample data package ready. Please note the import project category must be set to “Integration” – the reason is explained later in this post.

The package must be uploaded to a persistent storage to trigger the Function and start the import process. Azure Storage Blob works quite well for that purpose as it’s fully integrated with other Azure tools, offering redundancy (LRS, ZRS, GRS/RA-GRS), low latency and access via REST APIs.

Once the zip package is uploaded to Storage Blob (1), the Function app is triggered and a stream connection is opened to download the blob file (2), initiating the import process flow to Data Package API (3).

A sample function app containing the code to perform the operations mentioned above can be downloaded from the GitHub repository below (please note it’s a basic sample for learning purposes only):

https://github.com/ffilardi/fo-datapackageimport

The function is triggered when a new file (.zip) is uploaded to a storage account in a container named “package-import“. Update the local.settings.json file with the required parameters, and make sure these parameters are deployed as application settings variables when publishing to Azure.

Once the function is triggered, four calls are required to start the import process to FinOps. Here’s the sequence flow:

Additional calls may be made to request the job execution status update. That means polling-based recurring requests until the execution finishes. To avoid that we’ll adopt an event-based approach using the Business Events framework and a HTTP triggered function, inserting the acknowledgement data in a log table for reporting and management.

Acknowledgement events

The idea is similar to the export scenarios using DMF and Business Events, as explained in a previous blog post here. But in this case we’ll build events that are triggered when import jobs are finished.

Azure Table storage provides a NoSQL key-value store, handling massive semi-structured datasets. It’s a good fit for logging solution, providing a schemaless design, possibility to perform OData-based queries, easily consumed by many BI and reporting tools.

With a variety of out-of-the-box data connectors, for PowerBI is one of best choice to query, filter, visualize and interact with the data by connecting directly to Azure Storage Table to create reports and real-time dashboards.

Back to the process, once the import job is complete a Business Event notification is sent to Azure Function via HTTPS endpoint (4), containing execution status and other metadata. This information is then saved (5) into the Azure Storage Table. The data is then ready to be consumed by PowerBI (6).

In a nutshell, the custom business event listen for insert actions on table DMFDefinitionGroupExecutionHistory – where new records are created when an import project starts its execution and when it finishes. It will also filter the projects by category (Integration) and operation type (Import). The reason for that is to avoid catch events for manually imported files via user interfaces (projects using other categories).

The business event extension requires the implementation of 3 classes:

• Event handler class: define the trigger and filters to get the event/data.
• Event contract class: define the content for the notification message payload.
• Event class: the event itself, called by the handler class to initialize/populate the contract and apply any business logic (if/when required).

A sample implementation is available here:

https://github.com/ffilardi/D365FO-DMFImportSatusBusinessEvent

Just copy the files to your model, run a code build and once it’s done, rebuild the business event catalog in the Business events catalog form under System administration.

The new event must be activated using a HTTP endpoint. To learn how to do that just follow the instructions from my previous post here.

And finally, to receive and process the event, a HTTP triggered function endpoint receives the business event payload, extracts the relevant data and transforms into a document to be inserted into a Storage Table. A sample implementation to be used as a guideline is available as part of the same repository/project mentioned above – check here.

Reporting and management

The following fields are extracted from Business Event payload and written to the log table:

• Entity (PartitionKey)
• Event Id (RowKey)
• Business Event Id
• Project Name
• Project Description
• Execution Id
• Company (Legal Entity)
• Total Records
• Total Created Records
• Total Updated Records
• Total Error Records
• Operation Type
• Project Category
• Started Date & Time
• End Date & Time
• Status

Based on that information we can build dashboards and reports in PowerBI to monitor the import results, errors, record counts and so on.

• 

Wrapping up

There are many tools, patterns and ways to build an automation solution for batch integrations with FinOps. There’s no right or wrong approach, we must find the most appropriate for each case.

In a cloud-first world, leverage the scalability, availability, security and out-of-box features provided by SaaS and PaaS solutions, and adopting a serverless and event-based architecture in a pay-per-use model are key components to consider for any integration scenario, allowing us to focus on our business solutions instead of creating the software stack needed to implement it.