Welcome to the Iamdata Solutions Asset Management Monthly Newsletter - June 2022
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 done 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 Microsoft applications suite on their desktops and are already using Excel extensively to help them understand their data better.
Now, I have to admit that I have a love-hate relationship with Excel - lets be clear, Excel is NOT a database and it cannot replace a GIS, but it can serve as a great analysis tool.
Utilising Excel 3D Maps tool
Microsoft 3D Maps for Excel is a three-dimensional (3-D) data visualization tool that lets you look at information spatially.
The information includes location, and number of CRM’s in each location and the time/date when the CRM was lodged.
Visual is better
Human beings are wired to be visual. It takes a tenth of a second to make sense of a visual scene - probably because 50% of our brains are devoted to visual processing. Humans can understand data (numbers) more quickly and easily in visual format compared to text format. So, let’s take the 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.
Raw data
I'm using a dummy dataset for this demo but it could just have easily been exported out of your Council’s Customer Request system. CRM’s are usually integrated with a property and therefore will incorporate spatial coordinates or an address within the data. You need some sort of location information otherwise you won't be able to map it with 3D Map.
My dummy data looks at customer reported defects relating to Council’s 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, as always, you should spend some time cleaning and preparing the raw data.
Structure your data
To prepare your data, make sure all of the data is in Excel table format, where each row represents a unique record. The top row is your column headings and these should be text, and the data under the header columns so that 3D Maps will interpret it correctly when it plots the geographic coordinates. Using meaningful labels also adds value to the category fields when you design your tour in the 3D Maps Tour Editor pane.
To use a table structure that more accurately represents time and geography inside 3D Maps, you'll need to include all of the data in the table rows and use descriptive text labels in the column headings:
Include geographic values in your table. This could be:
Latitude/Longitude
City
Country/Region
Zip code/Postal code
State/Province
Address
Include date or time fields. 3D Maps requires at least one date or time field per row of data if you want to view your data over time.
With the data now cleaned and filtered appropriately, it will serve as the source tables for 3D Map.
Highlight the data and click Insert 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.
Stacked Column Chart
Clustered Column Chart
Heat Map
Bubble Chart
3D Map Tour - Tells a Story about our Stormwater Assets over time
You can apply designs and apply different scene options to pan in and out, or circle while the scene is playing.
Sharing
3D Map has an option to export the tours to video format, telling the story of the data and sharing the information is made much easier.
I'll be covering 3D Maps in more detail in future newsletters - watch this space!
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining the GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email me at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Comentários