Treehouse Software Customer Case Study:
County of Sacramento
DPSync: Justice Is Served. Batman and Robin are a lot busier these days, and that makes Captain Mark Dickerson of the Sacramento County Sheriff's Department quite happy.
Captain Dickerson's Batman and Robin don't wear capes and masks. They are the dynamic duo of machines hosting the County's Integrated Justice Information System (IJIS) databases on Microsoft SQL Server 2000. The databases are populated in near-real-time with data propagated from ADABAS in the mainframe-based Criminal Justice Information System (CJIS) using Treehouse Software's DPSync product.
Timely, Accurate Information Improves Public Safety
The IJIS Program is a complex, multi-agency initiative sponsored by the Sacramento County Criminal Justice Cabinet. Program Manager Brian Richards explains that one of the program's key goals is to identify and implement projects that are consistent with the technical recommendations outlined in the IJIS Information Strategy Plan (published in 2002). Each project must meet one or more of the following IJIS Program goals:
improve data sharing among stakeholder organizations;
reduce redundant activities; or
improve the ability to perform ad-hoc or trend analysis reporting.
"The purpose of the Program is to improve public safety through the timely and accurate sharing of critical criminal justice information among the stakeholder organizations in the criminal justice community," says Mr. Richards. "IJIS is helping us deal with a number of important issues, including the difficulty of sharing critical information between agencies, cost avoidance of implementing projects that duplicate capabilities, concerns with the accuracy of redundant data entry, and difficulty in performing effective crime trend analysis activities."
Besides the County's Sheriff's Department, IJIS stakeholder organizations include the County's Conflict Criminal Defenders, Coroner's Office, County Executive's Office, District Attorney's Office, Department of Health and Human Services, Department of Human Assistance, Probation Department, Public Defender's Office, and the Department of Revenue Recovery. Additionally, the Superior Court and Sacramento City Police Department participate in the IJIS Program.
"...it was clear that tRelational/DPS was the leading product for ADABAS-to-RDBMS data transfer."
MIS Director for the Sacramento Superior Courts
Making Information Available
Like many state and local governments, Sacramento County has had a long history with ADABAS, dating back to the late 1970s when development of the Criminal Justice Information System (CJIS) commenced. CJIS is the County's core operational information system that supports multiple stakeholders in the areas of arrests, warrants, criminal history, Court calendaring, and jail management. Mike Gilliam, an ADABAS Database Administrator for the County's Office of Communications and Information Technology (OCIT) notes, "We run a 24x7 shop on behalf of our hundreds of clients. ADABAS's reliability and performance is critical to meeting our service standards."
However, beginning in the 1980s, and increasingly in the mid-to-late 1990s, stakeholders started developing their own departmental systems. They needed to do ad-hoc reporting and analysis, and lacking easy access to ADABAS data from the mainframe, they depended on OCIT programmers to write reports and extracts. This led to a backlog of requests and ongoing difficulty with obtaining sufficiently current data.
During 2000 and 2001, County staff began investigating means to improve access to CJIS data by developing a data warehouse on the Microsoft SQL Server platform. Deploying the data on a standard RDBMS would enable querying and analysis using a variety of inexpensive, off-the-shelf tools. Doug Kauffroath, now MIS Director for the Sacramento Superior Courts, was the user champion for that initiative and describes the approach:
"Our investigations found that Treehouse's tRelational/DPS offered the capabilities we needed to efficiently get CJIS data from ADABAS into our new JBSIS (Judicial Branch Statistical Information System) data warehouse. We looked at other solutions, but it was clear that tRelational/DPS was the leading product for ADABAS-to-RDBMS data transfer. Funding for the project was partially to be gained through a grant, and to qualify for the grant we had to validate that certain assumptions about ability to produce reports would be met. To that end, we engaged Treehouse to deliver a turnkey tRelational/DPS implementation."
The consulting effort was led by Wayne Lashley, now Treehouse Director of Technical Operations. "The productivity and efficiency of tRelational/DPS made it possible for us to deliver a complete turnkey application over the course of only three short site visits," Wayne notes. The project was completed successfully and on time and proved that tRelational/DPS would meet the requirements for the Courts data warehouse.
IJIS and Data Currency
Among the continuing projects is the implementation of the IJIS Database Environment. Through 2002-2003, the IJIS Program established a Microsoft SQL Server-based reporting repository as part of the initial deliverables set out in its Information Strategy Plan. However, IJIS users required the data to be updated more frequently than once a day. OCIT responded by modifying operational procedures to create ADABAS protection log (PLOG) archives four times per day. PLOG archives are the data source tRelational/DPS uses for propagation?proven to be the most efficient, reliable, and effective approach.
By 2004, the demand was growing from IJIS users for even more frequent propagation. "Like many of our customers, the County is becoming a real-time enterprise," Wayne observes. "We knew we had to respond with an extension to tRelational/DPS that would allow customers to circumvent the operational issues around frequent PLOG switching, yet remain as robust, scalable, and high-performing as tRelational/DPS. Sacramento County is a great example of the quality and effectiveness of DPSync as a product and of Treehouse services."
"DPSync allows us to take a big step forward without replacing CJIS."
Assistant Chief in the District Attorney's Office / IJIS co-chair