Data Warehouse

Created on: 
Mar 24 2011

See ACES Screens and Online Pages for an example of pages or screens used in this chapter.

 What is the Data Warehouse?

Several organizations both within Department of Social and Health Services (DSHS) (such as Research and Data Analysis (RDA)) and outside of DSHS (such as Office of Financial Management (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 Information Management System (IMS) databases designed to meet operational needs, the Data Warehouse is a set of DB2 databases which run on a Windows Server and designed to meet reporting needs. Current Data Warehouse users access this serve directly. 

 What data does the Data Warehouse contain?

The Data Warehouse is made up of two databases. This includes the STAG database and the CARD 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 databases, the changes are also made in STAG.

  • The CARD database is a data mart that contains approximately 40 tables. A core group of Data Warehouse users, representing the various agencies that 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.

 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 the STAG and CARD databases. During the load, the Data Warehouse is unavailable.

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

State 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.

If approval is granted, access is set up for the new user. Access information is passed on to the new user through an ACES Data Warehouse Business Analyst.

 How do users connect to the Data Warehouse server?

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

 

See ACES Screens and Online Pages for an example of pages or screens used in this chapter.