The workshops identified several considerations for optimising our use of data and BI in relation to technical infrastructure and sound data management at Dorset Council.
In setting the scene, the question of appropriate technology may also be contingent on the council’s appetite to make greater use of ‘low-code and no-code’.
This approach refers to the development of platforms which require very limited coding knowledge and skills. This is a growing area, and it is anticipated that in the future much development will be undertaken using this approach, with reduced turnaround times and enhanced automation opportunities.
This will require consideration of the ‘guardrails’ placed around this activity to ensure it conforms to internal rules and best practice.
Having an appropriate technological infrastructure is key to deriving the maximum benefit from the data we hold.
‘…local authorities can take a look at their own data infrastructure as part of their modernisation process, and understand ways to develop interoperability between different platforms, systems and data sources inside of the organisation. This will allow them to work more efficiently and derive more value with the data they already have at their fingertips.’ (Glen Ocskó, Head of Local Government, Made Tech).
As a council we use the Microsoft platform which gives us access to an enormous range of valuable tools which can support us to become data driven. Using the Microsoft tool stack to choose the right blend of platform tools will help us to leverage not only the Microsoft Data warehouse platform, but the power integrations between Microsoft 365 technologies.
The underlying technology should support the aspirations of the business.
We also have Placecube, our customer platform, and links to and from this need to be coordinated.
Engagement with Placecube is essential to ensure that our strategies are aligned and complementary.
Data warehouse and data lakes
We need to establish an approach which provides us a single version of the truth and reduces data duplication.
This means having the ability to bring all our relevant data together into one place, and then to combine and join this data together.
To do this we need to make use of appropriate data warehousing, providing a single repository for the data we will use for BI and data science purposes. Data warehouses work via a process of ‘extract, transform, and load’.
Data is extracted from a variety of sources (e.g. line of business applications, SharePoint, spreadsheets, databases) and fed into a staging area in its raw format. The data then goes through several transformation processes before being loaded into the data warehouse.
These processes are significant in the lifecycle of data as they will include cleansing (data quality, master data management, de-duplication) as well as advanced processes such as filtering, joining, splitting, summarizing.
This ensures that high quality data resides in the data warehouse which can be easily reported, mined, or visualised in reports and dashboards.
In addition to the data warehousing, we will also explore the use of a data lake as a repository to store both structured and unstructured data.
Adding a data lake to our capability would allow us to store a vast amount of raw data in its native format until it is needed.
This would enable the council to ingest data at a larger scale and much faster.
Taking advantage of this technology will require strong governance. There will be various options for data warehousing.
The council already has a data warehouse which resides on-premises. However, as our usage increases and data requirements grow, this will not be fit for purpose to serve the longer-term data aspirations of the council.
Consideration will need to be given to transforming our platform and moving towards a cloud hosted environment, including an appropriate timeframe for this, costs, and any implications for legacy arrangements.
Visualising our data – PowerBI
Dorset Council has seen a significant increase in the use of interactive reports and dashboards with an associated central roll-out of the Microsoft PowerBI platform and creation of an interactive reporting suite by the BI and Performance Service.
This has been an important step forward in enabling and encouraging greater levels of self-service, where users have access to drillable reports, allowing them to move through various ‘levels’ of data and visualisations.
As a council we have invested in PowerBI ‘premium’ capacity, creating dedicated workspaces and capacity for our core BI team. This was driven by the demand from within the council for increased access and functionality from its reporting.
PowerBI can easily incorporate data security without additional licensing and cost and the outputs are available to end-users via a BI reports server which is available as part of our Enterprise Microsoft licensing agreement.
The server uses the user’s credentials to potentially determine whether they can view the output and which specific data is available.
Additional considerations relate to our ability to specially map information.
At Dorset council we have a technical team of Geospatial specialists. PowerBI has built-in links to ESRI products (GIS mapping software), but the GIS direction is currently to move to opensource software QGIS.
The decision was made based on the cost of ESRI products, but with increased adoption of Power BI, mapping options for power BI will need to be explored. New technologies like artificial intelligence (AI) and machine learning are also bringing the opportunity to potentially derive further efficiencies in our business.
Effective data management is vital to underpin these technologies, harnessing BI, predictive analytics, or adopting intelligent automation and the Internet of Things (IoT).
Procurement of new systems
Data and reporting requirements need active and complete consideration as part of system procurement processes.
Currently, our line of business applications (LOB) do not always offer sufficient reporting capabilities.
This then leads to spreadsheets and other databases sitting outside of the LOB applications to compensate which can create additional data loss risk or data securely implications.
Procurement needs to focus on ensuring any new systems have appropriate operational and statutory reporting capabilities, where data warehousing should be utilised for joining datasets together to enhance insights, not for BAU operational reporting.
Procurement processes also need to consider how data can be extracted from source systems.
Third party companies can charge for an API (application programme interface) or offer only manual downloads of data.
This can make the cost of accessing our own data prohibitive, and consequently it becomes difficult to join data sources to derive deeper insights.
New systems should, where appropriate, also to link to the local land and property gazetteer (LLPG) for addresses.
When the address gazetteer is used, a unique property reference number is attached to the record which makes it much easier when joining together data sets in the data warehouse (see unique identifiers).
We will achieve this by:
- bringing relevant data together from across our currently disparate systems into a secure cloud hosted data warehouse and data lake solution
- providing clarity on where and how data should be stored, to ensure a consistent approach across the organisation
- working closely with business areas when developing or procuring technical solutions to ensure that business and wider organisational needs are met
- conducting an options appraisal to decide the best storage solutions for our data including our data warehouse to ensure it is fit for the future, including an assessment of our data sharing abilities with relevant partners
- considering the level of enterprise functionality needed to meet the aspirations of the data and BI strategy
The impact is:
- we will be able to provide timely and regular data feeds as appropriate to the business need. This may include real-time data where feasible, appropriate, and desirable for business need
- we will have a technical infrastructure which supports our data management processes
- we will have confidence in our systems, and the use of them, across the organisation
- we will make the maximum use of our investment in Microsoft Products
- we will develop and procure systems which can integrate with our existing systems
- we will free up capacity from cleaning data, effort which can be redirected towards developing insights and understanding from our data