Suggestions to model marking an observation as "Abnormal"

Not if you create multiple tables (1:0 relationships) with specific attributes for each kind of concepts. It’s basic database design. I agree with Anshuman that the current model is so generic that makes easy queries and reports very complex (and hard to understand for the profane). At the very least I’d use views (with hardcoded concept ids), either normal or materialized.

@angshuonline do you think it will help to setup a design call for this?

Would be happy to discuss in design call. Thanks Daniel.

@jthomas do you think we could have a slot for this?

1 Like

Would love to schedule some design time. Here are the next available open slots. Would one of these work for you @dkayiwa @angshuonline? Is there anyone else required for this call?

  • Wednesday, November 30 from 7-8pm UTC
  • Monday, December 5 from 4-5pm UTC (day before OMRS16 so attendance will be light)
  • Wednesday, December 7 (Cancelled due to OMRS16)
  • Monday, December 12 from 4-5pm UTC

In FHIR, the Observation resource has an “interpretation” field which supports marking something as abnormal

As discussed in the other thread that Bharat linked to, @burke is in favor of supporting this in the obs table.

So, one possible next step is for one of the Bahmni tech folks to propose the specific data model change we should make.

I agree with trying to stay close to FHIR in this case, as @burke suggests. As I was reading this thread I considered the most common flags that appear next to results: For most numeric results, High, Low, Very/Critically High (typically shown as “HH” as FHIR uses), Very/Critically Low For culture sensitivity MICs (a numeric way of expressing how good the antibiotic is): Sensitive, Resistant, Intermediate For immunological tests (mainly for vaccinations and infection detection): Reactive, Non-Reactive For some tests that don’t have numbers (such as rapid strep or influenza tests): Positive, Negative FHIR appears to have all of these typical annotations covered.

Reporting and clinical decision support tools that wished to find the abnormals would look for H/L/HH/LL. Reporting that wished to find other important or hazardous info, such as positive Zika tests, would look for Positive or Reactive.

Can this be taken as the “normally abnormal” e.g. transient rises in systolic BP especially in the elderly? Also is there a problem if the observation in this format is misaligned with other observations that when integrated make it a ‘true abnormal’ or ‘true normal’?

I created to track this, and put it in the Needs Design state.

@akhilmalhotra, @angshuonline or @bharatak, who from Bahmni would want to push this forward?

HL7 has always included an abnormal flag with observations. In FHIR, it’s the Observation.interpretation. One of the reasons for doing this is because reference ranges can vary by so many factors (ethnicity, gender, age, timing of specimen, lab equipment used, etc.). The notion of “abnormal” is not so much a universal truth; rather, it’s a representation of whether the system reporting the observation believers it should be reported as abnormal.

We didn’t include abnormal flags in the early days of OpenMRS, but, if I had a time machine, I would have included it. The same goes for Observation.status to allow us to properly handle pending results, observations with exceptions (inadequate specimen), etc. If someone could come along and add status and abnormal attributes for obs, I think we would be very happy to add them.

The challenge is the central & often large obs table. Making changes to the obs table isn’t hardly possible for some larger implementations. This is where we need the third leg to this stool: a viable upgrade path for changes to large tables. For example, imagine an upgrade helper module that creates a second obs table with the new attributes and, in a non-disruptive (bandwidth-limited) way, begins copying obs to the new table and keeping any changes in sync (maybe with stored procedures?). A big implementation installs this module and a week later all their data has been copied into the obs2 table, so only a brief downtime is needed for the helper module to replace the original obs with the new populated obs table.

If you can give me (1) an Obs.abnormal, (2) an Obs.status (so status doesn’t get left behind), and (3) a viable upgrade path that doesn’t exclude larger implementations, I’m sold. :slight_smile:

I understand why this is important for implementations with huge obs tables, but it’s a tough ask for someone else who does not have such an implementation to do the solutioning for this. Especially to do it in a generic way that’s guaranteed to work across all implementations, regardless of DB and OS versions.

MySQL 5.6 supports “Online DDL”, and this apparently supports adding a column: @jdick, do has AMPATH researched using this at all?

In relational databases with huge tables the solution usually is to use partitions (physically splitting the table in multiple ones, by some column normally dates). Obviously this is a DBA task, can’t be done with liquibase scripts. But I don’t know if MySQL supports partitions.

Another option would be using PostgreSQL if it’s better than MySQL.

Thanks Darius. We have not but will look into it now and report back.


As far as i can tell, our obs table is some day going to require something NoSQL like. :slight_smile: Uber had a similar problem for their tripstore table which was growing the fastest, just as we have our obs table. They modified MySQL to give it some NoSQL capabilities to store data for this biggest table. The rest of the not so big data remained in their original PostgreSQL database. You can read details at:

Of course we do not have the capacity to modify a RDBMS. So we may choose an existing NoSQL for obs while leaving the rest intact, as we face the reality of Polyglot Persistence - using different data storage technologies to handle varying data storage needs!

Using a NoSQL data store will fix this particular problem (adding new columns fast) but will create others (complex queries for instance). It’s not a silver bullet.

Some people maintain two stores, one relational and one NoSQL that must be synchronized (queues, scheduled ETL processes …).

I’ve read the Uber article and it’s interesting, however I don’t think they modified MySQL, they built something on top of it. I don’t know why they didn’t pick up an already existing solution that provides sharding.

@jdick have you had a chance to look into this?

(This is triggered by me trying to get to inbox 10000, not any specific activity on this topic.)

The Bahmni is going to prioritize adding obs.interpretation (and we’ll also add obs.status, per @burke’s suggestion). We really want to do this in Platform 2.1.0, which should be released this month.

@jdick can you prioritize testing whether the Online DDL feature in modern MySQL will allow this change to happen smoothly for an AMPATH-sized database?

(I have also asked @teleivo if he might have time to test this, or perhaps @lluismf is interested. But really this is a huge issue for AMPATH, and we need AMPATH to be involved in testing the solution.)

I think a sufficient test is as easy as:

  1. Install OpenMRS on MySQL 5.7
  2. Populate a large obs table (by script or something)
  3. Manually execute “alter table obs add column interpretation varchar(50)” and check whether you’re still able to use the OpenMRS UI while this DDL is running

I am not sure why I didn’t see this sooner. I don’t work for AMPATH anymore but when we were upgrading from 1.9.7 to 1.11.6 we used a technic similar to what you have asked about. It is found here . I believe the technic can be modified and included in a generic upgrade module as suggested by @burke

1 Like

Thanks for that context @willa.

My understanding, then, is that it’s okay to make certain kinds of changes to the obs table in new releases, because there is a proven way for an implementation with a huge obs table to upgrade.

It would be nice to also automate the upgrade process for implementations with huge obs tables (and maybe the latest MySQL versions can handle this even easier). Since there are only a few implementations this applies to, I propose that the next time one of them is doing an upgrade they should collaborate with some OpenMRS community developers to build out a standard set of scripts for applying data model changes pre-upgrade.

Thus the Bahmni team can start to work on TRUNK-4976 because it’s not blocked by this particular concern.

(The ticket is still not ready-for-work for a different reason, because there’s an open question about the obs.status column.)

Yes I can, I love DB stuff :slight_smile:

I on a demo DB (MySQL 5.7) with 500k obs, took 30 seconds ALTER TABLE obs ADD interpretation VARCHAR(50) NOT NULL ;