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.
Read on, or watch this blog on YouTube!
For NPR-based systems (MAGIC, C/S and 6.0x), we have DrTableMain and DrTableColumns. These tables provide information about which MEDITECH NPR DPM, segments and elements populate the tables and columns in the NPR side (livendb) of the Data Repository. You'll find these two tables in the livedb, livemdb and livendb. (Note: the original system tables were named SysDrTables and SysDrColumns; many systems may still see these tables in their live and test databases. They will have valid data, but may be out of date and no longer receive schema updates.)
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:
T.TableName,T.Description AS TableDescription,
FROM livedb.dbo.DrTableMain T
INNER JOIN livedb.dbo.DrTableColumns C
ON T.SourceID = C.SourceID
AND T.TableID = C.TableID
C.ColumnName = 'AccountNumber'
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:
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
T.Application_FocApplID = 'REG'
AND C.ColumnField LIKE '%AdmitDate%'
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.