I love working with data. I love being able to visualise data in interesting and helpful ways, and I’m happy to share this little gem with you.
This type of analysis is usually performed by utilising the organisation’s corporate GIS. I understand not everyone has access to a full-blown GIS, and not everyone has the skills to use one. But, most people will have the suite of Microsoft applications on their desktops and are already using Excel extensively to help them understand the data better.
Visual is better
Human beings are wired to be visual. It takes a tenth of a second to make sense of a visual scene - 50% of our brains are devoted to visual processing. We understand data (numbers) more quickly and easily in visual format compared to text format.
So, let’s take the Excel analysis one step further and incorporate the spatial element within the data to generate some visualisations that will help us to have a deeper understanding about what the data is telling us.
The Raw Data
The data I’m working with for this blog is fictional – however, it could just have easily been dumped out of Council’s Customer Request system. CRM’s are usually integrated with a property layer and therefore will incorporate spatial coordinates within the data.
My fictional data looks at customer reported defects relating to Stormwater Assets over a 3-year period. I would usually undertake analysis like this to help me understand where there may be issues with the network and to help me make better informed decisions, for example, where best to concentrate the next CCTV inspections on the Stormwater network.
Before any analysis can take place, the raw data needs to be cleaned and standardised and to sort and filter the data, I have created PivotTables.
Heat maps utilising Excel 3D Maps tool
Microsoft 3D Maps for Excel is a three-dimensional (3-D) data visualisation tool that lets you look at information spatially. It is an excellent tool already incorporated in the Microsoft BI suite.
My data includes location, number of CRM’s in each location, and the time/date when the CRM was lodged.
With the data now cleaned and filtered appropriately, it will serve as the source tables for 3D Map.
Highlight the data in the worksheet and insert into 3D Map:
Data Analysis
We gain a better understanding about what the data is telling us when we can see it portrayed geographically in a map. We also gain a better insight when we can see the changes that have occurred over time.
It is relatively easy to pull in other statistical data into your spreadsheet from many other sources. For example, weather statistics data straight off the web could be useful in this analysis to see if failures in the Stormwater network is correlated to particular weather patterns that occurred at the same time.
Sharing
3D Map has an option to export the tours to video format as well as the usual capture screen button, making sharing the information much easier.
I hope you have enjoyed this short blog, and you'll have a go at exploring some of your own data spatially in 3D Map.
Comments