Delivering Data: Extracting Information with Data Repository and Report Writer

Q: Is there a delay in when data is available to pull to report?
A: No, generally there is no delay. The Report Writer accesses data in the transactional environment, so you can usually access information on a custom report as soon as soon as it is visible in MEDITECH. There are some rare exceptions involving information that’s stored in temp segments, but it’s usually still possible to access this data using techniques that we teach in our Level 1 and Level 2 seminars. For Data Repository, there is a minimal delay, since we are sending the data to an external SQL database. The DR transfers can be optimized to ensure that your most important reporting SQL tables are updated in terms of seconds, rather than minutes.

Q: How do you make DR reports available to MEDITECH staff?
A: For native SQL tools, SQL Server Reporting Services (SSRS) is widely utilized as a SQL report-distribution tool. Any other third-party solution can be utilized as well, as long as it is ODBC compliant. MEDITECH is generally not involved in these types of discussions/decisions, as this report distribution takes place in the Microsoft SQL environment.

Q: Can you launch DR reports from within the EMR?
A: You cannot; SQL reports must be launched from the SQL server, or any linked server.
Q: Do you have anything from a director level to show what is available?
A: The Report Writer is a very, very flexible tool. The rule of thumb is that if you can see a piece of information in MEDITECH, you can display it on a custom report.  You also have the ability to perform your own logic using any data that’s stored. The same is true for DR: if you can report on it in MEDITECH, it will be available in your SQL environment. SQL also provides additional flexibility which allows the incorporation of third-party data (i.e. Non-MEDITECH data) into your SQL reports, as well as the use of any 3rd party ODBC software.

Q: How frequently are the seminars held?
A: The Level 1 Report Writer seminar for Client Server is held 9 or 10 times a year, and the Level 2 seminar is held roughly 4 times a year. MAGIC seminars are held slightly less frequently due to the amount of interest we receive. The schedule of upcoming seminars can be accessed through the Customer portal on MEDITECH’s website or by following this link. Data Repository WebExes are held every other month, and are also posted to the Customer portal on MEDITECH’s website at the beginning of each month and can be found here.
Q: Are all standard reports initialized in the archive?
A: Not all standard reports have been initialized and uploaded to the archive.  Both MEDITECH staff and hospital staff regularly upload new reports to the archive, but due to the sheer number of standard reports that exist within MEDITECH, not all of them have been recreated as custom reports at this point in time.

Q: Are there plans to implement a data dictionary to show what the data is and where it comes from?
A: In the long term, there is a project in it’s infancy to redo the data definitions to include additional documentation on specific fields, segments, and index files. As you can imagine, this project is an immense undertaking. It is in the very early stages, and the scope and timeframe are still being evaluated.

As a short term solution, an updated Application Specific Information page is underway. This page will provide a substantial amount of information on the most commonly used fields, segments, and index files, as well as additional documentation on where this information is visible in each application.

Q: How did you know that table has stoppedupdating?
A: Either the DR transfer job will show as stopped in the ‘View DR Transfers’ routine, or there will be a ‘sequence error’ for the affected table in the DR error log.

Q: Can you use DR as a downtime solution?
A: Yes; reports can be run out of SQL when MEDITECH is down. It should be noted that the SQL tables will not be updating due to MEDITECH being down. The SQL data will only be current as of when MEDITECH went down, but will still be accessible.

Q: Can you update data in MEDITECH from DR?
A: No, that is not possible. The river only flows one direction: MEDITECH to DR/SQL.

Q: How do you download a file from the MEDITECH archive website?
A: You can click the “Download” link on the Report Archive to download the report to a .zip file on your PC. Do not attempt to unzip the file manually, as this will corrupt the file. You must use the Load Reports from File routine for Client Server facilities or Upload Reports From PC routine in Magic to load the report into your LIVE or TEST ring. This routine can be found under the Updates menu in NPR. 

Q: How often are the Report Writer supplemental Webex classes offered?
A: Each Webex class is offered roughly every 1-2 months. You can access the schedule for upcoming Webex classes, as well as the Level-1 and Level-2 Report Writer seminars, by clicking the “Seminars” link on the Customer Portal on meditech.com.

Q: In the Report Writer training seminars we would like to see more focus on clinical data. Specifically, we’ve found reporting out of NUR/PCS to be more complex.
A: Because attendees at our Level-1 and Level-2 classes come from so many different backgrounds and hold a variety of different positions at their hospitals, we’ve created workshop examples that will apply to as many people as possible and don’t require an intimate knowledge of clinical data. However, classes that focus on reporting out of specific clinical applications, such as NUR/PCS and LAB, are a great idea for the post-training Webex series. We will certainly keep this in mind as we continue to develop new Webex classes.
Q: What is the Data Repository implementation timeline for new customers? How long does the setup take from start to finish?
A: DR ships with around 9,000 tables, although many customers choose to start with only a few hundred commonly-used tables turned on; this includes all required Stage 1 ARRA tables. A listing of these commonly used tables can be found on the Data Repository Product & Resource Page for both MAGIC & Client/Server.

The testing period for Data Repository is highly dependent on the scope of each customer's anticipated SQL reporting requirements and the amount of testing they'd like to perform before going live. This period of time could range anywhere from one to six months depending on your testing preferences.

As soon as your DR has gone LIVE, current day-to-day data will be brought over to Data Repository so that you can immediately start developing SQL reports. Your specialist will then load into these tables all historical MEDITECH data (i.e. entered into MEDITECH prior to the DR go-LIVE) available in each application database via processes known as “Initial Loads”. Depending in part on the number of application databases being enabled for DR and the amount of historical data present in each of them, these ILs will take anywhere from a few weeks to several months.

Q: Are there plans to change the RW tool; i.e. like Cognos or other system?
A: No, there are no plans to make major changes to Report Writer functionality. SQL reports working with MEDITECH's DR is an alternative solution; Microsoft SQL Server includes powerful reporting tools standard, and there are many additional third party reporting tools available based on your organization’s particular reporting goals.

Q: Are there DR classes coming up in Boston?
A: All DR trainings are offered remotely, via WebEx on a bi-monthly basis. The session will discuss an overview of how Data Repository functions in greater detail, a demonstration of the various routines available within it, and daily responsibilities of DR staff. The latest session will be held in early November; links and sign-ups are available on the "Seminars" page, accessible from the Customer Portal.

Q: Using RW can be difficult, as can SQL. Is there an easier tool on the horizon?
A: Report Writer is a fourth-generation reporting tool that provides extensive flexibility in what types of reports can be created; attending RW training will acclimate new report writers to the tool. With classes held regularly in MA, as well as regional trainings in other areas around the country and an upcoming Adobe Captivate class, the RW group aims to make training as accessible as possible. Out of DR, MEDITECH is developing a Business and Clinical Analytics tool. It will provide substantial standard content, such as revenue cycle information and diagnosis trending, using data present in the DR. For those wishing to create customized reports out of either environment, SQL training is available both online and at local universities/colleges. SQL Server Reporting Services or even third-party reporting tools also exist as additional options.
Q: Where is the Data Repository Validation Tool available?
A: The validation utility is available with all 5.66 releases of DR. To access and configure validations, create a custom menu with the following routines attached:

  • DR.validate.dict
  • DR.validate.job
  • DR.validate.result
  • DR.validate.schedule

Q: Is there a current Development project to have data transfer more quickly to DR?
A: Data is available to Data Repository once the application database has filed it into the corresponding structure. For example, B/AR period-data will not be available until the PE process has completed within Billing. As this completes, B/AR will send the associated data to DR. Once made available to DR, its Transfer Jobs will bring data over as quickly as possible. Some variables that affect the speed at which they process include network traffic, hardware, and disk performance. Based on these factors, your organization's particular reporting goals, and volume of data being generated, additional transfer jobs beyond the standard set can be configured.
Q: When is SQL Report Exchange going to be available?
A: We are actively working on this project and hoping for next year.

Q: Will there be a cost associated with the new Business and Clinical Analytics system?
A: Yes; this will be an additional application.

Q: How many customers have been involved with Business and Clinical Analytics and can I get involved?
A: 20-30 customers have been actively involved with focus groups throughout the development process. If you're interested in joining these groups, please sign up on the Focus Group homepage, and sign up for the Quality, Operational, and/or Financial groups as desired, as well as the periodic Newsletter.

Q: Will Business and Clinical Analytics include data from the full suite of applications?
A: Yes, Business & Clinical Analytics will include standard content/dashboards pulling data from all applications currently enabled for Data Repository.

Q: What tools did Hayes Medical Center use to build their reports?
A: Hayes primarily used SQL Server Reporting Services (SSRS) to create and distribute their reports. Although, SQL Server Integration services (SSIS) was also used and some coding was done from directly within SQL Server Management Studio. Both SSRS and SSIS are included with your Microsoft SQL Server license, although they are not installed by default.

Q: Do you provide any materials that show where information is stored?
A: In Report Writer, yes we have a few main tools for this:

In Data Repository, the Report Writer tools, particularly Application Specific Information, will help identify what specific data elements are being sought. Once the elements of interest are determined, the Field Inquiry routine in Data Repository will identify the corresponding tables. From within SQL, the tables SysDrTables and SysDrColumns can also be used. These tables can also be related, in order to quickly identify other tables that contain similar data, such as all tables with an MIS query mnemonic. These two system tables are covered in greater detail in our Systems Tables Tutorial.

Q: Is there any training provided for Database Administrators, such as a checklist on what to look for?
A: MEDITECH does not provide SQL-specific training, although our WebEx seminar, scheduled for November 7th, covers the day-to-day responsibilities that the DBA or any other DR user should be reviewing.
Q: How do we know when key fields are are added in system updates moving forward?
A: For Report Writer, there is no real way to determine if new fields that have been added in MEDITECH from a Report Writer standpoint. When new functionality in any application is added, we then typically track down the information when someone wants the new data on a custom report. We can then make note of the data fields used to pull this information once we locate where it is stored in the database.

Within Data Repository, when an update moves into your test environment, your test SQL database will be updated to reflect this new schema. Knowledge Base Article 33386 provides a query that will compare your live and test databases, highlighting the changes between each. By reviewing this query’s output for any fields that are utilized in your reports, you will be able to adjust your SQL reports to account for any significant schema changes.

Q: Are there any sample reports for the items shown today?
A: All of the sample reports shown in this presentation for Report Writer can be found in our Report Archive.

Hayes Medical Center shared with us the output of some of their reports, but the source code is proprietary and is not available. When the DR Report Exchange launches, customer code will be made available.

Q: Is there any way to clean up old data for customers that have been Live with DR for sometime?
A: This functionality is not available, as the original design goal of DR was to avoid application purging and make data available indefinitely. Your SQL programming team could certainly write a script that accomplishes this, although this practice is discouraged due to the risk of unintentionally deleting extra data. To better improve scalability, a future enhancement is being designed that will allow the DR data to be split across multiple SQL Server file groups.