X214 Fleet Management Reporting Insights

Tender description

Summary of the work NI Water is looking to engage external support with the objective to combine and present the information from various systems on a series of reports via PowerBI to give a single view of our fleet assets, identify trends and insights to better inform management decisions in the future. Expected Contract Length Approx 3-6 months. Should not exceed 6 months but will be flexible to ensure work is completed Latest start date Friday 30 April 2021 Budget Range £60k to £90k Why the Work is Being Done A reliable fleet is essential in ensuring an effective, efficient mobilised frontline workforce. Without it, NI Water would not be able to provide its core business. NI Water has a fleet of circa 600 vehicles covering a broad range of vehicle types to service the various needs of the business. To manage the Fleet efficiently involves collecting large amounts of data from Ctrack, R2C, Jigsaw, and Oracle Financial systems. Northern Ireland Water has recently secured new contracts for the provision of service, maintenance and repair services for NI Water vehicles and for vehicle GPS, utilisation and productivity. NIW have identified the need for better insights into its Fleet to provide management information on performance, compliance, costs and utilisation. This data needs to be transformed for the production of 12no. key ‘Bluebox’ reports, 6 no. key performance measurements, 24 no. management information reports, to generate insights to better inform business decisions in the future. (An appendix including details of reports are available on request; email sourcing@niwater.com, 'REF X214') We need assistance from external experts to build a model that produces cyclical key reports and dashboards to give insight to how our Fleet is operating and how we can realise efficiencies. Problem to Be Solved The objective is to combine the multiple data sets into a single data model that will benefit the various business areas and create a single source to view our Fleet assets and develop the ability to provide, combine and trend management information on performance, compliance, costs and utilisation. Our goal is to ensure that our underlying datasets can be brought together and interrogated in parallel using one platform – Power BI. The main sources of data are: 1. Oracle – Master database of NI Water fleet assets with all relevant information included. Each vehicle has its own unique fleet number. 2. R2C – Contractor System records all NIW Fleet servicing, repairs, driver and financial costing entities 3. CTrack – Mileage, pool vehicle usage and idling data 4. Various fuel systems – Maxol, AllStar, Jigsaw (Fuel cards, fobs etc) 5. Manual spreadsheets i.e. RTAs, RTIs, phone logs, breakdown & recovery records, job scheduling reports Who Are the Users • Head of Customer & Operations Services & FMG Manager – They are responsible for the operation and maintenance of the Fleet and procurement of new vehicles. As such, the Head of C&O Services needs to take a general overview as to how each vehicle is being utilised, how it is being driven, cost to operate and maintain and if (and when) it should be replaced. Insights will be key to allow comparisons to be drawn over different functions and vehicle types to determine which vehicle is best suited and most economic for NI Water’s need. • CSD Business Services MI & Data Lead / CSD Business Services MI & Data Analyst – Provide monthly fleet reporting including utilisation, maintenance, costs, accidents/incidents, insights and trends to the business, focusing on data accuracy and highlighting problem areas • Compliance Data Analyst – Provide insight into trends, costs and performance • Contract Manager – Monitor contractor performance from Key Performance and Management Information Reports on a monthly and quarterly basis • Management Accountant – Finance Business Partner - provide monthly costs and analysis against budget for both the Company in totality and by Function Work Already Done Our in-house teams have already started work on the building of a Power BI Fleet platform. Data from Oracle is now refreshing within Power BI, alongside downloaded reports from R2C and CTrack and manual Excel reports detailing RTAs and RTIs. The logical data model is underway and a copy can be provided on request. NI Water have agreed the 6 KPIs to be measured as well as the Management information Reports to be provided. The data source and data requirements for these have been identified. The KPIs and Management Information reports have been developed in Excel, which are currently available. Existing Team Rod Neill Head of Customer & Operations Services Lisa Ward CSD Business Services MI & Data Lead Cathy Maxwell CSD Business Services MI & Data Analyst Neil Winters FMG Manager Jonathan Kirkpatrick Systems Accountant Laura Kinloch Management Accountant – Finance Business Partner Kieran Harkin Senior Contracts Manager Grainne Walls Compliance Data Manager Ciaran Nicell Head of Analytics Geoffrey Collins Senior Data Scientist Sean McAuley Project Manager Current Phase Live Skills & Experience • Demonstrate experience of amalgamating multiple data sources into a dataset in Power BI that can ultimately provide insights that will help to inform business decisions (10 points) • Demonstrate experience using insights gained to provide opportunities to find efficiencies in operations, maintenance, costs. This should include supporting data to identify actual gains you have made previously (10 points) • Demonstrate data expertise in helping to create logical and physical data models and dashboards, to ensure clients can be self-sufficient and can build on the platform as needed (10 points) • Demonstrate experience of work with 3rd party APIs to be able to import "real-time" database information (10 points) • Demonstrate experience in helping the client to upskill so they can administer and develop the application to create and generate new reports easily in the future (10 points) • Demonstrate experience with PowerBI, including creation of workspace apps, dataflows, building measured using DAX, good practice in data visualisation etc. All solutions must be compatible PowerNI platform (10 points) • Demonstrate experience of working with fleet management platforms. What efficiencies/savings would be typical in this type of work, outside the previously mentioned reports? (OPEX efficiencies/contract efficiencies/maintenance efficiencies etc.) (10 points) • Demonstrate experience of working collaboratively with a client as part of a joint team to get the best resolution and outcomes (10 points) • Demonstrate how you as a company stay up to date with latest emerging analytics techniques, including any academic affiliations or collaboration efforts (10 points) • Evidence of documentation provided for similar projects (10 points) Work Location Belfast, Northern Ireland (although working remotely) Working Arrangments This work will be conducted through remote working, engaging with the existing team via Microsoft teams and emails. Current restrictions due to the COVID-19 pandemic should be strictly adhered to at all times. Regular updates on progress will be required, to ensure this project is properly scoped, planned and NI Water are kept up to date on issues, risks and delivery milestones. Successful suppliers should: •provide regular updates on progress on issues, risks and delivery milestones. •have a well formulated plan and the necessary resources contacted ahead of time to ensure availability •have the correct professional resources available for queries Security Clearance Basic Access NI check is required. Data sharing agreements will be required as appropriate, sensitive data obtained should be anonymised to adhere to strict GDPR regulations. Additional T&Cs NI Water intends to use eTendersNI for the 2nd stage of this procurement. Shortlisted suppliers will be invited to submit their responses on their proposal, cultural fit and price via this platform. Scoring mechanism as follows will be used during both stages of the evaluation:- 5 - Exceeded 3 - Met 1 - Partially met 0 – Not met or not evidenced NI Water does not envisage the need for any additional terms and conditions. No. of Suppliers to Evaluate 3 Proposal Criteria • Identify a RAID (Risks, Assumptions, Issues, Dependencies) list that needs to be considered as part of the plan. • Describe how you will tackle this problem from start to finish. This needs to be well understood and a clear plan built so that nothing is missed. • Describe how you ensure accuracy of the data used. This data forms the basis for our analytics work so is essential our reporting/calculations are based on the right data. • Describe how you will work with/hand over to NI Water (existing team as detailed above) to ensure we can update, trouble shoot and manipulate the Power BI solution • Proposed team structure you will require including experience (CVs) Cultural Fit Criteria • You should be willing to work as a team. We expect knowledge transfer. Question: What drives your organisation to deliver and how will you ensure collaboration within the team? • You will be experts in your field. Question: Demonstrate you have the right solution and that your plan shows a clear understanding of the task in hand? • We need to ensure availability, quality of work plus adhere to timelines, budget. Question: How ca you ensure availability for the entirety of this engagement and remain within constraints? • We expect by working alongside our partner, we will grow our skill sets through knowledge transfer. Question: How will you provide knowledge transfer to our staff? • Trusted partner. Question: How will you bring us to the next level in ensuring we have 100% accurate, valid data? (10 points) Payment Approach Capped time and materials Assessment Method Presentation Evaluation Weighting Technical competence 65% Cultural fit 15% Price 20% Questions from Suppliers 1. Is there currently an incumbent supporting this piece of work? No this is a brand new contract 2. Would offshore resourcing for the project delivery being considered? In principle we have no issue with offshore delivery. However we will have to ensure adherence to our security protocols and use of secure sharepoint sites. 3. How many spreadsheets are being integrated in the PowerBI data model? We have no defined number on this. 4. Post lockdown, is there an expectation for the team to be present in NI water office location? No expectation in the short term but will monitor as the pandemic rules change. Regular contact meetings would take place via Teams for now. Post lockdown we would expect this to continue in some form as people and NI Water would not mandate you to be present in the office to complete the work. 5. Could we also request a copy of the “logical data model” that the DOS advert mentions? Thank you in advance. The successful supplier will be instrumental in helping to build the logical data model. 6. Is there an existing Data Warehouse within the organisation? Has any consideration been given to the benefits of loading the data from the source systems into a data warehouse to allow Power BI to consume data from there? Yes but this about having the right supplier to help with building the right solution. 7. 7 What version of Power BI is being used? – Professional/Premium We have a Premium licence and also many users with Pro licences. 8. 8 Are there any other Power BI projects ongoing/are there plans for future projects – will there be a need to integrate these? Yes. We are building an IOC (Intelligent Operations Centre) which will house many of the efficiency and operational analytics dashboards. These should all have the same “look and feel”. This information will be made available to the successful supplier but is not an initial requirement to consider. 9. 9 The systems listed as source systems – are these on premise/cloud or mixed? Mixed 10. Will you be using the Power BI data gateway for any on-prem systems? We do and have used Power BI data gateways for various other things. Again, we expect that the successful supplier will help to advise us of the appropriate solutions we need. 11. Are you planning to do any data science/machine learning activities on top of this data once it has been modelled and consolidated? Our aim here is to build a dashboard to provide visuals for the major reported KPIs. Also, we very much want a supplier that can tell us the stories behind the data – are we paying too much for something, are there correlations between any of the variables? We would expect the successful supplier to help us with the “art of the possible” and reveal what our data is actually telling us. 12. What volume of data is currently in each data source and what are the future growth forecasts? – Daily/Weekly/Monthly data volumes, are there spikes at particular times? Unknown at present. We may add further data streams to help with analytics. Again, we would expect the successful supplier to help us determine our data efficacy, data cleanup required, additional points needed etc. If there are spikes we would expect a supplier to identify those and suggest causes/ remedies. 13. How ‘real time’ do the reports have to be? We do not expect these to be up to the second but will work with the successful supplier as to getting them as up to date as possible. We will need to assess the various source inputs as to when updates can happen and then decide on a suitable update frequency. 14. Do you provide a staff member on your side that will offer consultations for the development team regarding the marine technical terminology? We see that it is very prevalent here and one needs to understand it in order to work efficiently on the project. Marine Technical terminology? This is for fleet management of our vehicles. However, people with expertise in the various applications will be on hand to help answer questions.