Powerful, easy up to date reporting on all devices with Power BI

We support a large number of tourist park operators and accommodation providers running NewBook, a cloud based reservation system. We got in touch with the developers last week and asked if we could get API access to a test tenant to see if we could connect NewBook to Power BI. We wanted to create some cool Business Intelligence dashboards with up to date reporting for our customers.

NewBook’s developers responded the same day with the details, and I spent the weekend putting it together. I had a lot of fun getting this working, and I think the end result is so powerful we’ll be offering it as a standalone service.

The architecture of the solution is pretty straightforward, though it took me a few iterations to settle on this one.

New Book Power BI SolutionOutline

The solution works via a .Net console app running on Microsoft Azure. The app pulls up to date booking and revenue information from NewBook and sends it on to Azure Table Storage. Power BI connects to the table storage as a data source. We used Power BI desktop to create some interesting graphs and charts, and published the results to a Power BI dashboard where it can be viewed on all devices.

What does it look like?

Here’s some sample charts that I put together yesterday, though you don’t have to be restricted to the below examples. One of the coolest things about Power BI is its powerful natural language Q&A feature, you can ask it pretty much anything and it’ll build the chart instantly – I’ll add an example of that below.

See a snapshot of your current guests:

Dive into the data to gain more insight. Dashboards link with the reports we published from Power BI Desktop. Clicking on a charts segment will update other charts. Now we can see that 8 of our 36 bookings are here on business, most of them booked direct, and are staying in a permanent cabin or van.

We can also view historical financial and booking data. This can help you get an understanding of your business’ performance over time.

Even better on mobile

Power BI apps are available for mobile devices too, where the charts become even more interactive. Tapping the charts and rotating them by touch unlocks more information.

 

Here’s the natural language Q&A feature. You can ask it questions about your business and it’ll generate a chart that you can pin to your dashboards.

 

Dashboards can be created, annotated and shared amongst your team. You can display them on TV’s, interact with them on phones and tablets or just use them via your PC or laptop.

Interested in Power BI and NewBook?

If you’re interested in Power BI, or if you’d like to get your NewBook reporting set up like this, get in touch with us.

Interested in using Newbook to manage your resort or tourist park property? Find out more here.

In the previous post I outlined connecting our temperature/humidity sensor to a Raspberry Pi and taking a reading from it. The next step is to send that data to the cloud and make use of it in Power BI.

Here’s a basic outline of the process

Connecting the sensor to the Cloud

Of course, Power BI isn’t the only thing you could use this data for, though our goal is to add this temperature reading to the Power BI dashboard in our office.

The first thing I needed to find was somebody who had done this before. A few days ago I found this post by Rakesh George that outlines his process: http://www.identitymine.com/blog/2015/06/19/iot-with-raspberry-pi-azure-and-windows-devices/

His temperature sensor setup is a lot more complicated than mine – it looks like he’s taking the binary values from the sensor and working out the temperature value. In our case, we had a great library from Adafruit that did all of that for us.

Compared to other IoT projects, this one is extremely simple. We’re not making use of Event Hubs or Stream Analytics or any of the high scale, high performance tools in Azure. We’re just sending a temperature and humidity value to Azure Table Storage once a minute, then connecting to that data via Power BI Desktop.

I downloaded Rakesh’s source files to see how it can apply to my setup.

Firstly, you’ll need to install the Azure SDK on the Raspberry Pi to make use of the Azure services, check the linuxruninstructions.txt in Rakesh’s Python source file download for instructions on this.

Most of the code relevant to Azure is located in the azuremodule.py file. I learnt a lot about the Azure Python SDK by reading and modifying this script file.

If you’re setting this up yourself, you’ll need to sign up for Microsoft Azure (free trials are available), create a storage account, and add the storage account name and primary key into the sample code.

After a little bit of tweaking, I was able to get it to work using the Adafruit library to retrieve the values from my sensor and upload it to my Azure Table Storage account every 60 seconds.

Once you’re collecting data, you can use the Power BI Desktop App to pull this data into your dashboards for reporting. As I’m testing it, I created a new dashboard for these temperature/humidity values.

Power BI Desktop can be downloaded from here: https://powerbi.microsoft.com/desktop

A basic version of Power BI is available free, though you can sign up for a 60 day trial of Power BI Pro, which has many of the same features, though it refreshes your dashboards hourly instead of daily.

Importing and preparing the data for Power BI

Click Get Data, choose Azure, Microsoft Azure Table Storage.

Get Data in Power BI

Enter the storage account name, click OK, then enter the primary key.

Connect to Azure Table Storage

Select the table to import.

Connect to data in Power BI

Import the data, and use the query editor to set the data types and separate the columns for humidity and temperature values.

Modify Data in Power BI

Create your Power BI graphs and publish them to Power BI.

Building Graphs in Power BI

Once published, you can access them via the browser or the Power BI apps on mobile devices.

View graphs on Power BI Mobile Devices

This week we’re exploring the capabilities of Microsoft Power BI (an Office 365 add-on) to give us a clear picture of our daily performance.

Power BI allows you to connect multiple data sources from a wide range of on-premise and cloud services and view the live data in a clean dashboard. You can view and share dashboards from the browser, the windows app, or the mobile apps for iOS, Android or Windows Phone. In our case, we wanted the screen in our office to focus on a couple of things: the performance of our support team, and our website statistics.

We’re tracking the support team performance because it’s the core of our business, and the website performance because we’re focusing on delivering more useful content and would like to see how it’s received.

The data we need to track these metrics is stored in external silos – Zendesk and Google Analytics. Luckily, Power BI makes it easy to connect these data sources to a single dashboard.

Here’s a video of it starting up, and a photo of the finished dashboard:

Power BI Dashboard

This setup uses a Raspberry Pi connected to an Azure Virtual Machine running Power BI through the browser.

We’ll be adding new features in the next few weeks  involving additional Raspberry Pis, some connected sensors and Azure SQL. Stay tuned!