[A StarSQL Enterprise Edition and StarPipes Case Study] State of Tennessee Puts Family First with Easier Data Manipulation Department of Human Services Chooses StarQuest and MicroStrategy Tools for New Data Warehouse Traditionally known as the "Volunteer State", Tennessee has a long and proud history of helping its citizens. Tennessee's Department of Human Services (Tennessee DHS) in Nashville provides a quality system of human services designed to meet the changing needs of individuals and families in the state, working to protect the vulnerable and to enable Tennesseans to achieve self-sufficiency and improve their quality of life. Tennessee DHS supports families, children and vulnerable adults through prevention and intervention services delivered by a technologically linked development. partnership of its staff and community-based providers, who value learning and skills Identifying the Problem The state's Department of Human Services knew that the information they needed to track the performance of its Family First welfare reform programs was somewhere in their computer systems. "The federal government has, in some ways, taken a hands-off approach and released the state from a number of regulations concerning the operation of Welfare programs. At the same time, they require new and highly detailed statistical reports which they use to evaluate the effectiveness of state welfare programs," states Dave Sartor, information systems manager for Tennessee DHS. "So, we had to take a very hands-on approach." Tennessee DHS was able to look at its whole program, restructure and plan for promoting the issues. They needed an easy way to access the data and to quickly run the numerous statistical reports requested by the various federal agencies. So, the department set out to build a new data warehouse. The Department of Human Services worked with the state's Office of Information Resources (OIR) of Tennessee's Finance and Administration Department, which provides direction, planning, resources, and coordination in managing the information systems needs of the state. OIR serves as staff to the Information Systems Council and under its guidance provides technical direction, services and infrastructure to the various state agencies. Tennessee DHS's data warehouse funnels information from a number of applications into a central file and equips executives with desktop query and reporting tools so they can perform various "What If" analyses, such as examining the impact a policy change would have on benefit disbursements. "We needed a lot of exploratory reporting-for example: How many recipients have left the program due to employment but have returned three times in the past 18 months due to job loss, but not more than five times? If we focus resources in this direction, how much will it cost?" explains Sartor. Much of the data being requested is used to explore the relationship between the services provided and their outcome. There's a lot of detailed drill down-type analysis that is not COBOL-suited. Executives had to request that special batch reports be run to determine how policy changes affected programs. A few days or a couple of weeks would pass before the reports were produced when they were really needed that day to be part of the planning process. By the time the reports were done, the answers may not have been what were expected and executives were not able to manipulate the information and get new answers with different variables. The Data Warehouse Solution The goal was to have a data warehouse that would enable users to run such reports from their desktop PCs. Sartor states, "Our goal was to reduce turnaround for new information requests from a week or so to the same day-and to give users the ability to change report parameters and explore data relationships interactively." "While the data warehouse promised many benefits, it also presented the state with a number of challenges," explains Angelo White, a systems programmer at OIR. Like many large organizations, the state houses its data on an IBM mainframe running IBM's DB2 database management system and has no plans to move it since the platform is reliable, secure and scalable. As most of the users work with desktop PCs running Microsoft Corp.'s Windows operating system, OIR had to design a communications infrastructure so users could move information from their desktop systems to the mainframe. Working with OIR, Tennessee DHS built its ACCENT-based (Automated Client Certification and Eligibility Network for Tennnessee) system to deal with the massive reporting requirements. To set up the data warehouse, DHS used MicroStrategy Inc.'s data manipulation products for it primary query tool and technical applications: MicroStrategy DSS Agent, DSS Architect, and DSS Server products. DSS Agent is client software that enables an end user to query a database, analyze information and create reports. While DSS Server sits on top of a database and shields users from application formatting issues so they can pull information from different applications for analysis. Warehouse components were completed with MicroStrategy DSS Architect, graphic application programming tool so customers can build applications that rely on DSS Agent and DSS Server. Then they needed to find the best way to access their DB2 data. Accessing the Data Initially, the organization looked at database connectivity tools from its mainframe supplier but the company was not as responsive as the state government organization desired. Since this was the state's first foray into the desktop-to-mainframe connectivity area, a number of questions arose about how to configure connections properly and the vendor was slow answering them. OIR went looking for an ODBC driver that would provide faster, easier access to DB2 than the product they were currently using. Several products were reviewed, but after a through trial, StarSQL Enterprise Edition (EE) was chosen. On the state's suggestion, MicroStrategy fully tested and certified the Berkeley, CA-based StarQuest Software product to work with MicroStategy DSS products. At first, the state ran its mainframe connection through a server running Novell Inc.'s NetWare operating system but then decided to move to Microsoft's SNA Server on Windows NT, which is becoming the industry standard server operating system. Then, the state installed StarSQL EE, a desktop client for mainframe connectivity and StarPipes running on a Windows NT server for managing the desktop users. StarPipes converts TCP/IP on the desktop to SNA on the host. After the installation, Tennessee DHS and OIR had to learn how all the new tools operated. "We found it difficult to grasp some of the new concepts, such topological associations and the products' various naming conventions", admitted White. "We found the PC area - especially when we had been working with Novell's server - was much more complicated than the mainframe." Users were working with a mix of 16 bit and 32 bit desktop devices, which further complicated the process of insuring proper connections. OIR went to the vendor for assistance. White rated StarQuest's customer support has been exemplary. "Right from the start, StarQuest's support team has been willing to work closely with us on any problem and has followed through to make sure any question that we had is answered so that the connection was up and running," stated White. As it worked through the implementation issues, the state found that the StarQuest products offered many benefits. "The client software was easy to install and maintain," states Sallie Cottingham, an IT project manager for OIR assigned to work on site at the direction of Tennessee DHS. "Features such as stored procedures and static SQL support enhanced the performance of accessing data." StarSQL supports the native security, so the state could continue to rely solely on its mainframe security system and anyone with a properly configured desktop system can access the mainframe data. This was important because a number of different users often work with the state's PCs. The client is hidden from the end user, he or she just knows it's easy to use. This also simplifies maintenance chores since state workers do not have to install separate user IDs on each machine. Another plus is StarPipes for Windows NT automatically monitors the number of user versus the number of client licenses the state has purchased. A Growing Need for Data While the connectivity piece is in place, the data warehouse continues to grow as a work-in-progress. The Department of Human Services system tracks a huge volume of information, coming from close to 100 county offices, thousands of employees, and hundreds of thousands of recipients. The state has allocated 10 IT employees full time as well as some part time resources to finishing the project. While no firm date has been set, the department anticipates that the application will be online in early 2000. Three other departments have already begun looking at accessing data for their applications. The Department of General Services' system will monitor agency purchases and inventory. The Department of Safety plans to create Web-based applications that will provide citizens with information like how to renew their driver's license. A help desk application will aid the Office of Information Resources in troubleshooting problems with state government PCs and telephones. In Conclusion "Putting the communications infrastructure in place to support the data warehouse has been challenging but gratifying work," concluded White. "Our users have been very happy with what we done. We'll be able to provide better service to the community because our users have fast, reliable access to mainframe data just as if it resides on any other server on our WAN." Sartor adds, "Many users will be able to do some of their own reports. This will help offload some of the workload responsibilities and contribute to the payback on project development costs." Tennessee DHS is prepared for the rollout of the first phase of the warehouse and has tested and run some basic reports and done some drill down analysis. "We've already gotten benefits from it. It has proven to be quite a bit faster and much easier to use," reports Sartor. "We'll definitely see a cost savings utilizing a single entity to meet many of our reporting requests, and the data is organized in a manner that facilitates statistical analysis and reporting. Sartor concludes, "Our super users in the first group of people will help us determine the next set of data elements we need to add. We'll certainly add more Welfare information, but I believe there will be interest in putting related programs, such as Food Stamps and Medicaid, into the warehouse. That way, we can easily explore relationships among these programs, and more effectively determine how we can best serve the people of Tennessee."