KPI list of measures

[eatyourpeas]

Possibly more an issue for @AmaniKrayemRCPCH and the rest of the NPDA team but putting here as a placeholder.

• KPI list of measures from NPDA team
• KPI scoring - fields and scores, denominators and numerators for queries. Queries themselves as SQL even if possible
• Create a KPI table to track the individual scores for each measure for each Patient
• Create a table in the UK to signpost how an individual child is scoring against each measure in the Patient detail view

[mbarton]

Calculations_AK_20240621.xlsx

[anchit-chandran]

Had quick call with @mbarton re implementation design.

Next step is just to make the most naive implementation for kpi aggs with a single query, no table, for a given PDU. This will include:

2 fields for the 'totals' fields

1. eligible
2. ineligible

4 fields for the remaining bulk of kpi measures

1. passed
2. failed
3. eligible
4. ineligible

From there, we can iterate and optimise performance if required.

Had query re my proposal for changing up organisation / pdu structure and the purpose of the Site model - would be good to catch up with you on this when free @eatyourpeas

ERDs:

https://lucid.app/lucidchart/c09d5aa7-3b32-49b0-a704-ede60f8141f7/edit?viewport_loc=-660%2C949%2C2912%2C1430%2C0_0&invitationId=inv_31fc73d9-1dd3-4b95-84d6-bffcb3159ad8

[anchit-chandran]

See lucid chart link for details^

Questions / queries about the current setup

1. Normalising PDUs

Highlighted in red, the data regarding pz_code is denormalized and multiple models are storing their own versions, sometimes with different fields names. This makes it harder to query. The first proposal here would be to make a single PDU table, and reference that via FKs to other tables (note: using pdu_id as the FK, instead of pz_code as we technically have a composite key {pz_code, ods_code}. makes application logic easier):

This would make it easier to create KPI queries - there is a single place table where we calculate KPI aggregations

2. Purpose of Site table

• Is this to enable transfers? @mbarton mentioned depending on the use of the table, we could move its fields into Patient?

3. Purpose of AuditCohort

It seems the AuditCohort tracks progress for a patient through the audit, linking NPDAUsers to their Patients?

class AuditCohort(models.Model):
    """
    The AuditCohort class.

    This class is used to define the cohort of patients that are being audited. The cohort tracks the progress of the audit

I was trying to start the KPI calculations, but I was unsure where to start with KPI1 - eligible patients, defined as:

Total   number of patients with:     
... other fields
*  a visit date or admission date within the audit period   
*  Below the age of 25 at the start of the audit period

I imagine using AuditCohort would determine the audit period but just confused at its relations and purpose. The problem I'm trying to solve is: for a given pz_code, for a given audit date range, what are the number of eligible patients?. Thought something like this:

There are also other potentially useful fields:

• Visit.visit_date
• Visit.hospital_admission_date

KPI Calculation Implementation

For now, we will run a long single SQL query to return KPI calculations, rather than storing in a model. Building this using the following class which instantiates with a given pz_code and calculation_date (audit cohort date range calculated using this). It has a calculate_kpis_for_patients method, which will calculate and return KPI values for those inputs.

A class-based approach enables us to easily reuse subqueries and attributes throughout the whole calculation (e.g., calculate audit date range once at the beginning and use it throughout; the first 12 KPIs are simple counts used as denominators for the subsequent calculations).

We still have localisation of the context of each KPI query logic and can test each independently.

[eatyourpeas]

This is very thoughtful and lots to digest here @anchit-chandran, thank you.

Site model
This is in hindsight possibly nolonger necessary. I imported the structure direct from E12 as I had assumed that centres inheriting patients from another centre midway through the audit year would not want to be responsible for the entire patient performance history. This way, as in E12, I assumed we could track transfers between centres. In fact though, the last conversation we had with NPDA team was that they did not need this feature and were happy to update the patient record with the new PDU directly, meaning previous affilation would be lost and all historical data would port to the new centre. The only thing the Site model currently stores is the date_leaving_service and reason_leaving_service. To my mind, it makes no sense to store these fields in the patient model, since they will have transferred to a new centre and these fields will refer to the old service which is not stored. Perhaps we need a conversation with @AmaniKrayemRCPCH and @cillian-rcpch about how they currently use these fields ?

pz code & ods code
I hear what you say about denormalisation. Part of the reason it is this way is because we abstracted away all the organisational lookups to the API. Remember we store organisation and PDU affiliation for two reasons - the first to define the relationship of the clinician with their PDU(s) (as some clinicians oversee more than one) and the second to define the affiliation of the patient. As above, I thought there would similarly be a M2M relationship through Site, but this now maybe is not needed. If you feel that that this can be achieved with one model for both puposes, then let's do that. I am much happier to have you and Michael sanction data structure this early on in the project.

AuditCohort
This model does not track individual patient progress through the audit as such (in the way that AuditProgress does in E12).
This was introduced to track audit submissions/uploads. NPDA is different to E12 - E12 is only interested in the first year of care for each child newly diagnosed with epilepsy - so each child only has one submission that remains active until one year after the end of that cohort has completed (or the following 2nd week in January). After that no data on that child is collected again. NPDA by contrast tracks the care processes of every child with diabetes in the UK, whether they are newly diagnosed, or whether they have had diabetes for many years. In this way NPDA is much more like a registry and in theory should summarise the diagnosis and care of every child in the UK with diabetes until they turn 19y. Since every child (with T1DM and maybe MODY at least) will most likely be cared for in a paediatric diabetes centre (it is very unlikely a GP would be happy to manage a child with T1DM on their own), we can assume the dataset is fairly comprehensive. T2DM is different since so much of it is missed for years. Anyway, all this means is that one child will have many submissions spanning many years.

An extra complicating factor is that NPDA will be moving to quarterly reporting this year or next. This means that although the annual submission is the most important, submissions still need to be made every quarter, with the totals of all the quarters equalling the end of year results. It also means that we can store multiple submissions for each quarter (with only one being active and each new one replacing the previous active one resetting the flag to false) - this had been an expressed requirement that we store all uploads for governance/paper-trail reasons, so that is the implementation we currently have. However, if we now are redesigning the submission process to allow only a single upload with subsequent edits occuring through the platform, maybe we don't need this complexity. Again, I think a conversation with @AmaniKrayemRCPCH and @cillian-rcpch would be good. Either way, the point therefore of this model is to track the submissions.

KPIs
This all sounds good - class-based approach sounds good, and given our experience in E12, calculation does not really put that as much stress on the database and the main thread as I had anticipated and so persisting the results I agree is not necessary. It has been a learning journey for me. And a vulnerability of persisting results we discovered was that it was possible to store incorrect results that would not be recalculated by default once stored. So in this way if I understand we instantiate the KPI class with each quarter/cohort and call a calculate class method to tot everything up and return the results? Presumably we would do that with every valid submission? Do we not also need to keep track of items that are unscored? Or do we run the KPI calculation only on completed and valid records, in which case unscored would not be needed?

The audit range therefore would be the quarter in question, or potentially that cohort (which would be a sum of all the quarters). The start and end dates of the audit range would be the start and end dates of each quarter (see retrieve_quarter_for_date in general_functions) or the whole audit year. This would create totals for individual patients and would be reported back to the user presumably each time a visit was added or edited. We would also need a way of aggregating results for all KPIs in a given cohort or quarter so that we could then report at the different levels of abstraction.

Also, although we have not been asked for this, I think it would still be good to be able to look at KPIs at organisation level as well as PDU. This would mean the class instantiation might also accept an ods code. I will drop this if Justin, @AmaniKrayemRCPCH and @cillian-rcpch tell me to, but my guess is that this is a helpful thing for some PDUs for no extra work.

[mbarton]

wrt Audit Cohort, I've written up my thoughts in the forum here (RCPCH internal): https://forum.rcpch.tech/t/submission-design/154.

I'm wondering if we split it out into two models: SubmissionPeriod and Submission.

[mbarton]

Sprint meeting - 22/08/24: @eatyourpeas to stub out all the KPI calculation methods, @mbarton to implement them and write tests

[anchit-chandran]

screenshot and new link: https://lucid.app/lucidchart/68321384-f874-43ad-8407-686f98783c81/edit?viewport_loc=-2960%2C-1098%2C6768%2C3144%2C0_0&invitationId=inv_34af6e98-c84a-4aff-a56a-21db8c972d68

[AmaniKrayemRCPCH]

Hi all,

Adding comments on the parts I'm tagged in. We can discuss some of this in tomorrow's sprint meeting but, for reference:

Site model -
At the moment, we don't tend to track transfers in the same way as E12. There's no transfer of the patient record within the audit platform. PDUs will enter all visits within their unit each year, and the transferring PDU will flag when a patient leaves their care. The receiving PDU doesn't do anything different, they just start entering the patient's visits.

During analysis, we will identify those CYP who have transferred (based on the 'leaving service reason'), assign them to their most recent PDU (based on visit date). Those CYP are not included in health checks analysis, as they haven't had a full year of care in either PDU.

For the platform, this means that we allow the same patient can be entered under different PDUs. For each unit's KPIs, exclude CYP with a date of leaving in the audit year from health check analyses.

We may choose to do things differently from above, but our analysis methodology can handle the current process.

AuditCohort -
We want to ensure they're able to edit data at any point in the year until the deadline, including data from previous quarters.

Our preference is continue with one file gets overwritten throughout the year. This means that, at the end of the audit year, the units have only one file with data. There's no confusion where a person may submit Q2 data under Q1 and then it's not included.

KPIs -
If there are two PDUs in one organisation, I can see how organisation-level reporting would be useful. Is it possible to handle this on a case by case basis - so only provide organisation-level reporting for a select number of PDUs?

[eatyourpeas]

Thanks @AmaniKrayemRCPCH
Site model - If you want to identify children that have been transferred during the audit year and exclude them from being counted, it is easier to track their transfers than include it all in the patient model. I can try and explain why tomorrow.
AuditCohort - this has been renamed to Submission. The live version now allows for what you describe. Only one csv is allowed per csv and this covers the audit year. The table uses the latest visit date to report which quarter the data is submitting to include
KPIs - We have deprecated organisation level reporting as we understood this after last meeting not to be required. I don't think we can easily put it back. The issue is that there is nothing in the .csv submitted that links the children to the organisation ODS code. We could use the ods code of the organisation of the user who uploaded the data as a proxy, but otherwise there is no way of separating the children in the .csv between organisations unless each is labelled with the ods code. Instead now we store the PDU, the governing parent trust/LHB and the name of the lead organisation in the PDU.

[eatyourpeas]

@AmaniKrayemRCPCH @cillian-rcpch Can I ask about measure 10 (coeliac)?
(Item 37 is dietician_additional_appointment_offered)
The definition list is:
Number of eligible patients (measure 1) whose most recent observation for item 37 (based on visit date) is 1 = Yes

So we are saying that any child that had an additional dietetic appointment in the year has coeliac disease?

That does not sound right - I figure there is something missing in the calculation that I have misunderstood. Could you explain?

If you have the SQL queries you use I would find those super helpful. If not would it be ok to ask for the measures to be in the same format as E12? That is

measure name/number
measure explanation
nominator
denominator

[AmaniKrayemRCPCH]

@eatyourpeas item 37 is gluten free diet

We consider anyone who's been recommended a gluten free diet to have coeliac.

[eatyourpeas]

Sorry I misunderstood the numbering. Thank you @AmaniKrayemRCPCH

[eatyourpeas]

@anchit-chandran KPI queries for numerators 1-49 now in. I will leave the rest to you if are happy

[anchit-chandran]

Closing as resolved