Business Intelligence: demonstrated
NHS organisations have a growing need for good, real time information to help them meet targets and financial imperatives and to help them improve their service to patients.
Microsoft has a range of technologies that can be used to capture, store and analyse data, and then present it to users as “business intelligence.” The UK healthcare team has been working with four acute trusts to show how these can be used to meet the needs of the NHS.
The NHS reform agenda is creating new demands for information. Trusts need data to help them meet targets, claim payments under the new financial regime of payment by results (PbR) and attract patients who are being given more choice about their care. “I can go from a scorecard, to a departmental analysis, to a consultant analysis in three clicks.”
Yet generating such data can be time consuming and difficult. “Today, the general experience is that trusts get a business analyst to collect information, which is put into spread-sheets and emailed to everybody who needs to work on it,” says Microsoft technical strategist Nick Umney.
“Then, there is a big email trail as reports are compiled and sent off to senior managers – who often complain that the data is prone to errors and out of date. Our vision was that, in future, analysts should build a data-warehouse: a single version of the truth.
“Then, we wanted to link in tools to get that data to the people who need to see it day to day and to let them share it with others: still working with that single version of the truth.”
From data to business intelligence
Microsoft’s UK healthcare team worked with four acute trusts in England to create a demonstration of how its software can be used to turn data into the “business intelligence” (BI) that is needed in the new NHS policy and business environment.
“The way we see BI happening is in layers,” says Mr Umney. “There is an infrastructure layer and a foundation layer - systems that generate data, such as the hospital Patient Administration System (PAS). Every trust has those. "The project shows how Microsoft technologies can be used to capture, store, analyse and present NHS data in a way that turns it into the BI that acute trusts need."
“On top of them, there is an integration layer, a data-warehousing layer and a reporting and analysis layer, all of which come out of the box with Microsoft SQL Server 2005. Then there is a presentation layer, and for that we used elements of the Microsoft Office suite, such as (the analysis software) Excel and (the collaboration technology) SharePoint.”
An NHS Scorecard
The trusts involved in the project identified four areas of information that they wanted to see included on an NHS “scorecard.” These were: information about their activity and finances; “patient facing” information (to respond to the choice and personalisation agendas) and details about the trust’s “people” (recruitment, sickness rates and so forth).
One of the trusts also provided the project with data to populate these areas. “We built a scorecard inside SharePoint that uses a traffic light system to show the status of each element,” says Mr Umney. “Alongside that, there is some trend information. So, if I look at bed occupancy, which is one element of activity, and it is showing green but trending down, I can go and find out what is happening.”
Shining a (traffic) light on activity
Managers can find out more by clicking on the “traffic light” buttons on the scorecard. “For example, if I click on “outliers” I can look at the figures [for how many specialties have patients on other wards]. And if I want to go further again, I can click on one of those specialties and examine the data by consultant code.
“That means that if one consultant seems to have a lot of outliers, I can go and find out why there is a problem and whether anything can be done to fix it. I have gone from a scorecard, to a departmental analysis, to a consultant analysis in three clicks.” "I can drop live data into that [map] and it will show me where patients are coming from, the ratio of male to female patients from each area, and other demographic information.”
Visualising key targets
However, the project was keen to use some of the other, sophisticated tools in the Office suite. For example, to display information about healthcare associated infections it deployed the drawing environment, Visio.
“We created a floor plan for one the trust’s hospitals in Visio,” says Mr Umney.
“I can drop the latest data into that, and see instantly where any problems are, because the map colours up red. When we first did this using real, hospital data, we picked up two wards where there seemed to be problems - and it turned out that a corridor between them was not being cleaned properly.”
Putting patients on the map
With the spread of patient choice and PbR, trusts are increasingly keen to find out where their patients are coming from and why they are choosing their hospitals – or otherwise. To display some of this information, the project turned to the mapping software, MapPoint.
“We created a map of the area around the trust that provided us with data,” says Mr Umney. “Again, I can drop live data into that and it will show me where patients are coming from, the ratio of male to female patients from each area, and other demographic information.” “I can pull up several years’ worth of data, select the subset I want and generate a graph that shows trends. The really exciting thing is that I can generate predictions based on those trends."
Live reports on live people
The project also looked for ways to make it easier for staff to generate reports that include graphs and other elements that make data simple to understand. “The really good thing, though, is that the data is not static, so reports are not static either,” says Mr Umney.
“Let’s say I want to generate a report about people who have joined and left the trust in the last month. I can see the headline figures very quickly. But if I click on “leavers”, I can get a list of names. And if I click on a name, I can see their employment history. Again, I have gone from general data to personal data in three clicks.”
Eyes on the road ahead
Crucially, however, the project wanted to get away from the “driving through the rear-view mirror” approach to data collection and usage that the NHS has traditionally been forced to take. “Let’s say I want to explore accident and emergency admissions,” says Mr Umney.
“I can pull up several years’ worth of data, select the subset I want and generate a graph that shows trends. The really exciting thing is that I can generate predictions based on those trends. Then I can plan against those forecasts, monitor whether things are on track, and, if not, go back and re-plan.”
Proof of concept
The demonstration generated by the project is a “proof of concept.” It shows how Microsoft technologies can be used to capture, store, analyse and present NHS data in a way that turns it into the BI that acute trusts need.
It is not a product that can be bought off the shelf. Trusts interested in using some or all of its elements will need to work carefully with partners to identify data sources and work out how these can be manipulated to meet their specific demands.
“Getting all this together is not a small job,” Mr Umney admits. “But people are going to have to do something like it. Trusts have got to get the data for 18 weeks – there are quite severe financial penalties if they can’t show they are meeting the target – they have got to get the data for PbR.
“What we are saying is that Microsoft has the tools to help them do it better, and that many of those tools are things that trusts are already licensed for, through the new Enterprise Agreement [that Microsoft recently signed with NHS Connecting for Health]. So we are getting a lot of interest in this.”
Find out more
Arrange to see the Business Intelligence in healthcare demonstration by contacting Nick Umney by email at nicku@microsoft.com