PowerBI, Logic Apps and Azure SQL Database

Keeping business users, IT and Senior Management happy for your next BI project...

PowerBI, Logic Apps and Azure SQL Database

Yes it’s a big title, but this blog example encompasses several elements of interest. I want to demonstrate how DSP-Explorer can take a specific business requirement and seamlessly encapsulate the technical elements around it to provide a solution that is scalable and manageable, and meets the needs of a number of stakeholders.

Business Requirement

Upload Data to Sharepoint, Visualise in PowerBI, and Share with the Team

Below we can see our visualisation: a depiction of real estate data of NYC. Imagine a team of people working with this data, wanting to visualise it quickly and share it with colleagues and prospects. 

The simple requirement may look straight forward but diving into the detail uncovers a few challenges. PowerBI, although integrated with Sharepoint, can’t really use it as a persistent data source and it’s not designed to be a persistent data source. Therefore…we shouldn’t use it as one! We need a database.

So if we use a database we then flip into another challenge. The business requirement has no interest in building, managing, and maintaining a database. Also, how are business users going to load data into a database? This might be an issue. They could send DBAs the data, or put it on a share, or push it through SSIS…all viable options, but let’s go lightweight.

I’m going to use Azure SQL Database for data persistence, as it combines all the excellent features of SQL with a flexible, low-cost platform. It’s a no brainer. 

My Azure SQL Database for this demo came in at the whopping price of £10 per month for 2 to 4 vCPUs and 80GB of storage. That itself is probably overcooking it, as you can see from the estimate within the portal.

We now have a light-touch data persistent platform which is good; it’s cheap but we need to get data into it, and more importantly we need business users to get data into it.

Azure Logic Apps

Azure Logic Apps allows you to automate technical workflows and integrations. In this example I’m going to use it to get data from Sharepoint into SQL Azure Database. SQL Azure Database can ingest data from Azure Blob storage while Azure Logic Apps can be the glue between the elements.

The great thing about Logic Apps is that the workflow between the various elements is visualised. In this example we:

• Detect a file being loaded in Sharepoint
• Automatically copy that data to Azure Blob storage
• Execute an ingest procedure into Azure SQL Database

On uploading to Sharepoint the Logic Apps kick in…checking back into the visualisation, we see more data has appeared.

The final element is sharing this new visualisation with the team, and again Azure Logic Apps can be useful to us. I can simply extend the workflow to undertake two parallel elements; a Slack notification and an email.

Loading an additional file into Sharepoint then not only updates the visualisation, it also drops out the new notifications.

Slack

Email

Simple, Secure, Solved.

So here we can see how PowerBI can provide rich visualisations; Logic Apps can help with integration and simplification of processes; and finally Azure SQL Database can provide a low-cost persistent enterprise data platform. 

The key thing here is that we’ve created a solution that has no VMs, involves less than 2 lines of code, and doesn’t require business users to operate in new ways that they have no interest or desire to undertake. They continue to upload to Sharepoint, data is automatically ingested and visualised, and most importantly it’s secure.

It’s a light-touch solution for all involved but will satisfy the needs of users (keep it simple), the needs of IT (keep our data secure) and the needs of management (reduce cost, deliver it yesterday). 

If you would like to hear how DSP-Explorer can provide scalable, manageable, and cost-effective solutions for your business, please get in touch today by emailing enquiries@dsp.co.uk, or give us a call.

To hear more about our Data Intelligent solutions, check out another blog post produced by our dedicated Machine Learning team on Portable Sentiment Analysis within Azure Cognitive Services.