Configuration of Assurance Database and Power Bi tool to extrapolate and analyse assurance data

Tender description

Summary of the work This project will cover the following areas: 1. Relocation of assurance data from different sources into one spreadsheet database. This includes Implemention of data entry control. 2. Improvement of database user interface. 3. Installation and configuration of Power Bi to analyse assurance data extracted, showing trends, correlations and trigger points. Expected Contract Length 20 working days Latest start date Monday 18 January 2021 Budget Range Our budget is capped at £14,400 (all inc.) for the project to be delivered within 20 working days. We expect suppliers to come in lower than this budget if possible. Why the Work is Being Done Within a control period, ORR’s Engineering and Asset Management (E&AM) team undertake different types of assurance activities to hold Network Rail to account as required by the "Holding Network Rail to account Policy" and the "Network License Conditions" they have signed unto. These activities produce a large amount of information and data which the E&AM team use to monitor Network Rail’s delivery and Asset Management capability against agreed regulatory targets. It is also used to inform ORR’s periodic review process. To utilise this data and information effectively, the E&AM team created an Asset Management Assurance model to assess and quantify it in a consistent way. We have also created an Asset management assurance database on an excel sheet to capture the data and information assessed. We now wish to link the information and data in our Asset management assurance database with other sources of assurance data and extrapolate them to Power Bi where they can be queried and analysed for trends, correlations and triggers. We aim for this project to be completed on or before the 19/02/21 Problem to Be Solved The problem is that we have engineering and asset management assurance data stored in different locations and formats which can't be assessed, queried or analysed for trends, correlations or triggers in a consistent way. Who Are the Users ORR's Engineering and Asset Management team needs to assess, query and analyse assurance information/data received from Network Rail as part of its regulatory duty to hold Network Rail to account. Other users includes ORR's senior management team who need to have access to quantified assurance information to make informed strategic decisions at a higher level. This is also done to hold Network Rail to account. Early Market Engagement Early market engagement has shown that this project can be delivered in the span of 20 working days. Existing Team There are no existing suppliers, you will be working with a Senior Asset Engineering in ORR's asset management team. When we say working, we mean you will be reporting progress to the Senior Asset Engineer and getting approval for work done. Current Phase Not applicable Skills & Experience • Have experience in Business Intelligence & Data Analytics • Have experience in using and configuring Microsoft Power Bi: Visualisation and Analytics • Have experience in using and configuring Microsoft Excel: Advance analytics and VBA Macro Nice to Haves Have experience in using Cloud Database Work Location Due to COVID restriction we expect this work will be done remotely in line with any public health requirements set out by the government. Working Arrangments Bi weekly meetings will be required with the individual or team. This will be done via Microsoft Teams due to COVID restrictions. Adhoc meetings or phone call are also allowed by both parties if there are issues that need to be addressed before a Bi weekly meeting. No. of Suppliers to Evaluate 3 Proposal Criteria • Methodology - The proposal should set out the methodology by which the project requirement will be initiated, delivered and concluded. • Delivery - The proposal should set out how and when the project requirement will be delivered. • Experience - The proposal should set out any experience relevant to the project requirement. • Cost / Value for money - A fixed fee for delivery of the project requirement (inclusive of all expenses). Cost breakdown required. Cultural Fit Criteria • work as a team with our organisation and other suppliers • be transparent and collaborative when making decisions • have a no-blame culture and encourage people to learn from their mistakes • take responsibility for their work • can work with clients with low technical expertise Payment Approach Fixed price Assessment Method • Case study • Work history • Reference • Presentation Evaluation Weighting Technical competence 40% Cultural fit 20% Price 40% Questions from Suppliers 1. You mentioned that you need PowerBI setup and configuring do you have any Microsoft 365/Azure Subscriptions in place already? Yes, we have Microsoft 365/Azure Subscription. Creating a Power BI report is part of our current Microsoft licensing agreement. 2. Could you elaborate if and how this fits in with a broader data problem? No, this project doesn’t fit into a broader data problem. We simply want to combine existing sources of assurance data into one spreadsheet, improve the UI of that spreadsheet, implement data entry control and have that data visualized and analysed by Power Bi to show trends and correlations across data sources, locations etc 3. Please could you provide a more detailed timeline post stage 1 submission • Shortlisted suppliers notified - 08/01/21• Interviews and presentations - 11/01/21• Award contract - 15/01/21• Start-up meeting and commencement - 18/01/21• Bi-weekly progress updates from the - 01/02/21• Interim presentation of Beta database and visualisation tool - 11/02/21• Debugging and final QA completed by the - 15/02/21• Final presentation of database and visualisation tool - 18/02/21• Signoff and integration into ORR’s existing asset information systems to be completed by - 19/02/21• Project completion on or before - 19/02/21 4. Are you looking to pull all data sources into a single spreadsheet? Could this be a database instead of a spreadsheet? Would you consider dataverse (formerly CDS) as the database As per the requirements published, we are looking to pull together our assurance data from different data sources into one spreadsheet before they extracted into Power Bi for analysis, correlation and visualisation. In the future we will explore options of using an actual database in lieu to a spreadsheet for data storage. We do not wish to consider Dataverse as a database 5. With reference to, “Improvement of database user interface”, what are the current issues with the database user interface The following improvements are required on the consolidated excel sheet:1. It is should be interactive - Provide instructions on what data should be entered into what cell. Provide error messages when wrong data is entered2. It should be user friendly 3. It should have data entry control and validation.4. It should utilize VBA Macros to speed up tasks and automate processes.5. All of the above points should be encompassed into aesthetic data entry forms for each source of data. The goal is to make data entry easy, interactive and controlled. 6. With reference to the requirement, ” This includes Implementation of data entry control”, What type of data entry is expected into the consolidated spreadsheet? Can we assume that after the data consolidation activity, all the data entry happens in the consolidated spreadsheet instead of updates at individual sources There are four primary sources of assurance data (see below) currently stored on separate excel sheets and we need them consolidated into one excel sheet on separate tabs. There are other data sources but these are secondary.1. Asset Management Assurance Database – This is where assurance data which has be assessed using our assurance model is stored2. Risk Register 3. Recommendation Register 4. Lessons Learnt Register When data is entered into the consolidated excel sheet, the separate data sources should be updated. When data is entered into separate data sources, the consolidated excel sheet should be updated. 7. What are the defined KPIs (Key Performance Indicators) and Metrics that are envisioned to be implemented in Power BI The KPI and metrics envisioned will be based on the consolidated data. Some of these are bespoke, for instance the metrics for Asset Management assurance is based on our Asset Management assurance model. There will be metrics for risks associated with assurance activities such as Severity and Likelihood. Recommendations will have metrics like Recommendations opened or closed. Obviously there will be more metrics than these and KPIs will be provided. More importantly, Power Bi has to be configured to utilize all the data in the consolidated spreadsheet to the maximum, in terms of trends, correlations etc 8. Will the relocation of data from different sources be an ongoing work for each data refresh, or it is considered to be a one-time activity If configured properly, the data moving between the separate data sources and the consolidated data excel sheet should update automatically. When data is entered into the consolidated excel sheet, the separate data sources should also be updated automatically. When data is entered into the separate data sources, the consolidated excel sheet should also be updated automatically. The consolidated excel sheet should have a two way link to the separate data sources on separate excel sheets.In regards to data exported to Power Bi for analysis, this will be a onetime activity as we should be able to refresh it ourselves.