Welcome to the first Iamdata Solutions Asset Management Newsletter - January 2022
I have to admit, I do get asked this quite a lot. When I first started the business, iamdata.solutions, the name seemed such a good idea! A catchy name that lets our potential clients know what we are about.....? Or perhaps not!?
It's too late to change our name now, but as this is our first Newsletter, I guess this is the time to talk about some of our past Asset Management projects and the many ways we have helped Councils in Australia.
Having worked in the Asset Management sector for over 20 Years, I have a wealth of experience and knowledge I want to share to help Asset Mangers optimise asset performance to meet residents and other stakeholders’ expectations in the most efficient and cost effective way.
For more examples of past project we have delivered, please check out our Past Projects Page
Power BI Report Development
View all your asset data, discover insights through data analysis and analytics via Power BI. There is no need to login into your Asset Management system, and other corporate systems to search for your information. You can view it all in real-time via Power BI.
Power BI is a great tool to analyse and visualise your data. It makes it possible for you to understand thousands of rows of data by visualising your data in various charts, graphs, and tables. Data Analysis and Data Analytics are very important components in the management of your Infrastructure Assets. With good information, you can manage your Assets efficiently and cost effectively.
View the data in real-time
When Power BI is connected to live data via one of the many built-in data connections, consumers of the reports will be able to view live dashboards with real time updates. As data gets processed via the many different corporate databases, such as Finance & Property, Asset Management System, Customer Request Management system etc, and Power BI is connected to that data source, the reports and dashboards will reflect those updates and changes after each refresh. This is a great timesaver, and the consumer of the data can be confident that the data is current.
NOTE: Power BI works perfectly with static data sources such as Excel, but we must remember that any dashboards or reports created from a static data source will just be a snapshot in time, not a living document.
Power BI dashboards are dynamic, I can set the report up so that if I click on an element in one visual, then the other graphs also focus on that element, providing me with information across the graphs, charts, and tables for the element that has been selected.
Power BI Reports are great for sharing information across your organisation. Once the report is published, you can share the report with anyone within your organisation and allocate specific viewing or editing rights.
Our Role:
We have developed many Power BI Reports for Councils across Australia. There are many connection types available in Power BI, which means that we can connect the reports directly to Council’s databases ensuring that the data displayed in the various reports are up to date. Some of the reports we’ve developed for our clients include:
1. Asset Financial information
2. Infrastructure Maintenance Information
3. Data Verification and Validation Checks Power BI Reports similar to this:
GIS and Asset Data Cleansing
You could have the best IT System in the world, but if the data that is powering it is incomplete, out of date, or incorrect, the system becomes worthless. Your organisation must have confidence in your data, otherwise how can you trust any analysis or reporting that comes from it and how can it be correctly informing your organisation?
The Asset management systems (and any other corporate systems) can only provide you with insights to help you make well informed decisions about the assets, if the data is in good order.
You’ve probably seen this cartoon dozens of times across the internet – but, it still makes me smile, although somewhat ironically. Of course, we all want our data to be in pristine condition, but it can seem like such a big and overwhelming task that you may not know where to begin.
Our Role – Example 1
Data cleansing is an essential part of managing and maintaining your Asset Management systems and GIS. We have been able to help our clients by identifying areas of concern in many different types of datasets.
We look at the construction, arrangement, formatting, spellings, duplication, extra spaces, as well as issues with the actual data, such as out of date data or missing data. We undertake many different validation and verification steps using an assortment of different tools, depending on the types of data we are cleansing, whether it be attribute data or spatial data.
For example, you would want to extract the data from the system and immediately begin building your analysis / reporting / visualisation. However, when the data is bad you could spend up to 80% of the time cleansing and manipulating the data when the actual analysis only takes 20% of your time. And, of course, while this is happening time and time again, the issues in the core data within the system are not being addressed or corrected.
Another example of where data issues may arise is where a client has two datasets from two separate sources that needs to be consolidate into one dataset. Data can become disparate with missing, incorrect, and out of data information as the result of this consolidation and sometimes these types of errors are not identified and do not get fixed at the time of consolidation.
Our Role – Example 2
We recommend that spatial data be managed within a database. Some Geographical Information Systems do have their own geodatabase, but there are still some GIS that do not. For example, MapInfo. We have helped many Councils move from TAB files to building a GIS database utilising Microsoft SQL Server.
What is Spatial Data in SQL Server?
Spatial data relates to the points, lines, and polygons that is a geographic representation of a feature on the Earth. In our examples, we have chosen Line (roads, footpaths, etc), Polygon (property, buffers, etc) and Point data (defects, etc). Spatial data is stored as coordinates and topology which allows the data to be mapped. SQL Server supports geometry data and geography data. We recommend using the geometry configuration. The difference being:
· Geometry – Stores the X and Y coordinates that represent lines, points, or polygons.
· Geography – Stores the latitude and longitude coordinates that represent lines, points, or polygons.
Process
Organisations like Councils collect, manage, and share enormous amounts of data and usually the data is being held within bespoke systems, such as Property & Rating, Customer Request System, Asset Management System – all of which are usually Microsoft SQL Server databases. Every operation that a Council is engaged in doing has a spatial element and if the spatial data is also being managed and maintained within a SQL database, it is relatively easy to incorporate spatial data into the mix to provide the additional benefit of location intelligence.
Microsoft SQL Server handles spatial data just as easily as any other data type. The benefits of using SQL Server to manage your spatial data includes:
All Spatial Data are stored in the one point of truth, safe and secure within a database
Dataflow Scripting provides the ability to perform complex data transformations very simply
Dataflow Tasks to easily identify the data-source and the data-destination, SQL scripts to replace current MapBasic scripts, set-up data viewer to view the data flowing through the SQL SSIS scripts – helps with checking and de-bugging.
Package Configuration – ensure production quality packages with good deployment practices.
Control Flow Tasks – helps to define workflows
Web-service Tasks – e.g. ability for the organisation to consume publicly available data e.g. demographics data to enable us to perform spatial analysis with our corporate data e.g. determining the best area to build a playground, determining where Council requires wider footpaths to accommodate wheelchairs use, etc.
Ability to set up Checkpoints in each step of the SSIS Workflow to help maintain consistency of the data, especially useful when managing large quantities of data.
Logging – Error Monitoring – able to record and gather information about the error to enable us to easily identify the problem and fix it.
Other Benefits to the Organisation
Other benefits that will be gained through implementing this model will include the on-going improvements to the attribute and spatial data. It will become easier to identify areas where attribute data and spatial data are not matching. A SQL script providing the relevant error information is the first step to being able to fix any issue. Automated scripts can be set-up to create email alerts that will inform us immediately if a package or transformation has failed.
It is also true, that when the organisation starts using the ‘one-source of truth’, any errors identified can be rectified at the source, and the correction will automatically flow through to other applications. By definition, this model promotes more accurate data.
Develop Infrastructure Spatial Data
Our Role
Create a digitised spatial layer of Council's Traffic Control Devices from Aerial Photography utilising on-screen digitising method of tracing geographic features from aerial photography directly on the computer screen. The spatial dataset was then merged with Council’s Traffic Control Devices Excel spreadsheet to create a complete Traffic Control Devices spatial dataset.
Create a digitised spatial Layer from aerial photography containing Traffic Control Devices located within the Council area.
Methodology
Utilising on-screen digitising method of tracing geographic features from aerial photography directly on the computer screen. The spatial dataset was then merged with Council’s Traffic Control Devices Excel spreadsheet to create a complete Traffic Control Devices spatial dataset.
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.
Comments