Exploring public data with Power BI – abandoned mines in Ontario

Power BI is Microsoft’s data exploration and dashboarding tool. While it hasn’t risen to desktop prominence like Excel and Outlook have for the majority of knowledge workers, it is an incredibly capable tool which allows you to quickly visualize data from a number of data sources and explore the data using a graphical interface.

Today we’re going to take a look at some publicly available data: abandoned mines and mining hazards in Northern Ontario, using Power BI.

Our dataset: the Abandoned Mines Information System

In order to help track the environmental and public safety hazards created by abandoned mines, the Government of Ontario created a database containing all the locations of abandoned mines, the features and hazards on site, and the safety threat posed by each mine site. This information is stored in the Abandoned Mines Information System and can be downloaded from the Ministry of Energy, Northern Development and Mines.

Once you download the data you’ll find that the archive contains two Excel files, one which is an index of all the mine sites, and one which lists the features at each of the mine sites. You can use the AMIS SITE ID to map the features to the mine sites.

Since the information is in Excel files, loading the data into Power BI is simple. All you need to do is to import the two Excel files and Power BI will automatically determine that the two tables can be joined together using the AMIS SITE ID field.

Choosing appropriate visualizations

Once we’ve acquired the data and loaded it into Power BI, the next step is to determine what visualizations we want to use to display the data. This is the most important step of the dashboard building process since we are determining what information the users will see and, by extension, how they interpret it.

Since this is primarily spatial data, a map is the best form of representation. From there, we need to decide what additional attributes in the data are important. I settled on the feature description (a free-form text field describing the feature), class description (a broad category for features), and hazard status (how dangerous the feature is) as the primary fields. To support data exploration, I added a drop down menu so that users can filter by the feature category and class.

One of the great features of Power BI is that you can also filter the data by clicking on graphs. While I added the class description and hazard status as pie charts, you can filter the dashboard by clicking on the segments of the pie. This allows you to avoid having a large number of dropdown filters since the charts and other graphical elements can do double duty.

The finished dashboard

Once we’ve finished, we get a neat little dashboard which shows the location of abandoned mines in northern Ontario. Users can use the dropdown filters to explore the buildings by type, and the pie charts to filter based on the danger posed by each structure. This gives us a quick and intuitive overview of the information and helps us identify additional questions for further investigation.

From here, we might want to look at either dangerous or environmentally disturbed locations which are close to major population centres, or to determine areas which have higher concentrations of structures that need remediation. Either way, this high level summary provides the data to allow us to ask those questions and begin to dig deeper.

Need help with understanding your data or presenting it in an intuitive manner? Use the contact form to reach out to me and I’d be happy to help!


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *