Transforming business decision-making with Power BI

What if you have a wealth of data but it’s all coming from disparate sources, and the process of extracting any meaningful insight is arduous? You’re losing critical time and money and left unable to make informed business decisions. The Institute of Quarrying (IQ) was struggling with exactly this issue.

The IQ has been supporting people working in the mineral products industry for more than a century. It’s a professional body providing accreditation, development and support to over 5,000 members of the mineral extraction industry. And while the organisation generates huge amounts of data from a wide range of sources, the team was struggling to access and analyse it in a meaningful and effective way.

So they enlisted PTR to help transform their global data management system.

Background

Sarah Fry, Head of Membership and Marketing at IQ, explains:

“Thanks to the pandemic and a major server issue, we were suddenly thrown in at the deep end and had to switch to a cloud-based solution without much preparation. We got up and running but realised we didn’t have the knowledge in-house to get the most out of the Microsoft 365 platform. I was also on a long-term mission to rid the business of painful spreadsheets. We had lots of data in different places but we were struggling to get visibility of that data and had no view of the whole picture.

“I started investigating options and discovered Power BI, which sounded as though it might provide a hybrid solution - the ability to connect all our data sources together and produce the type of dashboard reporting we lacked, without the need for an additional CRM solution. So we started exploring options and quickly came across PTR.”

Key issues

The IQ provides a variety of services for members around the world, which in turn generates a complex web of data stored in multiple different applications and formats. While some applications already provided in-house reporting functionality, their use was limited and lacked the ability to report across multiple data sources. The IQ needed to collate its disparate systems into an easily accessible single source of truth.

Requirements:

● The ability to link member data across different business applications to get a full membership view.

● Evidence of the effectiveness of Power BI reporting to the wider business, to secure buy-in for further development and data integration.

● A robust, scalable solution with little technical support.

Objectives:

● Prove that key data sources could be loaded and combined to create a reliable ‘single source of truth’ across the business.

● Provide visibility of Key Performance Indicators, such as member engagement and retention.

● Provide richer, interactive and compelling reports.

● Ensure quick and easy access to reports and dashboards.

The solution

PTR worked closely with the Institute of Quarrying to fully understand their business processes and challenges, and identify key data sources with which to create a proof of concept dashboard. This had to be produced in a short period of time yet provide tangible benefits to the organisation. So PTR and IQ worked together to define key business terms and identify KPIs to feature in visualisations on the dashboard.

PTR then developed a semantic model within Power BI that created relationships between the different sources, quickly identifying areas where membership data was inconsistent across platforms, and highlighting the potential for data quality improvements.

This provided crucial evidence to the business, demonstrating the clear benefits of a bespoke Business Intelligence platform.

PTR then helped IQ to fully implement Power BI, and developed a structured learning programme on the wider Microsoft 365 platform for the whole IQ team - to ensure everyone’s knowledge of the tools was consistent across the board.

Sarah Fry explains more:

“We can now get an accurate snapshot of data whenever we need it. And the dashboards are visually appealing - the data is presented in such a way that you actually want to engage with it. We can now slice and dice data from different sources and clearly see relationships that we just couldn't see before. We can also see how individuals relate to larger groups, and how they fit within groups - drilling down into geography or membership type, for example.

“We’re now able to share insights with interested stakeholders and we have insight fully driving our decision-making, rather than making assumptions about what we think is going on. The business is much more effective now that we have an accurate picture.”

What we delivered:

● Proof of concept evidence for the business case.

● A collaborative solution that utilised and maximised existing tools, preventing unnecessary supplementary investment.

● Real-time, 24-7, self-service access to data across the organisation.

● A single consolidated view of data and a full 360 view of member engagement.

● An empowered team with the skills and knowledge to take control of data projects.

How we achieved it:

● We took an initial selection of key business applications, serving as proof of connectivity via Power BI (and avoiding the need for an enterprise Data Warehouse).

● We developed a Semantic Analysis Model within Power BI, enabling comparison of data across different applications.

● We created an automated refresh process to maintain data currency and quality.

● We built a membership view dashboard (hosted on the Power BI web service) to present the modelled data using clear, dynamic visuals.

● We developed and delivered a bespoke structured Microsoft 365.

Why PTR?

Head of Membership and Marketing Sarah Fry concludes:

“Not many other suppliers could demonstrate PTR’s level of credibility in Microsoft 365 and Power BI. Keith was also extremely empathetic and really listened to our concerns and challenges. He was the first person to identify that a lot of our issues stemmed from a lack of expertise and training. PTR helped the team acquire the skills we needed to manage our own data in-house, using the tools we already had at our disposal. Keith was the first consultant we met who was an advocate of upskilling and empowering rather than outsourcing.

“What I really liked about working with PTR is that they’re flexible - they tailor all their support to our specific needs. They fully understood that we’re a really small team with no dedicated in-house IT resource. And they worked around the fact that we needed to complete training in shorter segments, because of the way our business works.

“They don’t blind clients with science - they’re honest, transparent and supportive, and take a very simple, practical approach without being patronising. Data management for us is an ongoing evolution, as the needs and priorities of the business continually change. When we need specialist advice or support - particularly around Power BI - PTR are always our go-to experts. But for day-to-day projects in the 365 environment we are all now much more confident, thanks to PTR’s training.”

Data Sources

● In-House MySQL Membership Database

● Hubspot CRM System API

● Excel

● Moodle Learning Management System API

Technology

● Power BI DataFlow for Scheduled Data Loads

● Power BI DataSets for Modelling Data

● Power BI Self Service Business Intelligence and Reporting

Key Benefits

1. The connectivity of different data sources provides a single source of truth for the whole organization & highlights data inconsistencies across platforms.

2. Semantic Model provides a universally accepted set of definitions of business logic, hierarchies, dimensions & relationships.

3. Power BI provides a one-stop shop for organization-wide, real-time, self-service Business Intelligence from any device, at any time, anywhere.

4. Rich, interactive visuals engage interest and drive greater insight, with the ability to drill in and out of the details and see connections in the data.