The purpose of this paper is to explain the data warehouse system developed by the Western Australian Ministry of Justice.

The data warehouse currently enables the Ministry of Justice to research all aspects of the criminal justice system and deliver strategic and cross functional information in a timely and efficient manner.

The data warehouse is currently under further development and is in the process of delivering civil court statistics and corporate support details such as financial information.

This paper looks at the infrastructure supporting the data warehouse, its application and issues surrounding it’s use.

The Ministry of Justice is a public organisation, established in July 1993 and is comprised of the Courts, Prisons, Community Corrections, Juvenile Justice, Crown Solicitor’s Office, Parliamentary Counsel’s Office, Registrar General and the Public Trustee. This brings together a large organisation, which was established in response to the Western Australian’s Government commitment to a co-ordinated system of justice.

The Ministry also: employs some 3,500 people; holds approximately 2,200 adults in custody pa; supervises 6,000 adults in community supervision; spends some $268 million per year.

The Ministry’s mission statement:

To ensure access to a fair and cost effective system of justice, which protects the rights of individuals and is responsive to community needs.

The Ministry of Justice has a number of information technology systems. Courts in particular, currently have 15 different case management and judicial support systems that are jurisdictional specific. Prisons also have their own system, but is integrated with community corrections. These systems do not electronically communicate with each other.

Courts have, for some time now, required the need to access a range of different statistics from its system. The judiciary have specific requirements as well as court administrators. A number of specific programs have been developed on each court system to deliver raw data. However requirements change rapidly and the need for more flexibility in the manipulation of data was required.

Data warehouse was seen as one method to manipulate data and apply consistent rules of measurement across all jurisdictions.

Initially, the warehouse holds Criminal Justice System data. There are four sections in this warehouse: a Programme Level, a Secure Programme Level, a Justice Level and an Analytical Level.

Secure Program Level

This level is a view of the Programme Level. Staff will see all coded data but names, detailed address fields and comment fields will not be available and all identifiers are encrypted. The level exists so that staff can perform detailed ad-hoc queries against data, knowing that the information returned will be as accurate as the operational system. The ‘knowledge worker’, would use data browsing tools that access a database at the record level.

The Justice Level will contain data structures that comply with the Ministry’s Corporate Data Model. This Level will enable staff to answer questions of a cross jurisdictional and cross programme nature. For example, What was the success rate of all assignments to Offender Development Programmes where the assignments were conditions of Court issued Dispositions? The data will be sourced from the Programme Level with two operations being performed simultaneously.

This double conversion will pose some issues for staff when the data are analysed. Some structures from the operational systems do not look at all like those in the Information Model and will mean slightly different things.

A key part of the project is the data matching process. The process will need to construct a single table, which contains records of all individuals that are known to the Ministry, despite those individual records across systems. The process will need to cope with different spellings and name constructs and deal with the multi-cultural nature of the population.

Analytical Level

This is the highest level of the Ministry Data Warehouse and is broken down into specialist analysis areas. This will be the entry level for users, where the most intuitive query tools are located and which require minimal training. The first two areas are being implemented initially:

The Ministry’s data warehouse is constructed on a Data General AVIION Intel based computer-running UNIX. It has 64 Gigabyte of disk, four processors, 512 Megabyte of RAM, two five disk arrays (utilising RAID 5) of 20 Gigabytes each. The are three mirrored disk pairs that hold executable files with the development and production code. Disk space is a critical issue when using OLAP structures.

Some multi-dimensional structures are larger than the whole of the operational system that the data was sourced from. Calculating OLAP space requirements is not easy when precise specifications are not available or the calculations to determine sizing cannot be given easily. It was initially difficult to convince other IT staff that a 150 Gigabyte machine was needed.

Oracle 7.3 RDBMS software is used to store the data at the Programme and Justice Levels with Oracle Express Server together with being used to store the multi-dimensional structures.

A typical warehouse Client machine is a Pentium 100 or greater with 16 Megabytes of RAM which has all the Developer 2000 product software and the Oracle Sales Analyser tools available. Each team member has a Pentium machine with 32 Megabytes of RAM and a 17" screen.

The developer needs to be able to work with the Developer 2000, the Express tool set and the Designer 2000 product. The Designer 2000 product is where the Warehouse definition is stored and is an important part of this project, without which we would have taken much longer to design the logical models and document the systems.

There is no middle ware. All the core systems are built with the Oracle RDBMS and at this stage, the maintenance of data mapping specifications can be dealt with our existing IT technologies.

The Data Warehouse will initially be refreshed weekly in its entirety. (This may well change in the future.) Oracle export files from each operational system are used to refresh the warehouse. The export files are compressed and then transported over the network. This usually takes 2.75 hours.

The data are then loaded into the Programme level. This is completed in 2.5 hours.

After the load, each Programme Level area will have the encrypted identifiers added to the respective tables. This will ensure that the Secure Programme Level will be able to effectively see the data. This stage was not complete when this article was written.

The Justice Level data are then loaded from Programme Level. A single person table is constructed from the equivalent operational tables using data matching techniques. Then the data are copied table for table by translating the data values and rearranging the records to the Corporate Data Model. Some operational tables do not look like the Corporate Data Model so significant interpretation will need to be done. Because middle ware is not used we extended Designer 2000, to map data across systems. The decision to use PL/SQL was made to ensure full functionality was available to the developers.

The OLAP area is then loaded and the Project team has had to deal with data structures that do not behave like Oracle relational tables.

A full refresh is undertaken because we want to ensure that all data accurately represents the contents of the operational systems. Ministry staff need to delete individual records from operational systems as duplicates are found. Not all offenders tell the truth about their identity.

In the longer term, the Ministry may attempt to employ data replication to keep the Programme Level synchronised with the operational systems.

There have been a number of related projects that needed to be under way prior to the implementation of the Warehouse. They are Ministry-wide IT projects which include: the Data Analysis Project which gives a Corporate Data Model and initially proposed a Logical Architecture for the Warehouse; the Core Identity Project (and later the EDI project) which will provide the mechanism to track people and information objects across the justice system. Then there is the Data Warehouse Project itself, which accepts specifications and environmental information from those projects, build the information resource and in turn provides some feedback to those projects.

There are 14 people on the Project team but of them 8 are core members and 6 are part-time. Every team member has had training in Oracle Express and Oracle Sales Analyser. The five part-time members are from: other IT Project teams (2), the Offender Management Division (1) and from the Crime Research Centre at the University of Western Australia. The deal was that the project would provide training at no cost to them and in return the Project would receive 4 OLAP cubes from each of them. The mix of technical skills includes Methodology, CASE, OLAP construction, RDBMS, and technical infrastructure. There is redundancy within the team.

We adopted a concurrent approach. The operational system was incorporated into the DWH in parallel. A Project team member was allocated to each system, and that person was responsible for the Programme Level build, the associated OLAP structures and the incorporation of the Programme Level data into the unified Justice Level database. The strategy is to deliver the OLAP structures to Stakeholders as quickly as possible. The phases are:

Since the inception of the data warehouse, the integrity of court data has been brought into question. A number of data tables within the warehouse showed unknown judges, penalties that did not match offenders and case delays that could not be possible.

This meant that data collection methods employed by court staff were failing and there was inadequate validation of data before it entered the system. This problem cast doubt over the data warehouse system and the tool was blamed for the results. This issue is currently being addressed and proper audit systems are being developed and implemented.

Even though the team could prototype the cubes, formal specifications were written. The Office of the Auditor General (AOG) monitors the performance of government organisations. Part of its role is to audit the development and final of an organisation's key performance indicators. The Auditor General needs to be assured that the KPI's built by the team are built according to specifications signed off by clients and that the IT documentation is adequate for auditing. This includes the process that constructs the data that feed into the OLAP data loaders.

The OLAP specifications were written to ensure that what was meant, could be understood and interpreted correctly by the technical team member allocated to that business area. Support staff will need to know why the counting rules are what they are, as well as how the data is built prior to loading into OLAP structures. To do this effectively, the Project team developed its own methodology that addresses these points and ensured the documentation is complete.

The important lesson here is that absolute clarity is required to understand the meaning of the data shown in the OLAP structures. We had to answer the question: 'What does that number really mean?'

Automated procedures were built into the refresh process to detect database alterations to the operational database from the previous refresh. With the large amount of system development in progress, not all changes are notified to the Project team. The Data Warehouse Project incurs changes to its Programme Level while it is still under development.

We found that some of the aggregation (roll up) processes needed to be purpose built. Accepting the default roll up process from the OLAP data loader cannot be assumed. For example, while it is sufficient to total the number of new charges across time periods, the same procedure cannot be used to total the number of distinct persons brought before the courts. Since one of the information objectives is to determine the prevalence of offending (for a time period) then that person cannot be counted twice for that period but the number of charges for that person should be totalled. The cubes become quite complex when multiple measures do not refer to the same object.

The overriding consideration that impacts the design of this warehouse strategy is that each of the Ministry’s information systems has had a different development history. The systems are independent and exist without any interaction. This constraint has led to a multi-layered warehouse architecture. The layering allows data to be transformed, in an orderly fashion towards the Ministry’s Information Model, and keep the exact representation of the data held in the operational computer systems.

As an aside, it is worth noting that the model has been incorporated into the Ministry’s development methodology. The longer-term intention here is, to move towards more consistent and more rapid development, which automatically factors into the requirements the need to address cross programme analysis and other corporate goals.

In the longer term, activity based costing, business process reviews and new IT systems will change the warehouse. The need to acquire new Offender Management Systems and replace existing Court systems with generic ones, will mean that the Programme Level will change. The strategy is to minimise the changes to the Justice Level.

One of the business goals of the Ministry is to place more emphasis on the delivery of programmes that address offending behaviour. Better understanding of our business will mean looking at Corporate data differently and that means changes to the Justice Level. Here the strategy is to minimise that change to Programme Level, which reflect the structure of operational computer systems.

This is a contentious issue for any Data Warehouse Project. The benefits for this project are implied. The Government has set out clear requirements at the policy level and this project has been funded from those policy statements. However how does the Project measure its success? Looking at the business requirement does this. The Ministry must be able to:

The Express engine is a powerful product that is well proven over many years. Oracle Sales Analyser is another product with a good history. We stayed with the Sales Analyser Data Loader because SQL Bridge is not the loader or the data access vehicle we expected or need it to be.

Sales Analyser was chosen because it is a very fast Prototyping product. Oracle Express Objects wasn’t around at the time that procurement decisions were made. Sales Analyser is undergoing further development and we expect that the merging of the server functions will benefit our project. Some members of the project have now been trained in Oracle Express Objects and by converting slices of existing OLAP structures, we can demonstrate more effectively possible management applications to Programme Directors.

Data warehouse provides an excellent tool to manipulate data in a multitude of ways. While the establishment investment can be considered to be high, it does save money in the longer term because of it’s flexibility and the elimination of the requirement to write new statistical programs.

The various levels of access is a key feature of data warehouse – the justice level, secure program level, program level and the analytical level (OLAP). It is the analytical level that provides great benefits for the judiciary and court administrators alike.

Similarly the technical foundations significantly reduces the impact on operational systems. Where normal statistical systems are an extension of operational systems, the ability to download all the data on a separate server, provides the user with unlimited access without reducing the response times on operational systems.

Data quality is a very important issue that needs to be addressed. Quality data collection systems need to be in place to ensure that the data is clean and accurate. Without these systems, the detail within the warehouse cannot be credible and your investment in such a program would be wasted.

ATTACHMENTS