Overview of Data Warehouse and Business Objects Development

11  Download (0)

Full text

(1)

New Ways of Defining and Measuring Waiting Times Business Objects Development Day

NHS National Services Scotland, Boardroom 1 & 2, Gyle Square, 1 South Gyle Crescent, Edinburgh EH12 9EB

Introduction and Scene Setting

After the initial welcome by Kate Harley and the context setting by Sean Brennan, Andy Carver described the progress to date with the New Ways programme. This was in order that all at the meeting had a common understanding of work done to date and what is required in the final six months of the programme. All slides are available on the data warehouse section of the New Ways website:

www.newways.scot.nhs.uk

Sean had described the process whereby a strategic approach to the storage of data was adopted with a central data warehouse used to collect, validate and store the data from local health boards system(s).

The progress had been steady since the start of the New Ways project in May 2005, and all suppliers had developed their PAS systems to be New Ways compliant. The roll-out of these systems varied depending on the PAS system involved. Sean presented the following table ( updated version shown below):

(2)

.

Overview of Data Warehouse and Business Objects Development

Nicole Allan provided an update on the development of the Data Warehouse and the Business Objects Universe & Reports.

Build of Physical Database – Modelling Technique Used Which is Conducive to Reporting

Build of Automatic Load Routines for Loading Data Submitted in XML File Format or Fixed Width File Format

Build of Error Handling Routines – Trap Errors with File Format and Automatically Notify Contact at Sending Location via Email

Build of “Views” of Data To Simplify End User Reporting – E.g. Monthly View Derivation of New Database Items to Facilitate & Simplify End User Reporting

(e.g. length of wait)

Build of Validation Modules That Run All Supplied Records Through the National New Ways Validation Rules.

Build of Validation Error Reporting – All Errors Logged to a DB Table & Include Details of the Type of Error, Row Number Within Source File & Data that is in Error. BO Report Built to Allow Sending Locations to View Error Details

Build of Validation Error Handling Routines to Automatically Notify Contact at Sending Location via Email

Build of Initial Business Objects Universe o Dimensional Data

o Measures o Filters

Build of Sample Business Objects Reports Build of Dashboard to be used as Starting Point

Unit & System Testing Ongoing. Testing “test extract files” provided from some suppliers and health boards.

11,000 records loaded to date from different sources. The following errors were encountered:

Use of Non-National Codes (GP Practice, Reason for Removal, Patient Type) – > 20,000

Date Decision Taken To Place On Waiting List < Receipt of Referral – 78 Type of Offer Not Supplied for Offer – 1727

Date of Offer Not Supplied for an Offer – 1727

Date of Offer < Date Decision Taken to Place on WL – 2

Date of Receipt of Response > Date of Proposed Appointment – 2908

Outcome of Offer Missing Where Date of Receipt of Response Exists – 3175 Start Date of Period of Unavailability Invalid Date – 235

Period of Unavailability Overlaps With Another Period – 1 End Date < Start Date for Unavailability – 47

Date of Removal From List < Date Decision Taken To Place on WL – 2389 Appointment Supplied Within Period of Unavailability – 58

Missing Mandatory Data When WL Entry Removed/Closed - > 2000 Patient Status Not Appropriate When Patient Removed From List – 4068

Location Not Supplied for Inpatients, Daycases, Return Outpatients – 12, 763

(3)

Demonstration of Data Warehouse and Business Objects Universe Nicole Allan demonstrated the Business Objects (BO) Universe.

It was acknowledged that the strategic development undertaken does not restrict everyone to only use BO as the analytical tool. Other statistical tools can also be used but, as Scotland has purchased the national license for BO, it was a logical choice for most users in Scotland.

The BO Universe is constructed with Folders, Objects, Measures and Filters. These elements are used to assemble reports and allow for:

Ease of navigation/location of data. Ease of understanding of data. Consistency of Reporting Consistency of Data

Simplification of Report Creation

Folders allow for grouping of Objects, Measures and Filters allowing easier location of the items required for a report (e.g. all patient details stored under Patient folder).

Objects include:

Data items sent directly from the extract – e.g. Case Reference Number. Data items derived/calculated as part of the load process to allow for consistency of data and reporting – e.g. Patient Full Name (concatenation of Surname, Forename to allow for consistent presentation within reports) or Days Wait (to allow for consistent calculation of the waiting times). Dimension Data Items “associated” with core data – e.g. Postcode linked to Geography dimensions, which contains data such as constituency. Measures currently include:

Counts – e.g. Number of Appointments, Number of Patients. Sums – e.g. Total Days Unavailable.

They can also include averages, medians, modes, and maximums etc. depending on user requirements.

Filters

Pre-defined filters have been created for conditions that are seen to be standard filters that will be applied to reports time and time again. An example is the exclusion of any waiting list entries that have been marked as being physically deleted from the source system.

Note: - Specific report filters can be created as required, based on almost all of the objects and measures within the universe.

(4)

Data Extraction and Validation

The data extracted from the PASs will be uploaded into the central warehouse and then validated.

This validation will be appropriate to the completeness of the episode at the time of data load (e.g. CHI will not be validated as mandatory until the episode is identified as complete by the presence of a date of removal from list).

New Ways will be ‘drip-fed’ on a daily or weekly basis. There needs to be a data flow schematic proposed and submitted to the New Ways Warehouse Group for consideration/validation.

Some Health Boards felt that this approach would create extra workload on already busy staff. There was also concern that because of the time frame (daily or weekly extracts), there was not enough time to ensure the quality of the data before being submitted for ISD’s use.

Action: Andy Carver to propose a data extraction process. Pre-defined Reports

Nicole showed the group some BO reports, which her team had created to give examples of the type of reports possible.

Nicole emphasised that developing the reports is in most cases, relatively

straightforward exercise, however the reports have to be defined by the service. Nicole asked that the workshop attendees to send:

a) Existing reports that should be replicated in the warehouse

b) Details of any new reports they would like added to the warehouse ISD will then compile a list for prioritisation, agreement, and addition to the warehouse.

On a question regarding comparing actual with expected activity, Nicole

confirmed that it would be possible for reports created centrally to include local targets.

Nicole was also asked if local data items could be uploaded along with national data items. Nicole advised that this could be captured as part of the extract but is not part of the current national development and would need to be agreed as additional work based on HB requirements. This was viewed positively and observation was made that this may help facilitate local use of Business Objects.

Action: ALL to send Joyce Anderson (joyce.anderson@isd.csa.scot.nhs.uk) copies of reports they wish to see replicated and identify which new reports they would like to see created.

(5)

Need for New Ways Data Warehouse Working Group

It became clear early in the morning session that it was essential that a smaller working group needed to be established to discuss and take forward issues raised today and agree the final stages of development.

This group will need to be in contact on a regular basis in this key stage to support Nicole Allan and the development team in this period of development. The following individuals volunteered / were nominated at the end of the workshop: Clare Harper Daryl Main Frances Paton Jenni Douglas Joanne Adamson Joyce Wardrope Sally Smith Stewart Hatrick

Action: Joyce Anderson to contact New Ways Data Warehouse Working Group nominees and organise initial meeting

Sharing BO reports

Discussion around how reports are shared e.g. if one Health Board develops a report for their local use, other Boards may find this useful so how can this be facilitated? New Ways website may be a means of facilitating this, but a better option may be to establish a shared folder within Business Objects where HB users can place reports they may feel are of benefit to others.

Nicole asked the group to think about how reports produced locally would be tested and quality assured so that they were suitable for wider use. Stewart Hatrick concluded that the creator of the report could not be held responsible for it’s wider use in a different context, and it was down to the individual to make a judgement on whether if the ‘shared’ report was suitable for their local use. Nicole stated though that some arrangement between HBs in terms of testing and QA the reports would result in a reduction of cost and effort to each individual HB.

ACTION: Sharing of BO reports to be encouraged. This to be facilitated by publication on the New Ways website or via shared folders

(6)

Dashboard

Nicole demonstrated the type of screens that could be shown on a “dashboard”. Agreement on items on a ‘national’ dashboard will need to be sought however the view could be localised for each Board, although this work may take place outwith the New Ways project. It was noted that it would also be possible to have local targets included on any local dashboards.

Action: Agreement on items for national Dashboard to be facilitated through New Ways Data Warehouse Working Group

BO Universe

Nicole confirmed that she was happy to share a list of all the elements in the BO universe by creating an excel spreadsheet with a list and adding a description for each. This would allow the group to see at a glance what was already present and what might be missing.

Action: Nicole to create list of items within BO universe with descriptions and pass to Joyce Anderson to publish on the New Ways website

Access Control

A number of issues were raised around who can see which reports and what information and at what level.

Felt that this was an issue that has major Data Protection implications.

Recent problems with sharing of information in work on HEAT were identified by the group as highlighting potential problems for New Ways.

Clear need to identify what can and what cannot be shared. There is precedent for looking at ‘own’ patients however there is currently little cross-Health Board sharing of data, which New Ways could potentially provide.

If a patient doesn’t live in your board area, and isn’t someone you are treating then there is no argument to justify access, but access to non-patient identifiable

information from other Boards may well be mutually beneficial.

Felt that agreement at Chief Executive level may be required to clarify on information governance issues and data protection issues.

The potential for requests under Freedom of Information was also identified as an issue.

ACTION: Sean Brennan to discuss with the NHSScotland Information Governance team in ISD

(7)

Change Control / Management of Data Warehouse post New Ways

The New Ways Warehouse Group plus additional ISD personnel may need to in time to become / form a Change Control Programme Board for the Data Warehouse / Business Objects reporting through whom all requests for change will be directed for their prioritisation and approval. This group will undertake these duties through a regular meeting schedule (e.g. quarterly), which may be held ‘virtually’

ACTION: New Ways Data Warehouse Working Group to investigate and establish a change control programme board at end of the implementation phase of the New Ways project.

Communication

It was suggested that a Chat room or Blog could be set up for New Ways as a

vehicle for sharing and discussing thoughts and ideas. Joyce Anderson advised that there may be issues with hosting this via the SHOW website. ult on an ISD website. Sean suggested this could be hosted externally. Angela Forbes identified an ISD project in Cancer Audit that use an external chat room.

Action: Angela Forbes to forward Cancer Audit chat room link details to Joyce Anderson for further investigation for New Ways

Business Objects Training

A question was raised regarding BO training. Nicole advised that there is no formal BO training provided by ISD. There is however a national BO contract that allows Health Boards to purchase BO training at an agreed reduced cost.

Nicole advised that a support pack would be issued to each board.

ACTION: Nicole Allan to pass details of National BO Training contract to Joyce Anderson for next New Ways newsletter.

National Licence for BO

While BO is provided under a national licence, NHSScotland has different versions in circulation. Consideration should be made to seek to get all Boards on the same version of BO.

Action: Nicole Allan to ask Jill Smith re potential for standardising on one version of BO.

Sub Specialty and Specialty Groupings

It was suggested that there should be no national codes for sub specialty and yet it was identified that analysis at a level below specialty may or will be a requirement. It was noted that there is sometimes a requirement to aggregate specialties up into “macro” specialties.

Action: Sean Brennan to investigate sub specialty and specialty groupings and report back to New Ways Data Warehouse Working Group

(8)

National Data Recording

There was discussion around data reporting at a national level and issues were identified. One example – If User A goes to a meeting with Report A and then User B goes to same meeting, with the same report run three days later, which data is used? Nicole suggested the simple pragmatic answer is to simply include a dated header for each report run. It is then obvious to all which data is the most up-to-date. Snapshot Reports

Discussion around the need to look at the requirement for a snapshot where data was ‘frozen’ and for specific dates where national reporting is undertaken.

Suggestion that this could be done week on week to show 18 week target –

effectively a weekly census so that a position could be taken at a fixed point in time. NA advised that this could be achieved however her team would require the

‘business rules’ for what would be required. It may be that some Health Boards choose to run and save a report that shows movement up or down on their targets each week.

Issues regarding this also to be considered in Andy Carver’s document.

Action: New Ways Data Warehouse Working Group to consider and advise Action: Andy Carver to consider issues of ‘freezing’ data for his paper Capacity Planning

The issue using the BO tools to link capacity reports with demand reports. Agreed that this was a local issue as it would be difficult to link live capacity data with out of date new ways data. The data required to enable capacity planning can only be captured and available locally. There is no national requirement to monitor the capacity detail.

Patient Journey

Discussion around whether New Ways can provide end-to-end reporting for Cardiac and Cataracts, linking outpatient activity to inpatient/daycase activity.

Validation

Although validation forms part of the detailed specification given to all suppliers, the following questions and issues regarding validation were raised:

If a single record fails validation should just that individual record be excluded or would it be easier to have the whole file be rejected and re-submitted. Service need to reach agreement on what would be preferable, but current system

development is that individual records are rejected rather than whole files (outwith header or file issues). If a HB chooses to re-submit a whole file rather than just the records in error, the system handles this by simply updating the records that were previously okay.

(9)

What makes a record valid? Nicole advised that different rules are applied depending on the stage that a record is determined to be at (e.g. different mandatory data items when a record is “closed” as determined by the presence of the date of removal from list).

Action: New Ways Data Warehouse Working group to agree on approach to the validation of data – single record exclusion or resubmission

Cross Health Board transfers

Need to reach agreement across Scotland on start and end dates with regard to cross Health Board patient transfers such as are experienced with angiography where patient moves from one health board to another to receive treatment. Provider codes were mentioned in relation to this but not discussed.

(10)

Actions

1. Action: Andy Carver to propose a data extraction process. 2. Action: ALL to send Joyce Anderson

(joyce.anderson@isd.csa.scot.nhs.uk) copies of reports they wish to see replicated and identify which new reports they would like to see created. 3. Action: Joyce Anderson to contact New Ways Data Warehouse Working 4. Group nominees and organise initial meeting.

5. Action: Sharing of BO reports to be encouraged. This to be facilitated by publication on the New Ways website.

6. Action: Agreement on items for national Dashboard to be facilitated through New Ways Data Warehouse Working Group

7. Action: Nicole to create list of items within BO universe with

descriptions and pass to Joyce Anderson to publish on the New Ways website

8. Action: Sean Brennan to discuss with the NHSScotland Information Governance team in ISD

9. Action: New Ways Data Warehouse Working Group to investigate and establish a change control programme board at end of the

implementation phase of the New Ways project.

10. Action: Angela Forbes to forward Cancer Audit chat room link details to Joyce Anderson for further investigation for New Ways

11. Action: Nicole Allan to pass details of National BO Training contract to Joyce Anderson for next New Ways newsletter.

12. Action: Nicole Allan to ask Jill Smith re potential for standardising on one version of BO.

13. Action: Sean Brennan to investigate sub specialty and specialty

groupings and report back to New Ways Data Warehouse Working Group

14. Action: New Ways Data Warehouse Working Group to consider and advise

15. Action: Andy Carver to consider issues of ‘freezing’ data for his paper 16. Action: New Ways Data Warehouse Working group to agree on approach

to the validation of data – single record exclusion or resubmission

(11)

Appendix 1: Attendees

Attendees Organisation

Brian Lawson Golden Jubilee National Hospital Daryl Main Golden Jubilee National Hospital Lynn Hay Golden Jubilee National Hospital Andy Carver ISD Scotland

Angela Forbes ISD Scotland Catriona Haddow ISD Scotland Joyce Anderson ISD Scotland Kate Harley ISD Scotland Nicole Allan ISD Scotland Sean Brennan ISD Scotland

Carol Golding NHS Ayrshire and Arran Sally Smith NHS Ayrshire and Arran Fiona Kali NHS Borders

Meriel Smith NHS Borders Joyce Wardrope NHS Fife Keith Rougvie NHS Fife Sachin Singla NHS Fife

Bill Gray NHS Forth Valley Sarah Lauder NHS Forth Valley Faye Simpson NHS Grampian Linda Duguid NHS Grampian

Antoinette Parr NHS Greater Glasgow and Clyde Bill Sinclair NHS Greater Glasgow and Clyde Frances Paton NHS Greater Glasgow and Clyde Jim Crombie NHS Greater Glasgow and Clyde Mairi Dick NHS Greater Glasgow and Clyde Stewart Hatrick NHS Greater Glasgow and Clyde Caroline Liddle NHS Highland

Clare Harper NHS Highland Frances Matthewson NHS Highland Marion Mark NHS Lanarkshire Brad Kirby NHS Lothian John Rankin NHS Lothian Victoria Elliot NHS Lothian Mark Clouston NHS Orkney Joanne Adamson NHS Shetland George Brown NHS Tayside Jenni Douglas NHS Tayside

Figure

Updating...

References

Related subjects :