Michigan

1
Michigans Judicial Data Warehouse (JDW)
2
Why Build a Judicial Data Warehouse?

• Currently, Michigans 241 trial court
  locations are supported by 29 different case
  management systems deployed on 150 different
  servers. A statewide court database does not
  exist and this creates a void in the knowledge
  about individuals and cases in the Michigan
  Court system.
• Centralized Name Index
• Provides Court Personnel with the ability to find
  a person known to the Michigan Judicial System
  for all cases in which he/she is involved.
• Statewide Database
• Provides the SCAO and Court Personnel with the
  ability to build queries for statistical analysis
  or summary information from trial courts with
  different case management systems.
• Standardization
• Provides the SCAO and Court Personnel the ability
  to execute those queries, without having to
  understand the data specific to a county or
  court system.

3
Who is building the Judicial Data Warehouse?

• State Court Administrative Office
• Mark Dobek/Margo Runkle
• Judicial Information Systems
• State of Michigan
• Department of Information Technology
• Bull Information Systems
• Neal Zuzga
• Project Manager

4
How is the Judicial Data Warehouse being built?

• Bull Services utilizes the following key
  processes to build the Judicial Data Warehouse
• Extract, Load, and Transformation (ELT) Process
• Update Process
• Auto Balance Process
• Standardization Process
• Address Cleansing and Standardization
• Unique Person Identification Process
• Field Staff can access the Judicial Data
  Warehouse via
• Web-based Name Search Application,
• Hummingbird BI-Query (reporting tool).
• Sensitive and non public data is restricted to
  proper personnel.

5
Standardization Process

• Standard Values have been established for
• Alternate Name Type (examples AKA, DBA, MIN)
• Attorney Type (A, I, R, U, W)
• Case Security (P, N)
• Case Status (C, D, O, P, U)
• Disposition Code (examples AMD, DIS, GTY, JGE,
  SET)
• Gender (F, M, U)
• Party Type (examples BUS, D, FOC, GDN, JUV, P)
• Offense Code Type (M, L, S)
• Offense Original or Amended Indicator (O, A)
• Offense Modifier (A, C, L, S)
• Race (A, B, H, I, M, W, U)
• Standardization and cleansing of Addresses
• Postal Soft is used to standardized addresses.
  Unique Addresses are stored and address history
  is maintained.
• Translation Tables
• Each source system has a conversion table listing
  their source codes along with the converted JDW
  standard codes.

6
Unique Person ID (UPI)

• ID assigned to uniquely identify an individual.
• ID is assigned based on pre-defined matching
  criteria.
• Currently, matching criteria is based on
• Drivers License Number
• Social Security Number
• State Identification Number
• Name/Date of Birth/Address
• Conservative approach used to match and assign
  the same UPI number to an individual.

7
When is the Judicial Data Warehouse being
implemented?

• When implemented Statewide (EST 12/2008)
• 83 Counties
• 241 Courts
• 29 Software Providers
• 100 plus software applications
• Currently Implemented (2/2007)
• 80 Counties
• 181 Courts
• 22.8 million cases in the warehouse now

8
Roscommon
Roscommon
JDW Implementation Map February 2007
Implemented
Partially Implemented. Refer to Regional
Implementation Map.
Partially Implemented
On Hold (Application Decision Pending)
Partially Implemented and Application Decision
Pending
9
(No Transcript)
10
Data Sharing/Functionality Highlights

• DLN Matching with MDOS compares the courts DLN
  with the DLN from MDOS. This results in more
  accurate matches when assigning the UPI number.
  The MDOS DLN, Address, Gender, and DOB is
  displayed in BI-Query and the Name Search
  Application.
• Death Record Matching with MDCH compares Name
  and DOB with individuals with court records to
  the MDCH Death Record Database, allowing court
  personnel to cease collection efforts or cancel
  outstanding warrants on deceased individuals.
  Report available in BI Query only.

11
New Functionality Under Development

• Sweep Reports Matching data with Department of
  Corrections to provide information on inmates who
  owe the courts fees. A predefined query will be
  developed so users can easily generate reports on
  these individuals and initiate the process to
  collect those fees.
• Updating Data in the JDW This is currently a
  monthly process. Efforts are underway to change
  this to a more frequent basis.
• New Data Efforts are underway to add new data
  to the JDW. This includes Parole and Probation
  information from the Department of Corrections,
  and Sentencing and Financial data from the courts.

12
Conclusion

• There are several benefits to implementing a
  Judicial Data Warehouse, namely
• One statewide court database
• User-friendly applications (web-based Name Search
  application and the BI-Query reporting tool)
• On Demand, Adhoc Reporting capabilities (BI
  Query) allowing field staff to retrieve
  information within minutes.
• The JDW brings the courts into the 21st century
  with information technology resulting in better
  decision making, quicker turnaround time, and the
  ability to easily exchange information with other
  agencies to serve and protect Michigan citizens.

13
Judicial Data WarehouseApplication Demo
14
JDW Web Application Main Menu
15
JDW Web ApplicationSearch Options
16
JDW Web Application Search Results Screen
17
JDW Web Application Case Detail Screen
18
JDW Web ApplicationCase Detail Screen (continued)
19
JDW BI Query ApplicationMain Menu
20
JDW BI Query Application Predefined Reports
21
Predefined Report Example State Offense By
Police Agency Report
22
JDW BI Query Road Map Adhoc Queries
23
JDW BI Query Links of Interest
24
Example Adhoc Query

• Show me the total number of Personal Protection
  Orders filed in Genesee County in 2005.
• PPO Case Types
• PH - Stalking
• PJ - Juvenile
• PP - Domestic
• PO Protective Orders

25
Case Type Count
PJ 41
PP 1713
PO 32
PH 440
26
(No Transcript)
27
Example 2

• Show me everyone with an Outstanding Warrant
  in Lake County. I want to see the persons name,
  address, date of birth, and DLN.

28
Lake County Outstanding Warrants - total 563
Last Name First Name DOB DLN Address City State
ABRAHAM KIRLEE     RR 1 IDLEWILD MI
ADAMCZAK JOSEPH 19860119   4600 FULLERTON ROAD LUTHER MI
AGUIRRE JOSE 19821107 D03376802 8224 W AVALON DR PHOENIX AZ
AIRHART RANDY 19610206 A663730051098 636 LAKE ST KALAMAZOO MI
ALDWORTH MICHAEL     PO BOX 340 BALDWIN MI
ALDWORTH STEPHANIE     RR 1 IDLEWILD MI
ALDWORTH MICHAEL     2692 W SKEELS RD MONTAGUE MI
ALDWORTH MICHAEL 19780811 A436603676628 2692 W SKEELS RD MONTAGUE MI
ALDWORTH MICHAEL     13289 N WOODBRIDGE AVE BITELY MI
ALLISON DAVID 19880115 A425135755042 5440 GRAND RIVER DR NE GRAND RAPIDS MI
ALLMAN GARY 19460409 A455271441278 25038 CURRIER ST DEARBORN HEIGHTS MI
ANDERSON NADINE     15330 ASBURY PARK DETROIT MI
ANDREWS WANDA 19550713 A 536 866 487 70 FLORENCE ST DETROIT MI
ARNOLD RONALD 19650718 A624744738567 41 LONDON LN MOUNT MORRIS MI
ATLOGICH ELIZABETH 19560511 A342210585359 2902 W. 32ND ST BALDWIN MI
ATWELL STEVEN 19730426 A340777288320 93 HYACINTH ST SW GRAND RAPIDS MI
AUSTIN BILLY       BALDWIN MI
AUSTIN BILLY     3894 CAMELOT DR SE GRAND RAPIDS MI
29
Example 3

• Show me the number of open cases by Judge in
  Midland County.

30
MIDLAND COUNTY OPEN CASES BY JUDGE
Judge's BAR Number Open Cases
32468 1175
0 666
39794 630
38701 521
11990 504
31391 268
43096 202
22405 82
21712 72
43826 52
2751 52
36209 14