DR 6.1 Overview
When asked to write this article, I was excited to able to share the discoveries of what I had learned over the past couple of years working with the 6.x data repository. When I actually sat down to write it however, I realized I had writers block. I wanted to say so much, I didn’t know where to start. So let’s start from the beginning.
Similarities between 5.x and 6.x Data Repositories
Before we even start to look at the data repository itself, we should have a general understanding of the data. We want to know…
- Where the data lives.
- The granularity (level of detail) of the data (Patient, Visit, Appointment, Episode, Procedure)
- The general relationships between the modules
In the 5.x world those relationships looked something like this
In the 6.x world, those relationships are, for the most part, identical
The modules have simply been renamed in some cases to more accurately reflect their function and keep pace with contemporary terminology.
The granularity of the modules on the far right (BAR, SCH (5.x) CWS (6.x) and PCS) is below the visit, but in every case there is a VisitID in these modules to allow you to link back to the ADM (5.x) or Reg (6.x) modules.
Native Data to DR tables
When you look at the 5.x Data Repository tables and compare them to the NPR DPMs and segments, you will see a high degree of correlation between the two. This means if you know NPR and in particular the DPMs and segments in which to find the data, finding the data in data repository becomes much easier.
That relationship exists to a higher degree in the 6.x environment between the objects and records in Object explorer and the tables in the 6.x data repository. Furthermore, as a general rule, the naming of the objects in object explorer closely mimics the table names in the 6.x data repository.
Understanding this granularity or level of detail is one of the keys of quickly navigating the 6.x DR, so we’ll take a minute to review. There are multiple levels of granularity in the Data Repository Data, but there are three levels that are common to ALL modules. They are
- SourceID If you have a single database implementation, this field should be identical in ALL tables. If you have multiple databases, the SourceID will represent the database.
- PatientID This level of granularity, refers to the Patient or person. Each person should have ONE and only ONE PatientID
- VisitID This level of granularity refers to a visit to the hospital. The VisitID will link all activity of the patient for a particular presentation to the hospital. (Emergency,Observation, Inpatient etc.)
- Module Granularity Module granularity refers to the top level of detail in each module. For CWS, this would be the appointment, in PCS, the intervention, In OM, The OrderID. At the module level the Main table will ALWAYS have a reference VisitID or PatientID (ArmAuth_Main).
 The VisitID is also associated with the visits table in ArmAuth_Visits.
6.x Table Naming Conventions
In 5.x Data Repository, I found one of the most annoying naming conventions to be the D prefix on dictionary tables (DLabTest or DPhaDrug), which took the dictionaries out of alphabetical sequence with the rest of the module tables. This no longer the case in the 6.x world. Dictionary tables are now in alphabetical order with the rest of the tables in the modules.
The table names in the 6.x data repository are in camel case. Example : RegAcct_Main. This example is used intentionally to highlight two other significant naming conventions.
ALL Tables that have a visit granularity end with Acct_Main.
Examples : RegAcct_Main
Furthermore, tables that end with _Main represent the top level of granularity for their table group.
Examples : CwsAppt_Main (Main Appointments Table)
BarBch_Main (Main Batch Table)
MisLoc_Main (Main Location Dictionary Table)
Finally, the 6.x table naming convention has implemented a standard use of the underscore character. The underscore is now your friend in that it is used as a prefix to highlight the child relationship to a parent table.
Example : BarBch_Main (Main / Parent Table)
BarBch_Txns (Child Table of BarBch_Main – Transactions)
BarBch_TxnAddlData (Child Table of BarBch_Main - Sibling of BarBch_Txns -Transaction Additional Data)
BarBch_TxnAddData_Queries (GrandChild of BarBch_Main - Child of BarBch_TxnAddlData - Queries for Transaction Additional Data)
Knowing these simple naming conventions makes navigating the tables in the DR much easier.
The 6.x data repository is far more normalized than it’s 5.x counterpart. What does that mean for those of us who do not have a computer science degree and the student loan debt to accompany it? Simply put, it means if you want information about a doctor, you may have to go to one and probably more tables to get that information.
Normalization is the process of reducing duplication in storing data in a database. Thus instead of storing the doctor name, NPI number, fax number, address etc. on every visit, we create a table of doctors, each with a code (mnemonic or more accurately UnvUserID) and store the information about the doctor in this table. Then we store the 10 character UnvUserID on the visit. To get the doctor information from the visit we use the UnvUserID to access the information on the doctor table.
As stated previously, the 6.x data repository is far more normalized than it’s 5.x counterpart. Let’s look at a real world example of our doctor scenario above.
For a particular query we will require the following information about a doctor
In 5.x DR that query would look something like this
In 6.x, because of the high degree of normalization, the query is far more complex
As you can see the 6.x query requires far more joins. Now imagine you had to get this information for the Attending, Admitting, Primary Care and Emergency Physicians. Copy and paste is your friend, but with that many joins, your stored procedure may become very difficult to read.
To the rescue… Inline Table Valued Functions.
Inline Table Valued Functions
Table Valued functions have been around for a while now. They are a means of code encapsulation that allows you to write a piece of sequel code and then easily reuse it in different stored procedures or, better yet, multiple times in the same stored procedure. The problem with table valued functions is that when implemented in a stored procedure, they reduce the stored procedure from multi-threaded processing to a single thread. Because of this, people have avoided using them.
Inline Table Valued Functions remove this issue. Because the function is inline, the MS SQL optimizer can see what the function is doing. In essence, the inline code of the function is substituted into the code of the stored procedure where the call to the function occurs. Inline Table Valued Functions can be used like parameterized views and have the advantage of being able to be deployed across databases.
Below are two examples of a function that returns basic provider information, the first as a Table Valued Function, the second as an Inline Table Valued Function:
Inline Table Valued Functions – Implementation
The most common way of implementing these functions is through the use of the APPLY clause of a SELECT statement. The format would be
OUTER APPLY dbo.fnProviderInfo(RAM.SourceID, RAM.VisitID, 'Admitting') AS PROV01
where RAM is the alias for dbo.RegAcct_Main. The columns could then be accessed in the SELECT column list as follows
There are two forms of the APPLY clause; CROSS APPLY and OUTER APPLY.
CROSS APPLY works like an INNER JOIN. If an empty data set is returned by the function, then the row, to which that data set would have been applied is dropped from the population. If the data set has two rows, then each row in the returned data set is applied to the row in the original data set.
OUTER APPLY works like a LEFT JOIN. If an empty data set is returned by the function, then the row, to which that data set is preserved and the columns in the returned data set are applied to that row as NULLS. If the data set has two, rows then each row in the returned data set is applied to the row in the original data set.
The advantages of using these functions are obvious.
- By coding the multiple, often complex, joins required to get related data into one function call, you make your stored procedure easier to read maintain.
- You can write the code once and reuse it.
- If there is an error in the function, correct it once and it is corrected everywhere it is used.
Although we have used this technique in a fairly simple example of retrieving doctor information, it can also be used for such thing as
- Insurance Information
- Order Information
- Diagnoses Information
- Procedure Information… etc.
I hope you have as much fun reading this as I did writing it. (I AM A GEEK… I wear it as a badge of honour).
By Ken MacPherson at 12 Oct 2016, 14:37 PM