With Power BI Embedded analytics, Athena provides extensive data on California's water usage
We set out to bring Power BI Embedded analytics capabilities into a new ASP.NET web application for Athena Intelligence, a data processing and visualization platform for land, food, water, and energy data for the state of California.
The core team:
- David Sypnieski – Founder and CEO, Athena Intelligence
- Kevin Day – Software Engineer, Athena Intelligence
- Tawny Snyder – Co-Founder and COO, Athena Intelligence
- Tim Reilly – Technical Evangelist, Microsoft
- Heather Shapiro – Technical Evangelist, Microsoft
- Mostafa Elzoghbi – Senior Technical Evangelist, Microsoft
Our solution required the creation of an interactive dashboard for Athena Intelligence customers in research and private industries to view aggregated water usage data for the state of California. This involved the creation of Power BI reports that accessed a PostgreSQL database. These reports were later integrated into a new ASP.NET MVC 4.5 web application that runs in Microsoft Azure with a SQL Server 2016 database to support user authentication.
California-based Athena Intelligence, Inc. provides data services from vast amounts of public data and a business intelligence application that incorporates additional private data sources.
Its business intelligence application makes it easy for companies and organizations managing food, water, or energy production to analyze the risks and opportunities in their supply chain by combining natural resource, processing/manufacturing, and production practice data. By enabling the use of existing data and allowing for easier capture of new data, we unlock the value in the supply chain’s data and make it simple for companies to manage their resources more intelligently.
Athena Intelligence is looking to scale its applications using Power BI Embedded and Azure. It currently has a manual workflow for building charts but is seeing redundancies in workflows and would like to start leveraging the analytics, visualization, and authorization features of Power BI and Power BI Embedded. The Athena Intelligence platform will be used by external customers including farmers, agricultural researchers, and local government authorities.
We will connect to Athena’s existing PostgreSQL databases to import data into Power BI Embedded reports to bring statistics about water usage and distribution across the state of California to Athena’s customers. These dashboards will be integrated into a new ASP.NET web application and connected to Athena’s existing website.
Key technologies used
- Power BI Embedded
- Power BI Desktop
- Azure SQL Database
- ASP.NET MVC
- Azure App Service
Solution, steps, and delivery
- Connect to Athena data in PostgreSQL DBs in Azure IaaS.
- Create a report in Power BI Desktop.
- Set up Power BI Embedded workspace in Microsoft Azure and upload reports.
- Embed report into sample ASP.NET MVC application and authorize users for specific reports.
- Use Azure SQL and Web Apps to deploy application.
1. Connect to Athena data in PostgreSQL DBs in Azure IaaS
Two factors limited our ability to use DirectQuery in our reports. First, the complicated nature of the data and the number of tables/databases already supported would have taken too long to convert to Azure SQL Database. Second, features of Athena’s data platform are dependent on shapefiles that are natively supported on PostgreSQL. Shapefiles are a geospatial vector format for GIS software and maintained by Esri. Shapefile support is available as a plug-in for SQL Server, but was beyond the scope of our working period. In the future, the dependence of Athena’s Power BI dashboards on shapefiles will be built with R, which is supported by Power BI.
The data they’re aggregating is highly unstructured, but iterating with Power BI helped them connect things they previously found difficult to assess.
Here is a depiction of the architecture employed:
2. Create a report in Power BI Desktop
After connecting the PostgreSQL database into Power BI Desktop, reports were created for the three different types of users of Athena’s data platform. The three reports are Generic, Food, and Utility.
Here’s an example of the advanced Power BI queries created by Athena:
These queries turn into reports that represent different users of the product and in the future will be customized per customer demand.
An example would be a report targeting the food industry:
3. Set up Power BI Embedded workspace in Microsoft Azure and upload reports
By identifying users, we were able to efficiently create isolation from reports using workspaces. We created workspaces that would serve reports for utility companies, food and agriculture industries, generic research, and a final one for test/samples. Athena currently maintains a single database for all of its customers and created discrete reports for different consumers of its product. In the future, Athena will work toward separate databases for specific scenarios where user and company data is aggregated with public water data.
Here’s what it looks like in the dashboard:
There are several methods for publishing a report in a Power BI Embedded workspace:
- Using the provision sample app: Sample app
- Using the Power BI command-line tool: Power BI Command Line Tool
We used the command-line tool to publish our reports in the workspace. Below are steps and screenshots of the process:
- First we installed Node.js.
- Then we installed a Power BI Node.js package using the command prompt:
npm install powerbi-cli -g
- Finally, we used the command “powerbi - import” as listed in the screenshot below to add the Power BI Desktop reports to the Azure Workspace Collection:
The report was created with a static dataset, rather than direct query, so we did not have to update connection strings to the uploaded dataset. We documented these steps in the PoC Readme for easy replication for Athena.
4. Embed report into sample ASP.NET MVC application and authorize users for specific reports
The application builds off of the sample provided by the Power BI Embedded team found here: Embed Sample
We began by setting up a new PowerBIController within the existing web application. Through this controller, we were able to limit access to specific Athena Power BI reports. In code, we created an embed token using:
var embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id);
This allowed us to view the embedded report within the existing website:
With this first report integrated, we needed to expand the web application to allow registration and login capabilities. Then we needed to grant specific users access to specific reports.
In order to give users access to the app, we added in the identity authentication framework to allow users to log in.
We set up a SQL database with user information created from the identity package. When users sign up on the website, they are asked which customer profile they fit better—for example, are they part of a utility company, food/agriculture industry, or doing generic research?
Depending on their selection, we only allow users to view this specific report. This is done by only displaying a report that matches the category that they select. Through identity, the user is associated with the category. This category is then compared to the report names, and only the reports in the workspace that contain that category will be displayed.
For clarity, there is one database (PostgreSQL) with data collected and maintained by Athena Intelligence hosted on Azure Virtual Machines. They connect to that database from the Power BI Embedded desktop, run queries to access new data, and create reports to be uploaded to Power BI Embedded workspaces. For more information about connecting Power BI to PostgreSQL, see the documentation at PowerBI.microsoft.com.
A second database (Azure SQL) was built to support user registration and login. Upon user creation, we associate the user with a specified workspace collection so they can see all reports uploaded to that collection.
The data in the reports is only refreshed when another query is run from Power BI Desktop and then reuploaded to Power BI Embedded in Azure.
5. Use Azure SQL and Web Apps to deploy application
Now that we have a working application, we need to deploy the web app to Azure with staging capabilities for easy testing and deployment.
We used the Web Apps feature of Azure App Service with an Azure SQL database instance to support the user authentication/login.
To make ongoing development easy, we created two databases—one for testing and one for production. Accordingly, we created a staging slot for the application that connects to the test database via a slot connection string.
Here’s what this looks like in the Azure dashboard:
This staging slot is connected to a private Git repository. As changes are committed, the developer is able to check them in staging before hitting swap to move the new application into production.
Visit the site in production at http://athena-dashboard.azurewebsites.net.
Staging slots in Azure - slot settings
This blog post by Tim Reilly was written as a generic technical guide for using deployment slots and slot settings to test web apps before pushing to production.
Authentication using identity
It is unnecessary for all of Athena’s customers to see all of the reports in the workspace collection because many of them are from different industries. Because we have separate reports for each industry, row-level security was not applicable in this scenario. Instead, we restricted what users could see by adding in authentication through extra user fields in identity. During the registration process, users are asked which industry they are part of. As a result, as seen in the code below, we limit which of the four reports the user sees based on their industry genre selection when they signed up. If the user is not logged in, they cannot see any reports. If they are signed in, they can see only the reports that match the genre name that they selected.
Final thoughts from Athena
“Thank you, Microsoft, for this. This has opened up things that I couldn’t have imagined six months ago.”
— David Sypnieski, Athena CEO