1 / 33

Fall VAMAP Conference November, 2004

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.

JasminFlorian
Télécharger la présentation

Fall VAMAP Conference November, 2004

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 SCHEV’s 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 institution’s data file to SQL server. • Load institution’s 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 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.

  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 institution’s file.

  19. 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.

  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 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.

  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 institution’s database.

  26. DisplaysDisplay Log Table • While the display programs run, information pertaining to each program’s 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 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.

  29. Live Site

  30. Fall VAMAP ConferenceNovember, 2004 SCHEV Biopsy of a Data Warehouse Marina Moschos Wendell Pai Sumi Shahid

More Related