This video is to support the SQL template to allow you to produce SPC
charts using SQL and today I'm with Modupe Ogunlaja - the author of the
script. Hi there! So we're not going to talk about the merits of statistical process
control in this video, but if you do want to know about that we've got a great
resource - Making Data Count and there's the web page and Modupe - it's a very
popular resource - how many hits do we have up to date on this website? We've got over
30,000 hits currently on the website. Over 30,000 hits! So if you go to the website there'll be resources - interactive PDF
guides, videos you can watch which will tell you about the merits of using
statistical process control. But also this is where you can actually download
the SQL templates which you can then run locally and it's in the resources
page and there is also the exact web address to pick up the SQL template. Now
I said we weren't going to talk about statistical process control but we're
just going to give you one example. This is one of our favorite examples. So a
classic RAG table with a two-point comparison arrow. Now Modupe if you
were placed with this table in a meeting room where is it likely that
your attention is going to fall? My attention will be focused on the red sections. Yeah - the red sections. That's often culturally
where we tend to concentrate and I've got one example of how you can get
different messages from the red sections. So here's the same chart - general surgery -
drawn as a statistical process control chart and in this example you can see
despite it being red actually we've had some fundamental improvement going
on and we're actually getting really close to the target so you can imagine
the conversations that might be had with this specialty is actually carry on
doing what you're doing you're showing strong improvement you might get over
the target. But here's another specialty Rheumatology with exactly the same red
outlook and the downward arrow but when we look at it through statistical
process control a completely different message and in fact this specialty is
under a lot of pressure under a lot of decline and you'd want a different
interaction with the speciality. So statistical process control gives you
a more meaningful outlook on what is happening with your data
Right - back to the SQL template script. So the purpose of the script is to provide
you with an initial solution to create the attributes of an XMR chart in a
table within your data warehouse or server to allow you to visualise
statistical process control charts. So
you will have your data source or input which could be your data warehouse or
your data server. Then this script is going to sit next which will manipulate
those tables and provide you with the SPC calculations. And then you've got your
output which will be your visualisation tool. And what sort of visualisation
tools Modupe could this script potentially work with? You can use
tableau, SSRS, Power BI or any other product available out there. So we've
tried to make this script to be visualisation tool agnostic so whichever
software tool you've got it's just about plotting the metadata that the SQL
script creates. Now you don't have to use SQL. I just like to point out we do have
some alternative tools and if you go to our website there's also an excel
template which will allow you to plot individual indicators and create
SPC charts that way. And for those of you that use Alteryx we also have an Alteryx
macro. And do get in touch with us via email if you want any information
about that. So those of you that know a bit about statistical process control
will know that there are quite a few charts - quite a wide range of charts. This
script will just create the XMR chart. And why have we decided to do that? Well
because it's the simplest and most versatile of control charts that should
handle a large variety of data in healthcare that you might be using.
So it's not to say that you shouldn't graduate on to some of the more
specialist charts but this is a great place to start. Really briefly you will
need to know the anatomy of an SPC chart because that will help you with the data
that comes out of the script. So it really is just a time-series graph with
three reference lines. So there's a mean line in the middle
and there are some process limits on the outside and those process limits are
created on the data itself. So you would expect around 99 percent of the data to
fall within those limits and because we know that those limits are set on the
data itself you do need a robust amount of data to create reliable process
limits. There's lots of different opinions in the industry but we tend to
fall with about 15 to 20 points will create a reliable limits. You can go
lower than that, you just need to be aware of the risk that might have with
the control limits not representing the variation of your data. The script will
outline where statistical process rules are broken- which are special cause - and
those are times where you want to go and investigate. And these are the main rules
that the script will outline. So a single point outside the process limits, or a
run of points above or below the mean line, a run of points in consecutive
ascending or descending order or two out of three points close to the process
limits. And it will flag if any or all of these rules are broken at any particular
time because there's no hierarchy with these rules. If there's any
special cause variation you would want to go and investigate it because it's
not something that's likely to happen through random chance or the natural
variation of the system. There is something behind that either
something that's improving the system which you might want to support or some
extra concern or pressure in the system which you'd want to understand and
perhaps try to alleviate. So when you download the SQL template you will be
able to run it straight off because what we've done is we've put inside the
templates some sample dummy data so you can run it straight off and you can view
the output and perhaps link it to your visualisation tool. So you can get a feel
of what should happen when you run the template. Once you're happy with how the
template works you will want to remove the sample data and use your own data
from your servers or your data warehouse and to do that you need to locate
section 1 which is the data input action and there will be two base tables
In that section one will be a data table and this will cover the following
attributes - a date field, an area code or an area name. Now what this could even be
trusts, this could be regions, this could be specialties, this could be wards - any
area/division that you're interested in. And the second thing is a measure code
and name and this will relate to the key performance indicator or the metric or
measure you're interested in. This example we've got with a KPI refering
two weeks waited and then value will be the data, the measurement, the recording
for that particular date in that area for that particular measure. So for this
in 2019 in August we had for the North outpatient team 12
weeks waited for KPI one. The second table you will need is a lookup or
metadata table which will add extra details to your statistical process
control graph. You'll need a link back to the original table and then there's two
extra fields. One is a target to allow you to put targets against your
statistical process control graphs and finally there is a high improvement
column. Now this is to help you identify which is a positive direction of travel
for improvement and if a positive direction of travel is higher numbers
you want this value set at one if you want a positive direction of travel to
be lower numbers you want to set it at zero. So for this example for weeks
waited in the north outpatient team we've set this flag or binary code at
zero because what we want is lower numbers - lower waiting times for patients
But if it was another metric - perhaps mandatory training - we might want to set
that flag at one because we'd want as many of our staff to complete the
mandatory training as possible. Now if you do all that with that metadata table
and the SPC table you will be able to create outputs like this - an SPC
dashboard where you can highlight improvements visually. You can
highlight concerns and you can place that against the target line
So to modify your script you need to identify those insert values which we've
provided and replace them to a link to your local activity table or tables but
very importantly keeping the column structure because it's that column
structure that will be used in the script to create your output table and
also to remember to create your own version of that metadata table which for
each indicator gives you the target and which direction improvements should be
So when you run the script you will get an output table presented and what I'm
going to do now is I'm going to run you through some of the key columns so you
can apply that or join it into your visualisation tool. So you've got the
area name or measure code and you'll probably want that as a filter because
there'll be one SPC chart specific for each area name measure code combination.
You want the sample date for your x-axis. You want to be using the value column to
plot your data. Now there are also columns to cover the mean and upper
control limits and lower control limits to turn your time series graph into an
SPC chart. And then at the bottom there there are also all the rules that we're
considering and there's a flag. One indicating that for that particular
point in time there's special cause variation, the rule has been breached or
zero to indicate common cause variation. And the final column I'd like to
highlight to you is we have a variation type column. Now you might want to use
this column to highlight your special cause on your visualisation so that your
decision-makers using this chart can instantly appreciate whether
particular time points are showing improvement, concern or common cause
variation. They don't have to count the dots or look at the rules and there's
would be an example just to overlay the colour or from the variation type onto
your chart.
I'm just going to go really briefly into adapting the script. There's obviously
lots of adaptions that you're welcome to make but we're just going to follow through
some of the more common ones you would want to do - and that's to do with
changing some of the rules. So, for example, for our count of points above or
below the mean line we've set it to seven. You might want to set it to a
different value and if you do what you need to do is you need to identify the
lines of commentary that have the comment change for local requirement and
when you highlight those areas of commentary. You just want to make a
simple change of that constant from 7 to 8 or 9 depending on where you want the
rule to activate. Now there's just one complicated exception to that is that at
the bottom there you will see that actually you want to use the value of
the constant you want minus 1 because it's counting the set. And you'll need to
do the same for all of the change for local requirements comment because
there'll be a set for above the mean line and a set for below the mean line to
Please make sure when you make these changes that you test what's happening.
So we're going to take a pause there and we're just going to see how this script
goes down and see how well it's used to see if there's any enhancements that
could be made over time because what the next thing we might be introducing to
you is you might come up with cross our statistical process control icons is we
will be expanding this script to allow you to place icons rather than charts
Just so as an alternative to that RAG summary display you could have a concise
display of a whole number of metrics and perhaps areas where you can identify
using statistical process control where change is happening and what the
capability of hitting the target is. I'm just going to run you for a few things to be
aware of running this script is just a reminder don't run the script
blindly. When you introduce your own data you must assure yourself that the
statistical process control chart is correct and is in being interpreted
correctly. And if you want more better understanding over interpretation of
statistical process control again following the Making Data Count website
Secondly I just want to remind you that the XMR chart will run based on all the
data provided so if you have a new months data that you add to the script the
process limits and mean line will address to include that new data. And the
third thing to mention is that feel free to adapt the script for your own
purposes but just make sure you that test any changes and if you come up with
an improvement or a more efficient way of doing what we've shown you today
please do get in touch. But the only thing we must say is do make sure that
it's system agnostic as far as possible because when we share these scripts
around we want minimal intervention when you apply it to your local data. We don't
have anyone having to go through the entire process changing column names
Just want at the start it to be set up in a nice holding table and then it can
run through right towards the end. So thanks for listening and good luck!