12 KiB
| layout | title | author | author-link | date | categories | color | image | excerpt | verticals | language | geolocation | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| post | Using Power BI to provide reports for SureSpot's personal parking assistant | Paul DeCarlo | https://twitter.com/pjdecarlo | 2016-11-21 |
|
blue | images/2016-10-27-surespot/feat_App.PNG | Microsoft teamed up with SureSpot Inc. to bring Power BI reports to customers using its online parking reservation service. |
|
|
|
We set out to bring Power BI Embedded reports into SureSpot's online parking reservation service. SureSpot allows customers to employ automated reservation systems into existing parking lots using on-site hardware that connects to Microsoft Azure. This hardware consists of a camera system that tracks available spaces in real time, a camera that scans incoming license plates, and a kiosk system that runs a Visual Basic .NET (VB.NET) application. The kiosk allows end users to pay for parking in person or reserve spaces ahead of time via web and mobile applications for Android and iOS. In addition, the kiosk allows operators to control gates and other aspects of the system remotely.
Our solution required the creation of a Management dashboard for internal users and a secure reporting system for customers of SureSpot hardware installations. This involved the creation of two Power BI Embedded reports that access data provided by an Azure SQL Database that is fed by the on-site hardware. These reports were later integrated into an existing ASP.NET web application that runs on a virtual machine in Azure.
Key technologies used
Core team
- Omri Shafran – CEO, Surespot Inc.
- Diego Rodriguez – Lead Developer, SureSpot Inc.
- Jesus Romero – Lead Developer, SureSpot Inc.
- Paul DeCarlo – Senior Technical Evangelist, Microsoft
- Mostafa Elzoghbi – Senior Technical Evangelist, Microsoft
Customer profile
SureSpot Inc., founded by Omri Shafran in 2013, brings parking into the future. From stadiums, airports, amusement parks, and malls to public parking garages, there are millions of parking spots around the country. The current model for the parking industry is antiquated and highly inefficient. Unlike nearly every other transportation system, there is little pre-booking available for parking lots. There may also be no way for drivers to pay before their arrival.
With the new SureSpot Terminal and Online Booking System, drivers will be able to reserve parking spots in multiple ways. The SureSpot Terminal Software will be able to show drivers a real-time parking spot inventory for them to choose what's most convenient. Drivers then simply arrive at the SureSpot Terminal, pay for a parking spot, and print out their ticket, or they can book online directly from their home PC, tablet, or smartphone! All tickets contain directions to the parking spot. For pre-booked tickets, the SureSpot Terminal automatically scans the receipt, be it from paper or from smartphone. Tickets also double as advertisements and coupons for mall shops, nearby restaurants, national brands, or nearly anything else.
Problem statement
SureSpot currently serves customers across the United States. With customers in every region, their data grows at a rate proportional to active locales. Stakeholders wish to view information within this data in an interactive online dashboard that allows filtration by geographic location. This can allow internal users at SureSpot a way to view the health of their operations by locale and allow for an easy way to assess business performance in real time.
In addition, SureSpot customers should be able to view similar information pertaining to their installed locales by allowing this data to be accessed securely in their administration panel. In this way, a customer can view the performance of SureSpot services as it relates to their business. This would allow the customer to assess the value of adopting SureSpot services in a quantifiable format. It would also assist the customer by offering insights such as customer traffic patterns, earned revenues, and active system issues.
Solutions, steps, and delivery
1. Access the Azure SQL datasource
The SureSpot parking system relies on an ASP.NET web application running in a virtual machine on Azure. An associated API service contributes data from SureSpot hardware into an Azure SQL Database backend. Here is a depiction of the architecture employed.
This required working together with the SureSpot development team to create a read-only user on their existing Azure SQL Database to allow importing their data into Power BI Desktop for report development. The IP address of the development machine was required to be whitelisted in the firewall before we could successfully authenticate.
2. Create the internal report
Our requirements asked us to directly convert an existing dashboard into an interactive Power BI report. The existing admin panel can be seen here.
We accomplished this by reviewing the ASP.NET source code for the existing dashboard to identify the associated entities used in their data layer and map similar relations within Power BI. This is very useful as the entire entity model is rather large and complex. A portion of the model zoomed out can be seen here.
We brought in some of the tables as-is but needed to implement relations that were created in code between some of these tables. Setting up the associations in Power BI required manually managing relationships between tables with shared keys. Our much smaller slice of the data model can be seen here.
We then set out to populate the dashboard with appropriate controls. Our finished internal report for SureSpot managers can be seen here.
3. Create the customer facing report
Our process followed the steps discussed in the creation of the internal report. Our resulting model can be seen here.
We needed to ensure users were only allowed access to the tables they had permissions for. This meant we needed to enforce row-level security on our data. We were able to do this by assigning a username column to a role of "Manager" by following the Power BI Embedded RLS documentation.
Note our use of the Dax Expression [UserName] = USERNAME(), which will be used later as a parameter when embedding the report into the ASP.NET frontend.
The final report can be seen here.
4. Set up Power BI Embedded workspace in Azure
We started by following the instructions in the Get started with Power BI Embedded sample. This will walk 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 ProvisionSample project contained in the instructions.
Due to our use of Direct Query in our reports, we also needed to configure the database connection for our uploaded dataset. We did this by selecting Option 7 - "Update connection string info for an existing dataset" in the ProvisionSample. We provided this option with a connection string in the format:
`Data Source=xxx.database.windows.net;Initial Catalog=xxx;User ID=xxx;Password=xxx!;`
5. Embed report into existing ASP.NET application
We began by setting up a new PowerBIController within the existing ASP.NET application. Through this controller, we are able to limit access to the SureSpot internal report by using an annotation of AdministratorTypeFilter. In code, we create an embed token using:
`var embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id);`
This allows us to view the embedded report within the existing website.
The customer report requires that we request an embedToken which restricts access only to tables related to the current user. This is done by calling an overload for CreateReportEmbedToken:
`var embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id, username, roles);`
Note that the username parameter will be applied to the Dax Expression mentioned in Step 3.
This restricts the information in the embedded report to data that belongs to the current user.
End customer example
SureSpot Power BI demonstration
SureSpot Power BI with RLS demonstration
Conclusion and general lessons
Implementing the Power BI SDK into existing ASP.NET site
Bringing embedded reports into our existing ASP.NET site required the addition of the Microsoft.PowerBI.Api NuGet package.
This package brings in a few typescript files that cause Visual Studio to enable the typescript compiler during installation. These files had to be manually removed from the project file to proceed with a successful build. This package also required upgrading Microsoft.AspNet.Mvc to version 5.2.3 and Microsoft.AspNet.Razor to version 3.2.3.
After installation, we had to manually modify all references to these assemblies in web.config with new minimum versions. Examples:
In Views\web.config
`<host factoryType="System.Web.Mvc.MvcWebRazorHostFactory, System.Web.Mvc, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />`
Must be changed to:
`<host factoryType="System.Web.Mvc.MvcWebRazorHostFactory, System.Web.Mvc, Version=5.2.3.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>`
In \web.config
`<add key="webpages:Version" value="2.0.0.0" />`
Must be changed to:
`<add key="webpages:Version" value="3.0.0.0" />`
Additional resources
