SQL Data Warehouse Development Partner

Tender description

Summary of the work The development partner will work on constructing and building an enterprise data warehouse with key focus on 2 specific work programmes of patient level costing and electronic patient records. Expected Contract Length 6-Months Latest start date 17 October 2016 Why the Work is Being Done To support and assist with the construction and building of an enterprise data warehouse Problem to Be Solved The development partner will work on constructing and building an enterprise data warehouse with key focus on 2 specific work programmes of patient level costing and electronic patient records. Who Are the Users The users will be Trust wide, including clinical, technical & non-technical Personnel Existing Team The development team from the partner will work with our in house development team. The team will work collaboratively on this development with the in house team and according to an agile development methodology. Current Phase Live Skills & Experience • 5 + Years Expert knowledge of MS visual studio SQL server projects 2012/13. • 5 + Years Expert SQL 2008-2016 development tools. SSIS / SSAS / SSRS packages. • Development management tools such as Git / bitbucket / Jira / confluence. • Agile development methodology. • Relational databases – Kimball methodology. • Database optimisation. • Enterprise data warehousing. • MDX – OLAP cube development. • Mentor and train other developers in a non-line management capacity. • Experience mentoring or helping colleagues optimise their code. • Experience of Microsoft Sharepoint versions 2007 +. • Developing reporting and dashboards in SSRS. • Experience of handling large data sets and scaling their handling and storage. • Work remotely but in sync with the in house development team. • Experience of creating and managing to detailed functional and technical specifications. • Team values on being friendly, approachable, supportive, constructive and collaborative. • Learning and development culture, focused on skills and knowledge transfer. • Experience of using quality assurance over data warehousing developments • Experience of business analysis on data architectures, OLAP cubes, reporting and dashboards. Business analysis that covers both functional and technical requirements. Nice to Haves • • Act as a “tech lead” on complex projects, providing support for product and delivery management and pragmatically weighing the complexities involved against the need to act • • Knowledge of relational and non-relational database systems • • SQL DBA admin and developer. • • Data science principles. • • Training and education through self evaluation assessment tools for personal development. • Emphasis on strong and positive organisational culture – core values of learning, development, collaboration and engagement. Work Location The partnership is likely to be virtual but to include on site visits from both sides with the daily work programme managed through cloud based development tools...UHSM is based within Greater Manchester. Working Arrangments 5 days a week from contract award. GMT for any overseas partners with standard hours between 8am and 6pm. Security Clearance Information governance / data protection controls as well as confidentiality clauses and adherence to the Trust’s policy on commercial sensitivities. No. of Suppliers to Evaluate 6 Proposal Criteria • Technical solution • Approach and methodology • How the approach or solution meets user needs • How the approach or solution meets your organisation’s policy or goal • Estimated timeframes for the work • How they’ve identified risks and dependencies and offered approaches to manage them • Team structure • Value for money Cultural Fit Criteria • Work as a team with our organisation and other suppliers • Transparent and collaborative when making decisions • Ability to work with users at all levels • Nurture more junior members of the team • Have a flexible attitude • Work using agile methodology • Sharing skills as part of the Dev Ops culture • People able to thrive in a self-motivated, autonomous and empowered environment • Experience co-operating and collaborating with other engineers Payment Approach Capped time and materials Evaluation Weighting Technical competence 60% Cultural fit 20% Price 20% Questions from Suppliers 1. Q 1. Do you already have questions that you want to be answered by this warehouse or this is still in progress. Q 2. And, what is the rate or price foe this work? Response to Q 1...We currently have a data warehouse from which reports are produced, however there are some inconsistencies in the build and approach. Therefore, part of the work will involve a rebuild of the data warehouse using defined design and development processes which follow recognised methodology to provide us with a structured data warehouse on which we can develop our BI platform and reporting needs.Response to Q 2...We do not wish to disclose the budget at this time 2. Please could you indicate a budget range for this project? We do not wish to disclose the budget at this time 3. Q 1. What is the expected budget/day rate for this opportunity? Q 2. Which resources will be assigned to the project from UHSM's side (roles/experience)? Response to Q 1...We do not wish to disclose the budget at this timeResponse to Q 2...The Trust currently has team of 4 report and database developers who are involved in managing the current data warehouse (including maintenance of SQL Agent jobs, ETL processes, security, etc.) , development of reports, maintenance of SSAS cubes, etc.It is expected that this team will be heavily involved in the project. 4. What is the size (db objects) and scale (volume) of 'patient level costing' and 'electronic patient records' work program databases. This is difficult to answer at this stage as these projects are either very much in its infancy or have not yet begun. For the electronic patient records work, the application database is estimated to begin at 400Gb scaling up to 1000Gb as data is recorded, but this is not indicative of the size and scale of the reporting databases which will be housed in the data warehouse. 5. How would you define current data warehouse: 'Simple' , 'Medium' or 'Complex'. Response (Part1)...We have data sources which are mostly a mixture of operational systems and flat file extracts, and the majority of these are loaded into individual staging databases, which in turn flow into a normalised data store. However, sat on this is a reporting layer in the form of a denormalised database which is used for much of the reporting, rather than subject orientated data marts – so only the first part of this structure could be described as Complex’ 6. How would you define current data warehouse: 'Simple' , 'Medium' or 'Complex'. Response (Part 2)...We also have some data sources which are not loaded into a staging area but straight into the data warehouse – which is more representative of a ‘Simple’ structure.With this in mind, the current data warehouse is probably best described as a mixture of ‘Simple’ and ‘Medium’ architecture. 7. Will new DWH have any 'unstructured data' as source as well, (since there is a reference to non-relational database systems) Potentially yes. We currently load data from our A&E system which is a Foxpro application, and with the electronic patient record system there may be a need to load documents – both of these may need to be factored into the new data warehouse. 8. What are preferred Cloud-based Programme Management development tools At present we are considering the range of products listed on the advert – utilising the GIT approach to development, Bitbucket for management of the repositories , JIRA for planning and tracking, etc.; tools and products which will work well alongside Visual Studio SQL Server Projects 9. As you expect the partnership to be virtual, what roles do they envisage are likely to include on-site visits and during what stages? Currently the expectation would be for on-site visits at implementation stage with subsequent visits as required. 10. What size team does the hospital envision the partner having, taking into account the potential throughput of project activity and dependencies on the resources needed for governance and testing by the hospital? The UHSM BI team has 4 report and database developers who we would expect the team from the development partner to work collaboratively and in sync with, therefore a team of a similar size is what we would foresee at present. 11. Are you open to the prospect of work done from secure off-shore (outside UK and EU) for economic reasons while adhering to “Information governance / data protection controls as well as confidentiality clauses and adherence to the Trust’s policy on commercial sensitivities”? Yes 12. What challenges exist in your current set-up that you expect to overcome via this procurement? This procurement will help provide scalable resourcing of industry standard expertise to support the work around refactoring the Trust’s data warehouse into a structure on which we can develop our BI platform and meet reporting requirements. 13. Is the partner to redefine the governance process with which “design and development processes will follow recognised methodology”? There will be a Trust ‘lead’ on the work/projects but we would expect that the development partner will support and guide our processes and methodology using the industry standard experience and expert knowledge that they will bring to the partnership. 14. Are there any preferred methodology or processes? At present we would be looking to follow recognised methodologies such as the Agile approach to development, the Kimball method for data warehouse architecture and design.