Garrett Pilot Data Mart

1 Introduction – The Pilot Data Mart

The Pilot Data Mart will be developed for the Garrett County Partnership for Children, Youth and Families (GCPCF) to analyze patterns of chronic service use in intensive in-home services and to analyze patterns of family violence ^[1]. The GCPCF is acting on behalf of the Garrett County Local Management Board (LMB). An Interagency Management Information System (IMIS) is incorporated in the "Information Technology" section of the LMB's Partnership Agreement with the Maryland Subcabinet for Children, Youth and Families and the Garrett County Commissioners. The LMB decided to prioritize tracking of family violence as a starting point for the IMIS. Data extracted from the Pilot Data Mart will be used to analyze patterns of service use across programs and across agencies.

The Pilot Data Mart will consist of two Online Transaction Processing (OLTP) databases and one Online Analytical Processing (OLAP) database along with the appropriate security, data entry and query interfaces, on separate workstations for each of these databases, in Client-Server configurations.^[2] The database system employed will be Microsoft SQL Server 7.0. Security, which is foremost in this project, will be provided by the NT server and WIN 95/98 workstation operating systems in conjunction with built in security features of SQL Server 7.0. The data input and query interfaces will be developed using either Microsoft Access or Microsoft Visual Basic 6.0. The remainder of this document will detail each piece of the Pilot Data Mart and how all of the pieces will work together as a system.

2 System Specifications

2.1 User Interface Proposal

When designing the user interfaces we will have the option of using either Microsoft Access or Microsoft Visual Basic (VB). I am suggesting that the user interfaces be implemented in VB for the following reasons. Interfaces that use Access have much more memory overhead. To access linked tables in an SQL Server database Access must load the JET database engine as well as whatever SQL engine we use. A Visual Basic front-end can take advantage of the new lightweight ActiveX Data Objects (ADO). These objects require much less memory and operate more efficiently than the objects found in JET.Also, VB provides greater flexibility and programmability to a user interface by allowing programmers to use ActiveX controls. ActiveX controls provide their own properties, methods and events, which can significantly decrease development time. The remaining discussion on user interfaces will be general and feasible in both Access and VB. In the near future a development environment should be chosen. Any changes to the completed user interfaces will be covered under a future maintenance contract.

2.1.A FVC-APP User Interface Proposal, WBS 1.3.2

The first database front-end application to be developed will be the Family Violence Coalition application (FVC-APP). This application will access the Family Violence Coalition database (FVC-DB)(detailed in section 2.3.A). The FVC-APP interface will employ the Client Identification Hashing Module (CIHM) (detailed in section 2.2.A). The CIHM will hash people’s names, birth date, and gender into a 160 bit binary number that will be stored in the FVC-DB. This hashed value will not be used by the Data Mart application (MART-APP detailed in section 2.1.C), but will be used directly by the FVC-APP. Name entries and all name aliases will be hashed, and aliases will be resolved to a single, unique ID number, at the time that they are entered into the FVC-APP.

The user interface of the FVC-APP will consist of numerous forms, designed in either VB or Access. These forms will contain data input and retrieval screens along with the necessary security and administration screens. The following sub-sections will detail the necessary screens.

Security Screens:

System Logon Screen: Due to the sensitive nature of the data being stored in this system, all users will be required to logon prior to accessing any functionality of the program. Once logged on, the program will read the user’s role and make only the appropriate features available.

Data Input Screens:

Data input screens will be organized in a manner that leads the data entry person from one screen to the next in a logical manner. Multiple screens will be used for data entry. When all required data is completed on one screen, the next screen will automatically be displayed. A navigation bar will also be provided to allow users to manually navigate the input screens if desired. Data integrity rules will be applied at this level to filter the majority of entry errors before they travel to the server.

Data Reporting Screens:

There will be a central form from which canned reports will be launched. These reports will be designed ahead of time. Additional canned reports will require edits to the FVC-APP source code. In addition to canned reports, users with the appropriate privileges will have access to a link that will start a report generating application, such as Crystal Reports. This will allow qualified administrators to create reports above and beyond the canned reports coded into the interface.

Administration Screens:

The FVC-APP interface will also include administrative screens that will allow users to add, but not to delete, items to the pick lists on the database.

2.1.B CHRONIC-APP User Interface Proposal, WBS 1.5.2

The next application in the Pilot Data Mart will be the Chronicity Study application (CHRONIC-APP).This application will access the Chronicity Study database (CHRONIC-DB detailed in section 2.3.B). The CHRONIC-APP interface will employ the Client Identification Hashing Module (CIHM detailed in section 2.2.A). The CIHM will hash people’s names birth date, and gender into a 160 bit binary number that will be stored in the CHRONIC-DB. This hashed value will not be used by the data mart application (MART-APP detailed in section 2.1.C), but will be used directly by the CHRONIC-APP. Name entries and all name aliases will be hashed, and aliases resolved to a single, unique ID number, at the time that they are entered into the CRONIC-APP.

The user interface of the CHRONIC-APP will be very similar in concept to the FVC-APP. It too will consist of numerous forms, designed in either VB or Access. These forms will contain data input and retrieval screens along with the necessary security and administration screens. The following sub-sections will detail the necessary screens.

Security Screens:

System Logon Screen: Due to the sensitive nature of the data being stored in

this system, all users will be required to logon prior to accessing any functionality of the program. Once logged on the program will read the user’s role and make only the appropriate features available.

Data Input Screens:

Data input screens will be organized in a manner that leads the data entry person from one screen to the next in a logical manner.Multiple screens will be used for data entry. When all required data is completed on one screen, the next screen will automatically be displayed. A navigation bar will also be provided so users can manually navigate the input screens if desired. Data integrity rules will be applied at this level to filter the majority of entry errors before they travel to the server.

Data Reporting Screens:

There will be a central form from which canned reports will be launched. These reports will be designed ahead of time. Additional canned reports will require edits to the CHRONIC-APP source code. In addition to canned reports, users with the appropriate privileges will have access to a link that will start a report generating application, such as Crystal Reports. This will allow qualified administrators to create reports above and beyond the canned reports coded into the interface.

Administration Screens: The CHRONIC-APP interface will also include administrative screens that will allow users to add, but not delete, items to the pick lists on the database.

2.1.C MART-APP User Interface Proposal, WBS 1.7.2

The analytic client application in the Pilot Data Mart will be the MART-APP application. This application will access the OLAP database called MART. This application will not have data entry screens. It will focus on reporting and querying data that has been aggregated from the FVC, CHRONIC and possibly other databases.User interface features that will be included in the MART-APP are as follows:

The logon screen will, of course, reappear in this application to control access privileges to the MART-APP.
Report printing screens will allow authorized users to print pre-defined reports.
Report generation screens will allow authorized users to define new reports based on any data that is stored in the MART database.
Query screens will allow the users to query and browse the database interactively.

In this instance only the "analytic workstation" (e.g. IMIS) housed at GCPCF will have permission to create NEW reports from the MART-APP. The analytic/IMIS station will also control access to specified reports. Only aggregate data will be reported by the analytic station. When a query initiated by IMIS yields data for an individual or a very small subset of individuals, IMIS (Interagency Management Information System) operating protocols will prohibit release of very narrow subsets of information. Withholding of any potentially identifying data will simply be noted using criteria established by the U.S. Census Bureau. (e.g. "Not shown to avoid disclosure of confidential information.")

2.1.D PDM Web Based Interfaces

In addition to the application-based interfaces, each Pilot Data Mart application will also have a Web-based interface. These Web-based interfaces will look and act just like the application based interfaces. The main difference is that the Web-based interfaces will allow for easier distribution to participating agencies and more efficient upgrading.

In the Web enabled scheme, all functions of the Pilot Data Mart will be contained in a Web browser.No software will be distributed to individuals because it will all reside on an Internet server and will be downloaded to the client browser as needed.Each client browser will be equipped with an authentication certificate that will be verified by the Internet server.Only users with a proper certificate will be able to access the server and PDM software.Once the certificate is authenticated by the server and the user gains access to the PDM, the user will be required to provide valid logon credentials to access the functionality of the PDM.This adds yet another layer of security into the scheme.

In addition to the client-side security measures, the server will also be running 128-bit strong encryption.Users of the Web-based PDM will be required to connect using Secure Socket Layering (SSL) via the secured Hyper Text Transport Protocol (HTTPS). This will ensure that no unencrypted data can be intercepted anywhere along the line.

The Internet server for the web-based PDM will be located at DSS.Special protocols will be observed for accessing the Internet server on the DSS network.Remote dial-in connections will be made through DSS in order to access the web-based PDM.

2.2 Functional Proposal

This system will select from/integrate multiple databases into one smooth running data mart. The database servers will reside in a Windows NT environment while the clients will run in a mixture of NT-95/98 environments.

While the interfaces of the client applications will look very similar, each will carry its own unique functionality. However, one set of functions will be universal to both the FVC-APP and CHRONIC-APP. These functions collectively will be called the Client Identification Hashing Module or CIHM. The following section details the functionality that the CIHM will provide.

2.2.A. The Client Identification Hashing Module (CIHM) Proposal, WBS 1.2.2

In this project the anonymity of each person within the data mart is essential.To address this issue a group of functions, the Client Identification Hashing Module, will be implemented. These functions will be implemented in VB.They will use the government’s SHA-1 hashing algorithm to convert the entrant’s first, middle and last names along with date of birth and sex into a unique 160 bit binary number. Using this algorithm, the slightest change to the input string will cause an entirely different digest to be generated. Since the MART database will contain no names, the hash values generated by the CIHM from within the FVC-APP, CHRONIC-APP and legacy migration applications (FVC-LEG and CHRONIC-LEG as detailed in section 2.3) will be essential to perform accurate reporting on anonymous data. The CIHM will also allow the user to designate that one particular name being entered is the individual’s name as it appears on his/her social security card. All other names entered for that same individual will be identified as aliases of the person by the CIHM. FVC-APP and CHRONIC-APP will resolve all aliases to a single, unique ID number.

The CIHM functions will be encapsulated within a class module. This will ensure efficiency and reusability. As mentioned above, the CIHM will be employed in the FVC-APP and the CHRONIC-APP as well as the legacy migration applications detailed in section 2.3.

2.3 Non-User Interface Proposal: FVC-LEG and CHRONIC-LEG

The non-user interface portion of the Pilot Data Mart system will consist of 3 database applications and two interfacing applications (FVC-LEG and CHRONIC-LEG) that will migrate legacy data from existing FVC and CHRONIC Access databases.

The legacy migration applications will simply read data from the existing FVC and CHRONIC Access databases, use the CIHM to generate hash ids and aliases, resolve aliases if possible, and then insert the data into the respective SQL database. There will be no user interface for these applications. They will be run from the command line and passed parameters detailing the location of the source and target databases.

The next three sections will touch on the system databases.

2.3.A FVC Database Proposal, WBS 1.4.2

The FVC database will be an Online Transaction Processing (OLTP) database designed to the specifications of the GCPCF in consultation with the Garrett County Department of Social Services and implemented in Microsoft SQL Server 7.0. It will contain information input from member agencies of the Garrett County Family Violence Coalition and will be used to log incidents of family violence.

Details on the database structure have been provided by the GCPCF and the GCDSS. In consultation with the FVC Data Management Team, it will be up to Micro-Integration to implement database security and integrity rules, along with query definitions. This will occur in subsequent phases of the development process. For details on the FVC Database structure refer to “Attachment B – Appendix A: FVC Database Structure Requirements” provided by the GCPCF in collaboration with the GCFVC.

2.3.B CHRONIC Database Proposal, WBS 1.6.2

The CHRONIC database will be an Online Transaction Processing (OLTP) database designed to the specifications of the Garrett County Office for Children, Youth and Families and implemented in Microsoft SQL Server 7.0. It will contain information used to study chronic service use in intensive in-home family services.

First draft details on the database structure were provided by the GCPCF. In consultation with the LMB Interagency Management Information System Team (IMIS), it will be up to Micro-Integration to implement database security and integrity rules, along with query definitions. This will occur in subsequent phases of the development process

2.3.C MART Database Proposal, WBS 1.8.2

The MART database will be an Online Analytical Processing (OLAP) database designed to the specifications of the Garrett County Office for Children, Youth and Families and implemented in Microsoft SQL Server 7.0. This database will be periodically added to from data contained in the FVC and CHRONIC databases. The data in this database will be used to analyze the effectiveness of the various programs run by the members of the GCFVC in their efforts to combat family violence and to evaluate the efficacy and effectiveness of various intensive in-home services administered by the LMB.

This database will not be input into from the MART-APP workstation, but rather will be queried heavily to obtain the necessary reports. Because of the complexity of the queries that will be needed, stored procedures in accordance with Microsoft SQL Server 7.0 OLAP Services will be implemented whenever possible to increase efficiency.

Details on the structure of the MART database will be provided by the GC PCF and the LMB Information Technology Consultant, Mark Moynahan. The MART server is located at the GCDSS. On behalf of the FVC and the Local Management Board, Bill Flockhart of the GCDSS will provide technical assistance for the design and implementation of the MART database. Micro-Integration will implement database security and data integrity rules, along with query definitions in subsequent development phases.

2.4 Documentation Proposal

Both physical and digital documentation will be provided to the GCPCF. A copy of every digital document will exist in physical form at the GCPCF office. Documentation will be presented to the GCPCF in the format of the Contract Data Items (CDIs) that are specified in the “Statement of Work” supplied by the GCPCF.

3. External Specifications

3.1 Delivery Proposal

Development of this system will take place, whenever possible, at Micro-Integration in Frostburg. During development, developers at MI will have access to the SQL Server machines housed at DSS in Oakland, as well as identically configured client and server machines in Frostburg. When a deliverable is due we will travel to Oakland and deliver, install, and/or configure it onsite.

3.2 Time Frame Proposal

The Pilot Data Mart is needed as soon as possible. Development has begun and will continue until the product is finished. 6 weeks worth of man-hours have been proposed for the completion of phases 2 and 3.

3.3 Maintenance Contract Proposal

Due to the need for flexibility in the data that is to be collected and reported on in the Data Mart, a maintenance contract should be draw up and agreed upon in the near future. Such a contract will ensure that the Data Mart will be flexible for the future needs of all parties involved.

4. Entry Acquisitions

For phase 3 to proceed a computer with SQL Server 7.0 will be appropriated from MI sources. We will also set up a secure room somewhere in the MI building for development to proceed with dummy data items provided by the IMIS Team.

5. Conclusion

The Garrett County Pilot Data Mart project is already in full swing. As soon as Phase 2 is signed off, work will begin on Phase 3. At that time, development of the working prototype can commence.

^[1] The Garrett County Local Management Board has a Memorandum of Agreement (dated February 9, 1999) with the Garrett County Family Violence Coalition to work collaboratively on an "interagency management information system."

[2] Prior to proceeding with Phase 3 for CHRONIC-APP and CHRONIC-DB, the architecture for these components of the Pilot Data Mart project may be amended upon mutual agreement of GCOCYF and the CONTRACTOR per terms stated in the 6/30/1999 contract.