1 / 30

PGA Tour Data Warehouse

PGA Tour Data Warehouse. Team Members Frank Paladino Aravind Yeluripiti. Project Goals. The Professional Golfers Association ( PGA) is the organizer of the men's professional golf tour primarily in the United States and North America.

calais
Download Presentation

PGA Tour Data Warehouse

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. PGA Tour Data Warehouse Team Members Frank Paladino AravindYeluripiti

  2. Project Goals • The Professional Golfers Association (PGA) is the organizer of the men's professional golf tour primarily in the United States and North America. • It organizes most of the events on the flagship annual series of tournaments also known as the PGA Tour. • The goal of this project is to build a data warehouse using data collected from the PGA Tour website.

  3. Task Delegation and work flow

  4. Source Databases • The source databases contain data related to PGA Players, Tournaments, and Results. • For each subject area, research was done to determine the appropriate data model for database. • After completing the data model, data was loaded into the source databases. • In order to obtain relevant data for our project, we extracted data from the PGA Tour website into comma delimited files and imported them into our source databases. • The “LOAD DATA INFILE ‘[filename]’ FIELDS TERMINATED BY ‘,’ command was used to load the data from files into the source databases.

  5. Source Databases • Player Database • The player database contains data related to the players profile and demographic information. • Tournament Database • The Tournament database contains detailed data related to tournaments, venues, and results of tournaments. • Statistics Database • The statistics database contains data that measures a players tour performance.

  6. Raw data - sample • Dustin johnson • Height: 6 ft, 4 in • Weight: 190 lbs • Birthday: 06/22/1984 • College:Coastal Carolina University • Turned Pro:2007 • Birthplace:Columbia, South Carolina • Residence:Myrtle Beach, South Carolina • Steven stricker • Height:6 ft, 0 in • Weight:190 lbs • Birthday:02/23/1967 • College:University of Illinois • Turned Pro:1990 • Birthplace:Edgerton, Wisconsin • Residence:Madison, Wisconsin

  7. Players Database

  8. Players Database Snapshots Locations Table Players Table

  9. Players Database Snapshots PlayerPersonalInfo Table

  10. Players Database snapshots

  11. Statistics Database

  12. Statistics Database Snapshots AdvancedStats Table BasicStats Table

  13. Statistics Database Snapshots TourStats Table

  14. Tournament Database Schema

  15. Tournament Database Snapshots CourseDetails Table PlayerTourney Table

  16. Tournament Database Snapshots Venues Table Tourneys Table

  17. Tournament Database Snapshots Scorecard Table

  18. PGA Tour Warehouse Database The PGA Tour Warehouse integrates data from the three source databases to a single schema that can be used to query player statistical, tournament results, and player performance. Our approach separated source data into: • Qualitative data – dimensions – data used to qualify or filter the data • Quantitative data - (facts/measures) – data used to measure performance

  19. Implementation of Data Warehouse The SQL implementation of data warehouse dimension and fact tables used a similar approach to that described below. For each target table to be loaded, a stored procedure was used to extract source data into a database cursor, compare the key fields to the target table, and if the row does not already exists, inserts the data into the target table. Example shown below was used to load the LocationDimension table BEGIN DECLARE done INT DEFAULT 0; DECLARE id varchar(10); DECLARE city1 varchar(45); DECLARE state1 varchar(2); DECLARE country1 varchar (45); DECLARE cur1 CURSOR FOR SELECT DISTINCT idLocation, city, state, country FROM tournament.venue; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; -- open database cursor cur1_loop: LOOP FETCH cur1 INTO id, city1, state1, country1; -- MySQL Bug 28227 (variable names cannot be same as column names) IF (done=1) THEN LEAVE cur1_loop; END IF; IF NOT EXISTS (SELECT * FROM pgatourwarehouse.locationdimension WHERE idLocation = id) THEN INSERT INTO pgatourwarehouse.locationdimension VALUES (id, city1, state1, country1, CURDATE()); END IF; END LOOP; SELECT 1 FROM tournament.venue LIMIT 1; -- MySQL Bug 60840 (need a statement using a table to avoid throwing warning 1329) CLOSE cur1; -- close the database cursor END

  20. Implementation of Data Warehouse Last, a call wrapper.sql script was used to call stored procedures in specific order so that referential integrity constraints were honored. • CALL loadLocationDimension(); • CALL loadLocationDimension2(); • CALL loadVenueDimension(); • CALL loadPlayerDimension(); • CALL loadTournamentDimension(); • CALL loadPlayerTourneyRoundFacts(); • CALL loadPlayerFacts(); • CALL loadPlayerTourneyFacts();

  21. PGA tour Warehouse Fact constellation

  22. Data Warehouse Snapshots LocationDimension Table

  23. Data Warehouse Snapshots PlayerDimension Table

  24. Data Warehouse Snapshots TournamentDimension Table VenueDimension Table

  25. Data Warehouse Snapshots PlayerFacts Table

  26. Data Warehouse Snapshots PlayerTourneyFacts Table

  27. Data Warehouse Snapshots PlayerTourneyRoundFacts Table

  28. Is PGA tour, a warehouse? • “A data warehouse is asubject-oriented, integrated, time-variant, and nonvolatilecollection of data in support of management’s decision-making process.”—W. H. Inmon • Subject-oriented • Players, tournaments • Integrated • raw data -> databases -> warehouse • Time variant • implicit: seasons (2012,2011) • explicit: each table time stamped • Nonvolatile • Initial loading and access of data

  29. Is PGA tour, a warehouse?...contd • Management decision making (querying) • How many players are from so-so city/state/country? • How many tournaments are being held in certain state in a specific month? • Etc..

More Related