# NHS Infrastructure (projects)
**Category:** [open forum](https://openhealthhub.org/c/open-forum/9)
**Created:** 2016-03-15 08:05 UTC
**Views:** 7255
**Replies:** 34
**URL:** https://openhealthhub.org/t/nhs-infrastructure-projects/180
---
## Post #1 by @mayfield.g.kev
Really a set of questions (so please bear with me) and I think the answer is a set of open source infrastructure projects. Also is this the right place for questions like this??
A problem I have at the moment is updating a list of GP's and Practices on a Document Management System. The normal solution is to provide updates via traditional HL7 but our PAS system doesn't support this.
So what I'm planning to do is a system where:
* A member of staff (or computer) downloads the relevant files either quarterly. (possibly from TRUD but possibly from this link http://systems.hscic.gov.uk/data/ods/datadownloads/gppractice)
* Unzips the files and load the files data into a database (probably the HAPI HL7 FHIR Server I mentioned in another thread)
* When this is done again I would be able to detect changes in GP's or Practices and possibly generate traditional HL7 messages.
So questions:
* Can I get this list elsewhere via an API? [SDS does provide an API (LDAP) for these but I need a list]
* Could I just process a list of changes from somewhere?
* If not would the solution above be useful for others? (The HAPI FHIR Server would provide an API which most modern systems would prefer rather than file uploads and traditional HL7)
---
## Post #2 by @clive.spindley
see comments below ...
Summary:
1. Data can be processed very quickly now (millions of records in seconds),
update only runs should be avoided (look at twitter, you can crate and delete
but no update - KIS)
2. Think new ... use BI tech
3. Moving files around manually is as much out of date as spreadsheets are (the
less duplication of data and files moving around the system the better)
>
>
> mayfield.g.kev
> http://mandrillapp.com/track/click/30623946/www.openhealthhub.org?p=eyJzIjoialo5OTZuNmRlX2xiS3RuOFhXTGNFWjJyTnVRIiwidiI6MSwicCI6IntcInVcIjozMDYyMzk0NixcInZcIjoxLFwidXJsXCI6XCJodHRwOlxcXC9cXFwvd3d3Lm9wZW5oZWFsdGhodWIub3JnXFxcL3VzZXJzXFxcL21heWZpZWxkLmcua2V2XCIsXCJpZFwiOlwiMDhmZTQwNjBmZTBmNDI5ODk1NzYyYTE4OWM4YzBiN2FcIixcInVybF9pZHNcIjpbXCJjMDRkNDY4MGFhOTRkZjAwYjc0ZTY3ODNkMjRlMjNhY2I2ZmY3ZDU4XCJdfSJ9
> March 15
> Really a set of questions (so please bear with me) and I think the answer is
> a set of open source infrastructure projects. Also is this the right place for
> questions like this??
>
> A problem I have at the moment is updating a list of GP's and Practices on a
> Document Management System. The normal solution is to provide updates via
> traditional HL7 but our PAS system doesn't support this.
>
> So what I'm planning to do is a system where:
>
> * A member of staff (or computer) downloads the relevant files either
> quarterly. (possibly from TRUD but possibly from this link
> http://systems.hscic.gov.uk/data/ods/datadownloads/gppractice
> http://mandrillapp.com/track/click/30623946/systems.hscic.gov.uk?p=eyJzIjoialV1aDdGcmhLeFU4QlZXeTE1ck9rS0tNb0V3IiwidiI6MSwicCI6IntcInVcIjozMDYyMzk0NixcInZcIjoxLFwidXJsXCI6XCJodHRwOlxcXC9cXFwvc3lzdGVtcy5oc2NpYy5nb3YudWtcXFwvZGF0YVxcXC9vZHNcXFwvZGF0YWRvd25sb2Fkc1xcXC9ncHByYWN0aWNlXCIsXCJpZFwiOlwiMDhmZTQwNjBmZTBmNDI5ODk1NzYyYTE4OWM4YzBiN2FcIixcInVybF9pZHNcIjpbXCJkOWY4ZDFhZGU2YzlhMDZlZDY0MmJjYWI0ZjQ2ZmJkZjQ5NDVhMDQ2XCJdfSJ9
> )
>
free months out of date, typical NHS, this is 2016, not 1916, there can be no
excuses for having info which is 3 months out of date
> * Unzips the files and load the files data into a database (probably
> the HAPI HL7 FHIR Server I mentioned in another thread)
>
so long winded, typical NHS, use BI to remove any manual tasks (this 2016 ...)
> * When this is done again I would be able to detect changes in GP's
> or Practices and possibly generate traditional HL7 messages.
>
why do you need to know what the changes are, what is your requirement?
> So questions:
>
> * Can I get this list elsewhere via an API? [SDS does provide an API
> (LDAP) for these but I need a list]
>
an API is to integrate systems, you are trying to integarte data, think outside
the box and use business intelligence tech
> * Could I just process a list of changes from somewhere?
>
best to avoid, see above, updating is pain (to "reload" the entire lot should
take no mre than a few seconds using BI)
> * If not would the solution above be useful for others? (The HAPI
> FHIR Server would provide an API which most modern systems would prefer rather
> than file uploads and traditional HL7)
>
share the good stuff
---
## Post #3 by @mayfield.g.kev
Clive,
What do you mean by BI tech?
I think you've misread the requirement. I have limited/no control over the source data or how data is added to 3rd party systems other than messaging.
How would you avoid ETL phase into your BI solution? I would love to avoid it, my line of work is normally measured in milliseconds and so yes months delay is poor.
---
## Post #4 by @mayfield.g.kev
I'm not a NHS Information/Data Analyst. So if you were thinking I was planning something using DTS or SQL Server Integration Services solution, I'm not.
---
## Post #5 by @phillip.rust
Hi All
So there are many techniques for downloading the GP Data.
Using a mechanism where you download the file load it into a database and compare it against an extract from your edms is a great idea and could easily be done. So if you break it down it falls into three steps
* Downloading the data.
* Comparing the data and creating an exception dataset
* Creating and updating the data in your destination system based on the exception dataset.
Downloading the data can be done several ways:
* Batch process (Powershell or linux script) - HTTP Get, unzip batch process and parse and insert into holding table.
* Integration engine - HTTP Get from within your integration engine, unzip batch process, process the data into holding table.
* Custom built API that allows you to download and distribute national files to a specified holding table.
Comparing the data is really a task for the database engine that you employ and you would need the download from TRUD and your EDMS system. once you have this use your database engine to do your comparison and produce the exceptions list ( a series of records for update, insert and delete )
Once you have done this you can get this data into your system in the following ways
* Use your integration engine to process the exceptions list into HL7 and send to your EDMS
* Use your SQL engine to dump Structured files (HL7 or otherwise) for your edms to pick up (Likely that your edms is using MIRTH so you could get your supplier to do this)
* Custom built application to parse the data and using NHAPI to generate HL7
* If your hl7 interface is not up to the task even though it says so in the specification you could always use products like Blue Prism to act as a robotic interface into the application directly to update your gps (Slow but effective)
Just my thoughts but there are so many ways that you can do this.
---
## Post #6 by @mayfield.g.kev
Yep, that's what I was thinking.
It's the first bit, have done it several times as I'm sure many of us have done (which makes me think it could be useful to share the code). How the last bit works will vary between systems and trusts.
None of the methods of getting the data in the first place are ideal. You can use LDAP queries to get the data but I'm not aware of any systems request data like that except for core NHS systems. (I keep meaning to write a FHIR demo which does this - don't have the time :( )
---
## Post #7 by @phillip.rust
I hear you. Would be great if we could have a FHIR interface for this with OAUTH2 or even an ODATA interface for downloading that we could wrap with FHIR. MMM we will need to do something like this for one of the projects that we are working on. It may be worth putting something together. We code in C# MVC 5 webapi. if we created something over the next 3 months and put it on a TFS repository would that be of interest?
---
## Post #8 by @phillip.rust
BTW Kevin. I was the guy sat next to you at the HL7 Shindig
---
## Post #9 by @adrian.wilkins
One concern that I have had raised to me about the ODS data is that some identifiers are preserved when a clinician moves to another role or setting.
Does anyone know if this is the case?
---
## Post #10 by @mayfield.g.kev
I thought so.
This is me carrying on from that.. I came away thinking we need to do more to kick off FHIR in the NHS and OpenSource community - once started it will run by itself pretty sure of it.
I don't know why it's not took off like it has in Europe and rest of the world.
One thing you said is definitely true .... hackday.
re: OAuth2. I understand a solution is being purchased for Code4Health, it would be sensible to get a few (FHIR) projects that use it.
---
## Post #11 by @mayfield.g.kev
Adrian,
Do you mean, consultants are still assigned to old organisation (with open end date)
---
## Post #12 by @jonny.rylands
Kev, do you know what OAuth2 solution is being procured for Code4Health?
Doing a bit of research into this domain at the moment, so would be good to know what else if out there.
Cheers
Jonny
---
## Post #13 by @clive.spindley
BI is an acronym for Business Intelligence, BI:
integrates,
aggregates and
visualizes (processes into
pictures) data
the above can be used to offer predictive analytics
ETL is embedded within BI
p.s. use of spreadsheets is not BI, the use of spreadsheets, each with their
"own" data, has become prolific and is causing significant problems
---
## Post #14 by @mayfield.g.kev
Company was opusVL, don't recall grant types being mentioned.
---
## Post #15 by @mayfield.g.kev
As you may know I'm already using using spring security to secure API!s. I didn't see any problems moving that to point to opusVL libraries. Don't think I'd have been able to use those libraries without spring or other Java product (opus is php or something like that)
Was told opusVL could use any LDAP database for authentication or others. not sure about authorisation probably LDAP again or within opus database
---
## Post #16 by @jonny.rylands
Cheers Kev.
---
## Post #17 by @ian
Hi Jonny,
The Code4Health platform requirement was primarily about developing an Oauth2/OpenID approach to allow Code4Health 'users' to connect to various 3rd party APIs on the C4H platform e.g. the Ehrscape back-end or terminology services, NHS Choices etc. So this was mostly about single sign-on for developers.
However it was also recognised that this would have some value in allowing developers to get familiar with OAuth2/OpenID in the context of SMART-on-FHIR, which will be part of the Platform etc, but still in the context of a demonstrator environment, not for production use, or for allowing SSO of real clinicians/patients.
However, I understand that some real-world projects are now at a stage where OAuth2/OpenId are seen as a viable approach and I think there are some discussions about we align the limited C4H 'dev space' requirements with emerging real-world requirements. It would certainly make sense to align this work as much as possible.
Ian
---
## Post #18 by @mayfield.g.kev
Ian,
So this opusVL is off or still under discussion? If not do we have timescale?
I'm not after this for login security/authentication (I can use OAuth2+Spring+(LDAP, Kerberos or OpenID) instead) but for role based access management (leading to SMART across the enterprise).
---
## Post #19 by @ian
Hi Kevin,
No definitely not off. I am just not clear if the work that OpusVL have agreed to do for the C4H Platform is suitable for real-world deployment, in terms of clinical providers/RBAC etc. That was not part of the original remit. I am not saying it is *not* suitable, just not clear of the suitability.
Clarification is being sought :)
Ian
---
## Post #20 by @mayfield.g.kev
[quote="adrian.wilkins, post:9, topic:180"]
ODS data is that some identifiers are preserved
[/quote]
Yep it seems to be the case. The GP file is listing many doctors as Active and assigned to a practice but many also have an end date (dates imply retirement).
I think active means they are still registered as a doctor and the end date means they are not practicing medicine (with NHS) - so for FHIR Practitioner these would be inactive? [Think this is the approach I've used in the past]
---
## Post #21 by @adrian.wilkins
Amusing data antics anecdote...
Wanted a squizz at the ODS data... downloaded it from TRUD.
It's a zip archive with all the documentation and data in it as CSV files. Despite being a bit pointless inside another archive, the data files are all individual archives.
They're titled with a suffix of .zip
```
awilkins@motoko:~/data/ODS_weekly/Data$ unzip egpcur.zip
Archive: egpcur.zip
End-of-central-directory signature not found. Either this file is not
a zipfile, or it constitutes one disk of a multi-part archive. In the
latter case the central directory and zipfile comment will be found on
the last disk(s) of this archive.
unzip: cannot find zipfile directory in one of egpcur.zip or
egpcur.zip.zip, and cannot find egpcur.zip.ZIP, period.
```
They're actually RAR archives. 7-zip File Manager correctly recognizes this and opens the archive up. I wonder how many automated ETL processes this has stymied...
---
## Post #22 by @clive.spindley
that doesn't make sense ...
if they are not practicing how can they be assigned to a practice?
(everyone need to stick to their jobs)
---
## Post #23 by @mayfield.g.kev
It doesn't make sense.
Would you use it to find the practice a patient belongs to (if the patient was registered to a retired GP). Still seems wrong though.
Adrian.... I think that maybe where my warnings are coming from.
---
## Post #24 by @robdykedotcom
Download a zip, parse contents, mangle for diff and fire changes into a DB?
I'd reach for Pentaho Kettle. When you've built a job it can be exported
and run to a schedule.
Rob
---
## Post #25 by @mayfield.g.kev
Is that open source? It's not clear from the website
Wondering if it's better to process the files using something like this and output a diff?
Currently I have:
1. http get
2. unzip file
3. for each line in file check to see (via REST read) entity exists or has been updated
4. if not exists then create (REST post) else update (REST put)
The rest calls @ 3 are a bit expensive - probably better to keep a copy of the last file processed and use that for the diffs.
May use the data in the file as triggers only and use LDAP to get the actual data (seems to be better quality) - so a diff would work still.
---
## Post #26 by @robdykedotcom
http://community.pentaho.com/projects/data-integration/
---
## Post #27 by @clive.spindley
as promised here is a link:
myModel1
i have decided to drip feed myModel as it is really, really complicated (it's not possible to overload it
with data but i suspect it could be possible to overload the people trying to understand it)
notes:
1.all health transactions should be assigned to a PATIENT pathway (even if it is just the NHS TimeLine,
uniquely ID'd bi the NHS Number)
2. care pathways are color coded e.g. a child PATIENT pathway where the care pathways is autism might
be coloured PURPLE (all the BI technologies make heavy use of color including Tableau ;-))
IF THIS IS OF INTEREST TO YOU PLEASE LET ME KNOW AS I INTEND TO PASSWORD PROTECT
(i am 'appy to be open with those that i trust, but i am not an idiot, nothing is free and in myWorld the
most important thing "i can do" is to develop my data model and play with it using Qlik tech)
i use Qlik technology to model data for and develop PATIENT health apps, I am comfortable with it,
Qlik Sense app tech is in it's early stage of development, they really are a way
of rendering their dashboards on mobile devices, they are not that "appy" yet but THEY are
able to invest in and "DO" the right things
Qlik will not share their plans4the future, why should they? they live in the real world, the competitive
world, they must stay ahead of the competition :-) but i trust they are "heading" in the right direction ...
(i.e. they do not have the same usability as, say, a bank app giving customers access to their
financial transactions) - i am looking @ other technologies
ps thanx 2Google for allowing me to share my model with others in a similar world to me
NEXT ? integarted Care Teams, then integarted Care Models
On 17 March 2016 at 20:59 Rob Dyke <discourse-system@openhealthhub.org> wrote:
robdykedotcom
March 17
http://community.pentaho.com/projects/data-integration/
Visit Topic or reply to this email to respond
In Reply To
mayfield.g.kev
March 17Is that open source? It's not clear from the website Wondering if it's better to process the files using something like this and output a diff? Currently I have: http get unzip file for each line in file check to see (via REST read) entity exists or has been updated if not exists then crea…
Visit Topic or reply to this email to respond
To stop receiving notifications for this particular topic, click here. To unsubscribe from these emails, change your user preferences
---
## Post #28 by @mayfield.g.kev
Quick update.
Have found a better source for the data. NHS Monthly amendments which means less processing to work out what has changed http://systems.hscic.gov.uk/data/ods/datadownloads/monthamend
Also have loaded several files into the HAPI FHIR Database. Quite impressed with the results (well it's great for a web developer but may not so for SQL devs.) A search for GP's in Erewash CCG is reduced to
GET http://localhost:8080/hapi-fhir-jpaserver/baseDstu2/Practitioner?organization.partof.identifier=urn:fhir.nhs.uk/id/ODSOrganisationCode|03X
Which returns JSON representation of GP's in the CCG. No messing with SQL or building the API layer.
---
## Post #30 by @adamlees
[quote="mayfield.g.kev, post:23, topic:180"]
Would you use it to find the practice a patient belongs to (if the patient was registered to a retired GP). Still seems wrong though.
[/quote]
Kev, a patient isn't registered to a GP at all but to a practice. This caused a lot of problems over the years with legacy systems expecting an individual not an org, but recently they seem to have reintroduced a relationship to a named "lead" GP (who is probably the senior partner in the practice) and may well not be the person that the patient regards as "their" doctor.
---
## Post #31 by @mayfield.g.kev
You're correct (did have to check a spine response to confirm).
I'd not noticed before, must have got used to seeing Patients ManaginOrganization and Practitioner everywhere
---
## Post #32 by @mayfield.g.kev
and in non FHIR terms .... Patients Practice and GP.
---
## Post #34 by @gary.brown
Just as a side note, a colleague has recently setup a cloud forum specifically relating to the NHS. We are working on digitising NHS projects in conjunction with GDS and NHS Digital and will be sharing our experiences https://www.nhscloudcommunity.co.uk/
This all began at the recent AWS Summit in London where it was recognised that there was a need for all NHS authorities, Department of Health, Public Health England and their partners to work with each other and share ideas and learning to get the best outcomes of using cloud technologies within our sector.
---
## Post #35 by @adrian.wilkins
Have gained some very useful cloudy experience here at DWP recently.
---
## Post #36 by @Lonsward
same thing here... i've been lurking here for a while (for a while i'm just sitting home doing nothing but searching for [rx coupons](https://rxcoupons.org/) because of my health issues and for lack of money, so i decided to learn a little bit while doing nothing literally) but now i decided to register since i wanted to talk with other people and i also had some questions. but definitely already gained lots of experience and useful info here!
---
## Post #37 by @pacharanero
@Lonsward thanks - glad you like the forum... please do invite other people to the community that you think would benefit from the info we share here...
Marcus
---
**Canonical:** https://openhealthhub.org/t/nhs-infrastructure-projects/180
**Original content:** https://openhealthhub.org/t/nhs-infrastructure-projects/180