Axiom Data Warehouse Information System Support

Date : January 2008 – June 2009

Description

The Tri-Force team worked on the Axiom data warehouse project to develop business intelligence tool to augment current MIS effort and spurt a great deal of avoidable effort.

Axiom Data Warehouse Information System Background

Tri-Force Consulting Services, Inc. supported the Philadelphia Prison System’s Axiom Data Warehouse. The Axiom Data Warehouse is a Business Intelligence tool that facilitates the reporting and analysis of Philadelphia Prison System data. Original functionality allowed staff members to perform basic assessments of the inmate population in support of initiatives to understand, manage, and reduce the population.
PPS has undertaken an initiative to create a data warehouse called Axiom that has already been demonstrated and applauded by PPS staff, Division of Social Services and the Managing Director’s office. Axiom is the foundation of an extremely powerful and adaptable business intelligence environment which will be expanding in content and scope over the next several months and has already been the vehicle for supplying key reports and analysis internally and externally. It will continue to provide the ability to:

  • Analyze the population from various demographic and judicial perspectives
  • Understand cause and effect of proposed and implemented initiatives
  • Assess and improve operations
  • Readily produce reports requested internally and externally based on PPS data

The Axiom data warehouse the source of the underlying data is Lock and Track Offender Management System. The data mart in PPS is the “Current Population” is a data mart providing information relevant to the current census – early release and special release procedures, initiatives, and results. There is a standard set of reports that are built into the business intelligence application that have been created and put into production on a routine basis.

Currently, there are numerous reports generated through Axiom such as the “30-Day until Maximum Sentence” or the “Length of the stay” which has been developed, validated and produced on a periodic basis as specified by the users of the report. Ad hoc analysis is performed at PPS, such as group of inmates often must be selected for transfer to another facility – certain criteria must match to identify the candidates. By dynamically manipulating the selection criteria, the final group.

The Commissioner requested various data that will be available through web portal at all times. It will contain various pieces of data, including current census, average intake time, average length of stay for the current population.

The Tri-Force team working on the Axiom data warehouse project provided a business intelligence tool that helped to augment current MIS effort and usurp a great deal of avoidable effort.

The existing Axiom Data Warehouse was assessed by Tri-Force Consulting at project kick-off. The assessment uncovered the need for design changes to be made to the data model to allow business logic processing to occur at the proper level.

Highlights

  • Software re-designs to transform the data model and the data dictionary to eliminate redundant business logic at the ETL level.
  • Managing the design and performance of the SQL Server database as well as enhancing and managing the nightly data loading (ETL) process.
  • Enhancement and management of the Cognos business intelligence framework for the data warehouse, which includes the library of reports, analytical cubes, archives, Meta data, and the user interface.
  • Integrating and expanding the scope of the data warehouse to add additional functionality encompassing all aspects of operations at the Prison System

Technology Stack

SQL Server 2005 Oracle 9i Data Modeling ETL Cognos 8