Using Azure, One Drive and PowerBI Pro to get a simple, low cost and auto-updating dashboard up and running

Introduction

Based on empirical evidence it would appear most SMEs in Asia could do more to build a coherent, comprehensive data strategy. More work can also be done towards implementing adequate Business Intelligence (BI) tools. As PE managers, we are usually limited to receiving reports by way of xls or ppt attachments in email and further, even the CXO team is often working off the same framework.

I believe effective BI tools can make a remarkable difference to the quality of decision making, not just for PE managers but also for the CXO team and other senior leadership functionaries at these various SMEs.

A usual cause for hesitancy is cost and I want to show here how one can create a simple data backbone for under 5 USD per month.

PowerBI costs are additional on that and start at US$ 9.9/ month/ user but if the corporate is using Office 365 licenses, PowerBI is included with the higher tiers of Office 365 (specifically, with plan E5 which is as of this writing US$ 57/ month).

Given this, I for one believe there is no excuse for firms not moving to BI platforms. I won’t be talking of the advantages of PowerBI and the wealth of analytical, no-code tools it provides (far exceeding excel in simplicity, visual clarity and data protection). That conversation I reserve for another article — this one is purely technical and intended for folks wishing to quickly throw together a low cost BI dashboarding solution.

Imagine an interactive, auto updating dash like the one here instead of stale xls and ppt files.

 

 

Image courtesy of https://www.udemy.com/course/microsoft-power-bi-up-running-with-power-bi-desktop/

Here’s a small video I made that shows off the interactivity of said dashboard — imagine moving all your BI needs from static xls and ppt to this!

Dashboard courtesy of https://www.udemy.com/course/microsoft-power-bi-up-running-with-power-bi-desktop/

 

With all that said, let’s dive right into it!

Setting Up Azure and PowerBI Accounts

Azure setup: start here to setup an azure account. Microsoft gives you a credit of US$ 200 valid for 30 days to test out almost any service offering.

 

 

 

PowerBI: Firstly download PowerBI desktop here — sadly windows x86 only for now but I usually run this in Parallels on my mac with no issues (recommended at least 8 GB RAM — if in Parallels that means 8 GB allocated to the VM, not 8 GB in total).

Secondly, create a PowerBI service trial here. Once you open PowerBI desktop, link the PowerBI service username with the login button on the top right of the title bar.

 

 

With all the initial setup out of the way, let’s roll up our sleeves and get cracking with the actual setup.

Note that you must clearly define the database tables and make sure to save the csv files in the same schema.

It’s a good idea to plan ahead — decide what the goals are. What do you need to monitor/ present? What data is available? Can you obtain said data regularly? If not, the whole point of automation is moot.

If the organisation already has some sort of ERP/ CRM in place, I suggest initially not trying to link the two projects as that is likely to need professional coders. Instead try to get the database team to dump required tables in csv format to a particular one drive folder (or even mail it to you) once a day/ week as needed.

While outline key steps below, I won’t mention the code used or detail the technical selections to be made — all that would make this a very confused and unwieldy read. I’ve written another medium article with the technical details and will link it below.

Azure Setup

For a small dataset (say <1GB), we choose a Basic Azure SQL (5 DTU) — this should be a total cost of US$ 4.99 per month. Basic Azure SQL databases are not particularly fast and don’t do well under repeated queries but this is for an SME so we should be fine. If not, we can change the database tier on the fly later (for higher charges of course — the next tier which Azure reckons is good enough for production use is US$ 15/ month). A simple tutorial to do all this is here.

 

 

Next, create an Azure Storage Account and then within it an Azure blob container and upload the initial csv files into the container. Tutorial here — note that you may need to first create a storage container and the tutorial to do that is in the link above.. Again, referencing code from my other article, you can initialise the database with the csv files.

 

 

Now to automate monitoring of the one drive folder where we will save our update csv files, we use Azure Logic Apps.

I prefer to use two discrete Logic Apps to aid fault tolerance. One to monitor the One Drive folder and upload files into the Azure blob container. I’ll skip the details but again the code and guide with screenshots is in my other medium article linked below.

The other Logic App will monitor the blob and once a csv file is uploaded there, it will insert that data in the SQL database.

PowerBI Setup

Once all this is done, in PowerBI Desktop, choose Get Data and then select Azure SQL Database, enter the credentials per the database setup process and viola! All your tables should now be in PowerBI. It’s really not much more difficult than excel and even if this is to be outsourced, the whole project doesnt take more than half a day of work and should be very cheap to outsource.

Again beyond the scope of this article to talk of how to create a simple dash in PowerBI but there are plenty of excellent tutorials around (highly recommend this one) and it’s really no more complex than creating an excel report with v/hlookup and pivot tables.

Once the local dashboard file is ready in PowerBI Desktop, it’s a push of a button to upload it to the PowerBI service and from there one can easily setup a scheduled refresh for the dash and share it with team members (note that all team members must be on the same email domain). Again all details in the other article linked below.

Summary

Hopefully the article above demonstrates (a) how easy it is to setup an automated BI dashboard, and (b) how cheap it can be for small scale use cases.

In my view there really is no excuse for firms to avoid getting on this bandwagon! Particularly for PE firms that usually struggle with delayed reports, this sort of simple dashboard should greatly aid effective monitoring.

If you need more convincing, let Microsoft help you

     Tushar Sinha