Michigans Judicial Data Warehouse JDW Why Build a

Michigan’s Judicial Data Warehouse (JDW)

Why Build a Judicial Data Warehouse? Currently, Michigan’s 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. Michigan’s Judicial Data Warehouse 2

State Court Administrative Office Mark Dobek/Margo Runkle Judicial Information Systems Who is building the Judicial Data Warehouse? State of Michigan Department of Information Technology Bull Information Systems Neal Zuzga Project Manager Michigan’s Judicial Data Warehouse 3

How is the Judicial Data Warehouse being built? Bull Services utilizes the following key processes to build the Judicial Data Warehouse: n n n 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, n Hummingbird BI-Query (reporting tool). Sensitive and non public data is restricted to proper personnel. n Michigan’s Judicial Data Warehouse 4

Standardization Process n Standard Values have been established for: n n n Standardization and cleansing of Addresses n n 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) Postal Soft is used to standardized addresses. Unique Addresses are stored and address history is maintained. Translation Tables n Each source system has a conversion table listing their source codes along with the converted JDW standard codes. Michigan’s Judicial Data Warehouse 5

Unique Person ID (UPI) n ID assigned to uniquely identify an individual. n ID is assigned based on pre-defined matching criteria. n Currently, matching criteria is based on: n n n Driver’s 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. Michigan’s Judicial Data Warehouse 6

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 Michigan’s Judicial Data Warehouse 7

n JDW Implementation Map February 2007 KEWEENAW HOUGHTON ONTONAGON BARAGA Gogebic LUCE MARQUETTE CHIPPEWA ALGER IRON SCHOOLCRAFT DICKINSON MACKINAC DELTA EMMET MENOMINEE CHEBOYGAN PRESQUE ISLE CHARLEVOIX ANTRIM OTSEGO ALPENA MONTMORENCY LEELANAU OSCODA BENZIE Implemented KALKASKA GRAND TRAVERSE ALCONA CRAWFORD IOSCO MANISTEE Partially Implemented. Refer to Regional Implementation Map. Partially Implemented WEXFORD MISSAUKEE ROSCOMMON OGEMAW GLADWIN MASON LAKE OSCEOLA ARENAC CLARE HURON OCEANA NEWAYGO MECOSTA ISABELLA MIDLAND BAY TUSCOLA SANILAC SAGINAW MONTCALM GRATIOT MUSKEGON KENT LAPEER GENESEE On Hold (Application Decision Pending) OTTAWA IONIA ST. CLAIR CLINTON SHIAWASSEE MACOMB OAKLAND Partially Implemented and Application Decision Pending ALLEGAN VAN BUREN BARRY KALAMAZOO EATON CALHOUN INGHAM LIVINGSTON JACKSON WAYNE WASHTENAW BERRIEN CASS ST. JOSEPH BRANCH HILLSDALE LENAWEE MONROE 8

9

Data Sharing/Functionality Highlights n DLN Matching with MDOS – compares the court’s 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 BIQuery and the Name Search Application. n 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. Michigan’s Judicial Data Warehouse 10

New Functionality Under Development n 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. n Updating Data in the JDW – This is currently a monthly process. Efforts are underway to change this to a more frequent basis. n 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. Michigan’s Judicial Data Warehouse 11

Conclusion There are several benefits to implementing a Judicial Data Warehouse, namely: n One statewide court database n User-friendly applications (web-based Name Search application and the BI-Query reporting tool) n On Demand, Adhoc Reporting capabilities (BI Query) – allowing field staff to retrieve information within minutes. n The JDW brings the courts into the 21 st 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. Michigan’s Judicial Data Warehouse 12

Judicial Data Warehouse Application Demo Michigan’s Judicial Data Warehouse 13

JDW Web Application Main Menu Michigan’s Judicial Data Warehouse 14

JDW Web Application Search Options Michigan’s Judicial Data Warehouse 15

JDW Web Application Search Results Screen Michigan’s Judicial Data Warehouse 16

JDW Web Application Case Detail Screen Michigan’s Judicial Data Warehouse 17

JDW Web Application Case Detail Screen (continued) Michigan’s Judicial Data Warehouse 18

JDW BI Query Application Main Menu Michigan’s Judicial Data Warehouse 19

JDW BI Query Application Predefined Reports Michigan’s Judicial Data Warehouse 20

Predefined Report Example State Offense By Police Agency Report Michigan’s Judicial Data Warehouse 21

JDW BI Query Road Map Adhoc Queries Michigan’s Judicial Data Warehouse 22

JDW BI Query Links of Interest Michigan’s Judicial Data Warehouse 23

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 Michigan’s Judicial Data Warehouse 24

Case Type Count PJ 41 PP 1713 PO 32 PH 440 Michigan’s Judicial Data Warehouse 25

Michigan’s Judicial Data Warehouse 26

Example #2 Show me everyone with an Outstanding Warrant in Lake County. I want to see the person’s name, address, date of birth, and DLN. Michigan’s Judicial Data Warehouse 27

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 D 03376802 8224 W AVALON DR PHOENIX AZ AIRHART RANDY 19610206 A 663730051098 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 A 436603676628 2692 W SKEELS RD MONTAGUE MI ALDWORTH MICHAEL 13289 N WOODBRIDGE AVE BITELY MI ALLISON DAVID 19880115 A 425135755042 5440 GRAND RIVER DR NE GRAND RAPIDS MI ALLMAN GARY 19460409 A 455271441278 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 A 624744738567 41 LONDON LN MOUNT MORRIS MI ATLOGICH ELIZABETH 19560511 A 342210585359 2902 W. 32 ND ST BALDWIN MI ATWELL STEVEN 19730426 A 340777288320 93 HYACINTH ST SW GRAND RAPIDS MI AUSTIN BILLY BALDWIN MI AUSTIN BILLY 3894 CAMELOT DR SE GRAND RAPIDS MI Michigan’s Judicial Data Warehouse 28

Example #3 Show me the number of open cases by Judge in Midland County. Michigan’s Judicial Data Warehouse 29

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 Michigan’s Judicial Data Warehouse 30