Transcript Data Warehouse

Data, Data – Where is
my Data?! Utilizing a
Data Warehouse
Presenters:
Jeff Cohen, Vermont IV-D Director
Jeanette Savoy, Central Registry
Supervisor, CDHS, CSE
Keith Horton, Georgia IV-D Director
May 23, 2011, 1:30 – 3:00 p.m.
1
Decision Support
Systems:
Using Data to Improve
Child Support Program
Performance
Jeff Cohen
Vermont Office of Child Support
2
Agenda
Decision Support for Performance
–Strategic Value
–Decision Support Components
–Reporting
–Dashboards
–Data Mining
–Predictive Analytics
–OCSE model data warehouse
3
Performance Drivers:
• Incentive Formula
• Self Assessment
• Strategic Plans
• State Legislature
• State Performance Audits
• Public Expectations
4
Building Blocks for Excellence
Business
Results
Customer
Focus
Strategic
Plan
Human
Resources
Process
Mgm’t
Leadership
Information and Analysis
5
Objective: Management by fact
• ‘What gets measured gets done’
6
Appropriate Data Representation
by Function
7
DSS Architecture
Decision Support System
Data Warehouse
Data
Sources
CS Case
Welfare
ETL
Extract
Transform
Load
Data
Storage
Data Marts
Source Data
Extraction
End User App
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 1
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Fact Table
PK
FK_1
FK_2
FK_3
...
FK_n
Measure 1
Measure 2
...
Measure n
Dimension 2
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 4
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 2
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 3
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 1
Metadata
Summary
Detail
Dimension 3
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 2
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Fact Table
PK
FK_1
FK_2
FK_3
...
FK_n
Measure 1
Measure 2
...
Measure n
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 1
Dimension 5
*
Data Staging
Other
Data
Sources …
End User
Access
Dimension 3
Data
Warehouse
DOC
Presentation/
OLAP
Services
Fact Table
PK
FK_1
FK_2
FK_3
...
FK_n
Measure 1
Measure 2
...
Measure n
Dimension 4
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 5
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Report Writers
Dimension 4
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Dimension 5
PK_1
Natural Key
Attribute_1
Attribute_2
...
Attribute_n
Ad Hoc Query
Data Mining
Forecasting &
Projections
8
Example Star Schema
Time
Dimension
Demographic
Dimension
Income
Gender
Location
Education
Case Status
Dimension
IV-D Type
Status Type
Facts
Fiscal Year
Quarter
Month
Day
Current $
Arrears $
# of Cases
Ownership
Dimension
Region
County
Worker
Case #
9
Some DSS Uses and Demos
•
•
•
•
•
•
Dashboard
Reports
Drilling
Data Mining
Predictive analytics
Linking
10
Demos Here
11
Screenshots
12
13
14
15
16
Statewide Strategic_Plan_Parentage_Establishment_for_Region_and_Worker_1195.pdf
17
18
19
20
21
22
Height
Data Mining – The
Problem
The Objective
Weight
23
100
Random
80
60
40
20
95
80
65
50
35
20
0
5
Cumulative % Hits
Lift: Random List vs Modelranked list
% of cases reviewed
5% of random list have 5% of targets…
24
100
Lift at 5% of list
reviewed
Random
Model
80
= 21% / 5%
In other words,
60
4.2 times
better
than random
40
20
95
80
65
50
35
20
0
5
Cumulative % Hits
Lift: Random List vs Modelranked list
% of cases reviewed
5% of random list have 5% of targets…
25
but 5% of model ranked list have 21% of targets.
Colorado’s
Experience:
Business Intelligence Grant
Performance Dashboard
Presented by:
Jeanette Savoy,
Supervisor of the Colorado Central
Registry
26
Purpose
• Synergistic relationship between
compliance and performance
• Initiate system caseload analysis
capability using business intelligence tools
• Replace monthly exception-based reports
• Improve individual and county caseload
performance
27
Emphasis
• Accurate representation of information
• Clear understanding by CSE workers
• Ability to drill down to case level to specify
actions needed
28
Project Goal
The CSe-Tools Performance Dashboard
will give CSE staff the tools to view
caseload health and identify actions to
help improve caseload management and
program effectiveness, as measured by
the four key performance indicators.
29
Dashboard Design Principles
• Keep it simple
• Provide information quickly and clearly
• Minimize distractions and unnecessary
embellishments that can create confusion
• Maintain consistency with the design to
ensure accurate interpretation
30
Project Development
• Business Intelligence Workgroup
– County, State and Federal representation
– Review proposed solutions
– Provide input on specific functionality
– Elicit support, participation and cooperation
• Project Development Team
– Small group of programmers
– Development of both data warehouse and
performance dashboard
31
Data Warehouse (Closet)
• Provides appropriate information for the
dashboard without overloading the main
production database
• “Warehouse” cost prohibitive
• Initial “closet” to be expanded in
incremental steps
32
CSe-Tools
• Browser-based application toolkit
• Front-end application for statewide system
• Interfaces with statewide system using
web services and file transfers
• Search and reporting capabilities
• Drill down capabilities to case and
financial detail information from the
33
statewide system
Performance Dashboard
• Prominently displayed in the middle of the
CSe-Tools homepage
• Initial and immediate portrayal of caseload
health on a single screen
• Visual display of prioritized information
• Ability to drill down to a list of cases that
require action
34
Performance Dashboard (cont.)
• Specific to the following CSE Key
Performance Indicators (KPI)
– Paternity Establishment Percentage
– Percent of Cases with Support Orders
– Percent of Current Support Paid
– Percent of Arrears Cases with a Payment
KPI = quantifiable measurement that reflects
an organization’s critical success factors
35
Dashboard Format
36
Work Lists
• Based on logic of KPIs
• Redesigned after implementation based
on feedback from grant participants
• Use of “tags” (colored dots) to identify a
set of criteria indicating the type of action
that may be needed
37
38
39
“Tags”
• Green
NCP employer is verified
Wage withholding is active
• Blue
NCP employer is verified
Wage withholding is inactive
• Black
NCP in Department of Corrections
• Peach
NCP employer is not verified
Reciprocal case is not initiating
NCP address is verified
40
KPI: Percent of Current Support
Paid
• The gold area shows the
entire measure amount (i.e.
-Total current support owed
for worker’s YTD).
• The vertical bar shows the
goal. (i.e. - MSO goal for
YTD caseload).
• The parallel bar shows
progress toward the goal
(i.e. - Total MSO paid for YTD
caseload).
Hover over each one to see
numerical amounts.
41
KPI: Percent of Current Support
Paid
42
Evaluation
• Data analysis for
– Percent of Current Support Paid
– Percent of Arrears Cases with a Payment
• Post-implementation surveys and
interviews
43
Evaluation (cont.)
• Statistical findings invalid
– Low number of demonstration participants
– Short time period (17 months) for grant
– Inability to develop assumptions and findings
representative of the State
– Inability to isolate impact of variables
44
Evaluation (cont.)
• Post implementation surveys and
interviews provided wealth of information
– Lessons learned will ensure successful rollout
of Performance Dashboard in Colorado
– Valuable information for other states
interested in implementing a performance
dashboard
45
Lessons Learned
• Training: Key to success
– Two-fold
• Functionality of the dashboard, especially if new
technology is involved
• How to use the dashboard to manage a caseload
• Define clear expectations
– Replacement vs. supplemental tool
– Resistance to change
46
Lessons Learned (cont.)
• Value – Caseload size
– Less value for workers with smaller caseloads
or from smaller counties
– Ten large counties in Colorado = 80% of
State’s caseload = very valuable to Colorado
47
Lessons Learned (cont.)
•
•
•
•
•
Support must come from the TOP down
Real-time interface is critical
More information is not always better
Ability to create personalized work lists
Identify cases reported on multiple work
lists
48
Lessons Learned (cont.)
• Functionality to record notes on work lists
minimizes duplicated research and allows
continuous analysis at a case level
• Matrix of appropriate actions for each work
list / tag is helpful for less experienced
workers
49
Finale
• Final grant report submitted September
30, 2010
• Statewide rollout to commence July 2011
50
Welcome to Georgia’s
Division of Child
Support Data
Warehouse
Keith Horton
GA, IV-D Director
51
Georgia Division of Child Support
Why a Data Warehouse?
• ARRA provided an opportunity to not only sustain a workforce but to
improve performance
• Georgia became data driven with the “Rapid Process Improvement”
process and “The Four Covey Disciplines of Execution”
• Established several initiatives that relied heavily on data to validate
worthiness
• Special Collections Unit
• Prison Re-entry services
• Problem Solving Court
• A more accurate and efficient way of providing reports to include
federal reports
• An accountability tool and an empowerment tool for field staff
52
Georgia Division of Child Support
Executive Dashboard - Overview
• Key Program Performance indicators signifying the health of the business are
available enterprise wide in near real time
• A combination of point in time and historical metrics communicate the Federal
Performance measures by which the business is evaluated
53
• The percentage of each ratio helps determine compliance with state goals and ability
to gain additional incentive funding
Georgia Division of Child Support
Executive Dashboard-Trending Graphs
• Once data is loaded, trends for different metrics can be tracked over the year and
year over year
• This leads to improved Program Management and Legislation Reporting
54
Georgia Division of Child Support
Executive Dashboard-Statewide Details
• As a compliment to the Overview page, the Statewide details report allows executives
to compare the performance of regions and offices to their respective goals
•Users can drill down to view the details of each region by clicking on the desired
regions
55
Georgia Division of Child Support
Executive Dashboard- Field Activities
•The Field Activities report has been created to describe the types and amount of specific
actions taken by individual workers within DCSS
•This report will provide DCSS executives with the ability to analyze the actions taken by
their top-performing workers and eventually use this information to tailor their
56
operational training program
Georgia Division of Child Support
Executive Federal Dashboard – 157 Report
• Federally mandated 157 reports: Case, Participant and Financial
• Numbers are used to determine percentage of incentive money which will be allocated
to the state
• Case agents can tailor reports using page prompts and the drill down allows agents
57to
shift their focus from their entire case load to only specific cases needing action
Georgia Division of Child Support
Federal Dashboard – 34A Report
• Federally mandated 34A reports: Distributions, Collections, Undistributed Collections
• Case agents can tailor reports using page prompts
• Drill down allows agents to shift their focus from their entire case load to only 58
specific cases needing action
Georgia Division of Child Support
Federal Dashboard – 34A UDC Page
• Pie Charts are used to represent percentage allocations of Financial Number
• Users can Drill down on Amounts to view break down by Region
59
Georgia Division of Child Support
Operational Dashboard – Enforcement
• Operational Dashboards are used by agents and Regional Managers to monitor
activity on a real time basis by region or worker
• The enforcement report presents case agents with metrics detailing the number of
cases grouped by the % paid of the total obligation
• The count can be drilled on to display the specific action that should be manually
taken against the NCP
60
Georgia Division of Child Support
Federal Dashboard – Enforcement
•
•
Workers can drill down on the Enforcement reports to view detailed breakdown
of information based on type of Action Needed on a Case
The actions are classified by the business and fed into the Data Warehouse on
a weekly basis.
61
Georgia Division of Child Support
Operational Dashboard– Fatherhood Page
• The entire organization is provided with insight into the Fatherhood program
• Reporting on metrics such as enrollments, completions, and effectiveness, a 360
62
degree perspective on the program is realized
Georgia Division of Child Support
Operational Dashboard – Training
• The Training Report is an Operational Report accessible only to Regional Managers
• The information gleaned from this report can help Managers access the effectiveness
of Training and validate the Workers performance in various courses
• Information of this nature can be used to encourage workers to participate in
courses to improve their effectiveness is service children
63