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!

Back in the 90s (it wasn’t that long ago, was it?), our development teams that built internal business applications were pretty simple. As we were building mostly desktop applications, we either had a team of Java Swing developers or a team of Microsoft WinForms developers. While we sometimes brought in user interface experts to help us with flow or imagery, everyone could do everything when it came to actual development. Life was much simpler regarding building out and maintaining our development team.

As the web took over for all the right reasons, we enterprise developers had to adapt. We had to learn a separate technology stack of HTML and JavaScript to build our user interfaces while simultaneously keeping our Java and C++ or C# stack for the backend. Java Servlets, Java Server Pages, and Microsoft ASP/ASP.NET initially bridged the gap for us, allowing enterprise developers to use their primary language skillsets still.

However, as more and more JavaScript frameworks arose, AJAX became a thing, and SPAs eventually took over, and many of us adopted. We learned one set of languages for our frontend work and another for our backend work. Those that couldn’t (or didn’t want to) became backend developers. In fact, it was around this time that developers began to specialize into frontend and backend roles. It’s a concept that seems common now but wasn’t always. Hiring got more complicated.

As technologies like Adobe Flex, Microsoft Silverlight, Java FX, and others arrived, enterprise application developers got excited. Could we really have nice-looking web user interfaces for our enterprise applications that were written by our (now) server developers? Could it be done using one language again for the user interface and backend? If you’re a Microsoft developer, you know full well of the sadness of Silverlight. A lot of promise that fizzled when the browser plug-in model died. The dream was dead again, and frontend stacks like Angular took over. 

Enter Blazor

I’ve been hearing bits and pieces about Blazor for several months, whether from podcasts or articles. It has kept my attention because it promises to fill that space again, letting one developer skilled in C# build both the front end and backend of an enterprise application.

To date, I had not really taken a deep-dive look at Blazor. That changed last night when I saw Carl Franklin’s Blazor Road Show here in Atlanta. Carl is well-known in the Microsoft community as he co-hosts one of my go-to podcasts, .NET Rocks. His stamp on Blazor drew me in instantly.

What I Learned And Why Blazor Has Me Excited

Carl spent three hours with us explaining the theory behind Blazor, its internal workings, its concepts, and building out demo applications. I left feeling like I could spin up a Blazor application in minutes and wouldn’t be working with a product that will be abandoned by Microsoft. Further, I could see a path to simpler IT development teams for the right shops for the first time in a long time. 

Now, don’t get me wrong. I have seen the benefit of working with some seasoned frontend developers in making an awesome user interface. We have a couple on staff at Ronin that consistently create beautiful user interfaces. However, if I’m a shop building and supporting internal enterprise web applications or creating and maintaining a suite of line-of-business applications, Blazor could be the future. How?

With Blazor, all development is with C# and Razor pages (think of a much cleaner and simpler version of ASP.NET Web Forms markup), so I only need C# developers to build web applications that function like any other SPA.

@page "/counter"

<h1>Counter</h1>

<p>Current count: @currentCount</p>

<button class="btn btn-primary" @onclick="IncrementCount">Click me</button>

@code {
    private int currentCount = 0;

    [Parameter]
    public int IncrementAmount { get; set; } = 1;

    private void IncrementCount()
    {
        currentCount += IncrementAmount;
    }
}

@page "/"

<h1>Hello, world!</h1>

Welcome to your new app.

<Counter IncrementAmount="10" />

The out-of-the-box Blazor UI components are probably good enough for many internal enterprise applications, minimizing or eleminating the need for specialized frontend user interface developers. That said, you can certainly customize the styling with CSS if desired. Additionally, companies such as Telerik and DevExpress have already created several themes and components that can be used, eliminating the need for any CSS development to achieve even better looking applications than Blazor produces out of-the-box.

There are other benefits, too. For example, depending on what Blazor mode you choose, performance may even be better. Blazor server-side mode, for instance, utilizes binary data over web sockets for it’s communication, eliminating the need for HTTP web sockets to be opened on each request and thereby performing faster than today’s web applications.

Microsoft already has Blazor support in their current development tools, and according to Carl, they are releasing further updates in May. 

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.

Azure Functions are a key part of the Microsoft Cloud computing platform. Focusing on integration and continuous delivery, we provide our clients with excellent results with cloud computing. However, while recently building out some Azure Functions for one of our clients, I encountered an unexpected problem while deploying them. I thought I’d share what I found with some of these CI/CD pitfalls.

The Problem and CI/CD Pitfalls

The problem arose during the setup of the CI/CD pipeline. Like most Azure implementations, we wanted to set up our build and release pipelines using Azure DevOps, which allows us to automatically run unit tests during check-in and automatically deploy code to Azure Function Apps. The setup was extremely smooth with Azure DevOps integration since the codebase was being stored in Azure Repos. The default branch of code (master) deployed perfectly to the Azure Function App.

As smooth as this was, however, I hit a wall when trying to deploy the code from the development branch to a development slot of the Function App. The Azure DevOps deployment logs were not particularly helpful, and web searches turned up little on the problem. After spending a lot of time experimenting, digging through logs on the server, and reading through the project Kudu source code, it became clear there was a problem using Zip Deployments. The reason this was so difficult to track down, and there is not much information out there about is that it’s only a problem under the following conditions:

• You’re deploying a non-default branch (not master), such as a feature or dev branch
• The deployment method is using:
  • An Azure Function App’s integrated Deployment Center
  • An Azure DevOps pipeline using an Azure Functions task

Both of these deploy using ZipPushDeploy, which at the time of this writing, counts on the source branch being the default (master).

The Solution

Taking some advice from a fellow business partner and Azure expert Byron McClain, I tried using an Azure App Service Deploy task from the release pipeline. Since this task’s advanced options let you choose Web Deploy over the default Zip Deploy, I was back in business. This is our workaround until the good contributors at Project Kudu can patch the issue.

For more details about the ticket, go here.
For more details about Azure Functions and Serverless Computing, go here.

Happy Azure Trails!

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.