Data Analytics, Power BI and Crime Statistics

2 weeks ago I was moved into an analytics team to help the data analyst render 150 million records of raw unstructured data. I was given an overview of the data, the problems preventing the rendering of the data and how the data is being collected. The data analyst was trying to render all this data using a single view. Over 5 days I built a propagation mechanism or 'data pipeline' that processed the data in a SQL Warehouse and aggregated it to be consumed without having to perform calculations during consumption - problem solved. The issue of waiting and crashing was no longer and the analytics team could now visually represent the data they had been sitting on for a number of months. In that short 2 week period, I briefly learned about MS Power BI - an easy to use graphical desktop application that can turn most data into something visual and rather meaningful.

After working in the back end for a very very long time via SQLCMD and SQLPlus with the aim of designing and optimization database systems, I consider this application a breath of fresh air. I am now able to see the data and now value data for the first time as opposed to valuing the system that prepares data on behalf of a client or customer.

This new application had me curious about its other uses. SO, I went to the SAPS (Police Force) website and downloaded crime statistics for the country. I then flattened the data in Google Docs and brought it into Power BI for the purpose of 'seeing' what was happening in South Africa in relation to crime.

The first screen shot is that of a Treemap diagram which aggregates all crimes over a 10 year period into categories (similar to that of GDP treemap diagram) and for the years of 2005 to 2014. I've made attempted murder and murder a bright red colour.

Oh yeah, and aaaall my camera equipment was stolen out of my car last week, so that crime fits squarely in the 3rd row, 2nd column: I am one of the 1.3million "theft out of or from motor vehicle" statistics. Moving on.

.


The screen shot above displays murders and attempted murders in the same time period.
We can see that the rate drops in 2011 and 2012 and slowly begins to climb again.
It should be noted that the 2010 Soccer World cup took place in South Africa in 2010 and our economy was quite resilient in and after the 2008 recession.