RM1043.8-1-West of England Combined Authority - Data Warehouse

Description

Summary of work In our Employment & Skills Service, our goal is to improve data reliability and enable more advanced data analysis to support the monitoring of operational outcomes and inform commissioning decisions proactively, as outlined in this proposed data processing warehouse project. The current data processing procedures are excessively time-consuming and heavily reliant on manual interventions, a situation that will only worsen as the Service continues to expand. The proposed data system should be flexible enough to easily scale up the data function as the Service grows, in line with the implementation of later enhanced devolution agreements. The data processing requirements involve extracting data from various projects and sources (APIs, SharePoint, Web portals etc), including a combination of databases and spreadsheets that require importation. Specifically, three additional projects depend on an existing CRM named Lamplight, now necessitating replication through Microsoft Power Apps or a comparable Microsoft architecture solution. Additionally, the data processing warehouse is tasked with importing publicly available data, including but not limited to sources such as the Office for National Statistics, IfATE Occupational Maps, and Department for Work and Pensions Data. As part of the development, it is crucial to draft data-sharing agreements where required. The Service is shifting towards a programme management reporting style, with a preference for PowerBI dashboards to showcase performance outputs and outcomes. These dashboards serve two distinct functions. Firstly, they report outcomes at a Head of Service level, providing high-level insights for the Head of Service and Directors to gain an overview of project performance across the Service. Secondly, the dashboards need to offer more granular reporting for project managers, enabling them to assess project performance against each output/outcome. Additionally, these dashboards should include a PDF printable function for broader sharing. A separate dashboard is necessary for relationship managers to view performance categorised by the RAG (Red, Amber, Green) rating system. The data warehousing project should also include a report-building functionality, empowering managers to create customised reports using accessible and easy to use processes, such as being able to export custom subsets of data to csv/spreadsheet ready for Power BI ingestion. Furthermore, the warehouse should have the capability to layer map data using map visuals in PowerBI. These maps should integrate internal and external data to illustrate, for instance, areas of deprivation and provision, facilitating proactive commissioning decisions. Access to the dashboards should be regulated through secure user access technologies, for example Microsoft single sign on (SSO). Each user account should be trusted and assigned to appropriate security groups, ensuring restricted access to databases, datasets, apps, & dashboards based on roles. Senior managers should have access to the entire system, project managers can only access their respective projects, and relationship managers are provided with a general overview of all providers' performance. External user access from the Unitary Authorities (UA) should also be considered and made technically possible for future implementation. The Employment and Skills Service (formerly People and Skills Service) is currently facing increased complexity, higher financial investment, and expanded scope. Consequently, this has resulted in a heightened level of responsibility towards the residents within the region, along with elevated expectations from external stakeholders, including government departments interested in quantifiable impact. We envisage timescales for the data solution will be up to three months for the build, with one month of testing and final tweaks, before the system is rolled out across the Service. Where the supplied staff will work South West England Why the work is being done The proposed solution should be compatible with Microsoft data warehouse & data entry/management products. (Azure, SQL databases, Power Platform, MS Office). The proposed solution should be deployable within our existing Azure tenant (managed by our external IT provider), and within our existing Hub & 3 Spoke (PROD/TEST/DEV) Landing Zone topology. The solution should use PaaS component where possible. For one of the teams involved, the proposed solution will replace their existing data warehouse built on VM/IaaS technologies. The solution should operate using lower cost Azure components/SKUs where possible, to reduce ongoing consumption costs. Consumption cost reporting & visibility should be made available to the business area, using resource tagging where possible to help identify which teams are responsible for certain costs. This project builds on the scoping work already undertaken in June 2022 and more recently in October 2023. This discovery work documentation is shared with suppliers interested in bidding for this requirement We have completed an initial overview of our existing Azure cloud architecture which will help shape the proposed solution. The supplier will work closely with the client Data Manager and our external IT provider to define the final data warehouse design. The proposed solution should be designed and built with the future in mind, whilst considering the Combined Authority's wider corporate data warehouse platform aspirations. Certain data warehouse components should be considered as shared components which could potentially serve and orchestrate additional data pipelines/data marts for other business areas outside of Employment and Skills. Knowledge transfer to the Combined Authority is essential, covering end-user instructions to operate dashboards/apps and separate documentation covering the technical solution & architecture. The technical design should be clear and transparent, so that the CA can replicate the deployment for other areas of business if required. The supplier should be able to provide technical support for the deployed solution, covering the 12 months after platform launch/go-live. Support beyond that period should also be made available if required. The business problem The proposed solution should be compatible with Microsoft data warehouse & data entry/management products. (Azure, SQL databases, Power Platform, MS Office). The proposed solution should be deployable within our existing Azure tenant (managed by our external IT provider), and within our existing Hub & 3 Spoke (PROD/TEST/DEV) Landing Zone topology. The solution should use PaaS component where possible. For one of the teams involved, the proposed solution will replace their existing data warehouse built on VM/IaaS technologies. The solution should operate using lower cost Azure components/SKUs where possible, to reduce ongoing consumption costs. Consumption cost reporting & visibility should be made available to the business area, using resource tagging where possible to help identify which teams are responsible for certain costs. This project builds on the scoping work already undertaken in June 2022 and more recently in October 2023. This discovery work documentation is shared with suppliers interested in bidding for this requirement We have completed an initial overview of our existing Azure cloud architecture which will help shape the proposed solution. The supplier will work closely with the client Data Manager and our external IT provider to define the final data warehouse design. The proposed solution should be designed and built with the future in mind, whilst considering the Combined Authority's wider corporate data warehouse platform aspirations. Certain data warehouse components should be considered as shared components which could potentially serve and orchestrate additional data pipelines/data marts for other business areas outside of Employment and Skills. Knowledge transfer to the Combined Authority is essential, covering end-user instructions to operate dashboards/apps and separate documentation covering the technical solution & architecture. The technical design should be clear and transparent, so that the CA can replicate the deployment for other areas of business if required. The supplier should be able to provide technical support for the deployed solution, covering the 12 months after platform launch/go-live. Support beyond that period should also be made available if required. The people who will use the product or service User type: Roles using service Definition: Data Analysts - must swiftly undertake intricate and comprehensive analyses spanning numerous funding streams, frequently requiring the integration of multiple datasets. Their primary aim is to perform analyses that evaluate the realised benefits in relation to predefined output measures. Furthermore, they produce summaries for a wide array of stakeholders, all the while pinpointing disparities within funding categories and identifying potential areas for targeted funding to address specific challenges. Relationship Managers - rely on data to make well-informed decisions regarding provider performance. They utilise data to steer conversations and, when necessary, provide precise assistance. Ensuring the reliability and accuracy of data is crucial, as the work of Relationship Managers can significantly impact decisions related to the provider management framework and lead to recommendations for formal interventions. Funding Officers – leverage data to inform their funding decisions, as they hold the responsibility of disbursing payments to providers for the delivery of funded training. The assurance of accurate data is essential to instil confidence in the process of approving payments for services. Moreover, precise data helps prevent duplication of funding, ensuring that resources are not allocated to learners who have already attained the qualification or are ineligible for funding. Thus, it is imperative the warehouse solution has a data validation functionality. Project co-ordinators – employ data to provide timely information to project team. They will use data to update documentation for wider circulation at the request of project team members. Project Managers – use data for performance monitoring at both individual and group provider levels, thereby guiding performance-related decisions. Additionally, data is harnessed to obtain insights into the characteristics of funded provision, the distribution of funding across geographical regions, the distinct types of supported services, and their corresponding levels. Moreover, data plays a pivotal role in illustrating the extent of funded training and in verifying that providers operate within their designated regions. Furthermore, Project Managers will leverage data to construct reports that inform all stakeholders about the performance of funded provision. Commissioning Manager – data is employed at both granular and macro levels to comprehend the funded provision and its geographical distribution. This data will be instrumental in creating dashboards and provision maps using Power BI, facilitating strategic decisions concerning the funding needs within each region under the Combined Authority's jurisdiction against the Employment and Skills plan. Additionally, data will be used to map the quality of provision, guiding deep dive assessments of Teaching, Learning, and Assessment across the delivery partners. Programme Managers – data is examined to emphasise the advancement of project teams. It is employed to conduct one-on-one meetings with project managers and assess project progress in alignment with specified objectives. This information aids in strategic evaluations of provider performance and informs decisions regarding future funding. Moreover, data is used to keep broader stakeholders informed about project performance and to compile reports for broader discussions. Head of Service - data is employed by the Head of Service to examine the performance of funded provision against the Employment and Skills plan. It serves to apprise corporate members and Chief Executives about the current performance and is instrumental in formulating justifications for additional funding or the redistribution of funds to address areas of greater need. Data Manager – a central, technical role sitting outside of the Employment & Skills Service needs to be familiar with delivered Azure cloud data warehouse solution. Must have elevated privileges on the Azure resources of this solution, to help with technical delivery and ongoing support. This role, along with select team members with data engineering skills from within the business area, should be able to understand the data warehousing solution technicals and be able to work closely with the chosen supplier during design and build. This feeds into our knowledge sharing aspirations. External IT provider – our external strategic IT partner must be engaged early on to enable supplier user access to the Azure tenant. Supplier technical designs must be submitted to our IT provider for visibility and assurance/security review especially if the proposed design requires networking/security changes. Any pre-market engagement done None Work done so far The proposed solution should be compatible with Microsoft data warehouse & data entry/management products. (Azure, SQL databases, Power Platform, MS Office). The proposed solution should be deployable within our existing Azure tenant (managed by our external IT provider), and within our existing Hub & 3 Spoke (PROD/TEST/DEV) Landing Zone topology. The solution should use PaaS component where possible. For one of the teams involved, the proposed solution will replace their existing data warehouse built on VM/IaaS technologies. The solution should operate using lower cost Azure components/SKUs where possible, to reduce ongoing consumption costs. Consumption cost reporting & visibility should be made available to the business area, using resource tagging where possible to help identify which teams are responsible for certain costs. This project builds on the scoping work already undertaken in June 2022 and more recently in October 2023. This discovery work documentation is shared with suppliers interested in bidding for this requirement We have completed an initial overview of our existing Azure cloud architecture which will help shape the proposed solution. The supplier will work closely with the client Data Manager and our external IT provider to define the final data warehouse design. The proposed solution should be designed and built with the future in mind, whilst considering the Combined Authority's wider corporate data warehouse platform aspirations. Certain data warehouse components should be considered as shared components which could potentially serve and orchestrate additional data pipelines/data marts for other business areas outside of Employment and Skills. Knowledge transfer to the Combined Authority is essential, covering end-user instructions to operate dashboards/apps and separate documentation covering the technical solution & architecture. The technical design should be clear and transparent, so that the CA can replicate the deployment for other areas of business if required. The supplier should be able to provide technical support for the deployed solution, covering the 12 months after platform launch/go-live. Support beyond that period should also be made available if required. Which phase the project is in Not started Existing team Project Lead Graham Smith - Commissioning Manager Technical Guidance, Implementation & Design Theo Faull - GIS & Data Manager (Azure expertise) Ellis Millar – Senior AEB Data Analyst Megan Johns – Senior Skills Analyst Strategic Guidance Graham Smith – Commissioning Manager Bob O’Brien - ICT Service Lead Julia Frost – Head of Performance End users Various staff from Employment and Skills Service, to be engaged with by the supplier IT Administrators PM & Technical Resources from our external IT provider. Responsible for granting Azure tenant access to third parties and assuring security of proposed solution. Address where the work will be done Much of the work can be delivered remotely. Where there is a requirement to visit the Combined Authority office this will be at 70 Redcliff Street, Temple Quay, Bristol BS1 6AL. Working arrangements It is anticipated that a substantial portion of the work will be conducted remotely (preferably using MS Teams) on a full-time basis. However, there may be occasional instances where collaboration with project leads from the Combined Authority is necessary, and such work will occur at 70 Redcliffe Street, Bristol. The employer will be provided with advance notice whenever such collaboration is foreseeable. Expenses will be capped at £2000 For the duration of the project. Security and vetting requirements Baseline Personnel Security Standard (BPSS) More information about the Security requirements: For those members of the chosen supplier requiring direct access to the live data and / or the data processing warehouse, DfE / ESFA requirements are that they undertake a basic DBS check. For all other individuals, we are happy to rely upon the employment checks conducted by the chosen supplier. DBS checks will be at the supplier’s own expense written into the total project fund. DBS checks could potentially be bypassed if the supplier agrees to only use sample/anonymised data during design, build, testing, release. This data would be stripped of all personal information. The proposed solution must be able to handle and store data up to OFFICIAL classification (OFFICIAL – SENSITIVE). The data warehouse solution may be subject to a penetration test (pen test) if there are any publicly exposed endpoints/access points, or if our IT provider determine this new service as a potential attack surface/risk to the wider Azure estate. This pen-test would be funded by the business area. Any issues flagged as high or critical, and that relate directly to the resources/component deployed by the supplier, must be remediated by the supplier at no additional cost to the client. Shortlisted suppliers will be required to complete a cyber security checklist. Latest start date 30 July 2024 Expected contract length Contract length: 2 years 0 months 0 days Budget Indicative maximum: £92000 Indicative minimum: The contract value is not specified by the buyer Contracted out service or supply of resource? Contracted out service: the off-payroll rules do not apply