Data Extraction
Overview
Why is getting data extracted so often a problem? The simple
solution should be to ask the legacy supplier to provide the
extract, but more often than not they will charge a premium
for this service and may not be able to work to, or more
importantly commit to, the project timescales. Also the
delivered files may not be what you expect; they may require
extensive rework to understand the contents and get them into a
useable format. If the supplier option is not viable then
asking an in-house resource to prepare the extract can be a
difficult decision, because the only qualified people are
often in short supply and not able to devote the time
required.
There is however another option to get the data required for
the migration. We have never come across a project where we
have not been able to successfully create an extract that can
be used by the migration team. We will use our knowledge of
Healthcare legacy systems to either identify existing sources
of data, such as data warehouse feeds, legacy reports and
other interfaces, or write adaptors using tools such as
KB-SQL. Whatever the solution we provide a deliverable that
will allow the project team to run extracts that meet the
following objectives:
How Long Should be Allowed?
When planning for the elapsed time to prepare the extraction
we would suggest that 6 weeks is allowed from the start of
the engagement to the point of delivering the first extract. This is not necessarily worked time, as a
lot of time is taken up with hand offs to the various parties
(IT infrastructure, legacy supplier, informatics team). This
is obviously a very broad estimate; a more accurate estimate
would depend on the situation and parties involved.
To enable time and quality to be controlled during this period we would recommend splitting the project into the following four stages:
| Extract Stage | |
| Extraction Strategy | Detailing technical requirements, software specifications, memory estimates, interim storage options, risks and mitigations |
| Extraction of data | Getting the data out of the current system and into a new agreed format, stored in a separate area to the old system. |
| Splitting of Extract | Splitting the extracted data into a relational database model. |
| Describing the data | Adding information to the data such as: column titles, table titles, primary keys, foreign keys, table relationships, column formats |
The next stage to be planned is the actual run time so that the go live window can be established and agreed by the Project Team. Because we have had so much experience of running extractions we are able to help fine tune this window. To give an idea of a typical Healthcare extraction window, the table below shows extract times for 6 of our recent projects. Please bear in mind that these times are impacted by server specification, connection speeds and extraction methods.
| Project | Records for Extraction | Extract Time (hrs) |
| Project 1: PCT | 22,994,378 | 2:05 |
| Project 2: Acute | 108,041,382 | 5:10 |
| Project 3: Acute | 193,589,254 | 6.42 |
| Project 4: Acute | 254,817,412 | 8.01 |
| Project 5: Mental Health | 76,896,810 | 4:10 |
| Project 6: Mental Health | 96,207,605 | 5:02 |
Our experience in this area is gained from our extensive track record in Healthcare. The table below shows the most recent projects where we have worked with data extracts. The majority of these projects were large scale data migrations but we have also extracted data for Warehouse population, quality of outcomes reporting, performance reporting and dashboards.
| PAS | Project(s) |
|
McKesson TotalCare |
Medway NHS
Trust Chase Farm NHS Trust Newcastle Foundation Trust Royal Free Hampstead NHS Trust |
|
McKesson STAR |
Surrey and Sussex Healthcare NHS Trust |
|
EDS Swift |
Weston General Hospital North Bristol NHS Trust Gloucestershire Hospitals NHS Foundation Trust |
| Eclipsys | Winchester and Eastleigh Healthcare NHS Trust |
|
i-SOFT CLINiCOM |
NPfiT – Fujitsu – Southern Cluster St.George’s Hospital NHS Foundation Trust |
| OxPAS | Milton Keynes Hospital NHS Foundation Trust |
| IRC |
Queen Mary's Sidcup NHS Trust Barnet NHS Trust |
| Unicare | Southampton |
| i-SOFT iPM | Birmingham Women's |
| Bury Primary Care Trust | |
| Pennine Care NHS Foundation Trust | |
| University Hospitals of Morecambe Bay Foundation Trust | |
| Cambridgeshire & Peterborough Foundation Trust | |
| Blackburn with Darwen PCT | |
| Birmingham Children's Hospital NHS Foundation Trust | |
| Ashton, Wigan & Leigh PCT | |
| Bolton PCT | |
| Humber Mental Health Teaching NHS Trust | |
| East Lancashire Teaching PCT | |
| RiO | Oldham PCT |
| Lambeth PCT | |
| Lewisham PCT | |
| Barnet PCT | |
| Barking and Dagenham PCT | |
| Enfield PCT | |
| Enfield and Haringey Mental Health Trust | |
| West London Mental Health Trust | |
| Hammersmith and Fulham PCT | |
| Brent Teaching PCT | |
| Harrow PCT | |
| Newham PCT |
McKesson TotalCare Extractions
If you are planning or already
running a project using data from your McKesson
TotalCare PAS you will know that getting the data is a
significant challenge. If you can get the supplier to provide
an initial data extraction for you, the outcome may be a
single composite file containing each patient’s entire
demographic, inpatient, outpatient and any other appointment
data. This is not a good starting point to work from in any
data project because to make that file fit for purpose (see
extraction objectives), it must be split into multiple data
sets whilst retaining the integrity of patient demographics
and events. The table below shows some of the projects where
we have helped Clients deal with their extraction and
migration challenges.
| Project | Process Stage | Experience |
| Project 1 | Extraction strategy | The Client asked the PAS supplier to prepare their initial data extraction for them, but this gave them data files that the migration supplier couldn’t use. Avoca provided an assessment and a successful proposal for a new data extraction strategy. |
| Splitting of Extract | Design and execution of a splitting process for McKesson Data Extract / Offload Files into a relational table structure. The split files were then used for the successful data migration. | |
| Migration Managed service | Avoca know everything there is to know about TotalCare PAS, having analysed and assessed every piece of data in every column in every file as part of the full data migration managed service . | |
| Project 2 | Extraction strategy | The Client only had one member of staff who knew how to run the extract. Avoca assessed this and provided a successful proposal for a new data extraction strategy. |
|
Extraction of data Splitting of Extract |
Avoca’s consultants worked with the Client's in house expert to learn their extraction and splitting process. Initially this was to provide cover to the project. However Avoca’s skilled consultants were able to help the Client so much that their remit expanded into trouble shooting any problems that impacted the extraction and splitting process. | |
| Describing the data | Describing and documenting the data structure from the extraction allowed the transformation team to understand what data was available for migration and how it should be used in the new Cerner Millenium system. | |
| Project 3 | Describing the data | Avoca assessed and reported on the data quality of the Clients TotalCare PAS. |
| Project 4 | Migration Managed service | This Trust had two legacy systems, one of which was TotalCare PAS. This was successfully extracted and merged with the other PAS by Avoca as part of their migration to Cerner Millenium. |
Free Extraction
Consultancy
We are so confident of our ability to help your
project, we will provide two days free consultancy on site,
at the end of which we will provide a data extraction
strategy and a no obligation fixed price to create and carry
out the extract. To book an appointment; e-mail
enquiries@avoca.co.uk
or call
01246 45 3737.