Pennsylvania’s Data Cubes for Select URS Tables

1. Pennsylvania’s Data Cubes for Select URS Tables Spring ONE-MHSIP Meeting April 22, 2004 Boston, MA

2. Why Data Cubes? • Eliminate additional work for staff to write and run queries annually. • Less time involved in generating table submissions. • Easily manipulated for other purposes and “carving out” data.

3. Who should develop the cubes? • Contract was under monetary limit that Requests for Proposals (RFPs) are required. • Contractor already on-site working with OMHSAS to develop similar cubes. • Personnel already familiar with our data.

4. Why CAI and CI? • CAI (Computer Aid, Inc.) was already on site managing similar projects for OMHSAS. • CAI was able to manage the sub-contractor. • CI (Collective Intelligence) was currently sub-contracting to CAI on similar projects for OMHSAS.

5. The Old Way • Unless otherwise stated, use data from the bhpa_encounter_fact table for FY01-02 (July 1, 2001 – June 30, 2002) and count the distinct recipients. Ages should be calculated based on the end date of service or June 30, 2002 if there is no end date of service. Omit Service Groups 17 and 12 from HealthChoices data. • Table 1 • To be completed by CMHS. • *************************************************************************************Table 2a • grouped by:  age groups - 0 – 3 • 4 – 12 • 13 - 17 • 18 - 20 • 21 – 64 • 65 – 74 • 75+ • gender • race • Using the BHPA_OMH_PT_DAYS_FACT table • End_date should be in the reporting period • Use SS_Number to get the unduplicated count • grouped by:  age groups - 0 – 3

6. Old Way (Continued) • 4 – 12 • 13 - 17 • 18 - 20 • 21 – 64 • 65 – 74 • 75+ • gender • race • ************************************************************************************* • Table 2b • Can’t fill out yet. • ************************************************************************************* • Table 3a • Omit Service Group = 1 • Also use the QSF • INDP_OF_LIVING_ID = 75 (For homeless) • INDP_OF_LIVING_ID = 99 (For Not Available) • Calculate the total number of distinct recipients for the total row. • Subtract the Homless and Not Available columns from the total column for the Not Homeless row. • *************************************************************************************

7. Old Way (Continued) • Table 3b • Service_grp_id = 1 • grouped by:  age groups - 0 – 17 • 18 - 20 • 21 – 64 • 65+ • gender • race • Same data from SMH data • ************************************************************************************* • Table 4 • Also use the QSF • VOCED_STATUS_ID = 70, 72, 80, 82 (For Employed) • VOCED_STATUS_ID = 74 or 84 (For Unemployed) • VOCED_STATUS_ID = 71, 73,81,83 (For Not in Labor Force) • Calculate the total number of distinct recipients for the total row. • The numbers for the ‘No data Available” row is produced by subtracting the sum of the Employed, Unemployed, Not in Labor Force rows) from the Total row. • grouped by:  age groups - 18 - 20 • 21 – 64 • 65+ • gender

8. Old Way (Continued) • Table 5 • Can you merge or link the two tables to get the unduplicated number of people who received both Meidcaid and non-medicaid services?  Are the numbers currently unduplicated between the two rows, Medicaid and non-medicaid?  The numbers for Medicaid, Non-Medicaid and Both should add up to the total number of unduplicated persons served. • ************************************************************************************* • Table 6 • age < 18 • *Count number where service start was on or before June 30, 2001 and discharge date was on or after • July 1, 2001.  (Column 1) • Number of Admissions during reporting period (Column 2) (NOT disctinct recipients.) • Number of Discharges during reporting period (Column 3) (NOT disctinct recipients.) • Calculate the LOS for residents who were discharged during the reporting period.  Then calcuate the average LOS • (Column 4) and the median (Column 5). • Calculate the LOS for residents who remain in the RTF on June 30, 2002, as of June 30, 2002.  Then calculate the • average LOS (Column 6) and the median LOS (Column 7). • *************************************************************************************

9. New Way

10. New Way (continued)

11. Benefits • More efficient generation of tables in November. • Know data is queried the same from year to year. • Easily adapted to include additional information. • Useful and accessible for other purposes.

12. Computer Aid, Inc. (CAI) Jeffrey McConnell 470 Friendship Road Harrisburg, PA 17111 (717) 651-3042 www.compaid.com Jeffrey_McConnell@compaid.com Collective Intelligence (CI) Chuck Russell 3540 N. Progress Avenue, Suite 200 Harrisburg, PA 17110 (717) 580-4967 www.CollectiveIntelligence.com ChuckR@CollectiveIntelligence.com Sharon Spring 108 Beechmont Building Harrisburg State Hospital P.O. Box 2675 Harrisburg, PA 17105 (717) 346-1097 sspring@state.pa.us Additional Information