Ritter Insurance Marketing website

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.

Core team:

  • 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.

Customer profile

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.

Problem statement

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

  1. 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:

    Architecture diagram

    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.

    Selecting Azure Table storage in the Get Data dialog box

    Second, provide the account name and access key.

    Connect to Azure Tables - Account Name

  2. 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:

    Quote Engine role selection

    Existing admin dashboard

    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.

    Table columns in Storage Explorer

    The table contains a column of type JSON. We wanted to expand the properties by adjusting the query.

    Table columns in Query Editor

    We adjusted the query to include the attributes of the JSON column. Now we can include these attributes in the report.

    RitterIMModel

    We then populated the dashboard with appropriate controls. Our finished report in Power BI Desktop for Ritter customers looks like this:

    Ritter Quote Engine dashboard

  3. 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.

    Menu of ProvisionSample console app

  4. 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:

    Admin dashboard with embedded report

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.

Video demo

Video: Power BI Embedded demo for Ritter

General lessons

Bringing embedded reports into our existing ASP.NET MVC 5 site required the addition of the following Power BI NuGet packages.

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.

Azure Table import process in Power BI Desktop

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.

Power BI Embedded rendering error

I tracked all the JavaScript dependencies that are needed to render a report successfully in the application. Here’s the list of added JavaScript libraries:

  • bootstrap-switch
  • dataTables
  • matchHeight
  • select

The following code excerpt shows the scripts added to Layout.cshtml before referencing the Power BI JavaScript library:

<!-- 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=1.1.3.16224, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
    </assemblies>
  </compilation>
</system.web>

Additional resources