Frankston City Council has more than 130,000 residents, delivers a wide range of services and manages a large volume of assets spread across 131 square kilometres – resulting in a lot of data to manage.
However, in order to get the benefits of the data, the organisation was relying on a small number of IT staff with a very specialised skill set to create all reports which often involved sourcing data stored in multiple systems.
QuantumIT was approached to create a new data warehouse which was scalable to include new data sources, reliable, easily maintained and enabled non-IT staff to generate reports on demand. The data sources included commonly used and popular council products.
Working closely with Frankston City Council’s in-house team in a collaborative project over a 12 month period, QuantumIT developed a data warehouse which, combined with the use of SAP’s Business Objects, has delivered real, actionable insights in the areas of asset management, as well as rates, and which can be extended to other areas in future.
The close partnership enabled by use of a Melbourne-based team helped to not only deliver a robust solution but also coach and build up the skills of in-house staff to continue to grow and maintain the data warehouse into the future.
QuantumIT developed the warehouse and leveraged an existing data reporting platform – SAP’s Business Objects – to enable once-off and routine reports as well as real-time dashboard information.
We utilised the modern and dynamic BIML (Business Intelligence Markup Language) technology for data transformation in order to generate SSIS (SQL Server Integration Services), delivering significant time savings and futureproof data reliability.
The reports enable users with specific queries to drill-down into the information, and to do what’s referred to in the industry as “slice and dice” i.e. to reduce a body of data into smaller parts or views that will yield more information.
In addition to freeing up IT staff, the business benefits included the ability to:
Asset / Work Order Management
• Identify if workforce crews are working at capacity, under-resourced or over-servicing
• Allocate resources efficiently; e.g. job-sharing or grouping common works
• Predict and allocate for peaks and troughs
• Predict plant items required in coming months
• Identify decreases in customer calls when workforce efficiency increases
• Monitor the performance of an asset over time
• Identify and analyse asset relationships between planned and unplanned inspections
• Locate and analyse problem equipment
• Track outstanding rate and charge amounts by instalments over time
• Understand outstanding amount fluctuation reasons; e.g. rate adjustments or refunds
• Reconcile rate types with land use and fire service level categories
• Compare rate types with valuations
• Identify direct debit exceptions and whether they conflict with instalment plans