Transcript drill down report

Recognize and Prevent
Medication Diversion
Pyxis and Meditech Data
Jamie McDonald
Proactive Diversion Report allows
the facility to view controlled
medication activity




Topics
Primary objective
Technical issues
Data decisions
Reports developed
Primary Objective
Monitor controlled medications
withdrawals and administrations
to identify possible diversions
before it gets out of hand
Items to Resolve

Archive Files from Pyxis (technical issues)



Measuring the data (data issues)



Automate the archival process
Automate the process of importing Pyxis data
into the Data Repository
What method of measurement?
How are we going to display the data?
User names that don’t match in Pyxis and
MT
Automating Pyxis Archive Solution




We have a PC that has the location of the Pyxis
Archive Files mapped to the W drive
\\172.20.100.63\f$\v4\maintain\archives\
We move the Pyxis Archive Files to a network
folder. “S:\Pharmacy\PyxisNT\”
At 5am each day, the files are moved from one
location to another. This is accomplished using
Window’s Task Scheduler and a batch
file. “CopyPyxisArchiveData2NetworkFolder.bat”
The batch file command is made up of one line of
text. “move w:\*.* s:\Pharmacy\PyxisNT\”
PyxisMedTransactions Data Import

Task:
Automate the moving of data from daily files
(through yesterday) into database tables.

Wrinkles:



"Daily" files are moved by a human to their folder.
Which means yesterday's file may not have been
moved into the folder yet today.
Perhaps more than just yesterday’s file may not have
been moved into the folder (vacations happen).
This automated daily job may not have run yesterday
for some reason.
PyxisMedTransactions Data Import

Solution Algorithm:


Givens:
 File has name of mmddyy.PYX and will always be in
the same folder.
General Solution:
 Generate all filenames holding data – starting with
the last date this automated job ran through
yesterday.
 Pull data from all those files and insert into a table
in the DR.
PyxisMedTransactions Data Import
Algorithm Schematic
Start
Run DTS to pull
Filename from
“Found” table,
Move data from
“Found” File to
“Today’s-run” table
Generate
today’s
filename
Done
No
Any
Missing
Files
Found?
Update
“Today’s Run”
table
Yes
Move Missing
Filename to
“Found” table
Copy “Today’s-Run”
Table to Final
Destination table
PyxisMedTransactions Data Import

Specific Algorithm:

1. Create a Stored Procedure that will generate
all possible filenames from the last date this
automatic job was run through yesterday to
be sought in the folder and insert the names
into a "Missing" table in the database.



To do the above, we pull the “Last Date" that the
"Missing" filenames were generated from the Final
Destination table.
Start date for generating the "Missing" filenames
is the last date they were generated + 1 day.
If the date for the filename we just generated is
not yesterday, insert the filename into the
“Missing” table and continue generating the next
filename by adding 1 day to that last filename.
PyxisMedTransactions Data Import

Specific Algorithm (continued)

2. Create a Stored Procedure to see if any file in the
"Missing" table is now in the folder, if so then move the
filename from the “Missing” table to the “Found” table
(remove the filename from the “Missing” table).
PyxisMedTransactions Data Import

Specific Algorithm (continued)

3. Create a DTS that walks down the “Found” table to
import the data from the filenames obtained from the
“Found” table until no more filenames are left in the
“Found” table.






DTS pulls the top filename from the "Found" table and
stores in a global variable.
If no filename is found (“Found” table is empty) then
skip move data step otherwise set DataSource to be
the global variable.
Move Data from DataSource to “Today’s-run” table.
Delete the top filename from the "Found" table.
If “Found” table is not empty loop to first step of DTS.
"Found" table is empty – DTS returns success result.
PyxisMedTransactions Data Import
The DTS workhorse…
Dynamically set global variable
filename from top of “Found” table
for use by Connection1.
If “Found” table is empty we will
skip Transform Data Task. Else
reset DataSource for Connection1
to be global variable filename.
Move data from the Connection1
DataSource file to “Today’s-run”
table in the database.
Update “Found” table. If
more filenames in “Found”
loop back to set global
variable else report
successful completion.
PyxisMedTransactions Import Data

Specific Algorithm (continued)


4. Create a stored procedure to update a couple of
columns in the "Today's-run” table (per customer
request).
5. Create a stored procedure to copy all "Today's-run"
table data to final destination table if it exists.
Whether there is data in “Today’s-run” table or not,
update “Last Date” field in final destination table to
be today’s date.
PyxisMedTransactions Import Data

Finish automating the task by
creating a job with these 5 steps
and schedule it for nightly run.
User Names…..
What did we do?
Created a table that maps all Meditech UserID’S
and Pyxis UserID’S that are not the same

Issues


Table has to be maintained by
pharmacy and IT.
Current Maintenance

Communication between PHA on Pyxis
user id changes and Monitor Meditech
user name changes.
General Data Decisions




Use statistically significant data to
determine outliers
Display Meditech data along with
Pyxis transaction data.
All transactions performed in Pyxis
for a particular medication and
location
Summary of transactions per
patient and medication.
Reports Developed
• Proactive Diversion Report, main report
Parameters passed (data range, location, medication)
• Patient Details by Nurse - drill down report
(1)Parameters passed (date range, nurse, medication)
• Patient Details by Medication - drill down report
(1a)Parameters passed (account number, medication)
• Pyxis Transactions by User - drill down report
(2)Parameters passed (date range, nurse, medication)
• All Medications for Nurse and Selected Date Range - drill
down report
(3)Parameters passed (date range, nurse)
Report on Report Manager View
Dropdown selection for a
single medication or all
Dropdown for all locations
or a specific department
The Statistics show the number of days a nurse withdrew a medication for a
location, the number of doses, the number of patients, the doses per day,
and the standard deviation of the doses per day along with the total
withdrawals from Pyxis and the total administrations in Meditech
Exported View of Diversion Report
1. Drill down on
patient count
3. Drill
down on
user
name
2. Drill
down on
count of
Days
(1) Drill down on Patients from Main Report
1-a. Drill down to patient account for all user
transactions for this medication
( 1 a) Drill down on patient from Patient Details by Nurse
Displays all medication
withdrawals, cancellations,
wastes, and administrations
(2) Drill down on Days from Diversion Report
Displays all transactions
out of Pyxis for selected
Date Range and
Medication for the user
(3) Drill down on user name from Diversion Report
Calculates stats for all controlled
medications for the user and
selected date range.
Other reports options not shown
•
•
•
Automated time period (last 30
days)
Email to Nurse Managers weekly
Set of a reports to monitor high
dollar medications
Thank you for attending!
Acmeware Inc.
Jamie McDonald
781-329-4300 ext. 203
[email protected]