Fall VAMAP Conference November, 2004

1
Fall VAMAP ConferenceNovember, 2004

• SCHEV
• Biopsy of a Data Warehouse

Marina Moschos Wendell Pai Sumi Shahid
2
Outline

• Phase 1 Building a data warehouse
  infrastructure.
• Phase 2 Designing and implementing a web-based
  collection system.
• Phase 3 Providing data accessibility to end
  users.

3
Phase 1

• Goal 1 Set up databases and data warehouse
  structure
• Goal 2 Port existing data from SAS system to
  SQL database
• Focus on the needs of users and the flow of data

4
Phase 1

• Building a Data Warehouse Infrastructure

5
Flow of Data
6
User Needs

• Greater convenience dynamic reports, GUIs,
  online accessibility
• Data suppliers need to retain access to all
  existing functions
• Data end users need to retain access to all
  existing reports

7
Legacy System Data Migration

• SAS data sets converted to SQL with data elements
  unchanged
• SAS formats (data dictionary) converted to SQL
  Support Tables
• Existing SAS programs converted to SQL views

8
Data Staging Area
9
Institutional Data

• Web-based front-end interface
• Each institution has a secure site on the
  webserver
• Each institution has an SQL database in the SCHEV
  staging area
• SCHEV has its own SQL administrative database to
  track file processing

10
Phase 2

• Web-based Data
• Collection System

11
Phase 2

• Next phase in building SCHEVs data warehouse
• Allows the institutions via the web to -
• submit unit record data
• edit the data
• create displays
• lock data file

12
Phase 2

• Upload

13
Upload

• What occurs when an institution moves its data
  to the SCHEV system -
• A Data Transformation Services (DTS) package is
  used to
• Create the target table for the particular data
  file the institution is uploading.
• Load institutions data file to SQL server.
• Load institutions data file into the created
  target table.

14
Upload DTS Package Design View

• The collection of steps and tasks which define
  the order of upload execution and all of the
  details of the transformation.

15
Phase 2

• Edits

16
Edits

• Edit Process
• Recoded edits from SAS to SQL.
• Stored edits in a separate table for each data
  file.
• Built a DTS package for each data file to run
  edits against the institutions file.
• Created an Error Report table in every
  institutions database in SQL to insert
  error/warning codes and values that were
  encountered by the data file edits.
• Built an Edit Log table in every institutions
  database in SQL to track whether the data file
  edits ran successfully or not.
• Created an Error Summary web page for every
  institution that
• summarizes the error information from the
  Error Report table.

17
Storing Edits

• Each data file has its own edit table that
  stores the
• error code, edit, order value, field name, and
  report
• year.

18
EditsDTS Package Design View

• Built DTS packages for each data file to run
  edits against the institutions file.

19
EditsError Report Table

• When the edits run against the institutions
  file, information
• pertaining to errors or warnings in the data
  are inserted
• into an error report table.

20
EditsEdit Log Table

• While each edit executes, information pertaining
  to the
• status of the edit run is inserted into an edit
  log table.

21
Phase 2

• Displays

22
Displays

• Display Process
• Transported display programs from SAS coding to
  SQL coding.
• Created a table in the Institution Management
  database to store all the display programs for
  every data file.
• Designed a DTS package for each data file to run
  the display programs that generate aggregated
  data.
• Built individual stock display tables in every
  institutions database to insert the aggregated
  data.
• Created a Display Log table in every
  institutions database to track whether a display
  program ran successfully or not.
• Created web pages for each display so the
  institutions could view the aggregated data.

23
Storing Display Programs

• This table was built in the institution
  management
• database to store all the display programs by
  data file
• and display name.

24
DisplayDTS Package Design View

• Built DTS packages for each data file to run
  display programs.

25
Display Tables

• A list of display tables is located in each
  institutions database.

26
DisplaysDisplay Log Table

• While the display programs run, information
• pertaining to each programs success or
  failure is inserted
• into the display log table.

27
Phase 2

• Locking

28
Locking File

• Locking Process
• After an institution creates their data file
  displays, they can lock their file by pressing a
  Lock File button.
• An automated email is sent to SCHEV Research
  staff notifying us the institution has locked a
  data file for a given year.
• Information pertaining to the institutions
  locked data file (i.e. institution name, data
  file, report year) is inserted into a File Lock
  table.
• SCHEV Research moves the institutions data
• file into a permanent unit record table,
  located
• in the unit record database.

29

• Live Site

30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
Fall VAMAP ConferenceNovember, 2004

• SCHEV
• Biopsy of a Data Warehouse

Marina Moschos Wendell Pai Sumi Shahid