Using TWIST Ad Hoc Data

1
Using TWIST Ad Hoc Data

• August 13, 2007
• Soon-Yong Choi
• One-Stop Management/
• Analytics FoxPoint

2
What Is Ad Hoc?

• "to this", "for this particular purpose"
• Ad hoc tables offer data from TWC's TWIST
  database for special purposes
• Updated once a day
• Web Reports use similar datasets, but ad hoc
  tables contain more data
• Bottom Line most staff-entered data in TWIST
  Phase IV can be accessed via ad hoc, in one
  simple query

3
What Can You Do with Ad Hoc?

• Download all customer data in one shot, instead
  of looking up TWIST individually
• Make a data query for particular sets of
  customers
• Access customer data for all 28 WDAs for
  comparison
• Access TWIST data not available in Web Reports

4
Query Scenarios

• How many and which WIA Adult customers have
  program detail open but no services?
• A list of all participants in occupational
  training between 10/1/05 and 9/30/06 who have
  exited?
• All Choices customers' monthly participation
  rates for the program year broken down by staff

5
Ad Hoc Tables

• Two types of tables
• TWIST Phase IV data
• Common Measures reporting data
• Structures differ
• Some tables are by unique customer
• Others are duplicated by program detail, service
  activity, or by service month
• Joining, getting unique records important

6
Getting the Right Data

• Filtering criteria
• By WDA, office, customer ID, fund begin/end
  dates, program detail, service begin/end dates,
  exit status
• Multiple records
• Program detail services have multiple records
• TANF tables accumulate records by service month

7
Getting the Right Data

• If target group is known, use a list compiled
  with TWIST Web Report extracts (SSNs)
• Specify criteria larger than needed
• If possible, get all records within one table
• Join and filter after downloading
• Join query operation is sometimes too slow
• Break down a query into smaller ones
• Re-specify query so that all data can be obtained
  from one table

8
Downloading Ad Hoc Data

• InfoMaker
• InfoMaker is used only for downloading data
• InfoMaker can print or export data, but no data
  operations or analysis
• Query has to be absolutly correct
• SPSS
• SPSS can download, clean, export, and analyze
  data
• Tabular statistics and reporting can be done
  easier in Excel

9
Using Lists from Web Reports

• Description file
• Explains all variables contained in the extract
• Shows which ad hoc tables the data come from
• Use 'business rules' to decipher specific data
  rules
• Extract data SSN list
• Use to compile a subset of customers
• Limitation
• One query is limited to 300 or so SSNs
• A long list may be time consuming

10
Ad Hoc Tables TWIST

• Table caseworker_notes
• Customer ID no SSN (needs joining)
• Subject only, no contents
• Creative use of subject
• Tables customer/customer_address
• Common customer info
• Customer ID or SSN query
• Phone number is in customer, but mailing address
  is in customer_address table

11
Ad Hoc Tables TWIST

• Table fund_service
• Level service
• One record per one service entered
• Assoicated program detail ID
• Service begin/end date
• Service code, placement, training, office, staff,
  cip code

12
Ad Hoc Tables TWIST

• Table good_cause
• Customer ID
• Action begin/end month
• Action reason code
• Table ita
• Customer ID
• Funding source code, transaction amount, date,
  type
• Revision date, revision id
• Table placement
• Service associated placement (employer) data

13
Ad Hoc Tables TWIST

• Table program_detail
• All program detail opened in TWIST
• By customer ID or SSN
• Fund begin/end date
• Final completion code/date

14
Table program_detail
15
Table program_detail
16
Table program_detail
17
Table program_detail
18
Table program_detail
19
Ad Hoc Tables TWIST

• Table rio
• All customers with RIO funded program detail
• Packet/doc data, TDCJ/TYC data
• Tables tanf_federal/tanf_summary (LBB)
• Choices monthly performance data
• Duplicated by service month
• 2p families duplicated by DHS case number
• Table wage_detail
• UI wages by SSN
• Quarterly wages shown in TWIST

20
Ad Hoc Tables CM

• Common Measures tables are different from
  existing ad hoc tables
• CM tables can only be joined with CM tables
• CM_Enrollment ? CM participation level
• CM_Service ? CM service level
• CM Enrollment_program_level ? CM program/fund
  level summary data

21
Ad Hoc Tables CM

• cm_enrollment_instance
• One record per CM participation period/instance
• Customer information
• Num/denom flags for various performance measures
• cm_enrollment_pgm_lvl
• Program level reporting data (num/denom
  calculations)
• cm_serv_instance
• Unique record for each CM qualified service
  (within an enrollment instance)
• Enrollment instance ID and program level ID
• CM service month/service begin/end date