fall vamap conference november 2004
Download
Skip this Video
Download Presentation
Fall VAMAP Conference November, 2004

Loading in 2 Seconds...

play fullscreen
1 / 33

Fall VAMAP Conference November - PowerPoint PPT Presentation


  • 510 Views
  • Uploaded on

Fall VAMAP Conference November, 2004. SCHEV Biopsy of a Data Warehouse. Marina Moschos Wendell Pai Sumi Shahid. Outline. Phase 1: Building a data warehouse infrastructure. Phase 2: Designing and implementing a web-based collection system.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Fall VAMAP Conference November' - JasminFlorian


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
fall vamap conference november 2004

Fall VAMAP ConferenceNovember, 2004

SCHEV

Biopsy of a Data Warehouse

Marina Moschos

Wendell Pai

Sumi Shahid

outline
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.
phase 1
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
phase 11
Phase 1

Building a Data Warehouse Infrastructure

user needs
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
legacy system data migration
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
institutional data
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
phase 2
Phase 2

Web-based Data

Collection System

phase 21
Phase 2
  • Next phase in building SCHEV’s data warehouse
  • Allows the institutions via the web to -
      • submit unit record data
      • edit the data
      • create displays
      • lock data file
phase 22
Phase 2

Upload

upload
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 institution’s data file to SQL server.
      • Load institution’s data file into the created target table.
slide14

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.
phase 23
Phase 2

Edits

edits
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 institution’s file.
  • Created an Error Report table in every institution’s 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 institution’s 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.
slide17

Storing Edits

  • Each data file has its own edit table that stores the

error code, edit, order value, field name, and report

year.

slide18

EditsDTS Package Design View

  • Built DTS packages for each data file to run edits against the institution’s file.
slide19

EditsError Report Table

  • When the edits run against the institution’s file, information

pertaining to errors or warnings in the data are inserted

into an error report table.

slide20

EditsEdit Log Table

  • While each edit executes, information pertaining to the

status of the edit run is inserted into an edit log table.

phase 24
Phase 2

Displays

displays
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 institution’s database to insert the aggregated data.
      • Created a Display Log table in every institution’s 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.
slide23

Storing Display Programs

  • This table was built in the institution management

database to store all the display programs by data file

and display name.

slide24

DisplayDTS Package Design View

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

Display Tables

  • A list of display tables is located in each institution’s database.
slide26

DisplaysDisplay Log Table

  • While the display programs run, information

pertaining to each program’s success or failure is inserted

into the display log table.

phase 25
Phase 2

Locking

locking file
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 institution’s locked data file (i.e. institution name, data file, report year) is inserted into a File Lock table.
    • SCHEV Research moves the institution’s data
    • file into a permanent unit record table, located
    • in the unit record database.
fall vamap conference november 20041

Fall VAMAP ConferenceNovember, 2004

SCHEV

Biopsy of a Data Warehouse

Marina Moschos

Wendell Pai

Sumi Shahid

ad