Logo of RockStep Solutions

Microsoft joined RockStep to create a Power BI Embedded solution, which provides access to Internet of Things (IoT) data analyses in real time to reduce operating and maintenance costs for their clinical-trial customers.

Research facilities have thousands of housing units that need to be checked on a regular schedule to ensure that conditions are within compliance. RockStep is teaming up with the University of Michigan to run a pilot IoT program that monitors key environmental measurements in real time and will proactively alert technicians when conditions reach a threshold range. By reducing the number of visits to each unit, they can cut maintenance and operating costs and improve the overall health of animals.

Customers will have access to the Power BI Embedded dashboards, allowing them to make decisions based on these real-time data and thus improve the overall health of animals.

Core team:

  • Abigail Ames (@rocksteptech) – Director of Technology, RockStep
  • Keith Sheppard (@keithsheppard) – Lead Developer, RockStep
  • Heather Shapiro (@microheather) – Technical Evangelist, Microsoft
  • James Sturtevant (@Aspenwilder) – Senior Technical Evangelist, Microsoft

Photo of team working

Customer profile

RockStep Solutions creates world-class scientific data-management tools for research. Its innovative software system Climb is designed to transform and modernize information management in a laboratory setting. The RockStep team’s experience comes directly from years of working at the Jackson Laboratory in Bar Harbor, Maine. The goal of RockStep is to create software that enables science by leveraging the team’s experience working in research laboratories to build software that is easy to learn, usable anywhere, and truly valuable to research.

Problem statement

RockStep will be deploying approximately 100 devices to pilot the IoT project, previously supported by James Sturtevant. These devices will be going into several different animal rooms and will continuously collect temperature, ammonia, humidity, noise, and light levels. For the pilot, RockStep will only be analyzing this data, but in the future they will layer experimental data so that the users can look at experimental results in the context of factors such as noise and light levels.

The first goal is to monitor environmental data to more accurately determine housing-unit change rates. Housing units must be changed at periodic intervals to keep the levels of ammonia and humidity within FDA regulation values; however, housing-unit changes are costly and disruptive to the animals. The IoT data will help the technicians in the facility to change housing units only when necessary.

We will be building PowerBI reports for users to view the streaming data from the IoT devices, perform real-time transformations on the data, do aggregations, create alerts, and store the data. We are assuming that the stored data will be used for historical analysis as well.

Our goal was to connect to the RockStep data hosted in an Azure SQL Database and integrate the created Power BI reports into their current Climb application.

Key technologies used

Solutions, steps, and delivery

RockStep and Microsoft teamed up to create a “proof of concept” that would display their simulated IoT data through embedded Power BI reports in a website running Angular 2.0. The solution was developed during a hackfest at the Microsoft office in Burlington, Massachusetts.

  1. Connect to RockStep data in Azure SQL Database
  2. Build a report template using Power BI desktop
  3. Set up Power BI Embedded workspace and upload reports
  4. Embed report into existing ASP.NET web API and connect through Angular application
  5. Integrate with Angular application

1. Connect to RockStep data in Azure SQL Database

RockStep hosts their data in Azure SQL Database, to which we connected through DirectQuery.

The overall architecture for their IoT solution is as follows:

Diagram of overall architecture

This solution is the base for the data that was passed into the Power BI Embedded solution architecture:

Diagram of Power BI solution architecture

We walked the team through how to create basic reports and visualizations within Power BI Desktop. We discussed the types of visualizations that might be useful to understand the simulation data, how to use external Power BI visualizations, and how to use the tool itself to create such visualizations. The simulation data currently includes temperature, but in the future it will include many other metrics that they can add to a basic report.

2. Create a template report in Power BI Desktop

Currently, each customer in their database has a different schema (that is, table naming) and needs different data pulled down for the same basic report. Using row-level security is not a fit for this scenario because it would have to dynamically change as new users are added. Instead, we created a template report for RockStep, which includes a parameter for the schema name. This connects to the correct data table in Azure before creating the report.

This change will require an onboarding process for every new client, but as the technology advances, it should support multitenant data.

To create the parameter, within Query Editor we added a parameter for the schema, as seen in the following image.

Adding parameter in Query Editor

We then used the advanced editor to change the query to take the parameter.

Changing query in advanced editor

When the template file is opened, the customer will be prompted for the parameter and the data will be updated appropriately. The following is a view of the report template:

Power BI report template

3. Set up Power BI Embedded workspace and upload reports

Each customer will have its own report and thus a new workspace within Azure. To help with the onboarding of new customers, we created a script that takes the Azure subscription ID, resource group name, workspace collection name, Azure location, .pbix file and file name, and the SQL Database username and password as parameters.

The script uses the Power BI command-line interface tool (Microsoft/PowerPI-Cli on GitHub) and the Azure CLI to log in to Azure, create a workspace collection, upload the .pbix file, and update the SQL Database connection string for the report. We also provided a Docker container that RockStep can use to deploy the script if they are unable to install of the requirements.

You can find the script, along with documentation, on GitHub (jsturtevant/power-bi-embedded-workspace-script) and the Docker image with all requirements pre-installed on Docker Hub (jsturtevant/power-bi-embedded-workspace-script). When running, the script looks like this:

Script to create workspace running in Power BI

4. Embed report into existing ASP.NET web API and connect through Angular application

The web API builds on the sample provided by the Power BI Embedded team on GitHub: Azure-Samples/power-bi-embedded-integrate-report-into-web-app.

We began by setting up a new Power BI controller within the existing web application. Through this controller, we are able to limit access to the RockStep internal report. In code, we create an embed token by using the following:

var embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id);

Because each customer has its own Power BI report, we updated the code in the dashboard controller to look for the tenant ID (the schema) that is passed. It will then find the report that matches that tenant ID. Going forward, new workspace collections are named as “SBIRPilot” followed by the tenant name. In the following code excerpt, the user passes a tenant ID (reportId) from the Angular component; based on the specified tenant, the workspace information is updated and a different report is pulled. Until Power BI gains more multitenant capabilities, it will be necessary to have a dictionary of workspace collection names, workspace IDs, and access keys that can be matched to tenant IDs.

private IPowerBIClient CreatePowerBIClient(string reportId)
    {
        if (reportId == "dbo")
        {
            this.workspaceCollection = "SBIRPilotPowerBI";
            this.workspaceId = "";
            this.accessKey = "";
        }
        else
        {
            this.workspaceCollection = "SBIRPowerBITenant1";
            this.workspaceId = "";
            this.accessKey = "";
        }
        var credentials = new TokenCredentials(accessKey, "AppKey");
        var client = new PowerBIClient(credentials)
        {
            BaseUri = new Uri(apiUrl)
        };

        return client;
    }

5. Integrate with Angular application

Integrating with Angular turned out to be fairly straightforward. On the client side, we leveraged the Power BI Javascript library (Microsoft/PowerBI-JavaScript) and created a small Angular directive to wrap the library. The code can be found in a gist by Keith Sheppard: keithshep/powerbi-embeded.directive.ts.

We then used that directive in a simple component that fetches the Power BI configuration JSON from the server. In our case, the Power BI configuration is dependent on the current tenant ID.

In data-reports.component.html we added the following:

<div [powerBiEmbed]="powerBiConfig" style="height: 500px">Loading Reports...</div>

And in the data-reports.component.ts we fire an HTTP request to update the Power BI configuration whenever the tenant ID changes:

this.http.get(this.reportUrl + this.tenantId)
    .map(response => response.json())
    .subscribe(_powerBiConfig => {
        this.powerBiConfig = _powerBiConfig;
    });

The following image shows the final site view. If we toggle between users, a different Power BI Embedded report is displayed.

Final 93port view

A final mockup of the project with the Power BI Embedded component integrated with the RockStep Climb app:

Mockup of Climb app integrated with Power BI Embedded app

Conclusion

The proof of concept is now complete, and RockStep is working to incorporate it into their working Climb app with real-time data from Raspberry Pi 3 IoT devices. The entire solution will be tested with a pilot at the University of Michigan. Learnings from the pilot will be integrated back into the solution and, because of the multitenant solution, will then become available for other RockStep customers. As the Power BI software improves, the hope is for RockStep to have less manual work when onboarding new customers.

“It’s really impressive what you can do with Power BI Embedded. You’re given a rich set of plotting components that you can use as an exploratory interface to your data in Azure without writing any code. This means you can rapidly iterate and find which views are providing you with the most value and make those views available within your web application.” —Keith Sheppard

General lessons

Multiple tenants in Power BI

To compensate for the current lack of multitenant capabilities in Power BI Desktop, as described earlier, we were able to create parameters that would pull data for various tenants. The process will be a bit manual for the onboarding of new customers, but with the template and scripts available to easily upload reports, the time hit should be minimal.

Visual Studio NuGet versioning

We ran into some issues with Visual Studio NuGet versioning with the Power BI Embedded SDK. We followed this guide from Paul DeCarlo that specifies what was necessary to get the SDK working and the app to run.