Solutions

Off-Line Decision Support

Business Problem

Inability to perform timely and flexible Criminal History analysis

Unacceptably long response times

Searches against the California Department of Justice (CalDOJ) Criminal History legacy system often required up to a few days or even weeks.

Inflexible architecture

When new search queries were needed, COBOL programs had to be developed or modified, resulting in additional response delays of up to several weeks.

Additionally, CalDOJ was not able to view trends in the data.

Solution

Criminal History Datamart

On-Line Analytical Processing (OLAP)

Designed for live ad hoc data access and analysis, OLAP is synonymous with multi-dimensional views of business data — which is well-suited for Criminal History analysis.

Data Modeling is critical

The key element of OLAP is an accurate and well-planned data model. CloverLeaf spent a lot of time understanding and verifying the business processes that the data supported.

Normalization

The Criminal History database was over 30 years old, was non-relational, and comprised several terabytes.

Extract-Transform-Load (ETL)

After analyzing the data collection methods, data flow methods, and source of data integrity, CloverLeaf developed the ETL specification and exception handling procedures.

Loading was divided into number of subprocesses and steps. Exceptions were captured and provided to CalDOJ for remediation.

Security

Due to the highly sensitive nature of the Criminal History data, securing the data and auditing all activity were critical requirements.

Securing data

CloverLeaf implemented a high encryption level at the "data-at-rest" and "data-in-transit" stages.

Activity auditing

CloverLeaf implemented advanced Oracle features such as Advance Security Options, Virtual Private Database, Audit Vault, and Database Vault to provide in-depth auditing of all activity.

Ad-Hoc Queries

Flexible and reusable

Queries created by internal users can be saved for future use by other in-house staff, and can also be made available to law enforcement agencies.

All the information pertaining to an event can be selected and filtered by any one of the core entity attributes (e.g. Subject, Agency, Location, etc.).

Timely

The datamart is refreshed nightly from its source. This means data is no more than 24 hours old, instead of several days or weeks.

Reporting

Flexible Formats

The Criminal History Datamart is capable of presenting report results in a variety of formats (e.g. pie charts, bar charts, etc.).

Statistics

Users can also launch a mapping application (ArcView) to get pre-defined statistical reports in the following categories:

• Crime Statistics
• Audit Statistics
• Subject Statistics
• Agency Statistics

Users are able to choose between various pre-defined themes, zoom-in/zoom-out, and select counties of interest.

CloverLeaf Added Value

Efficiency

CloverLeaf designed to data model to minimize server disk space and memory usage. This allowed CalDOJ to use existing hardware.

Data Cleansing

As part of the ETL process, data cleansing logic and processes were implemented to ensure data integrity.

Interoperability

CloverLeaf designed the Criminal History Datamart such that it could be linked to other internal enterprise databases.

Benefits

• Provides the ability to create flexible and reusable ad-hoc queries.
• Provides the following Business Intelligence application services:
  • Online Analytical Processing
  • Decision Support
  • Executive Information
• Provides timely responses to even the most complex queries
• Reduces the cost of new development and maintenance
• Moves another CalDOJ system toward its enterprise standard platform