Download What is a Data Warehouse PDF

TitleWhat is a Data Warehouse
File Size375.0 KB
Total Pages46
Table of Contents
                            What is a Data Warehouse?
	Figure 1-1 Contrasting OLTP and Data Warehousing Environments
	Architecture of a Data Warehouse with a Staging Area
	Architecture of a Data Warehouse with a Staging Area and Data Marts
	Logical Design :  Physical Design

Creating a Logical Design
Data Warehousing Schemas
	Star Schemas
		Figure 2-1 Star Schema
	Other Schemas
Another schema that is sometimes useful is the snowflake schema, which is a star schema with normalized dimensions in a tree structure.
Data Warehousing Objects
	Typical Data Warehousing Objects
	Fact Tables
	Dimension Tables
		Hierarchies
			Levels
			Level Relationships
		Typical Dimension Hierarchy
	
	Unique Identifiers
	Relationships
	When to Implement Parallel Execution
	Partition Pruning
		Partitioning Methods
			Hash Partitioning
			List Partitioning : discrete values
		Range and Hash Partitions of a Composite Table
	Partition Pruning
		Adding a Partition to a Range-Partitioned Table
		Adding a Partition to a Hash-Partitioned Table
	Dropping Partitions
		Dropping a Table Partition
	Exchanging Partitions
	Splitting and Merging Partitions
	Truncating Partitions
Indexes
	Executing a Query Using Bitmap Indexes
	Bitmap Indexes and Nulls
	Example 6-2 Bitmap Index
	Bitmap Join Indexes
	Bitmap Join Indexes
		Example 6-3 Bitmap Join Index: Example 1
			Table 6-2 Sample Bitmap Join Index
		Example 6-4 Bitmap Join Index: Example 2
		Example 6-5 Bitmap Join Index: Example 3
		Example 6-6 Bitmap Join Index: Example 4
		Bitmap Join Index Restrictions
	Typical Data Warehouse Integrity Constraints
		RELY Constraints
		Integrity Constraints and Parallelism
		Build Methods
			Refresh Modes
			Refresh Options
		Materialized View Logs
		Using Oracle Enterprise Manager
	Enabling Query Rewrite
	Types of Materialized Views
		Example 8-1 Creating a Materialized View: Example 1
		Creating a Materialized View: Example 2
		Materialized View Containing Only Joins
	Registering Existing Materialized Views
	Partitioning and Materialized Views
		Partitioning a Materialized View
		Partitioning a Prebuilt Table
	Overview of Query Rewrite
		Transparent Query Rewrite
		The Query Rewrite Process
	Enabling Query Rewrite
	How Oracle Rewrites Queries
		Text Match Rewrite Methods
			Text Match Capabilities
		General Query Rewrite Methods
			Materialized View Types and General Query Rewrite Methods
			Materialized View Restrictions
			General Query Rewrite Restrictions
	ETL
	Extraction, Transformation, and Loading
		Logical Extraction Methods
	Physical Extraction Methods :
		Online Extraction
		Offline Extraction
		Extraction Via Distributed Operations
			Partitioning
			Triggers
		Transformation Flow
			Multistage Data Transformation
			Pipelined Data Transformation
	Loading Mechanisms
	Transformation Mechanisms
		When to Use Merge
		Performance will improve because you Merge Operation Using SQL in Oracle9i
		Transformation Using Multitable INSERT :
		Transformation Using Table Functions
			Table Function Example
			Pipelined Parallel Transformation with Fanout
		Pivoting Scenarios
			Interoperation Parallelism and Dynamic Partitioning
                        
Document Text Contents
Page 1

What is a Data Warehouse?

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing.
It usually contains historical data derived from transaction data, but it can include data from other sources. It separates
analysis workload from transaction workload and enables an organization to consolidate data from several sources.

Figure 1-1 Contrasting OLTP and Data Warehousing Environments

Architecture of a Data Warehouse with a Staging Area

Page 2

Architecture of a Data Warehouse with a Staging Area and Data Marts

Logical Versus Physical Design in Data Warehouses

To create the logical and physical design for the data warehouse. You then define:

• The specific data content

• Relationships within and between groups of data

• The system environment supporting your data warehouse

• The data transformations required

• The frequency with which data is refreshed

Page 23

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#42277

Page 45

SELECT product_id, customer_id, weekly_start_date+3, time_id,
sales_web amount_sold FROM sales_input_table
UNION ALL
SELECT product_id, customer_id, weekly_start_date+4, time_id,
sales_thu amount_sold FROM sales_input_table
UNION ALL
SELECT product_id, customer_id, weekly_start_date+5, time_id,
sales_fri amount_sold FROM sales_input_table
UNION ALL
SELECT product_id, customer_id, weekly_start_date+6, time_id,
sales_sat amount_sold FROM sales_input_table);

Example of Oracle9i Pivoting :

INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount_sold)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;

Page 46

Interoperation Parallelism and Dynamic Partitioning

Similer Documents