`Background:

For the Pennsylvania Department of Environmental Protection (DEP) to conduct its program and business responsibilities effectively, a trustworthy enterprise data management system is required. Various environmental programs and administrative organizational units collect and use large amounts of supportive data every year. It is important that all enterprise data ‑‑ each data element ‑‑ be as accurate and reliable as possible.

DEP's senior management and the Quality Management Council have discussed the need to analyze and improve the enterprise data that we all use and share. We began by asking:

What purpose is served by the data we collect?

Who are the producers and who are the consumers of this data?

How is the data maintained?

Is there any data that should be eliminated?

Who are the responsible data element stewards or owners?

How do we measure the quality of our data? By these metrics, what is the quality of our data?

How can our data quality be improved?

Presently, we have approximately 92,700 Oracle and SQL Server enterprise data columns or elements. With this in mind, it will be necessary to employ a phased auditing approach. The first step in the process involves partitioning our data assets in such a way that suitable experts can identify and perform the data element reviews. The data elements must also be partitioned in such a way that the number of data elements assigned to program and technical staff is of manageable size.

A list of data elements currently supported for the program area in various enterprise and certain crucial program-unique data systems will be placed into DEP's new environmental Data Element Dictionary.

Purpose:

The primary purpose of the Data Element Specification Form is to gather information from the program staff and application developers to load data into an Oracle database called the eDED (environmental Data Element Dictionary). The eDED will provide the Department with a suitable, consistent, and standardized data quality assurance tool. Having a current record or inventory of data elements in use is necessary for us to control, manage, and validate the ever-expanding data management system critical to all of our work.

Staff Involvement:

Project and Team Leaders will work with the appropriate program staff in completing “Section 1: Program Information” of the Data Element Specification Form by listing all relevant enterprise data elements, both existing and new. This information is normally gathered from paper applications, existing program databases or spreadsheets, or other sources.

Data validations will be performed by the program Application Developers for completeness and accuracy. Application Developers will complete “Section 2: Developer Information” of the Data Element Specification Form by listing all relevant application information associated to the data elements defined by the program. This information can be gathered by utilizing the appropriate Functional Specification documentation and existing database elements.

In addition, the Bureau of Information Technology’s Database Administration Section will analyze the form for existing, correct production data elements and definitions or determine if new data definitions need to be created and verified. The Database Administration Section will also assign standards to the data elements.

Data Element Specification Form Definitions

SECTION 1: PROGRAM INFORMATION

PROGRAM

The name of the program requesting the data elements to be created in a production database.

PROJECT

The project associated to the data for this program.

PROGRAM REPRESENTATIVE NAME AND DATE

The name of the person assigned to collect the program’s data and the date of initial completion for Section 1.

ID

Sequential number to indicate the line item. NOTE: This field is carried to the beginning of each page.

FIELD LABEL

The label (or name) of the field on the source media being collected by the program. This could be a field on a form or a column heading in a spreadsheet, etc.

DEP PROGRAM FIELD DESCRIPTION

The DEP program’s description or definition of the field.

FIELD TYPE

The type of the field (i.e., alphanumeric, numeric, Yes/No (checkbox), date, etc.) being collected by the program.

FIELD LENGTH

The length of the field being collected by the program.

LOV (LIST OF VALUES) (Y/N)

Y=Yes or N=No to indicate if this field has a list of values/choices (i.e., County Codes, Application Types, etc.)

DEFAULT VALUE

The value assigned to the field that is automatically stored if no entry is made.

REQUIRED (Y/N)

Y=Yes or N=No to indicate if this field is required/mandatory.

SOURCE TYPE

The type given to the source data (where the field information is coming from). Examples: Paper Form, Spreadsheet, Access DB, Flat File, SQL Server DB, Oracle DB, ODBC, XML Stream, OCR Form, etc.

SOURCE

The name of the original source (where the field information is coming from) of the data. Examples: EPA, PennDOT, State Police, Public, etc.

DESTINATION TYPE

The type given to the destination data (where the information is going). This is only used if the information is sent outside DEP. Examples: Report, Flat File, Spreadsheet, etc.

DESTINATION

The name of the final destination of the data (where the information is going). This is only used if the information is sent outside DEP. Examples: EPA, PennDOT, State Police, etc.

TRACKING PURPOSE

The reason the program is tracking the source data.

BEST PRACTICE

The program’s best practice in entering and storing the data.

EPA/OTHER DEFINITION

The definition EPA or other agencies may use for the data item.

GENERIC COMMENT

Free form text to document any related information.

SECTION 2: DEVELOPER INFORMATION

DEVELOPER NAME AND DATE

The name of the developer assigned to the project and the date the developer completes initial review.

APPLICATION

The application being used to store the data (i.e., eFACTS).

SERVER

The server which houses the production data for the application.

DATABASE

The database used to store the data (i.e., Production).

MODULE TYPE

The module type assigned to the module (i.e, Form, Report, Batch, Package, Procedure, etc.)

MODULE CODE

The code assigned to the module (i.e., FX0401).

MODULE NAME

The name of the module that accesses the data.

TAB/BLOCK ON MODULE

The tab or block name of the module where the field is to be located.

FIELD LABEL ON MODULE

The display name of the field on the module.

REQUIRED (Y/N)

Y=Yes or N=No to indicate if the field is required.

SCHEMA

The schema or owner of the object (i.e., FIX_DBA, SYSTEM, etc.).

OBJECT TYPE

The type of object (i.e., TABLE, VIEW).

OBJECT NAME

The name of the object (i.e., CLIENTS).

OBJECT COMMENT (DESC)

The description of the object.

PHYSICAL ELEMENT NAME

The name of the physical element in the database.

PHYSICAL ELEMENT COMMENT (DESC)

The description of the physical element.

DATATYPE

The datatype of the physical element (i.e., CHAR, VARCHAR2, NUMBER, DATE, etc.).

LENGTH

The length of the physical element.

PRECISION

The number of digits in a number (i.e., the number 123.45 has a precision of 5).

SCALE

The number of digits to the right of the decimal point in a number (i.e., the number 123.45 has a scale of 2).

DEFAULT

The default assigned to the physical element.

NULL (Y/N)

Y=Yes or N=No to indicate if the physical element can be blank (null).

PRIMARY KEY (Y/N)

Y=Yes or N=No to indicate if the physical element is a primary key.

FOREIGN KEY (Y/N)

Y=Yes or N=No to indicate if the physical element is a foreign key.

XML TAG

The XML attribute name (if any) assigned to the physical element.

CONSTRAINT NAME

The constraint name (if any) associated to the physical element.

TRIGGER NAME

The trigger name (if any) associated to the physical element.

DEN (DATA ELEMENT NUMBER)

The data element number assigned to physical element. This number also refers to the standard element. If the physical element is new and does not have a DEN assigned, include the word “NEW” in this field. The Database Administrator will create a standard element and assign a DEN number.

DATABASE USAGE:

Y=Yes or N=No for each security item below to indicate how a module/field accesses the physical element.

SELECT (Y/N)

INSERT (Y/N)

UPDATE (Y/N)

DELETE (Y/N)

UNKNOWN (Y/N)

USER USAGE:

Y=Yes or N=No for each security item below to indicate how a user accesses the physical element based on their role.

ROLE

The role assigned to the user (i.e., APPL, etc.).

SELECT (Y/N)

INSERT (Y/N)

UPDATE (Y/N)

DELETE (Y/N)

Attachment A - Data Element Specification Form

Section 1:	Program Information
Program:
Project:
Program Representative Name and Date:
ID	FIELD LABEL	DEP PROGRAM FIELD DESCRIPTION	FIELD TYPE	FIELD LENGTH	LOV (List of Values) (Y/N)?	DEFAULT VALUE	REQUIRED (Y/N)?	SOURCE TYPE	SOURCE	DESTINATION TYPE	DESTINATION	TRACKING PURPOSE	BEST PRACTICE	EPA/OTHER DEFINITION	GENERIC COMMENT

Section 2:	Developer Information
Developer Name and Date:
Application:
Server:
Database:
MODULE
TYPE	MODULE CODE	MODULE NAME	TAB/
BLOCK
ON
MODULE	FIELD LABEL ON MODULE	REQUIRED (Y/N)?	SCHEMA	OBJECT TYPE	OBJECT NAME	OBJECT COMMENT
(DESC)	PHYSICAL
ELEMENT
NAME	PHYSICAL
ELEMENT
COMMENT
(DESC)	DATA
TYPE*	LENGTH*	PRECISION	SCALE*	DEFAULT*	NULL
(Y/N)?*

Database Usage	User Usage
PRIMARY KEY (Y/N)?	FOREIGN KEY (Y/N)?	XML TAG	CONSTRAINT NAME*	TRIGGER NAME*	DEN (Data Element Number)	SELECT (Y/N)?	INSERT (Y/N)?	UPDATE (Y/N)?	DELETE (Y/N)?	UNKNOWN (Y/N)?	ROLE	SELECT (Y/N)?	INSERT (Y/N)?	UPDATE (Y/N)?	DELETE (Y/N)?

Commonwealth of Pennsylvania	3.E_DED_Final

Department of Environmental Protection

Data Element ______________

<Enter Project Name Here>

1 of 11	9/19/2006

<Location of Project "file path">

Project Quality Assurance Data Plan

11 of 11	9/19/2006

Commonwealth of Pennsylvania

strotman

2006-09-12

mmcdaniel