Data strategy implementation & support

Description

Summary of the work Implementation data-architecture & providing-ongoing calldown-support, maximising use of existing-data. Comprising: • Implementation Azure-Data-Factory/Power-BI to integrate with CCWs Microsoft-Azure public-cloud computing-platform. Provide robust data architecture that consists: o Data repository(Microsoft-Azure) o Analysis/visualisation-software(Power-BI) o Ingestion/Transform(Azure Data-Factory) • Set-up/migration historic-data for Water-companies complaints/operational-data(largely Excel based) Installation of automatic-link to complaints received/captured on Oracle-CRM. Latest start date Wednesday 1 December 2021 Budget Range £80,000 (incl VAT) This will cover both the implementation and call-down support for the first year of the contract. £12,000 has been set aside for recurring annual support for the three-year period. Why the Work is Being Done CCW currently collects data from a number of different sources and in different formats. This data includes: • Complaints made to CCW (Oracle CRM) • Complaints made to water companies (Excel) • Water company operational data (Excel) • Water company performance and financial data (Excel) • Research data (Various – including Excel) This data is stored on multiple platforms and in different locations. There is currently no way of comparing different data sets other than through manual data manipulation. This typically involves copying data from one source to another, adding significant risks to our data quality and analyses which ultimately creates a huge reputational risk to CCW in that analysis we publish is not robust and authoritative. The current way of working is also largely resource intensive, menial and does not make the best use of our analytical capability. In 2020, CCW developed a data strategy. A key part of this was a data discovery piece that focused on the following technical objectives around the management, storage and access to data. The proposal resulting from the data discovery phase identified the technical approach which CCW now wish to deliver. Problem to Be Solved Transform the data architecture to enable CCW to develop a ‘single version of the truth’ and maximise the use of the data it collects. Key deliverables are: • Purchase and set up recurring annual license's for Azure Data Factory and Power BI for approximately 50 users. Advise on Azure subscription services, so we get a true reflection cost. • Implement Power BI. • Formatting of existing Excel based templates (in several different formats) in preparation for uploading. • Upload of Water company complaints and operational data to Azure from existing Excel based templates to enable effective tracking of performance over time • Transfer of existing data from Oracle CRM • Implementation of automatic uploads from Oracle CRM • Knowledge transfer of data components to CCW in-house Evidence and Insights team • Call down technical support • Completion of a full data protection impact assessment to ensure that the solution fully adheres to current data handling legislation. Who Are the Users The primary users are CCW analytical staff with dashboard access through Power BI planned for other key CCW roles. So all users have access to the single source of data. Early Market Engagement Our managed ICT service provider undertook a data discovery exercise in 2020. The outcome determined that the above approach was the most suitable and now being pursued. Work Already Done The implementation of the Microsoft Azure platform is currently being scheduled and is being targeted for completion by late 2021/early 2022 by our current managed ICT service provider. Existing Team There is a dedicated analytical team at CCW led by the Head of Evidence and Insights. The skills within this team are largely Excel based with no data warehousing specialties or previous competency around Power BI. To ensure the project is delivered effectively, an internal project team has been created to work closely with the successful supplier. Current Phase Beta Skills & Experience • Experience working with Azure server less data services (Synapse, Azure Data Factory etc.) • Ability to combine data analysis, data modelling and data engineering in an end to end solution • Advanced knowledge of visualisation techniques, especially Power BI, and embedded Power BI solutions • Agile design & development of data marts to support user-driven analytics • Flexible, agile & collaborative approach Nice to Haves Previous experience in the utilities/water industry Work Location We would require weekly meetings for project updates via video conferencing & face-to-face meetings at our office in Birmingham city Centre. Working Arrangments The successful supplier will need to work with the project team, consisting of various CCW staff around the business who will provide valuable insight into their area of work. There will also be a requirement to work with Oracle to successfully implement the connections to our CRM. We also have an IT Manager who will form part of the project team and will be able to provide technical support and laisse with our managed ICT service provider. Security Clearance Baseline check (blue) security clearance will be required for CCW. Additional T&Cs • Project commences December 2021 • Completion date for the initial contract term is 31 March 2022 • There will be a requirement for call-down support for an initial three-year period. It is envisaged that this level of support diminishes as in-house knowledge and experience increases. No. of Suppliers to Evaluate 7 Proposal Criteria • Technical solution and futureproofing • Approach and methodology • How the solution aligns with our data strategy and the wider organisational needs • Risk management and timeframe • Total cost Cultural Fit Criteria • Work as a team with our organisation and other stakeholders to find a solution for all • Be transparent and collaborative when making decisions • Take responsibility for their work to ensure a successful outcome • Share knowledge and experience with our team members • Challenge the status quo and find new ways to deliver • Can work with clients without the technical data architecture expertise to find solutions that work for all Payment Approach Capped time and materials Evaluation Weighting Technical competence 60% Cultural fit 15% Price 25% Questions from Suppliers 1. You ask for “Baseline check (blue) security clearance will be required for CCW” – is this the same as Baseline Personnel Security Standard (BPSS) or a different type of clearance? Yes these are the same standard. 2. Would it be possible to use a mixed onshore/near shore development capability to deliver and support the implementation? CCW would prefer an on-shore team for potential meetings with the project team, analytical staff and our managed ICT provider. 3. Would undertaking this work with a mixture of an on-shore team in the UK and a co-existing development team based in India be an acceptable arrangement for this project? CCW would prefer an on-shore team for potential meetings with the project team, analytical staff and our managed ICT provider. 4. Around “Formatting of existing Excel based templates (in several different formats) in preparation for uploading”. Please can you expand on what this would entail and the volume of excel documents that would need reformatting? CCW will have between 20-25 spreadsheets, which will includes annual complaints data from companies or CCW going back to 2006/07, as well as annual data, root cause breakdowns and monthly/quarterly breakdowns going back to start of the market. CCW will work with the successful supplier to condense these where possible. 5. 1. Five (5) data sources are described. How many distinct entities or files are involved? For example, for complaints registered directly with water companies are in the format of an Excel export, is that a single file and as single sheet? These are essentially single worksheets and not complex workbooks. 6. Will CCW IT staff be responsible for establishing secure connectivity between the on-premise Oracle CRM and Azure? No, there is no scope for this in the Azure work. We envisage the successful bidder to work with us and Oracle to secure the necessary connection. 7. A figure of approximately 50 Power BI users is described. How many report authors and how many consumers of reports does CCW anticipate? Estimate up to ten people for report writing and the remainder as consumers. 8. Does CCW anticipating publishing reports to public users? If so is the requirement to enable this, at a platform level, or to action this by delivering public reports in collaboration with the CCW team? For this tender public reports will be delivered through the CCW team. However part of our longer-term vision will be to make these available from our website and it will therefore be advantageous to ensure that the solution is future proofed to enable this to be developed as simply and effectively as possible at some point in the future. 9. It is stated that the vendor should purchase and set up recurring annual licenses for Power BI licenses for approximately 50 users. Power BI (Service) is purchased on a monthly subscription as a Pro or Premium Per User license, or as a Premium Capacity (with optional autoscale). If CCW has Microsoft 365 E5 licenses these already include Power BI Pro, and customers can purchase Premium Per User licenses as an add-on rather than pay the full price. Do any of the ~50 CCW users have access to Power BI under existing subscriptions? We are looking at Microsoft licensing as part of the wider CCW IT Strategy. Currently we envisage Power BI to be a one-off purchase. 10. Does CCW consider the cost of Power BI subscriptions to be included in or separate from the stated budget? CCW will manage the purchase of license's and £5'000 of the £80'000 will be put aside for this purpose. CCW are looking at Microsoft licensing as part of the wider CCW IT Strategy. Currently we envisage Power BI to be a one-off purchase. 11. Does CCW consider the ongoing monthly costs for Azure IaaS & PaaS and Microsoft SaaS products o be included in or separate from the stated budget? Our budget for Azure subscription services has not been included in the project budget. The successful bidder will advise on such services and costs. 12. How many Power BI dashboards and how many individual reports does CCW envisage for delivery? This is unknown at this specific time. We will need dedicated dashboards that allow us to report on the Household and Non-Household sectors at both individual and industry level. There will also be the requirement for a number of management information dashboards. The primary importance is the knowledge transfer process that will enable us to comfortably create dashboards and reports in-house. 13. It is stated that there will be a requirement to “Purchase and set up recurring annual license’s” – is this included in the £80K budget? £5'000 is included for this year’s budget and will be managed through CCW as required. 14. With the Call-down support / recurring annual support, What are the “on call” hours and SLA expectations? Our initial expectations would be a ‘generic’ 9-5 support with an SLA of 8 hours fix. This may change once we have the project up and running. Suggest bidders include all proposed support contracts for CCW to review. 15. Why would the current ITC service provider not be carrying out this work? As a public sector organisation, CCW need to ensure we get the best solution at the best price by going out to the competitive market. This gives all providers an equal chance as well as the current ICT provider. 16. You have stated that “The implementation of the Microsoft Azure platform is currently being scheduled and is being targeted for completion by late 2021/early 2022 by our current managed ICT service provider.”, what would happen if this runs behind, with a 1st Dec start date for this piece of work? Current forecasting suggests Azure will be in place December/January. Our original statement of works suggests we are able to continue with our Data Strategy project whilst Azure is implemented. 17. “There is a dedicated analytical team at CCW led by the Head of Evidence and Insights. The skills within this team are largely Excel based with no data warehousing specialties or previous competency around Power BI. To ensure the project is delivered effectively, an internal project team has been created to work closely with the successful supplier.” – what training / mentoring expectations are they? What do they mean by “work closely with” (will affect costs / timelines). CCW means work in partnership with the successful supplier. CCW staff will need to be an integral part of the work and of the team. To ensure knowledge is transferred at all key points, through day to day mentoring / working and training as and when required. 18. In Essential Skills and Experience list the third (3rd) point mentions ‘embedded Power BI’ solutions. Is this referring to the Azure power platform product, which is mainly aimed at ISVs for embedding Power BI reports in their apps, or is it simply referring to Power BI Service (not Azure) and the mechanism for publishing a public report with the generated link to embed it in a website? CCW aim is to create interactive public (or partner-) facing Power BI dashboards, accessible via an external website. 19. With regards to the impact assessment is there specific data handling legislation CCW would like considered for example GDPR? Yes GDPR would indeed need to be considered. 20. Completion of a full data protection impact assessment” – what if this fails? CCW hold the responsibility for meeting data protection legislation and therefore any failure will need to be owned and addressed by CCW. 21. Please could CCW provide a rough idea of Data volumes by row/GB? We are collecting 300 discreet pieces of information on each Excel spreadsheet although approximately 30% of these are calculated fields. 22. Will the output of the discovery be made available? CCW is certainly happy to share the outcomes and recommendations from this work.