Pennsylvania

1
Pennsylvanias 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
• Cant 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

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 lt 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
Additional Information

• Computer Aid, Inc. (CAI)
• Jeffrey McConnell
• 470 Friendship Road
• Harrisburg, PA 17111
• [phone]
• www.compaid.com
• Jeffrey_McConnell_at_compaid.com
• Collective Intelligence (CI)
• Chuck Russell
• 3540 N. Progress Avenue, Suite 200
• Harrisburg, PA 17110
• [phone]
• www.CollectiveIntelligence.com
• ChuckR_at_CollectiveIntelligence.com

• Sharon Spring
• 108 Beechmont Building
• Harrisburg State Hospital
• P.O. Box 2675
• Harrisburg, PA 17105
• [phone]
• sspring_at_state.pa.us