1 / 32

Module 1: Introduction to Data Warehousing and OLAP

Module 1: Introduction to Data Warehousing and OLAP. Overview. Introducing Data Warehousing Defining OLAP Solutions Understanding Data Warehouse Design Understanding OLAP Models Applying OLAP Cubes. Introducing Data Warehousing. Raw Data vs. Business Information OLTP Source Systems

dustin
Download Presentation

Module 1: Introduction to Data Warehousing and OLAP

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. Module 1: Introduction to Data Warehousing and OLAP

  2. Overview • Introducing Data Warehousing • Defining OLAP Solutions • Understanding Data Warehouse Design • Understanding OLAP Models • Applying OLAP Cubes

  3. Introducing Data Warehousing • Raw Data vs. Business Information • OLTP Source Systems • Data Warehouse Characteristics • Data Warehouse System Components

  4. Raw Data vs. Business Information • Capturing Raw Data • Gathering data recorded in everyday operations • Deriving Business Information • Deriving meaningful information from raw data • Turning Data into Information • Implementing a decision support system

  5. OLTP Source Systems • OLTP System Characteristics • Processes real-time transactions of a business • Contains data structures optimized for entries and edits • Provides limited decision support capabilities • OLTP Examples • Order tracking • Customer service • Point-of-sales • Service-based sales • Banking functions

  6. Data Warehouse Characteristics • Provides Data for Business Analysis Processes • Integrates Data from Heterogeneous Source Systems • Combines Validated Source Data • Organizes Data into Non-Volatile, Subject-Specific Groups • Stores Data in Structures that Are Optimized for Extraction and Querying

  7. Data Warehouse User Data Access Data Sources Staging Area Data Marts Data Input Data Access Data Warehouse System Components

  8. Defining OLAP Solutions • OLAP Databases • Common OLAP Applications • Relational Data Marts and OLAP Cubes • OLAP in SQL Server 2000

  9. OLAP Databases • Optimized Schema for Fast User Queries • Robust Calculation Engine for Numeric Analysis • Conceptual, Intuitive Data Model • Multidimensional View of Data • Drill down and drill up • Pivot views of data

  10. Common OLAP Applications • Financial Applications • Reporting • Planning • Analysis • Executive Information Systems • Performance measures • Exception reporting • Sales/Marketing Applications • Booking/billing • Product analysis • Customer analysis • Operations Applications • Manufacturing • Customer service • Product cost

  11. Relational Data Mart OLAP Cube Data Storage Relational Data Structure N-dimensional Data structure Data Content Detailed and Summarized Data Summarized Data Data Sources Relational and Non-relational Sources Relational and Non-relational Sources Data Retrieval Fast Performance for Data Extract Queries Faster Performance for Data Extract Queries Relational Data Marts and OLAP Cubes

  12. OLAP in SQL Server 2000 • Microsoft Is One of Several OLAP Vendors • Analysis Services Is Bundled with Microsoft SQL Server 2000 • Analysis Services Include • OLAP engine • Data mining technology

  13. Understanding Data Warehouse Design • The Star Schema • Fact Table Components • Dimension Table Characteristics • The Snowflake Schema

  14. Employee_Dim EmployeeKey EmployeeID ... Product_Dim Time_Dim ProductKey TimeKey ProductID ... TheDate ... Shipper_Dim Customer_Dim ShipperKey CustomerKey ShipperID ... CustomerID ... The Star Schema Dimension Table Fact Table Sales_Fact TimeKey EmployeeKey ProductKey CustomerKey ShipperKey Sales Amount Unit Sales ...

  15. Measures time_dim 134 1/1/2000 Fact Table Components DimensionTables sales_fact Table customer_dim Foreign Keys 201 ALFI Alfreds customer_key product_key time_key quantity_sales amount_sales product_dim 201 25 134 400 10,789 25 123 Chai The grainof the sales_fact table is defined by the lowest level of detail stored in each dimension

  16. Dimension Table Characteristics • Describes Business Entities • Contains Attributes That Provide Context to Numeric Data • Presents Data Organized into Hierarchies

  17. The Snowflake Schema • Defines Hierarchies by Using Multiple Dimension Tables • Is More Normalized than a Single Table Dimension • Is Supported within Analysis Services

  18. Understanding OLAP Models • OLAP Database Components • OLAP Dimensions vs. Relational Dimensions • Dimension Fundamentals • Dimension Family Relationships • Cube Measures • Relational Data Sources

  19. OLAP Database Components • Numeric Measures • Dimensions • Cubes

  20. REGIONWest CA OREast MA NY REGIONWestEastSTATE REGIONCA WestOR WestMA EastNY East OLAP Dimensions vs. Relational Dimensions OLAP Relational

  21. Dimension Fundamentals

  22. USA North West Oregon WashingtonSouth West California USANorth West Oregon WashingtonSouth West California USANorth West Oregon WashingtonSouth West California USANorth West Oregon WashingtonSouth West California USA North West OregonWashingtonSouth West California USA North West Oregon WashingtonSouth West California USA North West Oregon WashingtonSouth West California USA North West Oregon WashingtonSouth West California Dimension Family Relationships • USA is the parent of North West and South West • North West and South West are children of USA • North West and California are descendants of USA • North West and USA are ancestors of Washington • North West and South West are siblings • Oregon and California are cousins • All are dimension members

  23. Cube Measures • Are the Numeric Values of Principle Interest • Correspond to Fact Table Facts • Intersect All Dimensions at All Levels • Are Aggregated at All Levels of Detail • Form a Dimension

  24. Relational Data Sources • Star and Snowflake Schemas • Are required to build a cube with Analysis Services • Fact Table • Contains measures • Contains keys that join to dimension tables • Dimension Tables • Must exist in same database as fact table • Contain primary keys that identify each member

  25. Applying OLAP Cubes • Defining a Cube • Querying a Cube • Defining a Cube Slice • Working with Dimensions and Hierarchies • Visualizing Cube Dimensions • Connecting to an OLAP Cube

  26. Defining a Cube Atlanta Chicago Market Dimension Denver Grapes Cherries Detroit Melons Products Dimension Apples Q4 Q1 Q2 Q3 Time Dimension

  27. Querying a Cube SalesFact Atlanta Chicago MarketsDimension Denver Grapes Cherries Dallas Melons Apples ProductsDimension Q4 Q1 Q2 Q3 TimeDimension

  28. Defining a Cube Slice Atlanta Chicago Markets Dimension Denver Grapes Cherries Detroit Melons Apples ProductsDimension Q4 Q1 Q2 Q3 Time Dimension

  29. Working with Dimensions and Hierarchies • Dimensions Allow You to • Slice • Dice • Hierarchies Allow You to • Drill Down • Drill Up

  30. Visualizing Cube Dimensions

  31. Connecting to an OLAP Cube

  32. Review • Introducing Data Warehousing • Defining OLAP Solutions • Understanding Data Warehouse Design • Understanding OLAP Models • Applying OLAP Cubes

More Related