Statistical process control in SQL

Statistical process control in SQL


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!

Leave a Reply

Your email address will not be published. Required fields are marked *