Data Warehouse

	ACES	Search for:

Acronyms Alerts Screens WAC Number Index WCCC DSHS Home

Data Warehouse

Revised January 25, 2008

Purpose:

What is the Data Warehouse?

What data does the Data Warehouse contain?

How does the data get to the Data Warehouse?

How do I get direct access to the Data Warehouse (non-web interface)?

How do users connect to the Data Warehouse server?

Data Warehouse
What is the Data Warehouse?

Several agencies within DSHS (such as RDA and OFM) who provide budget information, statistics and other data to the legislature, governor's office, and federal government have a need to access both current and historical data that is entered into ACES.

In order to meet these data needs, the ACES Data Warehouse was designed and went into production in early 1998. While ACES is a set of IMS databases which run on MVS and designed to meet operational needs, the Data Warehouse is a set of DB2 databases which run on a Windows NT Server and designed to meet reporting needs. Current Data Warehouse users access this server directly through using the Impromptu Client software or via a DB/2 Client software.

What data does the Data Warehouse contain?

The Data Warehouse is made up of several databases. These include the STAG database, the CARD database and the Metadata database. Within each of the databases, there are tables that store different cross sections of data.

The tables in the STAG database contain the same data as the ACES segments on the mainframe. When ACES makes changes or new additions to mainframe fields, the changes are also made in STAG.

A set of approximately 40 tables make up the CARD database. The CARD database is a data mart. A core group of Data Warehouse users, representing the various agencies who use ACES data, developed the Individual CARD tables. These tables contain data from one or more of the STAG tables, with the data condensed in ways that make it quicker and easier to use.

The Metadata database is a data dictionary for the Data Warehouse. As of 01/05, a project is underway to get all of the information in the metadata database current and keep it updated.

How does the data get to the Data Warehouse?

Once a month, the weekend that the Month Begin jobs run, data is extracted from the ACES mainframe and loaded into STAG and CARD.

During the load, the Data Warehouse is unavailable.

For load dates see the Data Warehouse Load Schedules page on aces.online.

How do I get direct access to the Data Warehouse (non-web interface)?

DSHS employees with a legitimate business need to access the data in the ACES Data Warehouse can request access by completing the ACES Data Warehouse access form, getting the necessary signatures and returning the form to the address provided.

Once approval is granted the ACES LAN team sets up access for the new user. Access information is passed on to the new user through an ACES Data Warehouse Business Analyst.

New users can obtain additional information on connecting to the Data Warehouse server and extracting data from the New Data Warehouse User Frequently Asked Questions page at aces.online.

How do users connect to the Data Warehouse server?

Data Warehouse users can connect to the Data Warehouse using any ODBC compliant tool, such as DB2 Command Center, SAS, Access, Excel and Impromptu. Each user decides which tool they would like to use and is responsible for purchasing the necessary software and/or licenses.

Data Warehouse
Revised January 25, 2008

Purpose:
What is the Data Warehouse? What data does the Data Warehouse contain? How does the data get to the Data Warehouse? How do I get direct access to the Data Warehouse (non-web interface)? How do users connect to the Data Warehouse server?
Data Warehouse What is the Data Warehouse? Several agencies within DSHS (such as RDA and OFM) who provide budget information, statistics and other data to the legislature, governor's office, and federal government have a need to access both current and historical data that is entered into ACES. In order to meet these data needs, the ACES Data Warehouse was designed and went into production in early 1998. While ACES is a set of IMS databases which run on MVS and designed to meet operational needs, the Data Warehouse is a set of DB2 databases which run on a Windows NT Server and designed to meet reporting needs. Current Data Warehouse users access this server directly through using the Impromptu Client software or via a DB/2 Client software. What data does the Data Warehouse contain? The Data Warehouse is made up of several databases. These include the STAG database, the CARD database and the Metadata database. Within each of the databases, there are tables that store different cross sections of data. The tables in the STAG database contain the same data as the ACES segments on the mainframe. When ACES makes changes or new additions to mainframe fields, the changes are also made in STAG. A set of approximately 40 tables make up the CARD database. The CARD database is a data mart. A core group of Data Warehouse users, representing the various agencies who use ACES data, developed the Individual CARD tables. These tables contain data from one or more of the STAG tables, with the data condensed in ways that make it quicker and easier to use. The Metadata database is a data dictionary for the Data Warehouse. As of 01/05, a project is underway to get all of the information in the metadata database current and keep it updated. How does the data get to the Data Warehouse? Once a month, the weekend that the Month Begin jobs run, data is extracted from the ACES mainframe and loaded into STAG and CARD. During the load, the Data Warehouse is unavailable. For load dates see the Data Warehouse Load Schedules page on aces.online. How do I get direct access to the Data Warehouse (non-web interface)? DSHS employees with a legitimate business need to access the data in the ACES Data Warehouse can request access by completing the ACES Data Warehouse access form, getting the necessary signatures and returning the form to the address provided. Once approval is granted the ACES LAN team sets up access for the new user. Access information is passed on to the new user through an ACES Data Warehouse Business Analyst. New users can obtain additional information on connecting to the Data Warehouse server and extracting data from the New Data Warehouse User Frequently Asked Questions page at aces.online. How do users connect to the Data Warehouse server? Data Warehouse users can connect to the Data Warehouse using any ODBC compliant tool, such as DB2 Command Center, SAS, Access, Excel and Impromptu. Each user decides which tool they would like to use and is responsible for purchasing the necessary software and/or licenses.