Data mart
- Accessing the Waiting Times data mart
- Business Objects Training
- New BOXI reports and Functionality
- Frequently Asked Questions
- Data mart documentation
Accessing the Waiting Times data mart
Introduced in July 2011, is a new procedure for requesting/changing access to Business Objects for Waiting Times:
The Scottish Health Information Service (SHIS) will take over administrative responsibility for providing users with access to Business Objects - Waiting Times. SHIS already support several other data marts held within the ISD Corporate Warehouse.
All prospective Business Objects users must obtain the approval of their local Caldicott Guardian before they can be given access to the Waiting Times data mart. The Approver for each Health Board must provide the SHIS support team with a Caldicott approved 'Access to data' form before access can be granted. More detailed information on this new process can be found on this flowchart 
[100kb]
Any questions about this new procedure for gaining access, please contact the SHIS support team on 0131 275 7050 or nss.isdSHIS@nhs.net
The Business Objects User Manual [5MB] is specific to the Waiting Times Universe and gives useful advice on navigating Business Objects reports for New Ways.
The document 'Refresh Impact on BOXi reports' [KB] gives instructions on how to add flags to your user reports, including error reports, after the New Ways Refresh.
For more information on Business Objects training specific to New Ways, please contact stephen.bush@nhs.net.
New BOXI reports and Functionality
15 Sep 2011 - The Waiting Times data mart will be able to accept up to twelve new data items as of 30 September 2011. Please refer to Kate Harley’s letter [49KB]. These data items are UCPN, RTT clinic outcome code, RTT clinic outcome date, RTT stop date, RTT stop reason, Local sub-specialty code, Local sub-specialty description, Local clinic code, Local Service code, Local service description, Infrequent Service flag, RTT True Wait Time. Any NHS Board wishing to make use of some or all of these new fields, please contact jane.goodall@nhs.net or stuartkerr2@nhs.net for the revised xml schema and further advice.
15 Sep 2011 - As part of a Project to re-develop the Waiting Times data mart, a revised security structure has been put in place. This will allow Business Objects users to view ‘stage of treatment’ records that are resident or treated within their Health Board as well as records they submit as sending Health Board. Please see Frequently Asked Questions for more detail. This revised security will be available in the data mart as of 30 September 2011.
15 Sep 2011 - A new ‘Tracking report’ in Business Objects will allow Health Boards to track records by CHI and specialty across health Boundaries as long as your Health Board is one of sending, treated or residing. Prompts for this report are CHI and specialty so you will need confidential access to use this Report. Please note that it can take up to two minutes to run so it may be easier to schedule this report. This new report will be available in the data mart as of 30 September 2011.
General
Q: The Publication report is slow to run. What do I do?
A: In the run up to snapshot, when there are many users accessing Business Objects, you may find the Publication reports slow to run. It is always best to schedule the Publication reports for ‘now’ or a set time each day.
Q: What is in the Viewing Gallery folder?
A: The Viewing Gallery folder acts as a ‘showcase’ for modified reports that may be examples of best practice or could provide a model that other Boards may wish to adopt depending on their own reporting needs. All Board users can run reports in this folder and save copies to their Favourites folder but please note that they do not come with the gold standard of Standard reports.
Q: How do I carry across drill filter values and report prompts selected when exporting to excel?
A: Standard reports will automatically export report prompts and any drill filter values selected. If you wish to do the same for your own reports, a document has been written to explain how to show drill filter values (and report prompts) when exporting to excel [45KB].
Revised Security (implemented 30 September 2011)
Q: How will the revised security structure affect me?
A: There will be no effect on error reporting. Some Standard reports will allow you a wider view of records in the data mart as you will now be able to see records that are resident or treated within your Health Boards, as well as submitted by your Health Board.
Q: Will the Publication reports be affected?
A: No, the Publication reports will not be affected by the revised security in any way. These reports prompt for ‘Provider’ Health Board and are restricted to return records where the Provider code is for your NHS Board.
Q: Which Business Objects reports will be affected by this revised security?
A: Full Patient Waiting List (Standard and Snapshot versions), Diagnostic Waiting Summary, CRN Audit and MUI Audit. The Full Patient Waiting List and Diagnostic Waiting Summary now return all waiting list records for your NHS Board whether it is Sending Health Board, Health Board of Residence or Health Board of Treatment. A drop down filter in each report allows you to select Sending Health Board, if this is the only data you wish to view.
Q: If a patient is resident in NHS Orkney and is sent to NHS Grampian for treatment, can NHS Orkney view the Stage of Treatment record for this patient?
A: Yes, even though NHS Orkney are not sending the data to the Corporate Data Warehouse, they will be able to view the Inpatient/Day case record as they are the Health Board of Residence. NHS Orkney could use the Full Patient Waiting List and drill down to the MUI Audit report or run the Tracking report to see all MUIs associated with the relevant CHI plus Specialty.
Q: How do I build a Business Objects report to bring back all records where my Health Board is one of Sending, Residence or Treated?
A: In the Query filter pane, add the prompt for ‘Health Board’. This new object returns all records for your NHS Board where it is one of Sending, Residence or Treated?
Q: What prompts do I put in the MUI Audit report, if I am not the sending Health Board?
A: If you are Health Board of Residence or Health Board of Treatment then you can view the MUI in the MUI Audit report, as long as you enter the relevant MUI and correct Sending Health Board in the prompt screen.
Q: What is this new Tracking report?
A: The Tracking report makes use of the revised security and allows you to track a record by CHI plus Specialty across Health Board boundaries as long as you are one of Sending Health Board, Health Board of Residence or Health Board of Treatment. CHI plus specialty, is a proxy for UCPN and the Tracking report returns all MUIs and associated Waiting Times data for your chosen CHI plus Specialty.
New data items submitted to the Corporate Warehouse
Q: When can I view UCPN in the Waiting Times data mart?
A: The Waiting Times data mart will accept UCPN and other new data items as of 30 September 2011. Please see the appendix of Kate Harley’s letter [49KB] for detail of the new data items. There is a revised xml schema which contains the structure for up to 12 new Waiting Times data items including UCPN, some RTT data items and local sub-specialty etc. If your NHS Board can submit the revised xml schema, with UCPN populated, then you will be able to view UCPN in Business Objects. Please contact jane.goodall@nhs.net or stuartkerr2@nhs.net if you wish to explore submitting the revised xml schema.
Q: If my NHS Board, uses the revised schema can we choose which of the extra fields to populate?
A: Yes, you may populate the fields that will be useful to you. If you plan to submit sub-specialty codes, we strongly suggest submitting some of the RTT data items at the same time, which will help with tracking records. All unpopulated fields will be read as null.
Q: What will be the Validation rules applied to these new data items?
A: Validation will be minimal for the new data items. There will be no validation on local data items such as local clinic code and sub-specialty. Other validation will be schema to check date is in a correct date format or Flag 1. The only new data item with Flag 3 severity is an incorrect Infrequent Service Flag (submission other than Y or N) as this flag is used in the calculation of wait. Please see the appendix of Kate Harley’s letter for detail of the new validation or please contact jane.goodall@nhs.net or stuartkerr2@nhs.net for an updated validation manual, with validation included for the new fields.
Calculation of Wait
A brief summary of the method for waiting times calculations is available at How are waiting times calculated?
The Calculation of Wait paper sets out the calculation of waiting times that will be used in the national warehouse. The Calculation of Wait document is used in the national warehouse to form the basis of national publications, Business Objects reports, ISD responses to Information Requests etc.
- Calculation of Wait v1.0 [92.3KB] Published 01 April 2009 in line with changes to rules surrounding periods of unavailability (supercedes v0.4).
- Calculation of Wait Letter (Sent out with V02) 28 November 2007 [100KB]
Offer ordering
This document describes the offer ordering process in the New Ways Warehouse.
- Offer Ordering [24.3KB] Published February 2009
New Ways Guidance on the recording of main operation, location code and provider code
As each New Ways record is an episode of wait which is essentially closed when the patient is removed from the waiting list, data items relate to the patient whilst experiencing a wait unlike SMR01 for example, which is a 'discharge record'. The main data items that this approach affects are Main Operation, Location Code and Provider Code. This letter was issued on 20 July by Kate Harley, Head of Programme, ISD Scotland, to provide clarification on this.
