1、Modeling the Data Warehouse,Overview,Project Management (Methodology, Maintaining Metadata),Defining DW Concepts & Terminology,Planning for a Successful Warehouse,Analyzing User Query Needs,Choosing a Computing Architecture,Modeling the Data Warehouse,Planning Warehouse Storage,ETT (Building the War
2、ehouse),Meeting a Business Need,Supporting End User Access,Managing the Data Warehouse,Objectives,After completing this lesson, you should be able to do the following: List generic phases for modeling a data warehouse List the components of a warehouse data model Identify tools available for warehou
3、se modeling,Data Warehouse Database Design Phases,1. Defining the business model (conceptual model) 2. Creating the dimensional model (logical model) 3. Modeling summaries 4. Creating the physical model,1,2, 3,4,Performing Strategic Analysis,Performing strategic analysisCreating the business (concep
4、tual) model,Phase 1: Defining the Business Model,Creating the Business Model,Performing strategic analysisCreating the business (conceptual) model Defining business requirements Identifying the business measures Identifying the dimensions Identifying the grain Identifying the business definitions an
5、d rules Verifying data sources,Phase 1: Defining the Business Model,Business Requirements Drive the Design Process,Existing metadata,Production ERD model,Business requirements,Research,Primary input,Other inputs,Nonrelational legacy systems,Identifying Measures and Dimensions,Balance Units Sold Cost
6、 Sales,The attribute is perceived as a constant or discrete value:,The attribute varies continuously:,Measures,Dimensions,Description Location Color Size,Determining Granularity,YEAR?,QUARTER?,MONTH?,WEEK?,DAY?,Identifying Business Rules,Product Type Monitor Status PC 15 inch New Server 17 inch Rebu
7、ilt19 inch CustomNone,Location Geographic proximity 0 - 1 miles 1 - 5 miles 5 miles,Store Store District Region,Time Month Quarter Year,Creating the Dimensional Model,Phase 2: Creating the Dimensional (Logical) Model,Identify fact tables Translate business measures into fact tables Analyze source sy
8、stem information for additional measures Identify base and derived measures Document additivity of measuresIdentify dimension tablesLink fact tables to the dimension tablesCreate views for users,Dimension Tables,Dimension tables have the following characteristics: Contain textual information that re
9、presents the attributes of the business Contain relatively static data Are joined to a fact table through a foreign key reference,Fact Tables,Fact tables have the following characteristics: Contain numeric measures (metrics) of the business May contain summarized (aggregated) data May contain date-s
10、tamped data Are typically additive Have key value that is typically a concatenated key composed of the primary keys of the dimensions Joined to dimension tables through foreign keys that reference primary keys in the dimension tables,Dimensional Model (Star Schema),Dimension tables,Fact table,Star S
11、chema Model,Central fact table Radiating dimensions Denormalized model,Store Table Store_id District_id .,Item Table Item_id Item_desc .,Time Table Day_id Month_id Period_id Year_id,Product Table Product_id Product_desc ,Sales Fact Table Product_id Store_id Item_id Day_id Sales_dollars Sales_units .
12、,Star Schema Model,Easy for users to understand Fast response to queries Simple metadata Supported by many front end tools Less robust to change Slower to build Does not support history,Snowflake Schema Model,Time Table Week_id Period_id Year_id,Dept Table Dept_id Dept_desc Mgr_id,Mgr Table Dept_id
13、Mgr_id Mgr_name,Product Table Product_id Product_desc,Item Table Item_id Item_desc Dept_id,Sales Fact Table Item_id Store_id Sales_dollars Sales_units,Store Table Store_id Store_desc District_id,District Table District_id District_desc,Snowflake Schema Model,Direct use by some tools More flexible to
14、 change Provides for speedier data loading May become large and unmanageable Degrades query performance More complex metadata,Country,State,County,City,Using Summary Data,Provides fast access to precomputed data Reduces use of I/O, CPU, and memory Is distilled from source systems and precalculated s
15、ummaries Usually exists in summary fact tables,Phase 3: Modeling summaries,Designing Summary Tables,Units Sales($) Store,Product ATotal Product BTotal Product CTotal,Average Maximum,Total Percentage,Summary Tables Example,SALES FACTS Sales$ Region Month 10,000 North Jan 99 12,000 South Feb 99 11,000
16、 North Jan 99 15,000 West Mar 99 18,000 South Feb 99 20,000 North Jan 99 10,000 East Jan 99 2,000 West Mar 99,SALES BY MONTH/REGION Month Region Tot_Sales$ Jan 99 North 41,000 Jan 99 East 10,000 Feb 99 South 40,000 Mar 99 West 17,000,SALES BY MONTH Month Tot_Sales Jan 99 51,000 Feb 99 40,000 Mar 99
17、17,000,Summary Management in Oracle8i,Product,Region,Time,Sales summary,City,Sales,State,Using Time in the Data Warehouse,The Time Dimension,Where should the element of time be stored?,Time dimension,Sales fact,Time is critical to the data warehouse. A consistent representation of time is required f
18、or extensibility.,Creating the Physical Model,Phase 4: Creating the Physical Model,Translate the dimensional design to a physical model for implementation Define storage strategy for tables and indexes Perform database sizing Define initial indexing strategy Define partitioning strategy Update metad
19、ata document with physical information,Physical Model Design Tasks,Define naming and database standards Perform database sizing Design tablespaces Develop initial indexing strategy Develop data partition strategy Define storage parameters Set initialization parameters Use parallel processing,Tools w
20、ith a GUI enable definition, modeling, and reporting Avoid a mix of modeling techniques caused by: Development pressures Developers with lack of knowledge No strategy Determine a strategy Write and publish formally Make available electronically,Using Data Modeling Tools,GUI Tool Interface,Summary,Th
21、is lesson discussed the following topics: Creating a business model Creating a dimensional model Modeling the summaries Creating a physical model,Business model,Physical model,Dimensional model,Practice 7-1 Overview,This practice covers the following topics: Specifying true or false to a series of statements Completing a series of sentences accurately Practicing identifying a simple business model,
