Using Power BI to provide reports for the Ritter Quote Engine
Ritter Insurance Marketing, LLC, empowers customers to manage quotes, sales, policies, and geographic time-sensitive plans through its existing Quote Engine service, which uses Microsoft Azure to store logging and auditing information. We set out to bring the analytics capabilities of Microsoft Power BI Embedded into the Quote Engine.
- Scott Kowalski – VP of Operations, Ritter Insurance Marketing
- Scott Schwalm – Software Engineer, Ritter Insurance Marketing
- Khalid Abuhakmeh (@buhakmeh) – Director of Software Development, Ritter Insurance Marketing
- Mostafa Elzoghbi (@MostafaElzoghbi) – Senior Technical Evangelist, Microsoft
Our solution required the creation of an interactive dashboard for Quote Engine users and a secure reporting system for Quote Engine customers. This involved the creation of a Power BI Embedded report that accesses data stored in Azure Storage using Azure Table storage. These reports were later integrated into an existing ASP.NET MVC 5 web application that runs in Azure.
Ritter Insurance Marketing is a field marketing organization (FMO) in the senior life and health insurance markets. That means they promote and distribute insurance carriers’ products to licensed agents. And they equip agents with the technology and support they need to make faster, better, and smarter decisions for their clients.
Ritter currently serves customers across the United States. With customers in every region, their data grows at a rate proportional to active field agents. Stakeholders would like to view information within this data in an interactive online dashboard that allows interactive queries for application distribution. This gives external customers a way to view the Quote Engine information and the areas, policies, plans, and quotes that they are interested in.
In addition, Ritter customers should be able to view similar information pertaining to regions, plans, and quotes in their local area by allowing this data to be accessed securely in the administration panel.
In this way, a customer can view the performance of field agents as it relates to their business. This would allow the customer to assess the value of adopting Ritter services in a quantifiable format. It would also assist the customer by offering insights such as customer traffic patterns, services of interest, earned revenues, and active system issues.
Solutions, steps, and delivery
Access the Azure Tables data source.
The Ritter Quote Engine relies on an ASP.NET MVC 5 application running on Azure. Here is a depiction of the architecture:
This step required working together with the Ritter development team to share the account name and access key of the Azure Storage account on their existing Azure subscription to allow importing their data into Power BI Desktop for report development.
First, select Azure Table storage as the data source of the report.
Second, provide the account name and access key.
Create the report.
Our project requirement was to create a report that connects to Azure Table storage and integrate it in the Quote Engine. Here’s how the existing dashboard appears:
We accomplished this step by reviewing the ASP.NET MVC 5 source code for the existing dashboard so that we could integrate the new dashboard into the existing navigation system.
Upon connecting to Azure Table storage, the Power BI desktop was showing only four columns. But when I used Azure Storage Explorer, I was seeing more columns in the target table.
The table contains a column of type JSON. We wanted to expand the properties by adjusting the query.
We adjusted the query to include the attributes of the JSON column. Now we can include these attributes in the report.
We then populated the dashboard with appropriate controls. Our finished report in Power BI Desktop for Ritter customers looks like this:
Set up Power BI Embedded workspace in Azure.
We started by following the instructions in Get started with Power BI Embedded sample. This sample app walks through the creation of the workspace and how to upload projects into the workspace for access through Power BI Embedded. Currently, these processes are handled through a console app named ProvisionSample.
Embed report into existing ASP.NET MVC 5 application.
We began by setting up a new PowerBIController controller within the existing web application. Through this controller we can limit access to the Ritter internal report. We use the following code to create an embed token:
var embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id);
This allows us to view the embedded report within the existing website:
We integrated the completed Power BI report into the Quote Engine GitHub repository, which is available to application users. All project code changes have been merged successfully into the product code base.
Bringing embedded reports into our existing ASP.NET MVC 5 site required the addition of the following Power BI NuGet packages.
Power BI Desktop does not support direct query for Azure Table storage, so we imported the data—more than 1 GB—into our Power BI report file (*.pbix).
Ritter is working to move this data into Microsoft SQL Server by using a utility to copy the recent records into a database for further analysis.
Because the Quote Engine uses a newer jQuery library (version 2.2.4) than the one that is used in the Microsoft sample application, I was getting the following error message when rendering any Power BI report in the application.
<!-- latest bootstrap-switch, matchHeight, dataTables, and Select2 releases --> <script src="https://rawgit.com/nostalgiaz/bootstrap-switch/master/dist/js/bootstrap-switch.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.matchHeight/0.7.0/jquery.matchHeight-min.js"></script> <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script> <link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/css/select2.min.css" rel="stylesheet" /> <script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/select2.min.js"></script>
To be able to use HTML Power BI Extensions in any Razor view, I added the following configuration in views/web.config:
<system.web> <compilation> <assemblies> <add assembly="Microsoft.PowerBI.AspNet.Mvc, Version=220.127.116.1124, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/> </assemblies> </compilation> </system.web>