Transcript Slide 1

SUS PbR Data Load Tool
Version 1.00
Introduction
About the file loader
• The file loader has been developed to help users load
the SUS PbR Extract files into an MS-Access
environment
• It provides a “wrap” around the 4 PbR extract definitions
previously provided and:
– Supports simpler file loading
– Provides a log of the load which includes key characteristics of
loaded files
– Provides an mechanism to pass files to microsoft excel
– Provides a degree of security
• In order to support the additional functions around
security, the file must be loaded with a custom
workgroup file – APC.mdw.
More details are provided later in this pack
Changes
• The supplied structure tables (labelled “str…”) are now explicitly
maintained as structure files used to generate new instances of the
files to be loaded.
– These files cannot be loaded with data but are used to generate the files
to be loaded – reflecting the original guidance on the use of the file
structures
– There is a simple workaround if you have developed reports based on
the file structure tables; this is covered in the appendix
• Post processing is applied to A&E and Spell tables
– To null default derived age (8000) and date (1/1/1000) values
– To convert times for fields which are output in old NHS format (hhmm)
into properly maintained time values
Amongst other things, these changes offer a mechanism for loading data
into SQL server as they allow data to be transferred to
SQL server using the Access to SQL server route.
Corrections
• Corrections have been made to the previous file
structures:
– Some field names were issued with a trailing comma in the
name of the field due to an error in the software used to generate
them. These have been removed and the field name will have
changed in consequence
– For A&E (only) the fields
INTERCHANGE_BULK_REPORT_START and
INTERCHANGE_BULK_REPORT_START were previously
transposed and this has been corrected
– For A&E and Outpatients the field:
DOB has been renamed BIRTH_DATE for consistency
Getting started
Two versions of the database are provided on the SUS website at
www.cfh.nhs.uk/sus/reference under the PbR subheading
• 0607structures_v100_s.mdb
– for use with Access 2002 & 2003
• 0607structuresV100_2000_s.mdb
– for use with Access 2000
These have been compiled under the relevant Access version; there
are no other differences
Getting Started - 1
Join to apc.mdw file
• The database now has a custom file – apc.mdw (or
apc2000.mdw for Access 2000 users) - supplied with it.
This is an MS-Access workgroup file used to enforce
security.
• The database will not open unless linked to this file. This
can be done either:
– By setting up a custom shortcut – this is the recommended
approach
– By using the workgroup administrator within MS-Access.
Details of both approaches will be found in the appendix
Security has been added in recognition of the fact that some users have
been using the database to hold patient identifiable data.
While this improves matters, it is to be emphasised that users retain
responsibility for, and MUST take action to, ensure that any locally
maintained patient level data is kept in a fully secure environment.
Getting Started 2 - Users
• You will be prompted for a user name. Two are predefined:
– NHSAdmin – which has administrator rights
– NHSUser – which does not
• There is no password the first time you use either. Once
you enter the application for the first time, you will be
prompted for a new password and cannot continue until
you create one
• NHSAdmin can set up new users. The password
process is the same
Getting Started 3 – Digital
Certificate
The application has been digitally signed as
“SUSPbRLoadTool_R9”
Depending on your local system rights, you may be able to accept
the certificate on a continuing basis by clicking the “Always trust..”
box
Getting Started 4 - First use screen
If you do not enter a password you will not be able to continue
(This requirement also holds for locally created accounts)
Loading the data
Note – this part of the slide pack should be viewed as a
slide show
When you click to start the animation, it then proceeds
automatically up until the next command
Initial menu
Go To Next Slide
You can enter a
description of
the load here
Other than for A&E you can
identify the name of the table
Note that
access has a limit of 64 chars
to be created
here
when loading files – if the BT files names
(for A&E
you must
enter
it on
exceed
this you
may
need
rename
files
Click
to to
start
animation
the “Advanced”
screen
and reduce the length
because some post
processing is required first)
Click to identify
file to load
Initial view of load screen
Load for PbR extracts is preset, but the form will support
load of other datasets
Go To Next Slide
Go To Next Slide
For PbR extract files the following are taken from the loaded file:
Organisation that is subject of the run – org type and
code
Number of
records
loaded Identify cases to be
loaded
(can be more than
one)
From the loaded file – the
remainder of the query uid
Click to start animation
Results
Period
First part of query ID –
identifies query type
The date the file was
If import errors have been
loadedfound you can see them by
clicking here – or click
advanced
The date the query
was run by BT
Click Load
Load messages
Note: a warning message that
one record with a null key value
is normal and reflects the
deletion of a blank header record
– the application will delete if and
only if one record meets this
criteria
Go To Next Slide
Go To Next Slide
Load messages – in this
case the data loaded but
with errors
If load errors occur you can
Click here to see the access
generated import error table
… but no problems
with post
processing
Click to start animation
Go To Next Slide
Following data load of
all PbR Extracts
Newly created files
Go To Next Slide
Sending files to Excel
Click to identify
table or Query
Click to open
file dialog for
file save
Click to see drop
down of available
tables / queries
Note: To extract a subset of data to excel:
Click Export
• use MS-access to write a select query
• Save the Query
• In the MS-Access query window click right against the query,
and write a description for the query
• The query will be available for extract through the Save to
Spreadsheet form
Go To Next Slide
The load screens in more detail
Appending data to a table
• The Append flag to append multiple files into the same
table. The append flag can only be set from the
“Advanced” screen, though it is repeated for information
on the main screen.
• If it is important that files are processed in order, this can
be controlled by inserting a numeric value in the [SEQ]
field – data is loaded in ascending order.
• If the append flag is not sent any existing table with the
same file as the output will be deleted
Loading Your Own Data
• Tables names are based on the following conventions:
– Structure files – used as the framework for loading data
commence with “str”
– Output files – which will contain loaded data – start with “tbl”
These naming conventions are used primarily to limit drop-downs
An output file name with a leading Tilde, once specified, cannot be
changed and is used when a fixed table name is required to
support post processing
• Specification files
– Are specified via the “Advanced” tab within the native text load
functionality of MS-Access:
File>Import>External Data>[choose text file]>Advanced>[Create Specification]>Save as
– Once saved the specification can be accessed via
the dropdown on the detailed screen
Flags
The following flags are available for use by administrators through the
advanced window. They may be useful when loading local data
IsPbRExtract
• Indicates that after load data should be extracted from the data to
populate the fields relating to organisation, unique ID etc
Protect
• Prevents the relevant record being deleted from the database.
(Note that output table names are not protected from change unless
preceded by a tilde (“~”)
IsRepeatLoad
• Indicates that the most recent version of the
record should be copied back to provide a structure for the next load
Setting up your own loads
The following is not the only approach, but works
1. Choose the load option at the bottom of the screen and identify a
temporary table name and identify the file to be loaded.
2. You may need to change the heading for with/without headers on
the detailed screen (open by clicking “Advanced”)
3. Load the file
4. Inspect the file and rename columns and set column parameters as
required.
5. Delete all episodes in the file and rename to str***
6. If necessary to maintain a specification file, undertake a manual
load, naming the “str” file. Save the specification
7. Clear the str file by deleting all records
8. Set IsRepeatLoad and Protect flags
Appendix 1
Security set up
Appendix - Workgroup files
• Important
– if you create a new database of your own while joined to
APC.mdw, you will only be able to open it while linked to
APC.mdw and with the users identified in APC.mdw
– Backup the APC.mdw file and keep it secure – if it becomes
corrupted you cannot access the application
• Note
– Although you can save the .mdw file anywhere, it is best to
locate it in the same directory as the system.mdw file as it
becomes easier to switch between the system and apc files
– The default directory for the system.mdw file is:
"C:\Documents and Settings\<username>\Application
Data\Microsoft\Access
Option 1 - Using Workgroup
Administrator
• For Access 2002 & 2003 work group administrator can be found by
following the menu path:
– Tools>Security>Workgroup Administrator>Join
• For Access 2000 you must use the separate workgroup
administrator supplied with office tools
•
• Note the location of system.mdw (the default workgroup file) as you
may want to rejoin it
• Browse to and open the apc.mdw file (APC2000.mdw for Access
2000)
Creating
Option
2 - Usinga acustom
customicon
shortcut
•
•
•
•
Open the folder where Microsoft Access is installed (located by
default in C:\Program Files\Microsoft Office\Office11).
Right-click the Microsoft Access program icon, and then click
Create Shortcut.
Right-click the shortcut icon just created, click Properties, and then
click the Shortcut tab.
Replacing the <filepath> in the text below with the file path
appropriate to your system, replace the entry in the Target: field
with the text below:
–
–
–
the text assumes that access is loaded in the default location
It is easiest to prepare the string you want to use in a text editor and
then cut and past it into the Target field
There are advantages in placing the APC.mdw file in the same location
as the system.mdw file – see next slide
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"<filepath>\0607structuresV100_S.mdb" /wrkgrp "<filepath>\apc.mdw"
Note After you create a shortcut you can put it on your desktop,
or in a folder or add it to the Start menu. Search Microsoft
Windows Help for "adding shortcuts" for more information.
Appendix 2
If you have already written queries based
on files with names starting “str…”
Appendix 2 – existing use of “str”
table names
Use this application to load text files into defined “tbl” files
• Take a backup copy of the database which contains the
existing application
• Link from the old application to the relevant files in the
load application using
• File>Get external data>Link Tables
• Create the links
• Delete the tables in the old application which will
replaced by those loaded through the load application
• Rename the linked tables to the old table names