The following is a recent discussion between Nancy Kane, Systems & Programming DBA Manager, Scott Morrow, Database Specialist III, and Gary Vercellino, Database Specialist III, at Arizona Department of Economic Security, and Daniel Sycalik, Treehouse Senior Technical Representative.

Please describe your organization.

The Department of Economic Security (DES) combines a broad range of Arizona’s human service programs within a single agency. Each month, DES’ services are sought by more than one million Arizona children, adults, and families. These services range from employment assistance and job training to child and adult protection, child support enforcement, cash assistance, and services for the developmentally disabled. DES works closely with several other state agencies in its delivery of services to the citizens of Arizona. Among the entities DES works with are Arizona Health Care Cost Containment System (AHCCCS), Department of Health Services (DHS), and the Juvenile Justice System.

To support the needs of our clients, our agency is broken into nine divisions. Of these divisions, six provide the services to our clients and the other three provide the operational support for the other divisions and the agency. We are part of the Division of Technology Services (DTS), which provides technical and system services for the development, maintenance, and enhancement of automated business systems to meet the needs of DES. Our division comprises six administrations to support the various needs of the agency. The DBA group is divided between the Technical Support Administration and the Systems & Programming Administration.

"DPS offers the benefit of Propagation, the incremental change data capture, over the full refresh. The products also allow for a faster response to implement changes."

We are part of the Systems & Programming DBA Group, which consists of the Application Support Team and the DES Client Data Warehouse Team. The Application Support Team provides centralized ADABAS DBA support to the programming teams within Systems & Programming. They also ensure that the production and sub-production ADABAS databases are running smoothly and efficiently by performing necessary maintenance of the databases. The DES Client Data Warehouse Team facilitates the extraction, transformation, and movement of data from the DES production mainframe systems to the DB2 UDB distributed data warehouse environment.  While the two teams perform different tasks, they support each other and provide assistance as the need arises.

Please define your ADABAS Application environment.

Our current configuration is 13 production ADABAS databases and 44 sub-production databases supporting the various applications within the Agency. All of the databases are operational 24/7 with minimal downtime for backups and maintenance. Access to the databases is limited based on the application with some being available 24/7 and others being available 6 days a week. Our nightly batch processes for the applications begin at 6:00 p.m. and complete typically between 2:00 and 4:00 a.m. These applications are written in NATURAL, NATURAL Construct, and COBOL. Additionally, there are DB2 applications running NATURAL for DB2 and COBOL.

How many production applications/users do you support?

We currently support 18 different ADABAS production applications that are accessed at any time by the 10,500 employees within the Agency. We ensure that they have the information that they need at a moment's notice and with very little down time.

What prompted the purchase of tRelational and DPS?

We needed a tool that could extract ADABAS data to populate our Data Warehouse and replace our “homegrown” NATURAL extracts that we performed on a weekly basis. The extracts executed during the weekly batch processes and had to be coordinated within the weekly production schedule.

We looked at other vendors and chose Treehouse Software, Inc. The others claimed they worked with ADABAS, but with deeper investigation ADABAS support seemed more of an afterthought. Their claim was that they supported other source systems, such as SQL Server, but we needed something that really handled ADABAS well and we felt Treehouse has the best solution.

DPS offers the benefit of Propagation, the incremental change data capture, over the full refresh. The products also allow for a faster response to implement changes. Previously, we had to make program changes and manually change the offset and lengths for the fixed length records. DPS generates column-delimited data for load processing, and now we simply change the model and regenerate the parameters. The change request process has been made much easier for us.

Please describe your Data Warehouse.

Our Data Warehouse is a central repository for data from many DES Divisions and is a dynamic resource for ad-hoc reporting across the Agency. The data warehouse is in a Windows environment utilizing IBM’s DB2 UDB database platform. We currently have 270 tables populated from 285 files (ADABAS, VSAM, DB2, and flat files). Our largest table contains 26 million rows with the total volume of data in the 80 gigabyte range and growing. The Data Warehouse could be considered an Operational Data Store and not a true Data Warehouse, since it does not contain historical (time variant) data. Not all of the production data is transferred to the warehouse, but what is transferred is based on customer requests and criteria. Our focus is on relevant data to satisfy common and specific query requirements. The warehouse is used for DES-wide ad -hoc and production reporting.

"We looked at other vendors and chose Treehouse Software, Inc. The others claimed they worked with ADABAS, but with deeper investigation ADABAS support seemed more of an afterthought."

We currently have 132 active users of the data warehouse that have specific permission to access their data. Our users provide us a lot of positive feedback on the warehouse in general, our attention to detail and helping them understand how to better use the data contained on the warehouse. We recently asked the user community a question regarding agency wide ad-hoc queries and found that on average that they create or execute 100 ad-hocs per month.

Please describe your tRelational/DPS implementation.

We build our models at the application level with the exception of one application and process our propagations and materializations at the database level by concatenating the models. We propagate on a daily basis and materialize as needed, typically when table definitions change. There is a significant amount of data transformation that takes place within the models. Such transformations as date and time fields, concatenating fields, checking for valid numeric values, etc. are performed to facilitate the translation of data from ADABAS to DB2 UDB.

When we first brought up our warehouse, we were utilizing NATURAL extract programs, which did not lend itself to the normalization of the tables that tRelational offers. As we have converted the applications to utilize tRelational/DPS we had to maintain the existing table structure, but as we have brought in new applications to the warehouse, we normalized these models utilizing the features that tRelational offers. We have had discussions with our customers to normalize the tables, but this was not well received due to the existing report queries that would have to change to accommodate the change. If we had started with tRelational/DPS when we first brought up the warehouse, we would likely have a more normalized schema today.

"When we first brought up our warehouse, we were utilizing NATURAL extract programs, which did not lend itself to the normalization of the tables that tRelational offers."

We have successfully converted all of our NATURAL extract processes to utilize the features of tRelational and DPS. This has freed up valuable processing time on an already crowded production batch processing window and we are now able to provide updated information to our customers on a daily basis instead of on a weekly basis.

Do you use BI report tools to access the Warehouse?

No, we do not have a formal reporting tool. Our customers primarily use Microsoft Access in addition to SPSS for Windows and direct SQL queries to access their data and produce their reports. Our division is in the process of reviewing Business Intelligence (BI) tools. We are hoping to implement an enterprise solution to benefit the warehouse customers.

Are there currently any technical issues or challenges for the Warehouse?

One of our main challenges, in terms of customer need, is getting the customer relevant information from the Warehouse. Since this is a new platform for most of our customers, they are not completely proficient in accessing and retrieving their data. We recognize that training may be very beneficial to improving their reporting requirements from this platform.

We address this issue in part by conducting user meetings every other month, and we invite all users to discuss general warehouse topics. This meeting is an open forum, and we conduct training sessions in both Microsoft Access and SQL commands to address specific questions. We also invite our customers to share reports with the other members so that they may benefit one another.

Another challenge is in the area of data modeling and understanding the business processes for each application. Since we bring in data from many different programs within DES, it is difficult to know how all the data relates within each system and the bigger challenge of determining the relationships between systems. Understanding the business processes and logic behind the data is an important part of data modeling.

What are the current business challenges for your organization?

Our biggest challenge is in the integration of data between systems. A client who needs multiple services is often assigned multiple caseworkers. Ideally, a single case worker would be sufficient. The agency is aware of the issue and is working to develop enterprise solutions that would eliminate this issue. This will not be an easy fix, since we have been doing business this way for a number of years.

" I wish all our interactions with software vendors went as smoothly as our interactions with your company."

What is the long-range plan for the ADABAS/NATURAL applications and the Data Warehouse?

DES has been utilizing ADABAS/NATURAL since it was purchased in 1984. The department relies heavily on ADABAS as its main data repository. Although the department’s strategic direction for mainframe data lies with DB2, we expect to continue to use and support ADABAS for at least the next 5 to 10 years.
We anticipate challenges in migrating from the ADABAS/NATURAL applications to any other platform. We do see the Treehouse products facilitating the data transfer for any new direction.

Would you encourage other State agencies utilizing ADABAS to consider tRelational and DPS?

I would strongly recommend the use of the tRelational and DPS products for any other State agencies that are currently utilizing ADABAS, especially if they need to do any migration to another platform for data warehouses. These products are a true time saver in regards to table changes and table updates, along with the lower impact against production databases. The tools are very intuitive and require minimal training prior to use.

How would you rate the Treehouse Products, Services and Support?

I would rate it very good; on a scale of 1 to 10, I would give it a ten. Your responsiveness and follow-up are excellent. At times, it can be challenging for us to provide the information necessary to solve a problem, due in part to confidential information we are processing, but you are always able to provide us solutions to our issues. I wish all our interactions with software vendors went as smoothly as our interactions with your company.