W16 Data Mining Workshop

W16

Data

Mining

Workshop

Brantley Synco, Director, Internal Audit and Compliance, Baptist Health System

Jim Donaldson, Director of Compliance/Privacy and Security Officer, Baptist Health Care Corporation

 Introduction and Overview

 Databases Primer

 Scope and Parameters

 Seven Steps for Data Extraction

 Data Analysis and Application

 Data Sources and Manipulation Tools

 Question Period

 Session will show how to combine data from multiple 

sources into one report

 Presentation is aimed toward compliance 

professionals, NOT IT SPECIALIST

 Techniques can be applied to multiple extractions and 

analyses. 

 At then of this section of the workshop, participants 

will be equipped to import data, join tables, and 

create simple reports. 

Introduction

and

Overview

 What is a database?

“A comprehensive collection of related data organized  for convenient access, generally in a computer. “ – Dictionary.com

“…related data…”

Database

Primer

Poor Database Design

Poor Database Design (Result)

Database

Primer

Name Address City  State Zip

HIRE SSN1 LN1 FN1 ADD1 CITY1 ST1 ZIP1

MAR SSN1 LN2 FN1 ADD2 CITY2 ST1 ZIP2

CHG SSN1 LN2 FN1 ADD3 CITY3 ST1 ZIP3

DIV SSN1 LN1 FN1 ADD4 CITY4 ST1 ZIP4

MAR SSN1 LN3 FN2 ADD5 CITY5 ST1 ZIP5

Data Normalization is the process of dividing  datasets to 

reduce dependency and duplication.

 Deliverable: OIG monitoring reports

 Employees

 Physicians

 Application: MS Access

 Source Files: 

 OIG exclusion database

 Employee File (from HR)

 Doctor Master file (from credentialing)

Scope

and

Parameters

 Must have the confidence you can acquire and use the data

1.

"It's

not

held

in

only

one

table.“

2.

"It's

a

large

and

complicated

database

with

about

142

million

tables.“

3.

"You

will

have

to

get

the

OK

from

the

database

owner

before

I

give

you

that."

Step

1:

Get

your

head

on

straight

4. "Bill

Lumburger asked

for

this

same

data

last

week

for

his

new

TPS

report.

Have

you

ask

him

for

it?

5. "I

don't

know

if

we

can

pull

data

out

of

that

database."

6. "No problem! We can write a SQL script to pull the 

data you need. Then we can set up an ODBC 

connection to transport the data. We can drop the 

data into Oracle, SQL Server, or DB2 format and then 

you can extract it out into XLS, CSV, XML, or HTML. All 

I need is your DBMS license and the network location 

of your server. Just call me back when you have those 

and I'll get right on it."

Step

1:

Get

your

head

on

straight

 What do you want to know? 

"Do

we

have

any

employees

or

doctors

who

are

excluded

from

participation

by

the

OIG"

•

What

do

you

want

the

report

show?



All

parties

and

OIG

status?



Employees

separate

from

doctors?



Only

employees

with

possible

matches?

Step

2:

Start

at

the

End



What

data

do

you

want

to

show?

Step

2:

Start

at

the

End

Provider Data

OIG Data

Employee

Name

OIG

Name

Employee

Address OIG

Address

Employee

DOB

OIG

DOB



How

do

you

want

the

data

grouped,

ordered,

or

sorted?



Source



OIG



Provider



Similarity



By

Name



By

Address

Step

2:

Start

at

the

End

Employee  Name OIG  Name Employee  Address OIG  Address Employee  DOB OIG  DOB Smith Smith 123 Maple 123 Oak 3/3/68 3/3/68

Step

2:

Start

at

the

End

Ordered

by

Similar

Ordered

by

Source

• "

Where

is

the

information

I

need?“

We determined:

 Exclusions are in the OIG exclusion database. http://oig.hhs.gov/exclusions/exclusions_list.asp

 HR has the employee details

 Credentialing has a list of all active physicians. 

•

Step

Three:

Locate

the

data

•

"Who

can

I

call

who

would

know

where

I

can

get

this

information?"

 What format is the data?

 "Who is the database administrator or IT employee 

that supports these databases and applications?“



We

determine

the

following:



OIG

Database

is

available

from

the

OIG

is

a

.dbf

format.



HR

uses

PeopleSoft

built

on

a

DB2

database.



Credentialing

uses

a

commercially

available

system

that

regularly

exports

data

to

the

EMR.

Step

Four:

Reach

out

to

IT

Specialist



Determine

what

elements

are

needed.



Names



Addresses



UPINs



Limit

size

of

extract



Date



Active/Inactive

records



Other

element



Import

Data



Modify

data



Create

Joins



Export

Data

Step

Six:

Turn

Data

into

Information

 Other data types:

Import

Data

 Import OIG Data

 Short cut: Access will open .DBF format

Import

Data

 Data in design view

Import

Data

Open

Query

in

design

view

Open

Query

Wizard

Data

Import

Modify

Data

Datasheet

View

Modify

Data

Modify

Data



Changes

to

Employee

table:



Upper

for

First

Name

(v_First_Name)



Upper

for

Last

Name

(v_Last_Name_



First

5

characters

of

Zip

code

(v_Postal

Create

Joins/Relationships

Create

Joins/Relationships

Export

Data



By

using

linked

tables,

you

can

update

the

tables

and

use

the

same

queries.



Follow

these

steps:



Open

Linked

Table

Manager



Update

all

tables



Execute

the

make

table

queries



Open

the

matched

queries.

Step

Seven:

Don't

Re

‐

Invent

The

Step

Seven:

Don't

Re

‐

Invent

The

Wheel

1. The

actions

we

performed

can

be

used

across

almost

any

data.

2. Practice.

You

can’t

break

anything.

3. Remember…you

CAN

do

this.

Data Sources

to

Consider

and

Monitoring

Tools



You

will

see

screen

shots

from

specific

information

systems

used

at

Baptist

Health

Care

Corporation



We

are

making

no

express

or

implied

recommendation

for

these

systems

or

the

companies

that

offer

them.

 Network Logs

 Time and Attendance Logs

 Instant Messaging

 Internet Logs

 Clinical System Logs

 Emails

 Real Time

 Archived

 Door/Access Controllers

 Video Surveillance

 Electronic Payment System Logs

 Enterprise Data Warehouse

Some

Data Sources

to

Consider

 What expectation of privacy do your employees have?

 What degree of monitoring will your leadership allow?

 Are you a union shop?

 Are you a governmental organization?

 If you find something bad will the C suite act on it?

Before

Using

a

Source,

Consider

Your

E

‐

mail Logs

E

‐

mail

Logs

E

‐

mail

Automatic

Compliance

E

‐

mail

Automatic

Compliance

Monitoring

E

‐

mail

Automatic

Compliance

E

‐

mail

Automatic

Compliance

Monitoring

E

‐

mail

Automatic

Compliance

E

‐

mail

Automatic

Compliance

Monitoring

Instant

Message

Chat

Logs

Internet

Logs

Internet

Logs

Exported

and

Filtered

Network

Logs

 ‐

LDAP

Example



Helps

find

the

needle

in

the

haystack



Collects

data

from

various

audit

sources



Normalizes

the

data



Predefined

or

ad

hoc

reports



Email

alerts



Placing

software

on

a

computer

that

will

record

data

for

analytics

on

all

activity.



Agent

secretly

loaded

on

target

PC



Very

invasive



Records

every

keystroke

and

screen

shot



Allows

playback

much

like

a

DVR



Useful

when

you

have

good

reason

to

believe

you

will

find

evidence

of

adverse

activities

Direct

System

Monitoring

 Brantley Synco, Director, Internal Audit and

 Compliance, Baptist Health System

 205‐715‐5475 / [email protected]

 Jim Donaldson, Director of Compliance/Privacy and

 Security Officer, Baptist Health Care Corporation

 850‐469‐7773 / [email protected]