Transcript Database Management Systems

Data Warehousing
Databases support:
• Transaction Processing Systems
– operational level decision
– recording of transactions
• Decision Support Systems
– tactical and strategic decision making
– analysis of historical records
Can one database support both?
DSS
RDBMS
TPS
Can one database support both?
DSS
RDBMS
TPS
• low concurrency
• high concurrency
• large reads
• small transactions
• significant aggregation
• limited aggregation
Yes… but at a cost in performance.
The Solution…
TPS
DSS
Production
Database
(OLTP)
Data
Warehouse
Extract,
Transport & Transformation
Load
OLTP vs DW Characteristics
OLTP Database
Data Warehouse
High Read/Write Concurrency
Primarily Read Only
Highly Normalized
Highly Denormalized
Limited Transaction History
Massive Transaction History
Very Detailed Data
Detailed and Summarized Data
Limited External Data
Significant External Data
Data Marts (3-tier approach)
External Data
Sources
Production
Database
(OLTP)
Data
Mart
A
Data
Warehouse
ETL
Transformation
& Limitation
Data
Mart
B
Data
Mart
C
DSS
DSS
DSS
Data Marts (bottom-up approach)
External Data
Sources
Data
Mart
A
ETL
Production
Database
(OLTP)
ETL
External Data
Sources
Data
Mart
B
DSS
DSS
ETL
External Data
Sources
Data
Mart
C
DSS
Multi-dimensional (Sales) Data
25
40
90
50
30
70
55
60
35
Lime Soda
60
Orange Soda
Arizona
110
Diet Soda
Utah
80
Soda
California
March 3
March 2
March 1
Cube Operations
•
•
•
•
•
•
Cube (group by option)
Slice (implement in Oracle with where clause)
Dice (implement in Oracle with where clause)
Drill Down (implemented in report writers)
Roll-up (group by option)
Pivot (not implemented by Oracle (but by Access))
Cube Data Example
Create table sales (
Item
varchar2(20),
State varchar2(20),
Amount number(6),
Day
date);
Insert into Sales
values('Soda','California',80,'01-Mar-2004');
Insert into Sales
values('Diet Soda','California',110,'01-Mar-2004');
…
Examine these queries
Select * from sales;
Select Item, State, sum(amount)
from sales
group by Item, State;
Select Item, State, sum(amount)
from sales
group by Rollup(Item, State);
Select State, Item, sum(amount)
from sales
group by Rollup(State, Item);
Select State, Item, sum(amount)
from sales
group by Cube(State, Item);
Materialized Views
Materialized views are schema objects that can be used to summarize, precompute, replicate,
and distribute data. They are suitable in various computing environments such as data
warehousing, decision support, and distributed or mobile computing:
•In data warehouses, materialized views are used to precompute and store aggregated data such as sums
and averages. Materialized views in these environments are typically referred to as summaries because
they store summarized data.
•Cost-based optimization can use materialized views to improve query performance by automatically
recognizing when a materialized view can and should be used to satisfy a request. The optimizer
transparently rewrites the request to use the materialized view. Queries are then directed to the
materialized view and not to the underlying detail tables or views.
•In distributed environments, materialized views are used to replicate data at distributed sites and
synchronize updates done at several sites with conflict resolution methods. The materialized views as
replicas provide local access to data that otherwise has to be accessed from remote sites.
•In mobile computing environments, materialized views are used to download a subset of data from
central servers to mobile clients, with periodic refreshes from the central servers and propagation of
updates by clients back to the central servers.
Create Materialized View (partial syntax)
Materialized View refresh_clause
MV Example
Create Materialized View MVcustomer
REFRESH start with sysdate Next sysdate+(1/24)
AS
Select customerID,lastname,firstname, phone
from customers;
RDBMS Star Schema
Item
Store
ItemID
StoreID
Name
UnitPrice
Sales
Brand
SalesNO
Category
SalesUnits
SalesDollars
Manager
Street
City
Zip
SalesCost
ItemID
Customer
CustID
Day
CustID
StoreID
DayID
Name
DayID
DayOfMonth
Phone
Month
Street
Year
City
DayOfWeek