1 / 37

Keeping your Data Repository in Top Health

Keeping your Data Repository in Top Health. By: Ian Proffer MUSE Session 305. About the Presenter & Acmeware. This is our 11 th year at MUSE! Acmeware’s founder led the development of the DR while working at MEDITECH We work exclusively with the MEDITECH DR

casey
Download Presentation

Keeping your Data Repository in Top Health

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Keeping your Data Repository in Top Health By: Ian Proffer MUSE Session 305

  2. About the Presenter & Acmeware • This is our 11th year at MUSE! • Acmeware’s founder led the development of the DR while working at MEDITECH • We work exclusively with the MEDITECH DR • We have a friendly (and valued) relationship with MEDITECH • Ian Proffer • 15 years in IT, specializing in relational databases • 6+ years in healthcare, including MEDITECH C/S (Jefferson Healthcare) and Harborview Medical Center in Seattle

  3. Session Agenda • Database administration and maintenance • System-level and database monitoring • Data validation and auditing • Opening and tracking service issues with MEDITECH • Streamlining operational support • Discussion, questions and answers

  4. Some questions about your DR • Is your database being maintained? • Are the data transfers running? • Does the data match what’s in MEDITECH? • How much time do I have to spend doing this? • Note: Meditech screenshots are C/S 5.63

  5. SQL Server Database Maintenance • Database backups – a full database backup of livedb should be done daily; transaction log backups are not necessary • Data integrity checks and optimizations should be done regularly

  6. Data Repository Manager Objects within the SQL Server instance Similar look and feel to SQL Server Enterprise Manager Transfer Logs should be regularly monitored, though most errors are also reported to the DR Module

  7. Data Repository Manager Manage connection threads and view thread activity Connection Threads Status

  8. The Data Repository Module • Meditech DR Module Functions and Tools • Operations Overview • Master and Transfer Background Jobs • File Maintenance • Reviewing DR Errors

  9. DR Main Menu

  10. Operations Desktop - Status New feature: main menu selections are repeated here The Master Background Job is the main job all DR transfers and controls the start of the file maintenance routines.

  11. Operations Desktop Messages Messages here are the same as File Maintenance The File maintenance job clears out the storage history on the size of the database and the log files.

  12. DR File Maintenance Status The Status screen shows file maintenance and purge status by module.

  13. Data Transfer Menu • Use this menu for daily maintenance and troubleshooting potential DR issues. • Key Functions • Transfer Job Status • Check Pending Activity • Master Background Job Status

  14. Master Background Job Status View of the current status of the Master Background Job.

  15. View Transfer Job Status Details Example of a LIVE environment with several background jobs These jobs should be monitored daily

  16. View Pending Activity Enter ALL to get a list of pending activity by module, or… Lookup and specify a specific module or modules

  17. Pending Activity by Module • Pending rows to be transferred • Although transfers are running a particular table can get ‘hung up’ and accumulate pending data. • A high row count may indicate a problem.

  18. DR Errors and Exceptions • Use these menu items to check for table-level database issues, including: • Missing primary keys • Truncated data • Other data problems caused by sending MAGIC or C/S system

  19. DR Errors Selection Menu Select one or all modules, enter date range and click on summary.

  20. DR Error Summary • Summary of Unique Errors by Day • To View Details • Highlight the error line, click on Detail

  21. DR Error Details Click here to return to Summary page

  22. Download or Print Errors Select download folder here and click OK.

  23. Download or Print Errors Open it when you’re done.

  24. Initial Load Status An entire module or specific tables can have an IL performed to reload data into a table from the NPR data structure.

  25. Data Validation and Auditing Why do it? • To provide confidence in your DR data and reports • You can do it manually… • Compare your DR reports to standard or custom NPR reports in MEDITECH • Use NPR Report Writer to save DPM subscripts to a file, import the file into a SQL table, write a procedure that compares your table against the SQL table from MEDITECH • …or you can have software to do it for you. • DrAuditor by Blue Elm Software

  26. Checking for Timely Table Updates You can manually check each table too… • Use the RowUpdateDateTime flag to determine when a table was last updatedSELECT MAX(RowUpdateDateTime)FROM AdmVisits AS LastUpdate

  27. DrAuditor • Validates primary key rows and column values • More efficient and accurate then manual audits

  28. DrAuditor Analysis Export

  29. Opening Service Issues with MEDITECH www.meditech.com/customerservicehome.htm

  30. Opening Service Issues with MEDITECH Some things to remember: • Submit new issues regularly • Update existing issues as necessary (if the nature of a particular error message changes, for example) • Use this subject line format: 5.54.6 MriPatientVisits – Missing Primary Key • Include the error log output and other symptoms, e.g. error frequency, etc. • Review open service issues regularly; Meditech will request customer permission before taking any corrective action

  31. Making It a Little Easier So what do I do with this?

  32. Making It a Little Easier How we manage error logs and submit issues to MEDITECH: • The AcmeErrorManagement database • Imports error log .txt files into SQL Server • Parses each error into a table while eliminating extraneous rows (blank lines, page breaks) • Checks for existing tasks and DTS patches and updates new errors if they’ve already been reported • Builds a “worklist” of new errors that need to be evaluated and submitted to MEDITECH • Allows us to see common errors (and DTSes) across all our clients, helping MEDITECH resolve issues faster • Allows us to summarize and report DR error activity back to our clients

  33. Making It a Little Easier SQL Reporting Services Report – Error Status and Detail

  34. Making It a Little Easier How we leverage RowUpdateDateTime: spGetTableStatistics

  35. Making It a Little Easier Is a table receiving updates as frequently as we expect? • AcmeMonitor examines specific tables for RowUpdateDateTime within a user-defined timeframe and alerts via email when tables are out of sync

  36. Making It a Little Easier What about ring release updates? • Make a list of all tables used for reporting • List all stored procedures, views and function that use these tables • spTableDependencies • Note all Meditech DTS’s that affect these tables, looking for column name changes, table name changes, etc. • Compare the schema of tables in the livedb against the testdb • Third party tools like SQL Compare, SQL Delta, etc.

  37. Thank you!

More Related