Data Repository System Tables

How do I find data in the MEDITECH Data Repository?

Many new DR report writers have a challenging time locating data in the DR because they're unfamiliar with the database table and column naming conventions. Luckily for those users, MEDITECH has provided tools within each application as well as system level tables for both the NPR and M-AT platforms that help to identify table and column data.

For M-AT systems (6.1x and Expanse) there are DrTable_Main and DrTable_Columns. These tables provide information about M-AT objects, records and fields related to the SQL tables in the livefdb database.

Understanding MEDITECH data storage

Before going further we should discuss the MEDITECH data structures and how application data is stored within MEDITECH as well as SQL Server. Imagine that each NPR or M-AT application is a file cabinet. Using ADM (NPR) and REG (M-AT) as examples, these file cabinets would have drawers representing either a DPM for NPR (ADM.PAT in this example) or an Object for M-AT (RegAcct).

When we open a drawer we find folders, which represent the next level of storage: an NPR segment on the left and an M-AT record on the right. As a reminder, at this level, each is the equivalent of a table in the DR. This is important to note especially for M-AT, since a “record” in the context of a database table has a different meaning than it does here.

Finally, we can take a single sheet of paper from each folder (in this example account number) which represents an individual data element or column – what we would simply call a field from a DR table.

Finding data in SQL Server using DR system tables

Now that we understand the basics of MEDITECH data storage structures, let's look at how we can use that knowledge, along with some basic TSQL queries to the DR system tables to find tables and columns you want to use for a report.  (Download an updated version of the query below from the DR Network.)

The queries below can be used to find MEDITECH data in the DR by searching for SQL tables and columns by name; searching by the source NPR DPM, segment or element; or searching by M-AT object, record or field.

Let's look at a livedb-based example (NPR), saying I want to find which tables have a column named AccountNumber:

SELECT
  T.TableName,T.Description AS TableDescription,
  C.DpmID, C.SegmentID,C.ElementID
FROM livedb.dbo.DrTableMain T
  INNER JOIN livedb.dbo.DrTableColumns C
  ON T.SourceID = C.SourceID
  AND T.TableID = C.TableID
WHERE
    C.ColumnName = 'AccountNumber'
ORDER BY
    T.TableName

And here's a sample of the output:

I see that I can use AbstractData, AdmVisits or BarVisits to get the AccountNumber to use on my report. (Any of these are correct, but we always recommend joining to tables within the same application when possible.)

Let's look at another example, this time from M-AT (livefdb). We want to find any table from the REG application that might have an admission date, but we're not sure exactly what the column is called. We can use SQL wildcard searches (with the LIKE operator and % sign) in our query to find what we're after:

SELECT
    T.TableName, T.Description AS TableDescription, C.ColumnName,
    C.ColumnObjectClass, C.ColumnRecord, C.ColumnField
FROM livefdb.dbo.DrTable_Main T
    INNER JOIN livefdb.dbo.DrTable_Columns C
    ON T.DrTableID = C.DrTableID
WHERE
    T.Application_FocApplID = 'REG'
    AND C.ColumnField LIKE '%AdmitDate%'
ORDER BY
    T.TableName,
    C.SortOrder

And the results below show me the likely table I'm after is RegAcct_Main.

You can see how using the metadata provided with the DR will help you quickly identify tables and columns and make reporting out of the Data Repository easier and more efficient.