Asset Database Build & Exploitation Project

Description

Summary of the work Design, develop an Azure SQL based database to capture our asset data with a mobile friendly PowerAPP interface for our surveying team and some critical PowerBI reports for management information & reporting. Expected Contract Length 6-8 weeks Latest start date Sunday 31 January 2021 Why the Work is Being Done This work will allow the Group to exploit it's refreshed asset data from its stock condition surveys that are being carried out. The work must be completed by the end of March Problem to Be Solved The data is currently being captured in a 3rd party SQL based database. Greensquare needs to build it's own Azure SQL database instance to replicate the table structures and allow migration of this data into our estate. This data must be mapped, merged and migrated into the proposed solution. It also requires the development of a simple PowerApp to allow surveying teams to interface with the database and the production of 1-5 PowerBI reports to support analytics. This includes a front end application to view and interrogate the database. As the the database will hold asset data on properties, with unique ID of which is held in another Progress based database, the database must be required to interface with this database to be uplifted when the property population changes. We are very open to having innovative solutions based on the the CDS/Dataverse via PowerApps premium. Who Are the Users As an asset management lead, I need to understand the current condition of all of my stock so that I can understand my investment planning. As a surveyor, I need to be able to complete a survey on my mobile device that updates the database with the condition of the stock I find. As a BI Engineer, I need to be able to extract the data into an Azure Data Warehouse to support PowerBI reporting and data modelling for predictive analytics. As a database admin, I need to be able to ensure and monitor the size and efficiency of the Database for operational efficiency processes. As a user of the data, I need a simple user friendly PowerApp application view to the data, so that at the most basic level users can search for a property and the data (and potentially repairs/maintenance/compliance responsibility) for that property can be returned in an easily digestible format. This is particularly useful for the Repairs and Maintenance teams, who need to do this kind of querying regularly – for example, they may decide not to repair some aspects of a kitchen if it’s due to be replaced. Work Already Done Business requirements have already been captured both functional and non-functional. Existing Team There is a business lead, surveying lead, a project manager and a data/BI Engineer support. Current Phase Alpha Skills & Experience • Demonstrate excellent competence with building Microsoft Azure SQL databases • Demonstrate excellent competence with building integrated PowerApps into Azure SQL databases that are mobile friendly • Demonstrate excellent competence with designing PowerBI reports that interface with Azure Sql Databases • Demonstrate excellent competence with designing Azure SQL databases to best practice from a security posture. • Demonstrate excellent competence with working with field based colleagues to ensure solutions are fit for purpose • Demonstrate excellent competence in working in an Agile way to deliver capability in an incremental way • Demonstrate excellent competence in working on client side environments • Demonstrate the ability to deploy a competent team rapidly. • Demonstrate excellent competence in providing handover training and deployment support • Provide case studies of previous work. • Demonstrate excellent competence in building front end applications to interrogate and view the database. • Demonstrate Excellent Competence in data mapping/migration/merging • Demonstrate excellent competence in interfacing Azure SQL databases to other databases. Nice to Haves • Flex resources as required based on a dynamic approach • Provide innovative ideas whilst delivering the core requirements • Excellent experience in interfacing SQL with Progress databases Work Location Chippenham Wiltshire - however fully expect to be working remotely. Working Arrangments Remote working during COVID-19 No. of Suppliers to Evaluate 5 Proposal Criteria • Technical Solution • Approach & Methodology • How the approach or solution meets user needs • Estimated timeframes for the work • How they’ve identified risks and dependencies and offered approaches to manage them • Team structure • Pricing Model • Key Assumptions & Exclusions Cultural Fit Criteria • work as a team with our organisation and other suppliers • be transparent and collaborative when making decisions • take responsibility for their work • be comfortable standing up for their discipline • can work with clients with low technical expertise Payment Approach Fixed price Evaluation Weighting Technical competence 75% Cultural fit 5% Price 20% Questions from Suppliers 1. Who’s in the incumbent and how long they have been the incumbent? There is no current incumbent 2. Who’s on the panel? The tender panel consists of - Head of Planning & Business Intelligence, BI Manager, Service Transition Manager, Data Quality & Reporting Project Manager 3. Can you confirm the budget? This phase of the project is estimated to be between £75,000 to £100,000 4. Is there an incumbent? No 5. Please can you advise on a budget for this project? This phase of the project is estimated to be between £75,000 to £100,000 6. Will we be using an Azure subscription belonging to GreenSquare group for the project? Yes. Will agree with the successful bidder the right license to secure, however will provide this as part of the contract dependencies. 7. Is there a potential incumbent that you might have in place No 8. Do you have an estimate of the budget and/or max day rate? This phase of the project is estimated to be between £75,000 to £100,000 9. Regarding the question: “Provide case studies of previous work”. Can you please confirm that you only require weblinks in response to this question? Yes this is fine 10. Is there an anticipated budget for this phase of the project? This phase of the project is estimated to be between £75,000 to £100,000 11. Which SQL dialect is the existing database in? Is it Postgress? The tender mentions ‘Progress database’ – is that a typo? We expect the SQL database for the solution will be Microsoft SQL Server. Our Housing Management database is a Progress OpenEdge database and will only be used for an interface of master data from the OpenEdge database to the SQL Server solution and an integrated URL-linked view of the SQL server solution within the Housing Management system front-end. 12. Is there a budget range for this work This phase of the project is estimated to be between £75,000 to £100,000 13. When you say Field based colleagues are you referring to our developers based at your site or potential users of the solution out of the office ‘in the field’ No - we are referring to our surveying team - they will need to be able to use the interface application on ruggedised tablets whilst conducting surveys 14. When referring to “working on client side environments” are you seeking experience working at client offices or your IT environments Our IT environments 15. You ask ‘provide case studies’ are you seeking a list of case studies that can be detailed if successfully down selected. We only have 100 words. Links are acceptable 16. Can you please confirm that the functional and non-functional requirements will be issued as part of the proposal phase of this procurement? We will hold a detailed Q&A session on Tuesday the 19th at 13:00 GMT to discuss the project in more detail via MS Teams. We will also issue persons attending that call the info afterwards for reference.Please email - Robert.Glenister@greensquaregroup.com for an invite if you wish to attend 17. How much data is there in the existing database? Ideally please can we know if it is megabytes or gigabytes, how many tables, how many rows in the largest table? Estimated total size when complete 8-10GB5 tablesMax 21 columns min 2Though as our solutions is developed this may be expanded 18. All suppliers will be asked to provide a written proposal.’ Can you advise if this written proposal is separate to the Requirements and Evidence; and, if so, where / how it is to be submitted. If bidders are selected for the next phase of selection (i.e. short listed they will be invited to submit detailed proposals then inc cost & commercials 19. Can you advise where and how the pricing and commercial elements of the proposal are to be submitted. If bidders are selected for the next phase of selection (i.e. short listed they will be invited to submit detailed proposals then inc cost & commercials 20. What is the classification of the data we will be handling and what is the minimum security clearance level required of the team? This will be classed as 'internal' against our on classification criteria. As part of onboarding, contractors will be expected to sign NDA type agreements to say they will not share data. However there is no need for formal security clearances such as SC or DV 21. How big is the data? Rough order of magnitude, How many tables/columns/GBs/PBs? Please see answer to - "How much data is there in the existing database? Ideally please can we know if it is megabytes or gigabytes, how many tables, how many rows in the largest table?" 22. How many attributes about a property would typically be collected during a survey? For stock condition - 275 in total, with a break down of 112 photos, 94 yes/no questions and 69 other questions (mostly integer or date fields) 23. What frequency would the data need to be captured, eg is there is need for offline storage before upload in areas with no mobile signal. It is expected surveys will be done daily and yes there is a requirement to cache on a local device of no signal for upload is available and then to sync when one is. 24. What devices do your team use Windows, Android or Apple? Office/base users are using latest version of Windows 10 on mixed screen sizes - for field staff, Windows 10 surface pro's are expected but currently on android tablets 25. Do you have Power BI and Power APP Licences in place already or will this from part of the bid or are licences excluded and will be dealt with separately? We will provision the necessary Power BI and Power App licenses as required 26. How many surveyors will require end user training? Assume around 10 27. How may Power BI/Power Apps Developers do you have and what skill level do they have? None dedicated to this project, hence needing to go to market and this being part of the requirement for the successful vendor to build these components (with our support on requirements) 28. What is the estimated data growth in the master data when these “property population changes” occur ? Please see answer to "How many attributes about a property would typically be collected during a survey" 29. Are there any specific Disaster recovery / High Availability requirements for the data? Yes we would need to ability to recover to the previous days position 30. Is the data in the 3rd party database sensitive in nature? E.g. contains personal names / addresses etc and so will require a higher-level of security once in Azure ? No, only addresses but not linked to an individual or individuals. The database will identify if a property has been adapted to support disabled living, however this is the only 'personal' attribute and, again, not linked to any person in anyway. 31. Will the supplier be expected to build a new secure Azure environment for GreenSquare to host the data in the Azure SQL database or does one already exist they can use ? We will provision the environment on the suppliers behalf but would expect them to identify the config they require 32. Is there an offline requirement for surveyors? Yes 33. What is your current Housing Management system Orchard 34. Will you need a Data Warehouse as part of this? Not as part of this phase. We will be building a data warehouse in the future and so this will need to support that aspiration, but this will be a data repository with an interface/application layer. 35. You mentioned an Open Edge Progress Database is this your Housing Management System? Yes this is our Housing Management system (Orchard) 36. What are DB security reqs We are doing a DPIA to consider GDPR impacts. Otherwise, other than stand user logins using MS single sign on, there are no specific security requirements. We expect the database to be built to best practice and be able to log event for example. 37. Will you need to ingest data from PIMSS on a regular basis, i.e. do we need to create an SSIS protocol to do this? No - we will get the database transferred to us in MS Sql Server once. 38. Why the end of March timescales and is there a team full time on this? We recognise the timelines will be taught and we expect the successful bidder to recognise this in the proposals. There is a not an internal team available full time on this hence requiring support. However subject matter experts in the business will be made available and a weekly sprint structure has already been stood up. 39. Can you advise numbers of users? Surveyors - 10-20 maxConsumers - up to 30And all are part of the existing tenancy 40. What do the field service use? The field staff will use a mobile device (tablet) - we currently use android devices. 41. How will the data be transferred The new data being collected in PIMMS will be given to us in Sql Server 42. How will provisioning be done? We will provision all & we can do very quickly as we have a supplier in place. 43. Accessibility Checks? We will define browser & versions & accessibility requirements. 44. Are you already on Azure? Yes with some Azure Sql Server instances already in play 45. When surveyors are out and about, how often do they need to refresh? Surveyors will download their job list for the day and then once out in the filed they will complete their assigned surveys. Because we work in some quite remote locations they will need the ability to have their completed surveys held on the local device until such time as a suitable siganl is acquired or connected to WiFI again. 46. Can you confirm the current system? ARC Asset current and PIMSS is what all of the new data is being collected in.The bulk migration will be from PIMSS however we will also need the ability to take some information from ARC asset 47. What will handover be and to who? The field based end users in terms of accepting the interface application and being trained.The office end users in terms of accepting the interface application and being trained.The BI team in terms of the database set up, config and associated PowerBi reports. 48. Why was the use of PowerApps & Dataverse, mentioned when you are seeking to use Azure Sql Server? We do not use this at the moment, however we wanted to mention it to allow bidders to opportunity to suggest it as a solution in case they feel this could offer a better solution than our proposed route forward. 49. Take Photos? Survey? Yes, the surveys are a predefined set of conditional/rules bases questions (this will be place under config control for this project). As part of this, the surveyor may need to take a photo graph to be linked to a question response. 50. Why going down this route? We believe this will give us the maximum flexibility for the future. 51. Why are you using external support? We do not have enough dedicated skills resource to support this in the timescales 52. Do you think there is enough information to produce a response? We believe with the CQs answered this is enough 53. Would you be looking to implement HACT standards? Not for this stage 54. Do you anticipate complex Workflows? We do not see the need for work flows to be done at this stage? 55. Is there a need to Integrate into the housing management system? No direct integration. Orchard (our housing Management System) has a URL viewer which will allow us to keep the databases separate & native. 56. How do we submit Pricing & Commercials? As per previous CQS we expect this to be made available to the short listed bidders 57. Complexity of Reporting Other than slicers and drill downs to interrogate data, nothing more complex than this. We already have restricted workspaces set up to publish to.