DSCYF Reporting Environment

The Department’s information management has a distributed organization with central support.  Each division has one or more query writers who have a range of data extraction and query skills. A sanctioned set of query tools are provided; data sources are shared, a centralized shared report distribution (Oracle Portal and Application Server) is supported by MIS and I&A.  Because the divisions are in transition to the current environment, storage of reports varies across divisions with some reports stored in the central environment and some remaining on individual PCs.

The Department’s reporting environment evolved over several years of planning, development and implementation and is the result of collaboration among data-interested technical and non-technical staff and managers across the department. The environment consists of a set of reporting standards and a technical infrastructure that consists of Oracle-based tools.

Reporting Standards

Reporting Standards are outlined in the Report Development And Certification Standards Handbook. The foundation standard is the Collaborative Model for report development and publishing. The model is a single approach used by query writers across the department with standardized processes:

  • Requesting data or reports, defining requirements, and documenting reports through the use of a standardized Request Form.
  • Building reports using common Oracle-based query tools
  • Validating and accepting information by the requestor
  • Reviewing documentation that is prepared by the report developer
  • Publishing or distributing information on the department-wide Portal

Reporting Data Sources

Query writers use data objects stored in an Oracle database management system with data copied from the Department’s tracking system called Families and Child Tracking System (FACTS).  Copies of production FACTS data are refreshed nightly.  User-created tables, views, materialized views, and functions comprise a code library stored in two Oracle schemas (“QHouse” and DCG_CG1) and shared across the Department.  FACTS workbooks created in Oracle Discoverer are also used as a data source.

Query and Reporting Tools

The Department has moved to a single-vendor technical data infrastructure based on Oracle tools.  Sanctioned query tools include Oracle Developer Suite (Oracle Reports and Discoverer),  and Allaroundautomations’  PL/SQL Developer. In the past, reports were developed using Seagate Crystal Reports Professional and Gupta Team Developer.  Reports remaining in these tools have been re-engineered in one of the sanctioned tools. At the same time, many reports remain in MS Access. Computer Associates’ ERwin is used to model the FACTS data architecture and maintain the data dictionary.

Distribution of Reports and Workbooks

Since 2005, reports and queries have been published and shared through an Oracle Portal (via an Oracle Application Server). Reports are presented as either Oracle Reports or Oracle Discoverer workbooks that are scheduled and viewed on desktop or may be emailed if requested. The Crystal Info distribution system was phased out in 2005.