Encora designed and implemented an Azure cloud-based scheduler for automating work scheduling for a client in the utility operations space. The client had all their work and crew data on an on-premises, single-source-of-truth Work Management System (WMS). The Auto Assignment (AS) application had to write back the scheduling algorithm output to the WMS system. The request was to connect these two heterogeneous systems.
This scheduler is a cloud-based web application that auto-assigns the crew to open jobs and calculates the prerequisites for job completion. The diagram below illustrates the high-level functionality of this AS application.
The client expressed concerns about the security of the REST API-based write operations in comparison to the read operations. Ensuring the system's security was a top priority.
Solution
Since APIs were off the table, an alternative approach was required to integrate these systems. The client was already using the MS BizTalk Server to integrate other external applications with WMS. The solution approach had to be on similar lines. The decision was to combine a few Azure resources: Azure SQL Database, Azure Logic Apps, and Azure Service Bus in conjunction with the client's MS BizTalk Server application.
Before we dive deeper into the technicalities of the solution, let's touch base on what these components are.
Azure SQL Database
A fully managed, cloud-based service provided by Azure, the Azure SQL Database is relational and runs as a PaaS (Platform as a Service) solution on the Azure cloud. It gives you all the features of the SQL Server engine and is quite easy to integrate with other Azure services like App Services, Logic Apps, Functions, etc. Overall, it provides you with scalable, secure, and managed relational database solutions in the cloud.
Azure Logic Apps
This is a cloud-based Platform-as-a-Service (PaaS) solution that can automate tasks, workflows, etc. It helps create and design automated workflows that can integrate services, systems, and applications. Azure Logic Apps are used in most organizations and B2B scenarios for developing highly scalable integration solutions. This integration platform has lots of built-in connectors to integrate multiple applications, data, and services quickly and efficiently.
Azure Service Bus
The Azure Service Bus is a fully managed enterprise message broker service from Microsoft Azure. It has message queues and publish-subscribe topics. Messages are exchanged between connecting parties in formats like JSON, XML, or plain text. Producers and consumers of these messages can work asynchronously. It also supports 1: n mapping with publishers and subscribers. Subscribers can select messages from a published message stream. Messages in queues are always ordered and timestamped on arrival. While queues are used for point-to-point communication, topics work in publish-subscribe scenarios. The Service Bus can fully integrate with other popular Azure services like Logic Apps, Functions, Event Grid, etc.
MS BizTalk
Enterprise integration software from Microsoft provides capabilities to integrate various systems, applications, and data sources regardless of their platform or technology. BizTalk has a message queue mechanism for exchanging information between systems. It provides transformation and mapping capabilities to convert data formats and schemas. It is extremely popular in B2B integration as it allows organizations/ trading partners to exchange electronic documents using standard protocols like EDI, AS2, JSON variants, etc. The BizTalk Rules Engine, another key component of BizTalk, provides a declarative approach to define and manage business rules, separating application code from business rules.
Detailed Solution
Now that we know what these components are, let us see how we used these components to solve the client's problem. A quick recap of the problem: We had to write data back from the scheduler application to the on-premises WMS system. Since REST APIs could not be used for updates to the WMS because of security constraints and since the client was already using MS BizTalk Server to integrate this WMS with other external applications, we had to think of a solution on similar lines.
Below is a high-level 'simplified' diagram of our solution.
-1.jpeg)
The scheduler reads data from ORDS (Oracle REST Data Services), runs the algorithm, and gives scheduler suggestions. The work manager comes to the application and validates all prerequisites and schedule details like work time, assigned crew, etc. Once the work manager approves the suggestion, the scheduler needs to write schedule data back to the source system WMS through MS BizTalk.
The scheduler application running on the Azure App service uses the Azure SQL database to store all scheduling data. The timer-triggered Azure Logic App runs every two minutes and calls SQL stored procedure to fetch data from tables. The client shared 'n' BizTalk XML formats with us. The XML format for each business case like 'Schedule Work', 'Create Prerequisites', 'Update Prerequisites', etc. For each of these 'n' business cases/ XML, we had to create '2n' logic Apps and '2n' service bus queues combinations of request and response Logic Apps and SB Queues for each business case/ XML.
Request flow: Scheduler Angular UI -> Scheduler .NET API -> Scheduler Azure SQL Database-> Request Logic App-> Request Service Bus Queue-> BizTalk Request Queue-> WMS
- The scheduler (.NET API written in C# & ASP.NET Core) runs scheduling algorithm and updates data in Azure SQL Database tables.
- The request logic app has a timer-based trigger and executes every few minutes.
- The request logic app runs the database-stored procedure and fetches the required data which we need to update in WMS.
- If data is available, we create XML as per the required template.
- We use liquid templates for generating XML files.
- Next, we place the created XML in the service bus queue.
- We track the request XML transaction ID and body in our database for mapping with the response.
Logic apps have different triggers — timer-based, HTTP-based, event-based, etc. If you are wondering why we chose a timer-based trigger, it is because we wanted to send data in batches at frequent intervals. Our request logic app executes the SQL stored procedure to fetch eligible records, create 'custom size' batches and then create a request XML for each record in each block.
Once the XML is ready, we send it to the Service Bus queue and update this transaction ID in our database by executing another stored procedure. So, you see how convenient it is to use Azure Logic Apps to do all these operations with minimal coding. The screenshot below gives you a high-level design view of our request logic app.
Once the request gets to WMS, it sends us an acknowledgment/ response. This is the response flow.
Response flow: WMS-> BizTalk Response Queue-> Response Service Bus Queue- > Response Logic App-> Scheduler SQL Database-> Scheduler .NET API -> Scheduler Angular UI
- The response logic app gets triggered when any new message comes in the response service bus queue.
- In this logic app, once we get a message from the queue, we first validate the response XML using Schema Template (XSD).
- Once the XML is successfully validated, then we parse the XML and convert it into a readable form. This mapping from XML nodes to different properties is easily configurable.
- After that we call database stored procedure and update the response in database tables.
The screenshots presented below will give you a high-level overview of our response logic apps.
You can even add notification steps in between this workflow. Azure Logic Apps has an inbuilt connector for SendGrid, Twilio, and other messaging platforms. Though we have not added any notifications to our workflow, we have a powerful UI (User Interface) application written in Angular. Eventually, end users get notified on screen with all status changes. However, if we have a back-channel workflow where we do not have any UI, we can send notifications via email, SMS, etc., to the parties concerned.
Challenges Faced
We faced some challenges while doing this integration. A few of them are listed below.
- Frequent Changes in BizTalk Request/ Response XML
The BizTalk server is owned and managed by the client. They shared the request/response XML templates with us. The request was to ensure that our shared messages are in the desired BizTalk XML formats. Our message requests frequently failed during development because of the invalid XML format. We decided to add an extra XML validation step in our logic app flow. We created an XSD (XML Schema Document) from the client-shared XML templates and validated all generated XML against the XSD file. - Artifacts Management: Store Liquid, Map, and Schema Files
Liquid templates were used for generating XMLs and schema files (XSD) for XML validation. The challenge was to store or link liquid and XSD files with logic apps. To solve this, we added an integration account with our logic apps and used it to store these files. The integration account is a powerful Azure tool that helps store and manage artifacts in B2B integrations.
3. Manual to Automated Deployments
Earlier, logic app deployments were manual. However, this manual process was time-consuming and had chances of inconsistencies across different environments — DEV, TEST, and Prod. Later, we decided to automate this deployment using pipelines (CI/CD approach).
In this approach, we created JSON XML schema templates for each logic app. These templates were dynamic as we parameterized them through Azure pipelines. We added one folder in our .NET solution and the Azure code repository, placing all logic apps, and JSON and Parameter JSON files in it. We then used these files in YAML build steps to ensure automated, environment-specific deployment.
Conclusion
We saw an interesting use case with Azure Logic Apps and Service Bus Queues, for integrating two enterprise systems. The objective was to integrate an Azure-hosted application with the client's on-premises system. We could not use REST APIs for writing data back to the client's single source of truth because of security constraints. Finally, we decided to use combinations of Azure Logic Apps and Service Bus Queues to send data back to their system.
Our request logic apps are timer-triggered and read data from the SQL database. It also creates BizTalk XMLs and then sends XMLs to request service bus queue. The client's managed BizTalk server reads XML from our queues and updates the data in the on-premises system. For response flow, the BizTalk server puts XMLs in our response service bus queues. Our response logic apps are thus queue-event triggered. The response logic app reads data from the queue and updates it into our SQL database.
There are several ways to integrate enterprise systems —REST APIs, Dell Boomi, Apache Kafka, Azure Logic Apps, etc., to name a few. Each of these has its own benefits and drawbacks. Considering the client's requirements and existing Azure cloud-based infrastructure, we decided to combine Azure Logic App and Service Bus Queues.
Acknowledgement
This piece was written by Aditya Chouhan and Jitendra Shah from Encora.
Author Bio
AUTHOR –1
⦁ Name: Aditya Chouhan
⦁ Position at Encora: Engineering Manager, Innovation Leader- Cloud Services
⦁ Education- M.S. in Management of Information Systems, B.E. in Computer Science
⦁ Experience - 13 Years
⦁ LinkedIn Profile: https://www.linkedin.com/in/aditya-chouhan-53793911/
AUTHOR –2
Name: Jitendra Shah
⦁ Position at Encora: Technical Lead
⦁ Education- M.Sc. in IT
⦁ Experience – 13 Years
⦁ LinkedIn Profile: https://www.linkedin.com/in/jeetshah06/
About Encora
Fast-growing tech companies partner with Encora to outsource product development and drive growth. Contact us to learn more about our software engineering capabilities.
