Monolithic applications are everywhere. Many companies can’t function today without them. Some are the products a company sells, while others are the legacy (15+ years old) “off-the-shelf” applications that are the heart and soul of the enterprise (e.g. ERPs, EHRs, CRMs). 

Most monoliths are homegrown “line-of-business” applications (LOBA). They house all the different problem domains that comprise the company’s business model. They have tailored workflows specifically for the company’s processes. They are the company’s secret sauce and special spices. The LOBAs have allowed for growth from a startup company into a multimillion-dollar enterprise.

The Birth of Monolithic Applications

At some point, successful, evolving companies realize that the organic business processes they have grown are inefficient and take too long. Take for example, a company the configures, prices, and quotes products. Their products may be incredibly complicated. Their CPQ process may take days. Why? Because of the sheer number of parts that make up the product, the rules around compatibility, the variations of cost for each part, regulations, and in the case of healthcare, validating if prices will be paid by payers (insurance companies, Medicare, Medicaid, etc). Sometimes, these problems can be handled by ERPs and CRMs, but often times, these applications are too generic to meet the needs of the business.

To address this, many companies will choose to build a “line-of-business” application (LOBA) precisely tailored to address the problems and fill in the gaps for their ERP. They gather subject matter experts, department heads, and information technology resources to build the LOBA fast and within a budget. 

Usually, the initial requirements are to address a single part of the overall business process. This is done to get a “big win” and address a single area in the process of most pain. This works out well because they are starting from scratch, and it’s green field development. Most developers nowadays would build a simple “client-server” application because it’s fast to hit the ground running, easy to test locally, and trivial deployment and development iterations can happen fast. Most of the time, the very first version of the LOBA comes fast, addresses a single need, and is successful. The monolith is born.

The LOBA is a big hit with management. Quickly, there are plans to add more and more to the LOBA. In the above example, “configure, price and quote” (CPQ), the company decides to stuff even more of the business process domains into the LOBA. The company adds domain aspects from Order Management, Product Catalog Management, Inventory Management, and Delivery Management. Slowly, the LOBA development process becomes a tractor pull.

As more is added, moving the LOBA to the next version takes longer and costs more. The big wins are no more. The LOBA has large amounts of “code debt” and complexities. A change in one problem domain breaks other parts of the application. Often, a hero developer emerges, the single human who understands the LOBA. The company promotes the hero developer, who adds to their team to help maintain the monolith.

Executives start to grow concerned because the IT cost of the LOBA is high. They also hear from department heads about the problems: downtimes, performance problems, errors in the data, and the big one, “We need more staff to do our job.” Also, they find it hard to make strategic decisions because getting an estimate on required changes to the LOBA is harder. Executives quickly realize it’s time to make a significant investment in the company to take it to the next level. So begins the “digital transformation project” journey. The question arises during the early phases of the project. How do we upgrade the LOBA and do it in a way that helps us get as much ROI from it?

Part 2 of this blog post will address how companies can evolve their monolith line of business applications during their digital transformation projects. I’ll discuss how companies can break them apart along business problem domains, moving them out into separate micro-services. Finally, I’ll touch on how they can use Microsoft Azure technologies and enterprise design patterns to take a measured approach versus a Big Bang approach.

But then, reality sets in when you find out what the end result really is–an Excel document. You may think, “Well, that’s not really that fun,” but I had a lot of fun designing and building this feature. So, let’s walk through how it went end-to-end and some reporting with Azure functionality I was able to implement!

Requirements Of This Build

As with everything we build, there is always a set of requirements that must be met. In our case, it was a pretty simple set:

• The Report being created targets Work Load for the system.
• The result is an Excel document, which must contain defined columns.
• Users need a way to generate the report.
• Users need a way to save configured properties they used to generate the report so they can quickly re-run it later.

So far, straightforward. But let’s view it from a higher level and see how these Azure features need to fit in.

Architectural Guidelines

From the business perspective, the end result is very simple: “We need this,” but from the development perspective, there are quite a few layers to account for. For this particular project we have the following assumptions/limitations:

• Uses Microsoft Azure.
• Fits into a Microservice Architecture.
• Reports may contain large amounts of data.
• Currently doesn’t leverage a Data Warehouse.
• Minimal increase to the project’s administrative footprint.
• The potential for additional reports down the road.

Given the above, I dug into the Azure toolbox to develop the best approach.

The Approach

So, where to begin? First and foremost, I diagramed out what parts and pieces needed to be involved with the Reporting Feature.

Reporting UI

To get the report kicked off, we needed a set of pages to support the new functionality. In our existing Angular SPA, we would introduce the following views:

• A grid of saved reports with CRUD operations.
• A grid of report jobs to act as an audit table.
• A create/edit report form.

API Management Service

The existing Angular SPA accesses all of its APIs via an Azure Management Service. The Service is a great way to provide access to public-facing APIs while giving you control over requests made to them via policies. Here we can control validating who’s accessing our APIs (typically through JWT), caching, usage quotes and rate limits.

Report/Report Job Controller

With our existing microservice architecture, we already had a good home for these new endpoints. In one of our existing APIs, we added a new Report and Report Job Controller that would be responsible for:

• Standard CRUD operations.
• Generating the report.

The Report Job’s generate report endpoint would ultimately handle sending requests via Flurl to a new Azure Function to generate the Report.

Reporting with Azure Functions

The Azure Function would be the brains of the operation. Accessed via an HttpTrigger, we would POST a configuration model, and the Function would operate asynchronously. From there, the Function would need to communicate with any number of APIs to aggregate data, then generate and email the report.

To be able to email the resulting report, we can take advantage of Twilio’s SendGrid integration Azure Functions and Azure have.

So why did this approach work well for us?

How It Fits In

From diagraming how this feature could be built, you can see quickly how well this approach satisfied the business requirements and would seamlessly fit our current architecture.

• Users had a dedicated area to save and run reports and view the state of those reports.
• Users would not be sitting there waiting for the request to be completed since the Azure Function was running the report generation asynchronously. This also ensured the Azure Function didn’t timeout since it used an HttpTrigger.
• We leveraged an existing API and Database.
  • The advantage here is that we didn’t introduce new services that would require extra ARM template management or CI/CD management.
• We leverage an Azure Function to be the reporting brains.
  • Can hook our APIs in.
  • Can create strategies to generate different reports, allowing flexibility for enhancements in the future.
  • Can take advantage of SendGrind integration to email the reports.
  • Can create an ARM template to handle creating/updating the Function in a target environment.

With the plan laid out, let’s take a look at how easy it was to integrate SendGrid with the Reporting Azure Function.

SendGrid Integration

We include the SendGrid binding in our Azure Function to begin the integration.

 [FunctionName("CreateSystemReport")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest req,
            [SendGrid] IAsyncCollector<SendGridMessage> messageCollector,
            ILogger log, ExecutionContext executionContext)
        {
            log.LogInformation("Request for Report generation received.");

            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            log.LogDebug($"Event Message: {requestBody}");
            
            var context = new ReportProcessorContext(requestBody, messageCollector, log);
            context.ProcessRequest();

            return new NoContentResult();
              
        }

The SendGrid binding does need one piece of information to connect properly, and that’s its API key. One way to do that is by specifying the ApiKey name as a property in the binding.

[SendGrid(ApiKey = "CustomSendGridKeyAppSettingName")] IAsyncCollector<SendGridMessage> messageCollector

Alternatively, you can omit the ApiKey, which will default to looking in your settings file for a property named AzureWebJobsSendGridApiKey.

So, where do you get this key?

Setting up SendGrid in Azure

To acquire your API key for SendGrid, you must first create a SendGrid user account in Azure. This is a very straightforward process and is effectively a sign-up form.

When you’ve completed creating the account, you’ll see that there’s now a new service with a type of SendGrid Account.

Clicking on it will take you to the service overview page in Azure where you can click the Manage button to access the SendGrid portal.

SendGrid will walk you through creating an API key and testing it, but in case that isn’t the experience you’re presented with, you can create the keys yourself. Under Settings in the navigation menu, you’ll see an API Keys item.

This will list any keys you’ve created so far. For me, I’ve already created a key so you see my existing development one listed. If you click the Create API Key button in the top right, you’ll be presented with a very simple form.

After you fill out the form, you’ll be presented with your API Key. This is the key you can use to integrate your Azure Function with SendGrid.

Sending Emails with Attachments

Now that we have an API Key and we’ve connected SendGrid to our Azure Function, we can send emails. In our case, we want to send an email with an attachment, so let’s see how we did just that.

If you remember from our example above, our binding brought into the Function the IAsyncCollector<SendGridMessage> messageCollector.

 [SendGrid] IAsyncCollector<SendGridMessage> messageCollector

To be able to send an actual email, we just need to create a SendGridMessage object, pass it to your messageCollector and off it goes.

//Create message
var message = new SendGridMessage();
message.AddTo(_request.Recipient);
message.AddContent("text/html", "The Work Load report is attached.");
message.SetFrom(new EmailAddress("notreal@fake.email");
message.SetSubject(baseName);

//Send the message
messageCollector.AddAsync(message);

Super easy. So, how do we add an attachment to the message? Ultimately, we need to send the report along with the email. To do that, we create an appropriately named Attachment object that is handed to the message.

//Create the Workbook of the Report
....

//Save it to a stream  
MemoryStream ms = new MemoryStream();
workBook.SaveAs(ms);

//Create the attachment                    
var reportAttachment = new Attachment()
{
   Content = Convert.ToBase64String(ms.ToArray()),
   Type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
   Filename = "Report.xlsx",
   Disposition = "inline",
   ContentId = "Report"
};
                  
//Create message
var message = new SendGridMessage();
message.AddTo(_request.Recipient);
message.AddContent("text/html", "The Work Load report is attached.");
message.SetFrom(new EmailAddress("notreal@fake.email");
message.SetSubject(baseName);

//Add the attachment to the message
message.AddAttachments(new List<Attachment>() { reportAttachment });

//Send the message
messageCollector.AddAsync(message);

Once again, super simple.

Conclusion

It was amazing to me how simple the integration between Azure Functions and SendGrid turned out to be. The integration’s simplicity allowed me to focus on more important things related to business needs vs. an extensive implementation. Not to mention, SendGrid is free for up to 25,000 emails and scales for cheap beyond that.

I’m a happy developer with how this turned out, given the requirements and limitations. As always, I’d love to hear about your experiences with Azure Functions, SendGrid, or just technology in general.

About Rōnin Consulting – Rōnin Consulting provides software engineering and systems integration services for healthcare, financial services, distribution, technology, and other business lines. Services include custom software development and architecture, cloud and hybrid implementations, business analysis, data analysis, and project management for a range of clients from the Fortune 500 to rapidly evolving startups. For more information, please contact us today.

Revising Our Approach

For a few years, the approach we used was relatively bare metal. That is, we leveraged Azure Functions and Web Jobs to connect to data sources, transform the data with custom code, and ultimately push it to a target location. It got the job done for sure, but as you can imagine there is fair amount of boiler plate code. Additionally, the monitoring and scaling implementations were different for each solution. 

Today, every ETL discussion starts with Azure Data Factory. While we had to pass on using early versions of ADF in favor of the bare metal process to really get our work done, that’s not the case anymore. ADF has a ton of ways to ingest data, it scales well, and Data Flow offers a ton of transformation options without writing any custom code.

Our High-Level Decision Guide

Microsoft positions ADF specifically as an Azure service to manage ETL and other integrations at big data scale. While there are many ways to employ ADF for the solution, we’ve specifically found the following questions and answers most useful as our guide:

• If we only need to perform extraction and loading of data (for example making data from a legacy system available in the cloud), ADF’s basic pipeline activities are sufficient.
• If we also need transformations, we’ll start out using ADF’s Data Flow features. We have found that the majority of transformations that we need (joins, unions, derivations, pivots, aggregates, etc) can be handled with the Data Flow user interface.
• If the transformations involve some edge case scenarios, are hard to visualize in a UI, or there is just a comfort level developing it as code, Azure Databricks (ADB) integration can be used to perform these transformation.

Basic Pipeline Activities Approach

The basic workflow in an Azure Data Factory is called a pipeline. A pipeline is an organization of activities (data movement, row iteration, conditionals, basic filtering, etc) against source and target data sets. Directly, it offers little in the way of transformation activities, though you can hook it to Azure Functions or Azure Databricks (see Azure Databricks Approach below) for more advanced cases.

Below is an example basic pipeline I created very quickly using just a few standard activities. This pipeline simply reads an Employee CSV dataset from Azure BLOB storage, filters the records to only those of new employees, then loops over each row and calls a stored procedure to insert each employee record into a SQL Server database table. The resulting output in the bottom frame is from a debug session.

For more information on pipelines and available activities, check out https://docs.microsoft.com/en-us/azure/data-factory/concepts-pipelines-activities.

Data Flow Approach

A Data Flow is a visually designed data transformation for use in Azure Data Factory. The Data Flow is designed in ADF, then invoked during a pipeline using a Data Flow Activity. The transformations offered here offer a lot of power and configuration options through an easy to follow interface. Joining and splitting data sets, cleaning data, deriving new columns, filtering and sorting results, and running expression functions on row data are some of the possibilities with Data Flow. All of it is done through simple user interface controls.

Below is an example mapping data flow I created to show just a few of the transformation components that can be used. of the very quickly. This data flow reads HR employee data, contractor data, and billing info from three different systems. It performs some filtering and new column generation, then combines all of these results. Finally, it sorts the results and drops it to a CSV file.

For more information on Data Flows, check out https://docs.microsoft.com/en-us/azure/data-factory/concepts-data-flow-overview.

Azure Databricks Approach

There are times when it makes sense to simply write code to perform a data transformation. For example:

• There’s a weird edge case that the data flow user interface can’t accommodate.
• There’s going to be a high degree of refactoring (change) needed over time and the data flows will be large. It would be much easier / faster to tweak the code than to try to re-write large data flows.
• The source data is already exported as enormous amounts of unstructured data into Azure Data Lakes Storage, the file system natively integrated into Azure Databricks.

In these cases, Azure Data Factory pipelines can invoke notebooks in Azure Databricks using a Databricks Notebook activity. Notebooks define Scala, Python, SQL, or Java code to manipulate and query large volumes of data (terabytes) on its specialized Azure Data Lake Storage file system.

In the below example, I created a simple Databricks notebook to read two CSV files that have been dropped into Azure Data Lake Storage. These could have just as easily been Excel, JSON, parquet, or some other file format as long as there is an extension to read them into data frames. This example takes employee and HR files, joins the rows, computes a PTO Remaining column, orders the results, then stores the data back out as a new CSV file. Azure Data Factory would have a pipeline configured with a Databricks Notebook activity to call this notebook, passing the two CSV file names.

Note how you can seamlessly switch between languages in a notebook. This one is a Scala based notebook that switches to SQL midway through. The environment is also very interactive for debugging against large amounts of data, a nice feature since the ADF user interface can only show limited amounts of data in its debug sessions.

Interestingly, ADF’s data flows are implemented as generated Scala code running in its own managed Databricks cluster. This all happens behind the scenes, but it explains why it’s actually hard to come up with everyday use cases where data flows aren’t sufficient. For more information on transformations with ADB, check out https://docs.microsoft.com/en-us/azure/azure-databricks/databricks-extract-load-sql-data-warehouse.

Parting Thoughts

As awesome as ADF is, it is true that it’s not always the be-all and end-all for ETL. There are still times when ADF is only part of the solution. For example:

• The server infrastructure hosting an on-premise or alternate cloud database from which ADF needs to pull can’t host the integration runtime (a requirement for ADF to reach the data). In this case, we may have to design and build interesting ways to get the data out and into Azure, accessible to ADF
• Though accessible by network to ADF, the source data is contained in a format ADF can’t read. In this case, we may have to build or leverage 3^rd party software to extract the data into a digestible format for ADF
• The source data’s schema is so bonkers, Azure Databricks is necessary to pull off the transformation. However, the client may be unwilling to pay the heft ongoing cost of ADB, or they may not be comfortable supporting it down the road and would rather see a more traditional C# or T-SQL coded solution. In this case, we may fall back to a bare metal approach.

Like most software projects, one-size never fits all. But we highly recommend you give ADF a strong look on your next ETL adventure. We’d love to help you with it!

About Rōnin Consulting – Rōnin Consulting provides software engineering and systems integration services for healthcare, financial services, distribution, technology, and other business lines. Services include custom software development and architecture, cloud and hybrid implementations, business analysis, data analysis, and project management for a range of clients from the Fortune 500 to rapidly evolving startups. For more information, please contact us today.

Mapping API Data

For this particular project, I needed to receive a large web service API payload (a SalesForce event notification with an XML payload), do some interesting internal work (audits, fire off messages to an Azure Service Bus topic, etc.), and finally deliver the payload to another 3^rd party application. Since the incoming payload (XML) would be different than the payload accepted by the 3^rd party application’s web service API (JSON), payload transformation also needed to occur.

There are many ways to pull off this implementation in Azure (HTTP triggered Azure Function App, Azure App Service API, Azure Logic App, etc.), but for this project, we chose Azure Logic Apps. Using Logic Apps would allow the client to make minor tweaks to the process flow without needing us to come back and write additional C# code, which is a bit of a holy grail for many small IT shops requesting project help.

However, the approach did present a problem regarding the payload transformation. For small/simple payloads, it makes sense to use the Logic App JSON creation actions and built-in function expressions, but it gets messy as the payload grows in complexity. Writing some C# code to read a large XML or JSON payload and creating another is relatively simple. This could be placed in an Azure Function App and called from our Logic App, but I’d arrive at a mapping mechanism that would not be tweakable by our client later on.

We used Azure Logic Apps for a previous client to produce EDI flat files. For that implementation, an Azure Integration Account was used to hold maps. These maps described transforming an XML payload to an EDI flat file. That same exact approach would be a bit heavy as it involves navigating custom source XML schemas, using the Microsoft BizTalk mapper (heavy), and in some cases some custom XSLT or BizTalk scriptoid development. 

Still, something like this approach that was lighter weight and integrated with Logic Apps would be a solid fit.

Liquid is Solid

After some research, I stumbled upon the perfect solution for this client: Liquid templates.

Azure Logic Apps have a series of actions for data transformation, pulling the transformation definitions, or maps, from an Azure Integration Account. In addition to BizTalk maps, Integration Accounts natively support Liquid templates (https://shopify.github.io/liquid/). Liquid is an open-source template language that works a bit like XSLT. Using Liquid tags (control flow) and filters (output manipulation), intermediate and some complex JSON and XML transformations can be achieved from an Azure Logic App by editing a simple text file and loading it into an Azure Integration Account.

For example, suppose I have the following incoming XML payload from web service API (e.g. Sales Force):

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <soapenv:Body>
  <notifications xmlns="http://soap.sforce.com/2005/09/outbound">
   <OrganizationId>00DL0000005xwjAMAQ</OrganizationId>
   <ActionId>04kL00000000BCFIA2</ActionId>
   <SessionId>... session id ...</SessionId>
   <EnterpriseUrl>... url ...</EnterpriseUrl>
   <PartnerUrl>... url ...</PartnerUrl>
   <Notification>
    <Id>12345</Id>
    <sObject xsi:type="sf:Beneficiary__c" xmlns:sf="urn:sobject.enterprise.soap.sforce.com">
     <sf:Id>67890</sf:Id>
     <sf:First_Name__c>Susan</sf:First_Name__c>
     <sf:Last_Name__c>Smith</sf:Last_Name __c>
     <sf:Address_City__c>Atlanta</sf:Address_City__c>
     <sf:Address_State__c>GA</sf:Address_State__c>
     <sf:Phone__c>(678) 867-5309</sf:Phone__c>
     <sf:Primary__c>Yes</sf:Primary__c>
     <sf:Contingent__c>No</sf:Contingent__c>
     <sf:DOB__c>2012-05-11T00:00:00.000Z</sf:DOB__c>
     <sf:Allocation__c>80%</sf:Allocation__c>
    </sObject>
   </Notification>
  </notifications>
 </soapenv:Body>
</soapenv:Envelope>

Further, suppose I need to map this to an outgoing JSON payload for a target web service API as follows:

{
  "Id": 67890,
  "First": "Susan",
  "Last": "Smith",
  "City": "Atlanta",
  "State": "GA",
  "Phone": "6788675309",
  "Status": "CNTG",
  "DoB": "05/11/2012",
  "Allocation": 80.0
}

Notice a couple of wrinkles in this sample mapping:

• I need to strip some formatting from a phone number
• I need to collapse Primary and Contingent elements to a single JSON value
• I need to re-format a date to mm/dd/yyyy

Here’s a Liquid template I would write to get this transformation done:

{
  "Id": {{content.Envelope.Body.notifications.Notification.sObject.Id}},
  "First": "{{content.Envelope.Body.notifications.Notification.sObject.First_Name__c}}",
  "Last": "{{content.Envelope.Body.notifications.Notification.sObject.Last_Name__c}}",
  "City": "{{content.Envelope.Body.notifications.Notification.sObject.Address_City__c}}",
  "State": "{{content.Envelope.Body.notifications.Notification.sObject.Address_State__c}}",

{%- capture formattedPhone -%}
  {{content.Envelope.Body.notifications.Notification.sObject.Phone__c | Strip | Remove: "("  
      | Remove: ")" | Remove: "-" | Remove: "." | Remove: " "}}
{%- endcapture -%}
  "Phone": "{{formattedPhone}}",

{%- if content.Envelope.Body.notifications.Notification.sObject.Primary__c == "Yes" -%}
  "Status": "PRIM",
{%- else -%}
  "Status": "CNTG",
{%- endif -%}

{%- capture extractedYear -%}
  {{content.Envelope.Body.notifications.Notification.sObject.DOB__c | Slice: 0, 4 }}
{%- endcapture -%}
{%- capture extractedMonth -%}
  {{content.Envelope.Body.notifications.Notification.sObject.DOB__c | Slice: 5, 2 }}
{%- endcapture -%}
{%- capture extractedDay -%}
  {{content.Envelope.Body.notifications.Notification.sObject.DOB__c | Slice: 8, 2 }}
{%- endcapture -%}
  "DoB": "{{extractedMonth}}/{{extractedDay}}/{{extractedYear}}",

  "Allocation": {{content.Envelope.Body.notifications.Notification.sObject.Allocation__c 
      | Remove: "%"}}
}

This is just a simple text file I typed up in Notepad++ and then uploaded into an Azure Integration Account:

The Map Type field is key. Instead of using the output XSLT file from BizTalk mapper, I choose Liquid as the type.

Finally, I used the uploaded Liquid template in the Azure Logic App like so:

Real World Web Service Approach

I used this approach to perform all of the XML to JSON payload transformations that were needed for our client’s project. The best part of this approach is its well within their reach to tweak. As more fields are exposed from Sales Force that need to be shuffled down to their target web service API, the client can simply update the map and the JSON parsing.

The approach isn’t without a few issues. A few things I’d consider before using it for another client are:

• The built-in formatting is currently very limited. I had to write my own formatting markup to rework phone numbers and dates. There are a lot of advanced filters out there that would format strings as I needed, but until Microsoft supports registering these extensions, you’re stuck writing your own.
• When you do write your own filtering code, the problem is compounded by the fact that you can’t call a block of code over and over. You end up having to cut and paste any complex formatting. Again, this problem should be solved once Microsoft supports registering extensions.

All-in-all, if you are looking for that in-between mapping solution (easily edited, doesn’t need a lot of complex features, etc.), have a look at Liquid templates!

To learn more about or process, or talk to a Rōnin Software development consultant, contact us today!