Introduction

With the introduction of newer and newer offerings on the Azure Cloud Platform coupled with already developed on-premises systems, communication between the on-premises systems and the systems designed and hosted over the cloud is now a valid business requirement. This connection between the on-premises systems and the cloud systems is termed as hybrid integration.

 According to Microsoft:
"A hybrid cloud approach offers the best path to the cloud and a way to optimise your existing assets. Hybrid integration solutions allow you to seamlessly integrate applications, data and processes by building an API-enabled and connected enterprise, allowing you to operate with exceptional speed and agility."

Microsoft BizTalk Server is a proven integration platform which was used to integrate the disparate on-premises systems and achieve phenomenal results. With the release of BizTalk 2016, Logic App adapter was introduced. This adapter can be used in conjunction with the on-premises data gateway to connect with the Logic Apps hosted in the cloud to achieve hybrid connectivity. With the release of the Feature Pack 2 for the BizTalk 2016 server, a new adapter for connecting the on-premises BizTalk application to the Azure Event Hubs was released thus opening up another gateway for setting up the hybrid integration. This adapter has now given the ability to on-premises BizTalk server app to publish events to the Azure Event Hubs or to subscribe to the events published by other apps in the Azure Event Hubs. This article aims to discuss a sample scenario in which the BizTalk server acts as a publisher and the Azure Logic App flow acts as subscriber and both in conjunction work to achieve the desired result.

Logic Apps

Logic Apps are a piece of integration workflow hosted on Azure which are used to create scale-able integrations between various systems. These are very easy to design and provide connectivity between various disparate systems using many out of the box connectors as well as with the facility to design custom connectors for specific purposes. This makes integration easier than ever as the design aspect of the earlier complex integrations is made easy with minimum steps required to get a workflow in place and get it running.

Cognitive Services

 As per Microsoft:
"Microsoft Cognitive Services (formerly Project Oxford) are a set of APIs, SDKs and services available to developers to make their applications more intelligent, engaging and discoverable. Microsoft Cognitive Services expands on Microsoft’s evolving portfolio of machine learning APIs and enables developers to easily add intelligent features – such as emotion and video detection; facial, speech and vision recognition; and speech and language understanding – into their applications. Our vision is for more personal computing experiences and enhanced productivity aided by systems that increasingly can see, hear, speak, understand and even begin to reason."

Azure Event Hubs

Azure Event Hubs is a highly scalable data streaming platform and event ingestion service, capable of receiving and processing millions of events per second. Event Hubs can process and store events, data, or telemetry produced by distributed software and devices. Data sent to an event hub can be transformed and stored using any real-time analytics provider or batching/storage adapters. With the ability to provide publish-subscribe capabilities with low latency and at massive scale, Event Hubs serves as the "on ramp" for Big Data.


Fictional Scenario

ABC Restaurants is a  private chain of restaurants catering to a varied profile of clients. Recently as a part of a drive by their CEO to better their service, a system to analyze the feedback by the guests is to be commissioned. This system is to collect the feedback from the customer using an inhouse application and then store the feedback and the sentiment score in an on premises SQL datastore. In order to detect the sentiment in the feedback given by user, it was decided to make use of the Microsoft Cognitive Services. As the company was already using the BizTalk server to integrate their payments, customers systems, it was decided to design a BizTalk application by leveraging the hybrid integration power of BizTalk 2016.


Solution Designed For the Requirement

Following is the solution which is implemented for the business requirement.

  1. Get the Feedback from the inhouse application using the wcf service exposed by BizTalk
  2. Save the Feedback and user details in an On Premises SQL datastore.
  3. Once the insert is complete, send an event to the Azure Event Hub using the vent hubs adapter. This message contains the important details required to analyze the feedback/
  4. On Azure make the sentiment analysis Logic App listen to the event hub. As soon as BizTalk publishes the event, pick it up and analyze the sentiment by calling the Microsoft Text Analytics API
  5. Use the on premises data gateway to connect to the On Premises SQL datastore and update the score for the feedback.

Following integration diagram explains the solution implemented.


Solution Implementation

The implementation can be broken down into following parts

  1. On Premises Systems Design
  2. Cloud Systems Design

On Premises Systems Design

SQL Data Store

A SQL database named POCDatabase is used to store the data collected from the feedback. Following query is used to create the table to store the feedback details

USE [POCDatabase]
GO
 
/****** Object:  Table [dbo].[UserFeedBackAnalysis]    Script Date: 11/24/2017 7:04:58 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[UserFeedBackAnalysis](
    [UserID] [nchar](10) NOT NULL,
    [MailId] [nvarchar](100) NOT NULL,
    [ServiceFeedBack] [nvarchar](max) NULL,
    [SentimentScore] [nchar](10) NULL,
UNIQUE NONCLUSTERED
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
 
USE [POCDatabase]
GO
 
/****** Object:  StoredProcedure [dbo].[usp_InsertUserFeedBack]    Script Date: 11/24/2017 7:05:16 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO

Following is the SQl script used to create the insert stopred procedure used to insert the data into the table created above.

USE [POCDatabase]
GO
 
Create Procedure [dbo].[usp_InsertUserFeedBack]
@userId nchar(10),
@mailId nvarchar(100),
@FeedBack nvarchar(max)
 
AS
 
BEGIN
 
INSERT INTO  UserFeedBackAnalysis
(
    UserID,
    MailId,
    ServiceFeedBack
)
values
(
    @userId,
    @mailId,
    @FeedBack
)
END
GO

Following SQL script is used to update the sentiment score for a row entry for a particular user. This procedure is invoked from the Azure Logic App via ON premises Data gateway. 

USE [POCDatabase]
GO
 
 
Create Procedure [dbo].[usp_UpdateSentimentScore]
@UserID nchar(10),
@SentimentScore nchar(10)
 
as
 
BEGIN
 
Update UserFeedBackAnalysis
set SentimentScore =@SentimentScore
where
UserID = @UserID
 
END
GO

BizTalk Application

Following schema structure is used to accept the input and send output messages from the feedback service where the BizTalk WCF Publishing Wizard is used to expose following schemas as wcf service

Input
<?xml version="1.0" encoding="utf-16"?>
  <xs:element name="Request">
    <xs:annotation>
      <xs:appinfo>
        <b:properties>
          <b:property distinguished="true" xpath="/*[local-name()='Request' and namespace-uri()='http://BizTalkEventHubAdapterDemo.UserFeedBackInput']/*[local-name()='FeedBack' and namespace-uri()='']" />
          <b:property distinguished="true" xpath="/*[local-name()='Request' and namespace-uri()='http://BizTalkEventHubAdapterDemo.UserFeedBackInput']/*[local-name()='UserId' and namespace-uri()='']" />
          <b:property distinguished="true" xpath="/*[local-name()='Request' and namespace-uri()='http://BizTalkEventHubAdapterDemo.UserFeedBackInput']/*[local-name()='MailId' and namespace-uri()='']" />
        </b:properties>
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="1" maxOccurs="1" name="UserId" type="xs:string" />
        <xs:element minOccurs="1" maxOccurs="1" name="FeedBack" type="xs:string" />
        <xs:element name="MailId" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Output
<?xml version="1.0" encoding="utf-16"?>
  <xs:element name="Response">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Acknowledge" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Following is a Sample json message which will be sent to the Event Hub after the feedback and other details are inserted into the sql datastore.

{
  "FeedBack": "",
  "MailID": "",
  "UserId": ""
}

The schema generated for above sample message using the Json Schema Wizard is as below.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="unqualified" targetNamespace="http://BizTalkEventHubAdapterDemo.EventHubMessage" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Root">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="FeedBack" type="xs:string" />
        <xs:element minOccurs="0" name="MailID" type="xs:string" />
        <xs:element minOccurs="0" name="UserId" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

The insert stored procedure created during the database design is now consumed in BizTalk Application using Consume Adapter Services adapter option. 

The BizTalk orchestration designed to accept the messages from the WCF service, insert it into SQL and publish the message to event hub on successful insert is shown below.

   

A json encoder pipeline is required to convert the Event Hub Message from the xml format to the json format as shown for the sample message above. The Pipeline is as shown below.

The properties of the json encoder that need to be set to remove the root node envelope from the json message are as shown below.

On Premises Data Gateway

The On Premises data gateway can be set up completely using following Microsoft Documentation.

Connecting to on-premises data sources with Azure On-premises Data Gateway  

Cloud Systems Design

Creating Text  Analytics Api Analysis Account 

In order to use the Text Analytics Api connectors in the Logic Apps, first an Api account for the Text Analytics Api needs to be created. Once this is done, the connectors will be available to integrate the Text Analytics Api in Logic Apps. Following screenshots show the process to do so.

 



Creating Azure Event Hubs

In order to publish - subscribe to the events from the event hubs, an event hub namespace as well as event needs to be created, Refer following screen shot for sample.

  

  

  

Logic App Design

Following is the step by step approach in which the logic app required for this integration can be created.

  1. Select the Logic Apps from the list of the Azure offerings. Refer following sample screenshot.



  2. Create a logic app AnalyzeSentimentinFeedBackLA as shown in following sample screenshot.



  3.  Select the blank logic app template. Refer following sample screen shot.

  4. From the List of the Triggers, select the Event Hubs trigger as shown in sample screenshot below.



  5. In order to create the event hub connection, select the event hub namespace and the shared access keys. Refer below sample screen shot.




  6. Configure the Event Hub Trigger by selecting the required event hub from the name space connection created above. Refer following sample screenshot.



  7. Once the message from the event hub is received, it should be parsed into the json format so a data operation of json parser is set up as shown below.



  8. Now the text in the feedback node in the json payload can be sent to the Microsoft Text Analytics API. Select the Detect Sentiment Action from list of available Text Analytics API actions. Refer following sample screenshot.

  9. Create the connection for the Cognitive Text Analytics API as shown below. The keys and the urls collected at the time of the creation of the Text Analytics API account will be used here,.



  10.  The Action can be configured as below.

  11. Once the sentiment score is received from the Text Analytics APi, the On Premises SQL datastore can be updated with the sentiment score against the correct user id. The Action to execute the stored procedure from the list of SQL action is to be selected. Refer sample screen shot.


  12. Create the connection to the On Premise SQL server data store using the on premise data gateway. Refer following sample screen shot.

  13. Configure the update stored procedure created while designing the on premises SQL data store. The user ID can be passed from the parsed json and the sentiment score which is returned by the Text Analytics Api. Refer following sample screen shot.



  14. This completes the flow of the logic app.


Configuring The BizTalk Application 

The deployment for the biztalk application to the BizTalk group follows the same procedure as any other BizTalk application. Only extra thing that needs to be done is to create the Event Hub Send port and attach the json encoder pipeline to the send port. Refer following screenshot to configure the send port.

  

 

  

Testing

In order to test the hybrid integration a tool like SOAP ui can be used which is uesd to send the request to the on premises exposed wcf service. Following Scrrenshots show a sample testing scenario.

  1. The request response as recorded in the SOAP UI. 



  2. The row in the database table after the insert is performed by the BizTalk application.

  3. The message sent by the BizTalk to the Event Hub.
    {
      "FeedBack": "Bond Had a WOnderful Experience",
      "MailID": "bond.jamesbond@gmail.com",
      "UserId": "123007"
    }

  4. The row in the database table after the Logic APp calculates the sentiment score and updates the row.



Conclusion 

As clear from the test case run for the hybrid integration, the BizTalk application published the message to the Azure Event Hub which was subscribed by the Logic APP and it updated the on premises SQL data store entry with the sentiment score.


Sample Code

Sample code for the BizTalk application can be found at following location in the TechNet Gallery.

Sample Code For BizTalk Event Hub Adapter Demo 


See Also

An important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki. Another important place to find Logic App related articles is the TechNet Wiki itself. The best entry point is Logic App Resources on the TechNet Wiki.



References

Following links were referred while writing this article.