Keys to Data Repository Data Mapping - MEDITECH

1 Keys to data Repository data Mapping The MEDITECH data Repository application provides convenient access to your MEDITECH data in a SQL. database comprised of thousands of tables. To facilitate the best possible use of your data assets, MEDITECH provides resources to help you easily navigate these tables and pinpoint the data you need. These resources come in the form of SQL tables, data Repository routines and online data Model for linking tables. SQL Tables for data Mapping In SQL, two Mapping tables exist in both the MEDITECH TEST and LIVE databases: SysDrTables and SysDrColumns. Each can be queried like any other table. However, unlike other tables, they do not contain hospital-specific data from your MEDITECH system. SysDrTables and SysDrColumns indicate where your MEDITECH data exists in your SQL database. These tables also allow you to trace the path backwards to discover where a piece of data in SQL is stored in MEDITECH .

2 SysDrTables and SysDrColumns help you understand how your MEDITECH data maps to your SQL database. Starting with SysDrTables, this table contains a complete list of all MEDITECH tables that are available in your SQL database for reporting. When opening SQL Server Management Studio, begin a New Query in either your TEST or LIVE database, and run the following SQL query: This query will return the complete contents of the table SysDrTables. Each row in your results is one table that is available in your data Repository SQL database for reporting. Each column in SysDrTables provides information describing that table. Here are the columns to note: TableID Table mnemonic Application Feeder application from which table is populated Keylevel Type of information contained in the table Name Table name Using only this table you could identify all of the dictionary tables for a given application or all tables that contain visit specific information using the Keylevel column, or simply look up the name of a table given its MEDITECH defined mnemonic (TableID).

3 The following table contains a list of all Keylevels a description of each, and a table example for each: PS PATIENT SPECIFIC PatientID is the patient-specific primary MriPatients (PATIENTS). SINGULAR key PM PATIENT SPECIFIC PatientID is the patient-specific column MriPatientEmploymentInfo MULTIPLE which is part of the primary key. (DRCEMP). Another column(s) (besides VisitID and SourceID) is also part of the primary key which allows multiple rows to exist for a single PatientID. PF PATIENT SPECIFIC PatientID is a foreign key BbkHistory (BBKHIST). FOREIGN KEY. PV PATIENT SPECIFIC Both PatientID and VisitID are part of MriPatientVisitEvents VISIT the primary key (PATEV). V VISIT SPECIFIC VisitID is the patient-specific primary AdmittingData (ADMITTIN). key VF VISIT SPECIFIC VisitID is a foreign key OeOrders (ORDERS). FOREIGN KEY. NP NON-PATIENT No patient-specific key in table.

4 data PpEmployeePayroll SPECIFIC not specific to any patient file (PPEMPPF). T TEXT Store lines of text in table rows. PthSpecimenFindingsText (PTHFTXT). D DICTIONARY Used to store data from MAGIC DMisDirectionModule dictionaries (MISDIRMO). To realize the greatest benefit, use SysDrTables in conjunction with SysDrColumns. Run the following query to view the contents of SysDrColumns: Each row in SysDrColumns represents a single column that is available to you in your data Repository . To put this another way, each NPR Element in MEDITECH corresponds to one column in SQL, and SysDrColumns lists all of your available columns and their corresponding NPR Element. Each column in SysDrColumns contains information that helps define that Element. Here are the columns to note: TableID Table mnemonic for the table in which this column exists (a column common to both SysDrTables and SysDrColumns).

5 Name Column name DataType SQL data type for the column (ex. varchar, int, datetime, etc.). Length The defined maximum length of the column SortKey Indicates whether this column is a primary key NprDpm NPR DPM from which data is populated from NprSegment NPR Segment from which data is populated from NprElement Corresponding NPR element for the column ColumnPosition The position (counting from the left) of this column within the table Given a particular NPR Element you can identify every table to which that piece of data flows. More importantly, now that you have an idea of what each table contains, you can use the tables together to take full advantage of the information they contain and find any piece of data you need. Together, SysDrTables and SysDrColumns enables you to find where any piece of data resides in SQL, or from where it originates in MEDITECH .

6 Consider a scenario in which you have an NPR Element, , and you want to find where that piece of data ends up when it is filed in its application. Try the following query: You can see that this piece of data is available in just one column in your data Repository SQL database, called Allergy . Next, run the following query to include information from SysDrTables and find out what table the Allergy column is a part of: This query builds on your previous query by returning everything from both SysDrTables and SysDrColumns. You can now identify the SQL Table and Column where the data resides, but the query can be further improved to present this information more intuitively. The results of this query tell you that the element corresponds to the Allergy column, which is part of the AdmAllergies table. More importantly, this query is the basic format that you can use to work with SysDrTables and SysDrColumns.

7 By altering the where clause you can ask many useful questions about your MEDITECH data . If you take any piece of information from this article, make it this query! Try it out; augment the where clause, and start to gain a better understanding of how to navigate your MEDITECH database. In the previous example you worked with a known NPR Element to find a specific column, but you can also reverse this process. Given a column of data , if you wish to find the NPR DPM, segment, and element that populates it, you can use the basic structure of this query and make a few adjustments to the where . clause. Consider the Address1 column in the AdmVisits table. In working with this column, if you want to know what MEDITECH structure populates it, try the following query: You will see that the element populates the Address1 column. Finally, consider a possible scenario: you know your future reporting needs will require data from a particular DPM or Segment.

8 You can determine which tables the segment populates, and request your data Repository specialist enable and populate those tables (if they are not already). For example, if your future reporting needs require data from the segment, run the following query to determine which tables and columns this segment populates: These examples illustrate the power of SysDrTables and SysDrColumns, when used together, to be your map of MEDITECH to SQL data flow and guide you efficiently through your database. Given a piece of information as input ( element, column name, etc.), you can use this basic query format to derive valuable information about where data originates from in MEDITECH , or where it flows to in SQL. Application Routines and Functions MEDITECH provides additional resources to use in conjunction with SysDrTables and SysDrColumns. Use some of the following methods to identify pieces of information you can use as input to your SysDrColumns and SysDrTables queries.

9 Table Inquiry - Identify a table in this routine by name or mnemonic and it will provided detailed information about the table itself, the columns in the table, and the NPR Elements that correspond to those columns. If you are more comfortable working in MEDITECH environments than SQL, this is a good place to gather some of the same information as we identified with the queries above. Field Inquiry - Provide an NPR element and learn the data Repository SQL Table and Column in which this Element is stored. Shift+F9 - Within your MEDITECH applications you can determine the NPR DPM, Segment, and Element of any field that you come across by pressing Shift+F9 in the field. If you know you want to work with the data from a particular field in an application you can use Shift+F9 to identify the corresponding data Repository SQL information. In the event that the NPR data element is defined as 'Not captured by DR', please take the NPR element info provided from this Shift+F9, and use the tools outlined above to find the element or a similar named element.

10 Some MEDITECH screens are designed to use temporary data structures which do not produce the proper data Repository Information to the screen. However, the data structure that will ultimately house this temporary data will most likely have a corresponding SQL table. Online data Model Finally, at on the data Repository Product and Resource homepage you can access the online data Model under DR support. The online data Model allows you to view how tables can be linked in SQL and also depicts table relationships for SQL reporting.