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
- [Microsoft Power BI Embedded](https://azure.microsoft.com/en-us/services/power-bi-embedded/)
- [Power BI Desktop](https://powerbi.microsoft.com/en-us/desktop/)
[SureSpot Inc.](http://www.surespot.com/), 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.
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.
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.
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 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](https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-rls/).
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.
### 4. Set up Power BI Embedded workspace in Azure
We started by following the instructions in the [Get started with Power BI Embedded sample](https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-get-started-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](https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/) 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:
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:
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`:
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.
