As we move closer to wrapping up 2023, I find myself in a reflective mood! If you read last week’s blog you’ll have heard from my colleague Lee Burton that we have recently concluded the migration of 800GB of ECC data to the cloud and implemented our new data environment Microsoft Azure. We’ve come far in building our organisation’s data capability! However, you won’t be surprised to hear that there’s much more to come, more data to move and analyse, new functionality to switch on and learn, unidentified challenges yet to navigate, and endless opportunities that will benefit data professionals, commissioners and citizens.
Lee's blog, Migration to the cloud, a painful journey to a better place, summarises some of our reasons for moving to the cloud. Essentially our infrastructure was starting to creak, and new tools and technologies were needed to enable us to satisfy the growing demand for data analysis, and to better support our colleagues to make decisions about the services we provide to people throughout Essex.
Throughout this year we started turning on and setting up Azure services to replace our old data capabilities on a faster and more robust platform. Seven insight teams reviewed over 200 reports that existed on the old system to identify what was still relevant for their stakeholders, what was now superfluous but hadn’t been decommissioned and what was duplicating information.
We put our focus into building four initial parts, which are delivering immediate benefits:
- A way of moving our data. We were creating new extract, transform and load (ETL) pipelines using Synapse Analytics. Our data development set up a new workspace to host our ETL jobs and then set about creating pipeline structures to ingest from 16 different data source types. Now, not only can we easily ingest data from our core systems, but we have ways to easily import data from API connections, partner database systems and other commonly used data formats.
- A place to store our data – ECC has a new data lakehouse structure. We have a data lake to import copies of original source data so that we have traceability for where that data has come from and then we have ETL jobs that turn this source data into structured analysis data tables that are stored in our data warehouse. From this data warehouse, analysts across the organisation can access the data that they need.
- A way of analysing our data – Each of the different insight teams in ECC previously published their own versions of reports, which could lead to multiple versions of the truth. Our migration to Azure Analytics aimed to stop this happening again through the use of PowerBI data models. We now have a data model per business function for the council and these data models have two key purposes: they have data table relationships built directly into them so that analysts don’t have to guess how to join tables within our data warehouse structure – these relationships are predefined and secondly each data model has the calculations for performance measures and KPIs built straight into the model.
- A way of presenting and distributing our data – This project upgraded how our organisation used PowerBI – taking us from PowerBI Reporting Server to PowerBI Web Service. This helped to solve our problems about the platform being hard to find because the new link is simply powerbi.com. It also helped to solve our problem about the previous platform being difficult to navigate as reports are now grouped by business area rather than by the team that built them. And finally, we have started to make use of PowerBI Apps to distribute our content in a simple but secure way by having key user groups and using the PowerBI apps features to customise which user groups can see which reports to ensure that we are sharing data securely and proportionally around the organisation.
After setting up a new data platform and moving our data and Business Intelligence/Management Information to Microsoft Azure we now have a much more robust platform that is a lot easier to access and navigate, we’ve drastically reduced the amount of time it takes to refresh our data each day and we’ve upgraded our toolset to market leading technologies.
That sounds good. But what comes next?
We’ve taken the first step to having a fully integrated data platform inside our organisation. But we’re aware that these things keep changing and we need to keep up with that. There are three ways we want to keep developing:
- We want our data warehouse to become a data library. More and more of the data that the council uses gets stored in a data warehouse and it becomes the one stop shop for people who want to know some quantitative information about something in the council. That way we can reduce the need for those manual trackers and Excel -> Pivot Table -> Chart -> Powerpoint process. Therefore, as we keep identifying new data sources, we want to keep bringing them in to our data warehouse and joining these data sources together.
- We understand having good technology driving our data platform is only a small part of the story – this project has started parts of our data governance journey. But we’ve still got a lot further that we need to go to ensure that our data is well captured, well maintained, and well understood across the whole organisation. Now we’ve got good technology, we need to set up better processes to make sure that the data inside that technology is suitable, relevant, and correct.
- Expanding our ability to do new things. Previously a lot of our insight teams felt limited by what we already had. Now that we are using Azure, we know that we are using a tiny fraction of the 200+ services that Azure has to offer. We want to start exploring what the next services we can start making use of to allow us to work smarter with the information that is available to us and so that we can get to decisions faster than we have before.
After completing all of this work, what lessons would we share with our peers?
Know what data you’ve got! Complete an audit of your data – where you get your data, what data tables you have in your reporting set up and how your dashboards are being used across your organisation. We found that we had old data products that were not fit for purpose and had to be adjusted as part of the migration.
Never think of the process as ‘lift and shift’. When we started this work, it was originally pitched as a lift and shift of our existing data products which made all of our insight teams think this would be a simple and relatively light touch work (the thought most of us had was ‘we’ll move the data tables on to the cloud database, we’ll repoint our existing dashboards to the new tables and then just save them to the cloud service)’. It didn’t work this way. The reality was that our data had changed and needed reworking – some of our data tables were reworked to be more relevant so we couldn’t just reconnect our old reports. Similarly, the audit identified there were better ways that our dashboards could represent information so some of our reporting suite was completely rebuilt to make best use of the PowerBI web service and the data model capability that the web service provides.
Resource, resource, resource! We brought together data professionals from seven teams, who were balancing business as usual demands with resourcing the data migration and all that came with it, inevitably competing priorities impacted availability of resources and project delivery duration. Create dedicated capacity that is informed by implementation plan and will provide the required capability and capacity as and when it’s needed.
Leave a comment