Why your school NEEDS to try PowerBI (even if it’s just a little bit)

This week I’ve been experimenting with data sets in PowerBI to try and identify a range of schools in New Zealand. In former roles I’ve managed a BI team that has done considerable work with PowerBI but I have not been the primary constructor of dashboards and manipulating data sets. I say this because I want to emphasize how relatively easy it is to quickly build some useful visualizations for interacting.

In my case, I grabbed some publicly available data on NZ schools and downloaded it as a CSV file (Excel would be fine as well) and then used one of the many  data import options into PowerBI Desktop to “Get Data”:

There is a huge range of options for importing data into PowerBI

The nice part about the Get Data process is that, if the import file is formatted or separated in anyway, then the data is automatically placed into columns during the preview stage of the import:

School details are split into columns based on the comma separation in the CSV file

What PowerBI can’t do, of course, is easily identify or label the columns based on the data within them. This is important as otherwise when you start creating the visualisations you won’t easily know what the data is, they will simply be labeled “Column 1”, “Column 2” etc. Fortunately, it’s very easy to rename these:

A logical name for this column would be “School Name”

With the data now in PowerBI, the fun part begins – visualizing it and creating slicers for easy selection and drill down into the data you’re wanting to find. Here’s a dashboard I created with the above data, showing Christchurch secondary schools:

A quick PowerBI Dashboard utilizing publicly available data

There are a few things to note about the above:

• Schools are represented geographically and in different colours and sizes on the map.
  • Helpfully, the data from the website included the exact latitude and longitude of the schools, which PowerBI will easily use to accurately map. Without this, you can use suburb, postal code or street address (maps are powered by MS Bing Maps, which can struggle with smaller countries like NZ for accuracy)
  • The bubbles for each school is proportionate to the school roll size. Again, this data came with the CSV so I was able to use a visual filter to represent the number of students in each school
• I’ve created slicers on the right hand side based off the various data in the columns I imported from the CSV. In this case it allows the user to filter by:
  • School Name – if they’re looking for a specific school it will locate it by searching or scrolling through the list (I set the filter to allow multi-select so schools can be “compared” if required
  • Region – if a user wanted to see all schools in a certain region they could select this. The above screenshot is set to Christchurch
  • Decile – if a user wants to filter/locate schools based off a single Decile or a range, they can multi-select and only matching Decile schools will be displayed
  • School Type – again, a user may want to be looking at only Primary or Secondary schools, possibly Independent or Integrated – the School Type filter allows for this.
• I’ve used a matrix visualization to create a table in the bottom left that shows the schools matching the filtered criteria and includes useful information like the School Name, Principal’s Name, Region etc. This could be extended to include any field from the imported columns such as school website, phone number etc.

It took only about 30 minutes to create the above and then I decided to throw some Tertiary information in as well. The available list was a bit less detailed, but nevertheless I was able to create a quick selector for the various tertiary institutions:

A map, slicer for tertiary type and matrix table for NZ Tertiary Institutions

The last thing to do is then publish it to PowerBI.com. With the built in PowerBI Connector Gateway this is as easy as pushing a button:

Selecting “Publish” in the top right hand corner will push this report to http://www.PowerBI.com where it can then be shared with others in your organisation

This is a high level overview of a pretty basic data set, but it shows that schools can grab data they already have (academic results, student home addresses etc) and start interacting with it. I’m planning to do more with this report above by getting internal Office365 usage data and start analyzing which schools are using / not using O365.

There are a few “gotchas” when creating reports in PowerBI that I’ll write about later, but the aim of this one is to inspire schools to give it a go.