An Attempt to – Convert Data into Dollars
Live Streaming of Data to Power BI using Azure Steam Analytics and Azure Events Hub
Welcome to another blog. This time I am going to talk about real time data ingestion, streaming, analysis and reporting using event hubs, stream analytics and power BI. How we can utilize data as meaningful information and thus live stream to Power BI reports using readily available Azure PaaS components. I hope it is going to be great learning session for everyone. Let’s understand one of problem statement that can really be solved by using this solution.
Problem Statement: Over the years, health system has made a significant investments in revenue management technology and reporting is one of critical selling feature, but most of the time, these reporting tool didn’t provide us Real-time data, because system had too many integrities and there are some other data modelling issues. And I am working with one of the client and can envision this issue.
For example, reporting system was not able to track latest status of referral accounts in terms of payments, charges, claims etc. because different applications are running separately and data resides at different places and to collect and get accurate data, different development teams had to work extensively with BI teams. Then BI team pulls data from different sources and then write complex reports.
And as a result, the number of reports that the stakeholders(Client) requested from BI team was staggering and there was no centralized place to keep track of data of which reports were being created or for which accounts/referral claims at given time. Furthermore, the data of these fragmented reports was being pulled from different sources on weekly basis and sometime the level of granularity to find the root cause of an issue didn’t exist in any of data source. Finally, lot of chaos and manual work to get that data in right place.
This manual reporting process contributed to a significant problem with data timeliness. We know that timely data is critical for managing accounts. And in addition, due to enormous BI reporting queue, the turnaround time for these custom reports could be another delay. By then, the executive team’s window to revisit accounts/claims with insurance providers has sometime closed and finally this delay leads to decline in client’s revenue goal ??. And ultimately due to Data discrepancies, delays Organizations loss Dollars?. We should use power of readily available cloud services to Convert Data into Dollars for our clients/Customers.
Solution:
Advanced Real time analytics using Azure event hubs, Azure Steam analytics with Power BI
In traditional world, the occurrence of event which in our case is an update on any referral Account is reported much later than its actual time of occurrence to Power BI. Now there is no point to knowing this occurrence at end of day or after end of the week. We should read and analyze data as it flows into system. To achieve this, we have Azure Stream Analytics which can process streaming data e.g. in our case are referral account to power BI data-sets and thus provide real time reporting.
Before we get into Architecture and implementation, I want to give you a technical overview of each component used in this solution:
Azure Stream Analytics: It’s a high performance, managed event processing engine that setup real time analytics computation on streaming data. This data can come from devices, sensors, APIs, apps, media feeds etc. It can have Azure events hub, Azure IOT hub and blob storage as input. It can process the data and output that data to the Azure service bus, azure functions, real time power BI dashboards and other data stores. The transformation query has support of SQL like syntax that is used to filter, aggregate and join streaming data over specific time of intervals.
When we use stream analytics, we mostly distinguish it into two streams:
1. Cold Path: The cold path means data will be stored for further processing before we publish it to end users. For example, from stream analytic load data into Azure data lake or an Azure SQL Database.
2. Hot Path: This path means data is real time and will be directly displayed into Power BI. For this first we have to create Stream analytics Job and then do configuration to integrate with Power BI data-sets.
Azure Events Hub:
It a fully managed PaaS component service which sits between producers and event consumers to decouple the production of event steam from the consumption of these events. This service is capable of ingesting million of events per second having time retention buffer. Data packets sent to event hub can be transformed in any real time stream processor like Azure stream analytics or it can be stored in data stores as well.
PowerBI : It is a collection of software services, apps and connectors that work together to turn our unrelated source of data into coherent. Visually impressive and interactive insights. It is smart enough to connect with multiple PaaS components to read data in their own data-sets and tables and facilitates to visualize and analyze and share it.
Solution Architecture:
The Architecture of this system receives transaction data from different system, let take an example of 3 apps. In real it could be any number of systems and generate data with millions of requests per day. Since this must be quite comprehensive and we should consider the load very seriously otherwise the solution will not be able to handle volume. Reason we use Azure event hubs as it states : “A fully managed real time data ingestion service that can stream millions of events per second from any source”. And it can be integrated seamlessly with other Azure services to unlock valuable insights.
Let’s talk about our case, here we have 3 event producers App1- which are API endpoints, App2 which is BPM tool generates events on each action on the referral account and App3 is another source we can capture messages directly from UI app as well. All these apps act as event generator.
Every transaction from either of these 3 Apps creates a message in JSON data format and push this data into Azure event hubs using Send Event action of Event Hub client.
Azure Event Hub Client has exposed one method SendMessagesToEventHub to all three Apps. Once the JSON message is pushed into Azure Event Hub, it creates a data stream which is the input for Azure Stream Analytics and likewise we configured Power BI to be the output. The Output is published as a Real time data-set in Power BI work-space. Similar way we can configure Azure data lake/Blob Storage to be output. And this output is storing this data in data stores in JSON Format. We can store in other format as well like Avro, CSV or Parquet.
With this, we can create a simple dashboard in Power BI to check the real time dashboard features. Now the dashboard tiles get automatically refreshed when the back-end data changes from any of these 3 Apps.
This is an overview, hope it makes some sense. If it looks complex, don’t worry, I will go through solution in details step by step
Let’s start with the creation of a new Event Hubs Namespace as:
We used standard pricing tier and only one throughput Unit, and whenever the load increases, we can change these properties to scale it.
Once the namespace is created, now let’s create Event hubs for transactional data(to keep the messages through the event captured from All 3 apps). Here I added only 1 Partition count, again based on load, we can scale this up.
Now we have the Event hub in place, let us create a messaging service that send messages to this event hub.
Lets jump into some code, Create a C# Library Project and Add 2 classes
Interface : IEventHubMessaging
public interface IEventHubMessaging
{
Task<bool> SendMessagesToEventHub(string messageRequest);
}
Class EventHubMessaging that implements IEventHubMessaging
public class EventHubMessaging : IEventHubMessaging
{
private static EventHubClient _eventHubClient;
private static readonly bool SetRandomPartitionKey = false;
public EventHubMessaging(string eventHubName, string eventHubConnectionString)
{
var connectionStringBuilder = new EventHubsConnectionStringBuilder(eventHubConnectionString)
{
EntityPath = eventHubName
};
_eventHubClient = EventHubClient.CreateFromConnectionString(connectionStringBuilder.ToString());
}
/// <summary>
/// Creates an Event Hub client
/// </summary>
/// <param name="messageRequest"></param>
/// <returns></returns>
public async Task<bool> SendMessagesToEventHub(string messageRequest)
{
bool isSuccess;
try
{
// Set random partition key?
if (SetRandomPartitionKey)
{
var pKey = Guid.NewGuid().ToString();
await _eventHubClient.SendAsync(new EventData(Encoding.UTF8.GetBytes(messageRequest)), pKey);
isSuccess = true;
Console.WriteLine($@"Sent message: '{messageRequest}' Partition Key: '{pKey}'");
}
else
{
await _eventHubClient.SendAsync(new EventData(Encoding.UTF8.GetBytes(messageRequest)));
Console.WriteLine($@"Sent message: '{messageRequest}'");
isSuccess = true;
}
}
catch (Exception ex)
{
Console.WriteLine($@"{DateTime.Now} > Exception: {ex.Message}");
isSuccess = false;
}
return isSuccess;
}
}
}
So finally, our messaging service is ready. You can call this interface IEventHubMessaging to any Consumer App. Out of 3 Apps, I am taking an example of C# API. Likewise it can be consumed in any of client.
Let’s call this Interface in our API Controller.
public class AccountController : ControllerBase
{
private readonly IEventHubMessaging _eventHubMessaging;
public AccountController (IEventHubMessaging eventHubMessaging)
{
_eventHubMessaging = eventHubMessaging;
}
[HttpPost("UpdateAccounts")]
[ProducesResponseType(typeof(ApiResult<Object>), 200)]
public async Task<IActionResult> UpdateAccounts([FromBody] account attr)
{
bool isMessageDelivered;
try
// Assume Event data is coming from any datasource
var result = this.Content(JSONSerializer.Serialize(Event));
if (result != null)
{
var messageRequest = JSONSerializer.Serialize(attr);
isMessageDelivered =await _eventHubMessaging.SendMessagesToEventHub(messageRequest.ToString());
if (!isMessageDelivered)
{
// log message into storage account -- in case of failure, we don't have to loose data
}
}
return new OkObjectResult(ApiResult<object>.CreateSuccess(result, HttpContext.Request.Headers));
}
}
Now Data is pushed into Azure Event Hub in JSON Format.
Let’s go and check data in Azure Event hub. Go to Process Data- > Explore -> Enable real time insights from events
We can see row of data is inserted into event hub. You can click on table or Raw view as well. With these steps, now we have data into Azure Event Hub. Next is to create stream analytics.
Stream Analytics Jobs: For Stream Analytics, we also need to create a resource in Azure, to do so, Go-to Azure Portal and create a new resource of type Stream analytics Jobs.
Stream analytics Job requires configuration of Inputs, Outputs and the query to be performed on the event hub data that is being received from API App.
Note: There is also an option to work with Azure Machine learning functions which will give us access to perform more powerful analytics than a single query. - will discuss in details in separate post
But I will make it simple for this case, we are going to receive data send to Azure event hub and then query the data.
Let me explain each component of stream analytics one by one:
Input : To receive data from Azure event hub, go to -> Inputs – Click on Add stream input on top and then Event Hub. Refer the properties Tab on right hand side -give an alias name for the Input, select the event hub you want to connect and define properties like consumer group, serialization format, encoding and compression type as marked in below snapshot.
Output: we want to send the results of query to Power BI and to do so, we need to create an output as an instance of Power BI. Let’s define the alias of output, data-set name, and table name that needs to be created in Power BI. Note : we need to authenticate to subscription we have in Power BI. Choose Authentication Mode as token.
Query: once both Input and Output are created successfully, then we can create the query that this stream analytics job will use. Stream analytics queries use same syntax of SQL queries with few minor differences.
To learn about queries, please refer documentation as:
we can write any complex queries to create data set from your transactional data coming in azure event hub.
Moreover, Azure Stream Analytics query language can be extended with custom functions and can be either in written in C# language or JavaScript. User defined functions aka UDF are custom or complex computations that can not be easily expressed using SQL queries. These UDFs can be defined once and used multiple times within a query. One of exciting built in feature is support with Geo-fencing and Geospatial function that can be used for tracking account across various regions. For example. Geospatial data can be ingested in either GeoJSON or WKT formats as part of event stream or reference data. Now revenue management team can track status and productivity of account of various facilities by using location. I am not going in detail about this implementation- will cover in another blog. This feature can help teams to bring Account Heat Map in PowerBI reports to see live tracking of referral account, potential losses and various other KPIs based on various locations.
Now Finally all Azure resources are configured, and here we go, we are ready to start our stream analytics Job. After that we can test console Application. It takes 2-3 minutes and we can see data in Power BI. In Power BI, we can create visualizations we want for our data. I kept simple data for Demo purpose, but you are free to add any visual graphs or charts. You can see same data that we have configured in Query above.
Final Outcome : For every client/stakeholder or customer, an efficient revenue system should be designed to maximize revenue generation and to me we should achieve this by:
- Visualize data in Real time as much as possible
- Eliminate manual report creation, data extraction
- Ensure data Integrity and Data Quality
And all these three KPI can be achieved by above solution and finally stakeholders can expect results as:
Much Improved Revenue Cycle management: Since data is updated in near real time, the stakeholders can access application’s real time data at any given time and with most updated information and thus helps them to manage their revenue cycle more effectively. Revenue team can identity the backlog easily and thus get it resolved quickly.
High Quality data integrity and Data modelling: Revenue cycle team trust accuracy of data because it is real time data and validated.
Higher Operational Savings: Since we have removed manual data extracts, manual reporting’s and distribution, freeing up the BI team to work on strategic reporting needs in place of working with different team for data corrections.
I am sure everyone has enjoyed this learning session. Please share your thoughts, comments or questions??. And message me if anyone needs code. And finally, I wish everyone’s safety. Stay Home, Stay Safe.
Reference:
- https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-power-bi-dashboard
- https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-define-inputs
- https://docs.microsoft.com/en-us/azure/stream-analytics/machine-learning-udf
Disclaimer: The views and work presented in this article are my personal views.