1 / 30

Building a Data Warehouse

Building a Data Warehouse. By: Chuck Richardson Kathy Anderson. Learn how Santa Fe Community College selected the Extract, Transformation and Load (ETL) and Business Intelligence (BI) tools for the College’s new enterprise Decision Support System.

parley
Download Presentation

Building a 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. Building a Data Warehouse By: Chuck Richardson Kathy Anderson Learn how Santa Fe Community College selected the Extract, Transformation and Load (ETL) and Business Intelligence (BI) tools for the College’s new enterprise Decision Support System. In addition to data selection and modeling for the Data Warehouse, this presentation will take you from developing Microsoft’s SQL Server data transformations to designing Crystal Decisions dynamic reports for Web-based distribution.

  2. Introduction of Presenters • Chuck Richardson16 Years in the IT field 5 Years DBA experience in Oracle, Sybase and SQL Server 1 ½ Years at Santa Fe Community College • Kathy Anderson26 Years in the IT field15 Years at Central Florida Community College11 Years VSE DB2 experience 1 Year at Santa Fe Community College KK

  3. Title III Grant • February 2000 - SFCC’s Title III ProposalGoal: Improved AA student performance, persistence and graduation. • October 2000 - Title III grant awarded to SFCC • SFCC’s Five-Year Plan to build the Data Warehouse and Decision Support System is fully supported by: The SFCC Administration College and Title III grant funded resources KC

  4. Data Warehouse Tool Evaluation SFCC spent 6 months evaluating the components and tools needed to build a Data Warehouse. • Server Operating System • Database Engine • Extract, Transform and Load (ETL) • Business Intelligence (BI) CC

  5. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation Server Operating Systems considered - • Linux • Windows NT Server • Windows 2000 Server • Windows 2000 Advanced Server CC

  6. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation • Oracle • IBM DB2 • Microsoft SQL Server Database Engines considered - CC

  7. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation Extract, Transform and Load (ETL) Definition Three separate functions combined into one development tool: • Extract – Reads data from a specified source and extracts a desired subset of data. • Transform – Uses rules or lookup tables, or creating combinations with other data, to convert source data to the desired state. • Load – Writes the resulting data to a target database. CC

  8. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation Extract, Transform and Load (ETL) Tools considered: • IBM DB2 Warehouse Manager • Informatica PowerMart • Microsoft Data Transformation Services (DTS) CC

  9. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation CK

  10. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation Business Intelligence (BI) Definition - BI represents a broad category of applications and technologies for providing access to data to help enterprise users make better business decisions. BI includes: Decision Support Systems Forecasting Reporting Data Mining Statistical Analysis Online Analytical Processing (OLAP) KK

  11. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation Business Intelligence (BI) Tools considered: • Brio • Business Objects • Crystal Decisions KK

  12. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation KK

  13. Server Operating System Database Engine Extract Transform & Load (ETL) Business Intelligence (BI) Data Warehouse Tool Evaluation Selected by SFCC…… Server OS Windows 2000 Advanced Server Database Engine Microsoft SQL Server ETL Microsoft Data Transformation Services (DTS) BI Crystal Decisions KK

  14. Data Warehouse SFCC Sources of Data College personnel need direct access to decision support information from a variety of data sources: • IBM VSE VSAM and DB2 operational dataStudent Records Financial Aid Human Resources Finance • State Database submitted data • State Common Course Numbering data • Student assessment data • Student surveys • Network Server logs KK

  15. SFCC Data Warehouse Data Extract Schedule “Snapshots” of student and SDB data are - • Extracted from the VSE mainframe500+ data elements from more than 50 sources • Transformed Six target “snapshot” tables - All rows contain the same Snapshot Timestamp Add new and modified data to three history tables • Loaded into the Data Warehouse The process takes several hours. It is run during the Spring, Summer and Fall terms at - End of Drop/Add Mid Term End of Term KK

  16. Data WarehouseStudent Snapshot Data The Data Warehouse snapshot data is designed to be: • Consistent Captured at comparable times each term Data will not be updated • Flexible Ability to handle information needs not yet defined • Easy to accessData is in a single database Denormalized, redundant data – fewer tables to join • ExpandableNew data elements may be added in the future KC

  17. Data Warehouse SFCC Servers • Two Dell PowerEdge 6400 servers 1 server for Development 1 server for Production • Both servers currently contain: 2 Gig Memory 100 Gig Storage Dual Processors • Future expansion includes: Four Processors Storage Array CC

  18. Data Warehouse SFCC Server Components IBM HTTP Server SQL Server 2000 / DTS / Analysis Services DB2 Connect / CM2 / ODBC FTP Server E-mail notification via Collaborative Data Objects (CDO) CC

  19. CC

  20. Data Warehouse Data Transformation Services CC

  21. Data Warehouse Data Transformation Services C

  22. Data WarehouseBI Tool – Crystal Decisions Report Development - • Crystal ReportsCreate compelling views of data without relying on IT • Crystal AnalysisOLAP reporting with multi-dimensional cubes Web Delivery - • Crystal EnterpriseWeb-based delivery of information via ePortfolio Full report drill-down capabilities K

  23. Data WarehouseCrystal Reports SFCC Crystal Reports Developers - • 25 Crystal Reports licenses • 1 developer per Division within the college • Commitment, Aptitude • Job Description changed to include report development • On-site 2-day Crystal Report Design training classes • “Camp Crystal” training • ODBC connection to Data Warehouse • SFCC Crystal Users Group • Publish reports to Crystal Enterprise KK

  24. Cross-Tab and Chart Crystal Report KK

  25. Cross-Tab and Chart Crystal Report KK

  26. World Map Crystal Report KK

  27. Report Drill-Down Crystal Report K

  28. Data WarehouseBI Tool – Crystal Decisions Crystal Enterprise includes: • Crystal Management Console User, content and server management • ePortfolio A web interface for end users to view, schedule, and monitor published reports User authentication – LDAP, NT or Enterprise • Report Application Server (RAS) Allows users to modify reports K

  29. Crystal Enterprise ePortfolio KK

  30. SFCC Data Warehouseand Decision Support System Future Development Plans include: • Automatic scheduling of reports • Build OLAP cubes with Microsoft Analysis Service • Develop Crystal Analysis Reports • Daily ETL of Enrollment and Financial data • Upgrade to Crystal 9.0 Questions?chuck.richardson@sfcc.edu kathy.anderson@sfcc.edu KK

More Related