|For a video version of this blog (4:40), check out our YouTube channel!|
Utilizing Data Repository System Tables
Many first time MEDITECH Data Repository report writers find it difficult to find data in the Data Repository tables because they are unfamiliar with the MEDITECH 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.
Within NPR there are:
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.
NOTE: The original system tables that MEDITECH created where named SysDrTables and SysDrColumns. Many sites may still see these tables in their live and test databases. They can be used similar to DrTableMain and DrTableColumns but may not be up to date with the most recent MEDITECH version schema.
Within M-AT there are:
DrTable_Main and DrTable_Columns
These tables provide information about which MEDITECH M-AT object, records and fields populate the tables and columns in the M-AT side (livefdb) of the Data Repository.
Understanding MEDITECH data storage
Before going further we should discuss the MEDITECH data structure and how application data is stored within MEDITECH as well as SQL Server. The best example of this is to think of the NPR and M-AT hierarchy as a filing cabinet.
We can start with an application which can be considered a filing cabinet, such as ADM in NPR or REG in M-AT.
In every filing cabinet the drawers are the equivalent of an NPR DPM or M-AT Object which stores the folders and sheets of paper.
Each folder is the equivalent of an NPR segment or record in the M-AT platform which also correspond to a table in the Data Repository. Because the segment or record resides within the DPM, it will be preceded by the DPM name as seen below with the “main” segment and record:
Finally we get to the papers/files within each folder in our filing cabinet! These will equate to the data elements or fields in M-AT and NPR or the columns within each table in SQL.
Finding MEDITECH Data in SQL Server using DR System Tables
MEDITECH has provided customers with tools in the application fields (SHIFT + F9) in CS/M-AT and (SHIFT+F8) + (SHIFT+F8) in MAGIC which help to tell users if and where that fields data is captured in the Data Repository. This functionality will display the DR table and column as well as the NPR DPM, segment, and element where the data is captured to in the MEDITECH structure.
Paired with MEDITECH SHIFT + F9 functionality in M-AT and CS or (SHIFT + F8) + (SHIFT + F8) functionality in MAGIC, the system tables can help users to quickly identify the tables and columns that they would like to use in their queries.
The queries below can be used to find tables and columns in the Data Repository by searching for table names that contain columns with specific text in them OR change the column or table names that they are looking to find in the DR or search for tables based on NPR DPM, segment or elements or M-AT Object, Records or Fields.
These queries will be helpful for finding tables and columns that you may want to use when starting your reports. But what if you have already started your report and are looking for a table with a specific column that you want to join to?
The following queries will help users to do just that by utilizing a second join to the DrTable_Columns and DrTableColumns system tables:
The results from these queries can be used to help find tables in the same application or different applications that can be joined to. The tables from each platform can even be joined together to help find M-AT and NPR tables with like columns to pull data from both platforms.
Report writers should make full use of the metadata provided right within the DR to help quickly identify tables and columns needed for their reports and make reporting out of the Data Repository easier and more efficient.
By Taylor Solari at 18 Apr 2017, 09:41 AM