chapter 16 data warehouse technology and management
TRANSCRIPT
Chapter 16Chapter 16Data Warehouse Technology and Management
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Outline Outline
Basic concepts and characteristicsBusiness architectures and applicationsData cube concepts and operatorsRelational DBMS featuresPopulating a data warehouse
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Comparison of Processing Comparison of Processing EnvironmentsEnvironmentsTransaction processing
– Uses operational databases– Short-term decisions: fulfill orders, resolve
complaints, provide staffingDecision support processing
– Uses integrated and summarized data– Medium and long-term decisions: capacity
planning, store locations, new lines of business
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data Warehouse Definition and Data Warehouse Definition and CharacteristicsCharacteristics
A central repository for summarized and integrated data from operational databases and external data sources
Key Characteristics– Subject-oriented– Integrated– Time-variant– Nonvolatile
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data ComparisonData ComparisonCharacteristic Operational
Database Data Warehouse
Currency Current Historical
Detail level Individual Individual and Summary
Orientation Process orientation
Subject orientation
Number of records processed
Few Thousands
Normalization level Mostly normalized
Mostly denormalized
Update level Volatile Nonvolatile (refreshed)
Data model Relational Multidimensional
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Business Architectures and Business Architectures and ApplicationsApplicationsData warehouse projectsTop-down architecturesBottom-up architectureApplications and data mining
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data Warehouse ProjectsData Warehouse Projects
Large efforts with much coordination across departments
Enterprise data model– Important artifact of data warehouse project– Structure of data model– Meta data for data transformation
Top-down vs. bottom-up business architectures
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Two Tier ArchitectureTwo Tier Architecture
Data warehouse
Operationaldatabase
Operationaldatabase
Externaldata source
EDM
Summarizeddata
Transformationprocess
Data warehouseserver
User departments
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Three Tier ArchitectureThree Tier Architecture
Data warehouse
Operationaldatabase
Operationaldatabase
Externaldata source
EDM
Summarizeddata
Transformationprocess
Data warehouseserver
Userdepartments
Data mart
Data mart
Data mart tier
Extractionprocess
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Bottom-up ArchitectureBottom-up Architecture
Operationaldatabase
Operationaldatabase
Externaldata source
Transformationprocess
Userdepartments
Data mart
Data mart
Data mart tier
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
ApplicationsApplications
Industry Key Applications Airline Yield management,
route assessment Telecommunications Customer retention,
network design Insurance Risk assessment, product
design, fraud detection Retail Target marketing,
supply-chain management
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data MiningData Mining
Discover significant, implicit patterns– Target promotions– Change mix and collocation of items
Requires large volumes of transaction dataImportant application for data warehouses
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data Cube Concepts and Data Cube Concepts and OperatorsOperatorsBasicsDimension and measure detailsOperators
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data Cube BasicsData Cube Basics
Multidimensional arrangement of dataUsers think about decision support data as
data cubesTerminology
– Dimension: subject label for a row or column– Member: value of dimension– Measure: quantitative data stored in cells
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data Cube ExampleData Cube Example
Product
Soda Diet Orange Lime soda soda soda
Lo
cati
on California
Utah
Arizona
Washington
Colorado Time 1/1/2003 1/2/2003
….. 12/31/2003
80 110 60 25
40 90 50 30
70 55 60 35
75 85 45 45
65 45 85 60
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Dimension and Measure Dimension and Measure DetailsDetailsDimensions
– Hierarchies: members can have sub members– Sparsity: many cells do not have data
Measures– Derived measures– Multiple measures in cells
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Time Series DataTime Series Data
Common data type in trend analysisReduce dimensionality using time seriesTime series properties
– Data type– Start date– Calendar– Periodicity– Conversion
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Slice OperatorSlice Operator
Focus on a subset of dimensions Set dimension to specific value: 1/1/2003
Location Product Soda Diet soda Lime soda Orange soda
California 80 110 60 25 Utah 40 90 50 30 Arizona 70 55 60 35 Washington 75 85 45 45 Colorado 65 45 85 60
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Dice OperatorDice OperatorFocus on a subset of member valuesReplace dimension with a subset of valuesDice operation often follows a slice
operation
PRODUCT
Soda Diet Orange Lime soda soda soda
LO
CA
TIO
N
Utah 40 90 50 30
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Other OperatorsOther Operators
Operators for hierarchical dimensions– Drill-down: add detail to a dimension– Roll-up: remove detail from a dimension– Recalculate measure values
Pivot: rearrange dimensions
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Operator SummaryOperator Summary
Operator Purpose Description
Slice Focus attention on a
subset of dimensions Replace a dimension with a single member value or with a summary of
its measure values Dice Focus attention on a
subset of member values Replace a dimension with a subset of members
Drill-down Obtain more detail about a dimension
Navigate from a more general level to a more specific level
Roll-up Summarize details about
a dimension Navigate from a more specific level to a more general level
Pivot Present data in a different order
Rearrange the dimensions in a data
cube
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Relational DBMS SupportRelational DBMS Support
Data modelingDimension representationGROUP BY extensionsMaterialized views and query rewritingStorage structures and optimization
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Relational Data ModelingRelational Data Modeling
Dimension table: contains member valuesFact table: contains measure values1-M relationships from dimension to fact
tablesGrain: most detailed measure values stored
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Star Schema ExampleStar Schema Example
CustomerCustIdCustNameCustPhoneCustStreetCustCityCustStateCustZipCustNation
StoreStoreIdStoreManagerStoreStreetStoreCityStoreStateStoreZipStoreNationDivIdDivNameDivManager
SalesSalesNoSalesUnitsSalesDollarSalesCost
ItemItemIdItemNameItemUnitPriceItemBrandItemCategory
TimeTimeNoTimeDayTimeMonthTimeQuarterTimeYearTimeDayOfWeekTimeFiscalYear
ItemSales
CustSales
TimeSales
StoreSales
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Constellation Schema ExampleConstellation Schema Example
CustomerCustIdCustNameCustPhoneCustStreetCustCityCustStateCustZipCustNation
StoreStoreIdStoreManagerStoreStreetStoreCityStoreStateStoreZipStoreNationDivIdDivNameDivManager
SalesSalesNoSalesUnitsSalesDollarSalesCost
ItemItemIdItemNameItemUnitPriceItemBrandItemCategory
TimeTimeNoTimeDayTimeMonthTimeQuarterTimeYearTimeDayOfWeekTimeFiscalYear
ItemSales
CustSales
TimeSales
StoreSales
InventoryInvNoInvQOHInvCostInvReturns
SupplierSuppIdSuppNameSuppCitySuppStateSuppZipSuppNation
SuppInv
ItemInv
StoreInv
TimeInv
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Snowflake Schema ExampleSnowflake Schema Example
CustomerCustIdCustNameCustPhoneCustStreetCustCityCustStateCustZipCustNation
StoreStoreIdStoreManagerStoreStreetStoreCityStoreStateStoreZipStoreNation
SalesSalesNoSalesUnitsSalesDollarSalesCost
ItemItemIdItemNameItemUnitPriceItemBrandItemCategory
TimeTimeNoTimeDayTimeMonthTimeQuarterTimeYearTimeDayOfWeekTimeFiscalYear
ItemSales
CustSales
TimeSales
StoreSales
DivisionDivIdDivNameDivManager
DivStore
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Handling M-N RelationshipsHandling M-N Relationships
Source data may have M-N relationships, not 1-M relationships
Adjust fact or dimension tables for a fixed number of exceptions
More complex solutions to support M-N relationships with a variable number of connections
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Dimension RepresentationDimension Representation
Star schema and variations lack dimension representation
Explicit dimension representation important to data cube operations and optimization
Proprietary extensions for dimension representation
Represent levels, hierarchies, and constraints
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Oracle Dimension RepresentationOracle Dimension Representation
Levels: dimension componentsHierarchies: may have multiple hierarchiesConstraints: functional dependency
relationships
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
CREATE DIMENSION ExampleCREATE DIMENSION ExampleCREATE DIMENSION StoreDim LEVEL StoreId IS Store.StoreId LEVEL City IS Store.StoreCity LEVEL State IS Store.StoreState LEVEL Zip IS Store.StoreZip LEVEL Nation IS Store.StoreNation LEVEL DivId IS Division.DivId HIERARCHY CityRollup ( StoreId CHILD OF City CHILD OF State CHILD OF Nation )HIERARCHY ZipRollup ( StoreId CHILD OF Zip CHILD OF State CHILD OF Nation )HIERARCHY DivisionRollup ( StoreId CHILD OF DivId JOIN KEY Store.DivId REFERENCES DivId )ATTRIBUTE DivId DETERMINES Division.DivNameATTRIBUTE DivId DETERMINES Division.DivManager ;
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
GROUP BY ExtensionsGROUP BY Extensions
ROLLUP operatorCUBE operatorGROUPING SETS operatorOther extensions
– Ranking– Ratios– Moving summary values
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
CUBE ExampleCUBE Example
SELECT StoreZip, TimeMonth, SUM(SalesDollar) AS SumSales FROM Sales, Store, Time WHERE Sales.StoreId = Store.StoreId AND Sales.TimeNo = Time.TimeNo AND (StoreNation = 'USA' OR StoreNation = 'Canada') AND TimeYear = 2002 GROUP BY CUBE (StoreZip, TimeMonth)
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
ROLLUP ExampleROLLUP Example
SELECT TimeMonth, TimeYear, SUM(SalesDollar) AS SumSales FROM Sales, Store, Time WHERE Sales.StoreId = Store.StoreId AND Sales.TimeNo = Time.TimeNo AND (StoreNation = 'USA' OR StoreNation = 'Canada') AND TimeYear BETWEEN 2002 AND 2003 GROUP BY ROLLUP (TimeMonth,TimeYear);
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
GROUPING SETS ExampleGROUPING SETS Example
SELECT StoreZip, TimeMonth, SUM(SalesDollar) AS SumSales FROM Sales, Store, Time WHERE Sales.StoreId = Store.StoreId AND Sales.TimeNo = Time.TimeNo AND (StoreNation = 'USA' OR StoreNation = 'Canada') AND TimeYear = 2002 GROUP BY GROUPING SETS((StoreZip, TimeMonth), StoreZip, TimeMonth, ());
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Variations of the Grouping Variations of the Grouping OperatorsOperatorsPartial cubePartial rollupComposite columnsCUBE and ROLLUP inside a
GROUPIING SETS operation
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Materialized ViewsMaterialized Views
Stored viewPeriodically refreshed with source dataUsually contain summary dataFast query response for summary dataAppropriate in query dominant
environments
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Materialized View ExampleMaterialized View Example
CREATE MATERIALIZED VIEW MV1BUILD IMMEDIATEREFRESH COMPLETE ON DEMANDENABLE QUERY REWRITE ASSELECT StoreState, TimeYear, SUM(SalesDollar) AS SUMDollar1 FROM Sales, Store, Time WHERE Sales.StoreId = Store.StoreId AND Sales.TimeNo = Time.TimeNo AND TimeYear > 2000 GROUP BY StoreState, TimeYear;
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Query RewritingQuery Rewriting
Substitution processMaterialized view replaces references to
fact and dimension tables in a query Query optimizer must evaluate whether the
substitution will improve performance over the original query
More complex than query modification process for traditional views
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Query Rewriting ExampleQuery Rewriting Example-- Data warehouse querySELECT StoreState, TimeYear, SUM(SalesDollar) FROM Sales, Store, Time WHERE Sales.StoreId = Store.StoreId AND Sales.TimeNo = Time.TimeNo AND StoreNation IN ('USA','Canada') AND TimeYear = 2002 GROUP BY StoreState, TimeYear;-- Query Rewrite: replace Sales and Time tables with MV1SELECT DISTINCT MV1.StoreState, TimeYear, SumDollar1FROM MV1, StoreWHERE MV1.StoreState = Store.StoreState AND TimeYear = 2002 AND StoreNation IN ('USA','Canada');
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Storage and Optimization Storage and Optimization TechnologiesTechnologiesMOLAP: direct storage and manipulation
of data cubesROLAP: relational extensions to support
multidimensional dataHOLAP: combine MOLAP and ROLAP
storage engines
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
ROLAP TechniquesROLAP Techniques
Bitmap join indexesStar join optimizationQuery rewritingSummary storage advisorsParallel query execution
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Populating a Data WarehousePopulating a Data Warehouse
Data sourcesWorkflow representationOptimizing the refresh process
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data SourcesData Sources
CooperativeLoggedQueryableSnapshot
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Maintenance WorkflowMaintenance Workflow
PreparationPhase
IntegrationPhase
UpdatePhase
Propagation
Notification
Extraction
Cleaning
Auditing
Transportation
Merging
Auditing
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Data Quality ProblemsData Quality Problems
Multiple identifiersMultiple field namesDifferent unitsMissing valuesOrphaned valuesMultipurpose fieldsConflicting dataDifferent update times
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
ETL ToolsETL Tools
Extraction, Transformation, and LoadingSpecification basedEliminate custom codingThird party and DBMS based tools
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Refresh OptimizationRefresh Optimization
Accounting
UnknownProcesses
ExternalData Sources
InternalData Sources
DataWarehouse
ETLTools
Valid Time Lag
Load Time Lag
Fact andDimensionChanges
PrimarilyDimensionChanges
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Determining the Refresh Determining the Refresh FrequencyFrequencyMaximize net refresh benefitValue of data timelinessCost of refreshSatisfy data warehouse and source system
constraints
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Determining the Level of Historical Determining the Level of Historical IntegrityIntegrityPrimarily an issue for dimension updatesType I: overwrite old valuesType II: version numbers for an unlimited
historyType III: new columns for a limited
history
McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.
SummarySummary
Data warehouse requirements differ from transaction processing.
Architecture choice is important.Multidimensional data model is intuitiveRelational representation and storage
techniques are significant.Maintaining a data warehouse is an
important, operational problem.