Data Platform Project Weeknote 24

About the Data PlatformThe vision for the Data Platform is to rebuild a better data infrastructure to  deliver a secure, scalable and reusable cloud-based platform that brings together the council’s key data assets. Enabling us to democratise access to data (where appropriate), use technology to enable deeper insight, and derive greater value from our data to improve the lives of residents in Hackney. For more information, see our Playbook and our Glossary.    

How we’ve been building the platform this week:

Creating a reusable process to ingest data from a MS SQL database so that we can make Council Tax data available for reporting and reuse: 

We now have a fully automated and re-usable process of ingesting data from databases onto the Platform. We have successfully ingested parking data from Geolive, making this data available to our Parking analysts. Geolive is the corporate GIS database held in Postgres/PostGIS. It contains data from different departments, including Parking orders (these are managed in Parkmap and exported nightly as GIS flat files loaded into Geolive).

We’ve also significantly improved the cost and time efficiency of the entire ingestion process of Academy data as it previously took 15 hours + to ingest all of the 291 tables. This particular ingestion process splits up the tables into several Glue jobs which run concurrently taking no more than a few hours each to complete. The data is then programmatically split into the raw zone areas of two new departments: Benefits & Housing Needs and Revenues where relevant analysts can access, query (via Athena/ Redshift) and prepare the data for dashboards in BI tools such as Qlik. The work is currently being reviewed by our team and should be live very soon.

Creating a reusable process to ingest data from our in-house applications through event-streaming so that we can make housing data available for reporting: 

We now have a working setup for the Kafka implementation in Amazon MSK. We have been working to productionize the infrastructure and move the setup fully into our Terraform repository so that we can deploy the setup to Pre-Production and continue testing with Manage My Home. At the same time we’ve continued talking with the AWS team to evaluate if there is any potential to get the AWS Glue Schema Registry implementation working as a better alternative to the self-managed Schema Registry that we have working. With the current self-managed Schema Registry we will be required to make some changes to the Lamdba function implemented by the Manage My Home team, however with the AWS Glue Schema Registry implementation this additional work wouldn’t be required.

Migrating Qlik into the platform infrastructure and moving away from AppStream so that we can improve the user experience for staff:

 Following the Technical Design Authority’s decision to proceed with Qlik on the web with a WAF we were also asked to look at the user needs that result in downloading of the data and explore if this can be met through other means. Last week a number of users from Children and Families Service and Housing explained how important the feature is for them to carry out some key functions. However there are also opportunities to reduce the need if we can automate saving the data into google sheets for them to use. We are working with analysts in the service areas to deliver these whilst the Cloud Engineering Team configures the WAF.

How the platform has been used this week:

Academy data in Qlik so that analysts can use it : 

Now Academy data is in the relevant department’s raw zones, we are able to query the data in Redshift from Qlik and recreate the majority of the data pipelines that were in use prior to the Cyber attack. One focus has been to recreate the “Housing Benefit and Council Tax Support profiler” in Qlik which provided the service with an overview of the current claim caseload. We have been able to reproduce the data and refresh the dashboard and will spruce up the dashboard before sharing with the service. Housing Benefit data is also an integral source of the Supporting Families team (FKA Troubled Families) who use the data to evidence that cases have moved back into employment and claim payments from central government. We shall imminently be reinstating the reports for the team so they can restart the Payment by Results process. 

Steve Farr has also been working with Dave Ellen, an analyst in Revenues, to recreate commonly used queries relating to Council Tax and Business rates. Steve has successfully recreated a number of Dave’s queries in Athena and will be carrying on this work and exploring how to make “trusted” data sets available to managers in the service and onboarding Dave to use Athena to query the data on the Platform. 

We created a reusable training module to help onboard users so that more staff can use the platform to complete their work: 

This week we continued our training program, and ran module 3 training for our team members. We’ve used the feedback from the training to refine our processes. We’ve also been putting together a form which we will be rolling out to people who would benefit from being trained in the use of the data platform for the purpose of data analysis.

Up Next:

  • Continue to develop trusted datasets for Council Tax and recover previous data products dependant on Housing Benefit data
  • Continue testing our event streaming process with the Manage My Home team
  • Support the Cloud Engineering team to implement Qlik over the web, and draft another paper for the Technical Design Authority about the downloading of data
  • Investigate how we make Alloy data available to analysts in Environmental Services through the Alloy API

Data Platform Project Weeknote 21 21.02.2022

About the Data Platform
The vision for the Data Platform is to rebuild a better data infrastructure to deliver a secure, scalable and reusable cloud-based platform that brings together the council’s key data assets. Enabling us to democratise access to data (where appropriate), use technology to enable deeper insight, and derive greater value from our data to improve the lives of residents in Hackney. For more information, see our Playbook.

How we’ve been building the platform this week

Creating a reusable process to ingest data from our in-house applications through event-streaming so that we can make housing data available for reporting: We’ve been working this week to deploy, configure and test a self-hosted schema registry as an alternative to AWS Glue Schema Registry which we’ve had issues using as part of our Kafka consumer. The Schema Registry is now deployed and storing our schemas. We’ve been setting up Kafka Connect to use the schema registry which has involved trialling out a number of different configurations to test whether our AWS Connect Plugin can use the Schema Registry to deserialize events that are pushed into Kafka. In short, our preferred solution to the blocker we’ve been grappling with in recent weeks looks promising but we need to complete our testing to be sure.

Creating a reusable process to ingest data from a MS SQL database so that we can make Council Tax data available for reporting and reuse: Good news! We have some Academy data on the platform! This week we completed this tech spike and have managed to connect to the Academy Insights SQL Server database from the Data Platform; creating a reusable process to ingest data from SQL server databases and other types of databases. We will now start pulling in the rest of the data to our Data Lake (S3 storage) and monitor the computing power required as there are almost 300 tables in the Academy Insight database. As highlighted in the ADR (Architecture Decision Record), there are still a few questions that need answering specifically around how we can reuse previous data warehouse work to get it ready for consumption by Data Analysts and other users. We will be addressing these questions and exploring ideas in the coming weeks.

Setting up AWS cost alerting so that we can better monitor our usage and spend: We have identified how to set a budget which will notify specific users if expenditure is forecasted or actually goes over a set budget. We have also created a lambda to dynamically adjust the budget limit based on the previous month’s outgoings. This is currently in testing.
Migrating Qlik into the platform infrastructure: We had been working to provide access to Qlik through the Global Protect VPN rather than AppStream, however our testing found that this wasn’t a viable solution. We’ve now proposed that secure access to Qlik be provided directly over the web via Google Single Sign On (which includes two-factor authentication) and this week we took this proposal to the Technical Design Authority (TDA). We had a good discussion but a number of issues came up – would an additional firewall be beneficial? What are the implications for the egress of data out of Qlik (and possibly onto users’ own devices)? How will the Qlik infrastructure be maintained to ensure we’re doing all we can to minimise any vulnerabilities? We need to explore these risks and controls further with our colleagues in cloud engineering and security before going back to the TDA. It’s crucial that access to Qlik is well considered, but unfortunately for users this means further delay to taking it out of AppStream and improving their experience.

How the platform has been used this week:

A reusable training module to help onboard users – In the last few weeks we have been building a set of training modules that take users through the process of ingesting, transforming and deploying data into the Data Platform. The training gives users hands-on use of the AWS Console, GitHub, Docker and Terraform. We are trialling the materials with a cohort of Data and Insight analysts before refining the materials for wider use. We delivered the first module (ingesting google sheets) last week and will cover transforming data in python notebooks this week.
Exploring how Single View could use the data platform – Single View was an application which displayed information from a number of other applications in one place to help officers better understand a resident’s circumstances and save time. It hasn’t been available since the cyber attack, and a new project has recently started to revive this type of functionality (see their first weeknote here). We met with the team to tell them about the Data Platform and discuss two possible ways to integrate: 1) Single View consuming data from applications like Vonage and Academy via the Data Platform rather than directly from the application. This would mean we’re not duplicating the effort to get at these data sources. 2) Using the Data Platform to match resident records across datasets to provide automated or suggested matches to users, rather than them having to match everything manually. We don’t have to do both of these things at once, and it makes sense to focus on exploring the first. However, the Single View team first needs to decide who their initial users are and what datasets they need.

Up Next:

Building:
Ingesting Modern Tools for Housing data: verify if our new schema registry will work
Ingesting Council Tax data: Ingest a full set of Academy data from the Insight DB so that analysts can start to work with this data
Working with the Cloud Engineering and Security teams to further assess the options for providing secure access to Qlik
Using:
Running the training module on transforming data in Python with members of Data & Insight

Data Platform Weeknote 19: 07.02.2022

About the Data PlatformThe vision for the Data Platform is to rebuild a better data infrastructure to  deliver a secure, scalable and reusable cloud-based platform that brings together the council’s key data assets. Enabling us to democratise access to data (where appropriate), use technology to enable deeper insight, and derive greater value from our data to improve the lives of residents in Hackney. For more information, see our Playbook.

We’re now at a point where more analysts (mostly within Data & Insight for now) are beginning to use the platform more for their BAU work. As such, we’re trying to make a clearer distinction between what we’re doing to build the platform (the focus of the project) and how people are using the platform. For example, we’ve started using separate ‘Work In Progress’ streams on our Jira board and structured our Show & Tell to reflect building vs. using, and we’re thinking about what other changes might be helpful.

How we’ve been building the platform this week:

  • Creating a reusable process to ingest data from our in-house applications through event-streaming so that we can make housing data available for reporting: We’ve been frustratingly blocked on getting the housing data that we’ve received in Kafka into S3. There is a managed Connector but we haven’t been able to get the configuration to work and all the AWS documentation is suggesting that we need to develop our own. We’ve been trying to avoid this because a custom-built connector would be much more difficult for a Hackney team to maintain in future. Next week we have a new Data Engineer with Kafka experience joining the team, as well as a meeting with AWS, which we’re hoping will help solve the problem.
  • Creating a reusable process to ingest data from a MSQL database so that we can make Council Tax data available for reporting and reuse: We began a tech spike to determine the best way to ingest Academy data (via their Insight database) into the platform. We’re exploring options on how to most efficiently get the data and have been testing some approaches, but we haven’t been able to focus on this as much as we planned because the team needed to come together to try to unblock the Kafka issues.
  • Setting up AWS cost alerting so that we can better monitor our usage and spend: We’re creating a reusable process to alert the team if we have a spike in our spending. The alerting process is in place, now we just need to sort out the month-on-month comparison so that we can distinguish between ‘normal’ increases as the platform is used more and unusual spikes.
  • Improving the analyst experience of prototyping scripts: Analysts in parking have had difficulty switching between the different dialects of SQL used in the different tools that they used to prototype (Athena) and schedule/run scripts (Glue). We used our collab session to discuss how a notebooking solution would help meet this need, as well as others (e.g. analysts will need a Python prototyping environment that can run on a Chromebook rather than in Docker). However, we agreed that we could meet their needs either through a notebook or changing our ‘orchestration’ (i.e. how we schedule workflows and trigger jobs) and we want to test out basic versions of these to see which approach better meets their needs.
  • Migrating Qlik into the platform infrastructure: We’ve been working with the DevOps team to complete the required setup to move Qlik from the Production API account into the Data Platform Production account. In order to perform this migration we’ve had to re-setup a few bits of infrastructure around Qlik. Specifically we’ve been configuring a security certificate which is used to encrypt the connection to Qlik, as well as setting up AppStream which will initially allow users to connect to Qlik inside the Data Platform account.

Alongside this work we’ve been working to develop an alternative method of connecting to Qlik in the form of Global Protect VPN which allows users to gain access to the private network of the AWS accounts without exposing the devices held in it to the public network. This will allow users to connect to the Qlik server without having to expose Qlik publicly.


There are two main issues around the implementation of Global Protect. The first is that Google Single Sign-on isn’t working correctly when accessed through the Global Protect Portal as the portal loads the Google pages on your behalf which breaks some of the Google code. The second issue is that while using the Production Global Protect desktop application you do not have access to the internet. This wasn’t a feature in the staging environment but is the intended setup in production. We’re working with the Cloud Engineering team to address these issues, but may need to explore other options for providing secure access to Qlik if we can’t address them.

How analysts have been using the platform this week:

  • We fixed a bug for Parking analysts which was preventing their dashboards from getting the latest data. Their Liberator tables were getting so big that the ingestion process was creating new partitions (essentially creating two folders for the same day, rather than putting all the files in the same folder) so we’ve updated the process so it’s all going to the same place.
  • In preparation for getting housing data into the platform, we’ve mapped the data we need to recreate a tenant and leaseholder list against the platform API entities and shared this with the Manage My Home team so they know what further work is needed on their end.
  • We’re developing a set of refined bulky waste tables that present key information such as collection date and cancellation date. This requires a fair bit of data transformation because of the way this data is currently held within the Liberator tables in the raw zone. The refined tables will make it much easier to use this data in a dashboard.
  • We’ve added a Python library that enables us to convert British National Grid coordinates to latitude and longitude. This will make it easier for Qlik to map this data, and can be reused for other datasets.

Up Next:

  • Onboarding a new senior data engineer into the team
  • Hopefully unblocking our Kafka issues, or if not reassessing our approach
  • Completing the tech spike on Academy
  • Supporting analysts to keep using the platform to complete their BAU tasks

Data Platforms Weeknotes 18: 01/02/22

About the Data Platform
The vision for Data Platform is to rebuild a better data infrastructure to  deliver a secure, scalable and reusable cloud-based platform that brings together the council’s key data assets. This will enable us to democratise access to data (where appropriate), use technology to enable deeper insight, and derive greater value from our data to improve the lives of residents in Hackney. For more information, see our Playbook.

Making housing data available for reporting
We’ve been collaborating with the Manage My Home team from Modern Tools for Housing (MTFH) to set up an event streaming process to get data into the Data Platform for some time (if you missed our latest Show & Tell, you can watch our tech Lead Engineer, James, give a good overview here). This week we celebrated the first part of our event streaming process working with the Manage My Home Application. This has involved an event being generated in the front-end of Manage My Home, passed on to the lambda function, then being sent to Kafka in the Data Platform account.

Our progress this week was a breakthrough, but unfortunately we are still having trouble with the last step of this process when Kafka writes the received data out to S3. We had this working before but it’s now stopped working again after making various changes, so this week we need to focus on debugging and getting this full end to end process working. We will also continue our discussions with the MTFH and dev teams to agree on an approach to state syncing which will enable us to ingest historical data, not just new events, and to re-synchronise our data in the event it becomes out of sync with the Manage My Home Application.

Ingesting and using Noiseworks data from the platform

We have completed some work to make data and reporting available for the Noiseworks team, and test out our ‘time to insight’ in the process. Noiseworks is an application for managing noise complaints which has recently gone live in the Environmental Enforcement team. To get their data into the platform, we followed a similar process to how we’ve ingested Liberator (parking) data: we liaised with the third party supplier to create an AWS S3 bucket for them to drop files into, then exposed the data to be consumed in Qlik.

The service went live last Friday and we were able to get dashboards to the team by the following Tuesday. This is an example of how reusable processes can help achieve ‘faster time to insights’ – our North Star objective.

An example noiseworks dashboard:

Making additional Planning (Tascomi) data available for reporting
We’ve previously set up a process to ingest planning data from the Tascomi API, but realised that the API didn’t provide all of the data we needed. The supplier recently added new endpoints for us to access the additional tables we requested, so this week we onboarded two new data analysts (Marta and Tim from D&I) on to the platform to help us ingest them. This process involved them learning about editing a data dictionary, utilising a Terraform script, adding data quality checks to the PySpark scripts, and submitting a pull request on GitHub. The feedback from the analysts was that the process was fairly straightforward and that the documentation they were able to reference in the playbook was useful. This process has helped to reassure us that the reusable processes we’ve been building are indeed reusable!

Improving analysts experience of using the Data Platform

We have found that onboarding analysts on the Data Platform can be challenging due to the number of new tools and technologies that are required to gain access. Analysts have had to switch between different interfaces and use different dialects of languages such as SQL when creating scripts and Glue jobs. This has added a level of complexity in addition to many analysts having to learn many new skills to access the platform.

We hope that by using an online notebooking tool, some of this complexity can be eliminated. However first we have to assess the notebooking tools that are available to us and how they fare against the needs of our users. We have started this process with several members of the team helping out to evaluate tools.

Up Next:

  • Fixing the Kafka issue that’s blocking our end-to-end streaming process for housing data
  • Agreeing an approach to getting historical data from platform APIs
  • Running a Tech Spike on how best to ingest Council Tax (Academy) data into the Data Platform
  • Reviewing the criteria and objectives for notebooking tools as a team in our weekly Collab session

Data Platform Weeknotes: 17 24.01.2021

For more information about the HackIT Data Platform project please have a look at this weeknote on the HackIT blog.

Help us improve how and where we communicate

Thanks to those of you who have taken the time to fill in our feedback form. There’s still time if you’d like to voice your opinions about how we can best improve the way that we communicate with everyone who is interested in the work we are doing. 

Update on how Planning data in the platform is being used

This week we had some brilliant feedback come through from our collaborators in planning. Previously on the data platform project we had worked hard to ingest planning data from the Tascomi API so that planning analysts can produce the reports they need. It has been a challenging process at times but we heard this week that our collaborators are extremely happy with the data dashboards that have been created using the data platform ingested data.

We hope that managers will be able to use the information to inform their planning and decisions. We hope that in turn residents will be able to benefit from the  insight being gained about the way they use the council’s service.

Whilst we’ve been able to recreate the vast majority of planning’s KPIs through the data we’ve already ingested, there were still some tables we couldn’t access via the Tascomi API. This was unblocked this week, so we’re using it as an opportunity to test out our process and documentation by onboarding two D&I analysts who are now able to add new tables to the pipeline. This not only means we have more data in the platform, but we’ve also increased the people who are able to get data in too.

Making housing data available for reporting

We are working with developers from the Manage My Home team to set up an event streaming process to put data from the platform APIs (e.g. tenure API, person API) into the data platform. We hope that this will allow the housing services to see what data can be used within dashboards to provide important information to service managers and product owners, for example how many tenures are created or amended within Manage My Home. 

For a more detailed breakdown of the process involved in this, please have a look at weeknote 16

Housing data challenges: This week we have been testing running a lambda function in the development scratch account that gets a tenure from the development tenure API then pushing a message to a kafka cluster in the data platform account. We’ve had to make some small changes as we’ve been doing this. We are nearly there but are still working out the correct networking settings for the Kafka cluster which is proving a bit tricky. We’ve also had some staff absence due to covid which has slowed us down a little.

Future proposal for ingesting historical data 

We presented at the HackIt Technical Architecture meetup and discussed our proposal for a way to stream historical data. Currently the event streaming will only supply us with data from new events that are streamed (e.g. a new person is created, or a tenure is updated) but won’t provide any historical data. Our proposal includes some changes being made to the platform APIs which will require some coordination with our colleagues in the dev team so that the work carefully considers all of the affected dependencies.

Next: Meet with the dev team to refine our proposal for streaming historical events, and once we’re all on the same page we’ll likely take this to the Technical Design Authority as it’s a fairly wide-reaching proposal.

Backfilling data from our production environment to pre-production

We have completed a lot of work which will enable our move to a production environment. For a more detailed breakdown of this work, please refer to weeknote 16.


Making Council Tax data available for reporting

We’ve been exploring the database created by Capita’s Insight tool as a means to get Revs & Bens data into the platform. Last  week our initial investigations suggested it had most of the key tables that were used in previous analytical outputs relating to Council tax, Housing Benefit and Business Rates.

This week we’ve reviewed our findings with our collaborator in Council Tax, Dave Ellen. We still think the Insight database gives us approximately 90% of what we need, but it’s frustrating that the other 10% available in the live Ingres database isn’t there. We’ve been working with Chris Lynham to get further access to see if these tables could be hiding elsewhere within Insight or whether we really do need access to the full database.

Next : Decide whether the Insight database is sufficient (or at least good enough for now) and run a tech spike on how to ingest this into the platform.

Developing a community of practice

We are keen to connect with more analysts across the council and get more key  people engaged with our work. We have come up with an idea for a superuser group of analysts who we hope will build the foundation of a thriving data platform community at Hackney. We will be planning some meetups and workshops in the near future. We’ve also been asking ourselves, ‘what are all the things we need to improve in order to onboard new users easily?’