Transcript Business Intelligence (3 of 3):Technically Speaking, This

Business Intelligence (3 of 3):
Technically Speaking, This is How
We Did It
PRESENTED BY
Objectives
• Describe the basic IT concepts (which can be scaled to almost any
practice size) behind better reporting tools discussed in the prior
two presentations while remaining stable and secure
• Implement SQL Server Reporting Services to email critical reports
and create interactive dashboards.
• Identify and redesign EMR and PM processes to eliminate duplicate
work, reduce errors, and enable reporting.
Presenters
• Shawn McLain, DBA, Systems Programmer, Ortho
NorthEast (ONE)
• Mike Baeske, Software Applications Specialist – Team
Leader, Ortho NorthEast (ONE)
• Nate Moore, Moore Solutions Inc.
Gathering the Data
• Most registration data is stored in PatientProfile table
• Billing data is stored in PatientVisit, PatientVisitAgg, Transactions,
and BillingNote tables
• Scheduling information is in Appointments table
• ActivityLog records actions taken in the system, including changing
appointment details
• Majority of tables have pointers to the MedLists table, which
contain entries of custom content setup in Administration module
• Chart data is stored in the DOCUMENT, DOCDATA , and OBS tables
Chart Data Saving and Formatting
• To generate structured reusable data, save information into
observations
•
•
Form components can be linked to an observation:
OBSNOW() function can programmatically save values
• Ideally the data should be saved in a consistent format
•
•
Checkboxes, dropdown boxes, and radio buttons provide better reportable
data
Free text entry = bad data
Billing Notes Reporting
• With the addition of the BillingNote table, reporting has been made
easier
• Billing Note Subject lines can be preset and selected in a dropdown:
Integrated Reporting Tools
• Inquiries
• Crystal Reports
• GEMS Word Runtime Plugin
Inquiries
Inquiries
Benefits
Users can build and modify inquiries themselves
⁺ Links directly to corresponding chart area (Documents, Orders, etc)
⁺ Can synergize with crystal reports
⁺
Issues
Limited to filters defined by GE (No order instructions nor modifiers filter)
⁻ Filtering is limited by all “And” conditions or all “Or” conditions
⁻
Crystal Reports
Crystal Reports
Benefits
⁺
⁺
⁺
Can build context sensitive reports that are ran from the different modules
Security can be set from administration module on who can run specific
reports
Possible to update database after running report (Potentially very dangerous
if coding has an issue)
Issues
⁻
⁻
Running the reports can impact the entire system since reports are pulling
live data while users are working in it
Developer must setup report template, user cannot dynamically look at data
from different perspectives
GEMS Word Runtime Plugin
GEMS Word Runtime Plugin
Benefits
⁺
⁺
Can easily generate Word documents based on criteria or from a module
User can modify the document before printing or sending out
Issues
Programming options are more limited than with Crystal Reports
⁻ Have to worry about licensing of document editing software
⁻ Generates file from live data, which could impact users
⁻
KEY Resources:
• SQL Server Integration Services (SSIS)
•
Detailed Data copy and manipulation processes.
• SQL Server Reporting Services (SSRS)
•
•
•
Server / Website deployment – Always available
Active Directory Enabled – Report file access and control
Email, file share, webpage delivery mechanisms – Flexibility
• SQL Server Management Studio (Database Management)
• SQL Server Business Intelligence Management Studio
(Report and SSIS Package building)
SQL Server Integration Services (SSIS)
• SSIS is available as part of the SQL Server Install
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS)
(SSIS) Deploy
“C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe” /file
“C:\DTS-Packages\OBS Export.dtsx”
(SSIS) The Big Gotcha
• The User account that creates the package runs the package.
SQL Server Reporting Services (SSRS)
• Configure and Use SSRS to deliver reports via email
• Configure and Use Active Directory to control access to the SSRS
site.
SQL Server Reporting Services (SSRS)
• SSRS -> http://<SSRS Server>/Reports/
SQL Server Reporting Services (SSRS)
• How Many Users Are logged in?
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services (SSRS)
(SSRS) Achieved Goals
• Data available Off the production server
• Email PDF reports to users, email can contain an html link to the
current report
• Exception Reporting – Using a SQL statement decide IF the report
should be sent (Maybe the report is empty)
• Sensitive reports can be protected through AD group membership
Automated Email Examples
Setup Tips
• Data Connections used to pull information from database:
• Database views can be used to format data and apply filters before
sending to Excel (must be careful with filtering, considering the
dramatic impact it has on reporting)
Duration Reporting
Duration Report- Alternative Layout using PivotTable
Copay Report
Midlevel Availability Report
Questions?