D3A2 Data Preparation and Scrubbing Basics

1

• D3A2 Data Preparation and Scrubbing Basics

November, 2008
2
Presenter

• Eric Bell
• D3A2 Project Manager
• Northwest Ohio Computer Association
• bell_at_nwoca.org
• www.d3a2.org

3
Purpose

• Provide an introduction to school districts and
  ITC staff to the D3A2 implementation process.
• Support the work of ITCs that are supporting
  their districts need for D3A2 implementation
  support.

4
Learning Objectives

• Attendees will be able to
• Understand the roles districts and ITCs play in
  the data preparation process
• Describe what data is housed in the data
  warehouse, how it is prepared, and how it makes
  its way to the D3A2 team for upload
• Know what data to scrub and why
• Understand how item analysis and student scores
  data is coalesced in the Data Tools application
  to form a composite view of performance

5
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

6
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

7
Data Driven Decisionsfor Academic Achievement
8
Goals of D3A2

• Improve teachers comfort and

proficiency with using data
9
D3A2 Data Tool
District View
Teacher View
Standard Performance View
Student Proficiency View
10
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

11
Implementation

• Implementation involves the interplay of
  various roles, responsibilities, and tasks that
  are necessary to
• Request D3A2 implementation support
• Prepare and upload district data into the Data
  Warehouse
• Prepare educators to use the Data Tool
  application to inform their educational or
  administrative practices
• www.d3a2.org/implementation

12
ITC Participation

• The implementation effort is a shared effort
  between Information Technology Centers (ITCs) and
  their districts

13
Data Implementation ITC Roles

• ITCs help districts by
• Responding to implementation requests and
  coordinating district efforts
• Preparing districts to use the Data Scrubber
• Coordinating data transfers to the D3A2
  Operations Group
• Creating user accounts

14
Data Implementation District Responsibilities

• Districts responsibilities vary by region but
  generally involve
• Requesting service
• Submitting Data Use Authorization Document
• Participating in planning activities with the ITC
• Locating and preparing testing vendor data for
  upload (scrubbing)
• Implementing a formal data team process which
  includes the use of the D3A2 Data Tool

15
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

16
Whats in the Data Warehouse?

• The D3A2 Data Warehouse contains

Past Present Student Rostering Data
OAT/OGT Test Data
17
District Demographics Data

• Student demographics and course assignments is
  provided in the form of

• ITCs work with districts to generate student
  course rostering data and transfer that data to
  the D3A2 team for upload to the Data Warehouse

18
d3a2.com Extract Procedure

• Extracting roster data from EMIS involves running
  the d3a2.com procedure on the alpha server
• Seven extract files are created for each
  reporting period

BUILD_INFO_xxxxx.csv
STU_ENROLL_xxxxx.csv
DIST_INFO_xxxxx.csv
STU_INFO_xxxxx.csv
ID_SSID_xxxxx.csv
TU_TEACH_COURSE_xxxxx.csv
STAFF_INFO_xxxxx.csv
19
Transferring Data to the Data Warehouse

• ITC staff using Oracle Collaboration Suite to
  transfer these files to the D3A2 team
• EMIS or student information system extracts are
  the authoritative source of information about
  students and teachers
• Extract files are run through a rigorous
  validation process during upload
• Student extract files may be refreshed throughout
  the year to ensure accuracy within the data
  warehouse

20
OAT/OGT Test Data

• The Data Warehouse stores two types of OAT and
  OGT test data

Student Scores (Regular Returns)
Item Analysis

• These files are provided to districts and ITCs by
  the testing vendor via the Success website or on
  disk

21
Availability of Test Vendor Data Files
22
Downloading Files From Success

• Access the Success website http//portal.success-
  ode-state-oh-us.info/

Click on the Educators Workroom to access login
in to the data access area
23
Educators Workroom
Log in using the Test coordinator Username and
Password

• NOTES
• Test Coordinator accounts can view and download
  all data
• Principal accounts can view and download item
  analysis data only

24
Accessing the Downloads Area
Drill down to the desired test administration
period, or
Select from the most recent test administration
period
25
Data Download Area

• Select the test administration

• Choose Regular or Item Analysis

• Click on fixed Width (EMIS)

• NOTES
• When prompted to download, give the files a
  descriptive name and save to a secure location

26
Unacceptable Formats

• NOTES
• D3A2 is not compatible with the following files
• Alternate Assessment
• English Language Assessments
• Files in .CSV Format
• Impartial Returns Files (Late Returns)
• Test Vendor Files can become corrupt and
  therefore unusable when opened and then saved
  using spreadsheet and test editing applications.

27
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

28
What to Scrub

• OAT and OGT Students Results Files

student results files contain detailed data about
each student including their scores, proficiency
levels, and classroom assignments

• OAT and OGT Item Analysis Files

item analysis files contain information about how
each test item was answered by each class,
school, district and compared against the State
aggregate.

• From 04-05, 05-06, 06-07, 07-08, and current
  school year

29
Issues - Why Scrub?

• Scrubbing involves repairing common
  inconsistencies found in the test data received
  from the various test administrations
• The arrangement and format of the data in the
  results files varies from one test administration
  to another
• Incorrect or missing teacher and student
  identification numbers

• Teachers want to see students who were actually
  assigned to them.
• Administrators want to see how cohorts of
  students performed on the test.

Districts that have been analyzing their test
results data have already been cleaning their
data by hand for many years.
30
Teacher Student Identifiers

• Data in the test results files is identified by
  district-issued Identification numbers or the
  spelling of teachers names.

Caution district-issued IDs, including the
spelling of teachers names can change over time

• Data in the warehouse is keyed upon state-issued
  student and teacher identification numbers

Solution state-issued identifiers ensure the
qualities of uniqueness and immutability
31
Who Scrubs?

• Anyone who
• has sufficient access to district student
  information systems and
• is proficient using data systems.

EMIS Coordinator
Tech Coordinator
Curriculum Director
Principal
32
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

33
Scrubber Login Screen
the scrubber is a web-based application
the scrubber URL is http//scrubber.d3a2.org
a login account is required to access and use the
scrubber
34
Vendor Data Tab
Upload Raw OAT OGT Files
35
Process Results Tab
Track Scrubbing Progress
36
Lookup Data Tab
Resolve Incorrect Identifiers
37
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up

38
User Accounts

• Districts work with their ITC to have accounts
  created in the Ohio K-12 Network IDM system.
• Levels of Security
• district
• school
• teacher
• Multiple accounts can be created using the bulk
  upload template

39
D3A2 Implementation Page
Additional Information
40
Meeting Outline

• D3A2 Data Tool Application
• Implementation
• Whats in the Data Warehouse?
• Scrubber basics what to scrub? why scrub? who
  scrubs?
• Brief introduction to the scrubber
• User Accounts
• Wrap-up