Data Ware House Concept 2019 (Compatibility Mode) PDF
Data Ware House Concept 2019 (Compatibility Mode) PDF
Data Ware House Concept 2019 (Compatibility Mode) PDF
1
Data Warehouse—Subject-
Oriented
• Organized around major subjects, such as customer,
product, sales.
• Focusing on the modeling and analysis of data for
decision makers, not on daily operations or transaction
processing.
• Provide a simple and concise view around particular
subject issues by excluding data that are not useful in the
decision support process.
Data Warehouse—Integrated
• Constructed by integrating multiple, heterogeneous
data sources
– relational databases, flat files, on-line transaction
records
• Data cleaning and data integration techniques are
applied.
– Ensure consistency in naming conventions,
encoding structures among different data sources
– When data is moved to the warehouse, it is
converted.
2
Data Warehouse—Time Variant
3
Data Warehouse—Non-Volatile
4
Data Warehouse vs. Operational DBMS
5
OLTP vs. OLAP
OLTP OLAP
users clerk, IT professional knowledge worker
function day to day operations decision support
DB design application-oriented subject-oriented
data current, up-to-date historical,
detailed, flat relational summarized, multidimensional
isolated integrated, consolidated
usage repetitive ad-hoc
access read/write lots of scans
index/hash on prim. key
unit of work short, simple transaction complex query
# records accessed tens millions
#users thousands hundreds
DB size 100MB-GB 100GB-TB
metric transaction throughput query throughput, response
6
Why Separate Data
Warehouse?
• High performance for both systems
– DBMS— tuned for OLTP: access methods, indexing,
concurrency control, recovery
– Warehouse—tuned for OLAP: complex OLAP queries,
multidimensional view, consolidation.
• Different functions and different data:
– missing data: Decision support requires historical data
which operational DBs do not typically maintain
– data consolidation: DS requires consolidation
(aggregation, summarization) of data from
heterogeneous sources
– data quality: different sources typically use inconsistent
data representations, codes and formats which have to
be reconciled
Aggregation in SQL
7
Aggregation in SQL
Aggregation
Aggregation in SQL
• A data cube, such as sales, allow data to
be modified in multiple dimension
• Dimension table:
• Item, time, location
• Fact table:
• Contain measure (Rs_sold) and key of each
dimension table.
8
Aggregates
• Add up amounts by day
• In SQL: SELECT date, sum(amt) FROM SALE
GROUP BY date
Multidimensional View
BPL
IND
RAI
BIL
Q2
Q3
Q4
H C P S
Items
9
Data Cube: A Lattice of
Cuboids
all
0-D(apex) cuboid
time,location,supplier
3-D cuboids
time,item,locationtime,item,supplier item,location,supplier
4-D(base) cuboid
time,item,location item,location,supplier
3-D cuboids
time,item,supplier
• Base vs. aggregate cells; ancestor vs. descendant cells; parent vs. child cells
1. (9/15, milk, Urbana, Dairy_land)
2. (9/15, milk, Urbana, *)
3. (*, milk, Urbana, *)
4. (*, milk, Urbana, *)
5. (*, milk, Chicago, *)
6. (*, milk, *, *) 20
10
Aggregates
• Operators: sum, count, max, min,
median, avg
• “Having” clause
• Using dimension hierarchy
– average by region (within store)
– maximum by month (within date)
Multiple Simultaneous
Aggregates
4 Group-bys here:
(store,product)
Cross-Tabulation (products/store) (store)
(product)
Product ()
Sales
1 2 3 4 ALL
1 454 - - 925 1379
2 468 800 - - 1268
Sub-totals per store
Store
Total sales
Sub-totals per product
11
Cube Operators
c1 c2 c3
day 2 ...
p1 44 4
p2 c1 c2 c3
day 1
p1 12 50
p2 11 8 sale(c1,*,*)
c1 c2 c3
sum 67 12 50
c1 c2 c3
p1 56 4 50
p2 11 8 129
sum
sale(c2,p2,*) p1 110
p2 19 sale(*,*,*)
Cube Aggregation
Example: computing sums
c1 c2 c3
day 2 ...
p1 44 4
p2 c1 c2 c3
day 1
p1 12 50
p2 11 8
c1 c2 c3
sum 67 12 50
c1 c2 c3
p1 56 4 50
p2 11 8 129
sum
rollup p1 110
p2 19
drill-down
12
Cube Aggregates Lattice
129 Apex cuboid
all
c1 c2 c3
p1 67 12 50
city product date
day 2
p1
c1
44
c2
4
c3
city, product, date
p2 c1 c2 c3
day 1
p1 12 50
p2 11 8
Base cuboid
13
Cube
dimensions = 2
3-D Cube
Fact table view: Multi-dimensional cube:
sale prodId storeId date amt
p1 c1 1 12
p2 c1 1 11 c1 c2 c3
day 2
p1 c3 1 50 p1 44 4
p2 c2 1 8 p2 c1 c2 c3
p1 c1 2 44 day 1
p1 12 50
p1 c2 2 4 p2 11 8
dimensions = 3
14
Aggregates
• Add up amounts for day 1
• In SQL: SELECT sum(amt) FROM SALE
WHERE date = 1
Aggregates
• Add up amounts by day
• In SQL: SELECT date, sum(amt) FROM SALE
GROUP BY date
15
Another Example
• Add up amounts by day, product
• In SQL: SELECT date, sum(amt) FROM SALE
GROUP BY date, prodId
sale prodId storeId date am t
p1 c1 1 12 sale prodId date amt
p2 c1 1 11
p1 1 62
p1 c3 1 50
p2 1 19
p2 c2 1 8
p1 c1 2 44 p1 2 48
p1 c2 2 4
16
n-dimensional data cube
n
T= ¶ (Li + 1);
i=1
where Li is the number of levels associated with dimension
The dimension hierarchies used are “city < state < region” for place,
“age_value < age_category [20-30, 30-40, 40-50}” for age and
“salary_value < salary_class [50-60K, 60-65K, 65-70K]” for salary.
What is the total number of cuboids possible from this data cube?
17
What to Materialize?
• Store in warehouse results useful for
common queries
• Example: total sales
c1 c2 c3
day 2 ...
p1 44 4
p2 c1 c2 c3
day 1
p1 12 50
p2 11 8
c1 c2 c3
p1 67 12 50
c1 c2 c3
p1 56 4 50
p2 11 8 129
c1
materialize p1 110
p2 19
Materialized Views
• Define new warehouse relations using
SQL expressions
sale prodId storeId date am t product id name price
p1 c1 1 12 p1 bolt 10
p2 c1 1 11 p2 nut 5
p1 c3 1 50
p2 c2 1 8
p1 c1 2 44
p1 c2 2 4
18
Measures: Three
Categories
• distributive: if the result derived by applying the function to
n aggregate values is the same as that derived by
applying the function on all the data without partitioning.
• E.g., count(), sum(), min(), max().
• algebraic: if it can be computed by an algebraic function
with M arguments (where M is a bounded integer), each
of which is obtained by applying a distributive aggregate
function.
• E.g., avg(), standard_deviation().
Measures: Three
Categories
• holistic: if there is no constant bound on the storage size
needed to describe a sub-aggregate.
• Holistic measure is a measure that must be computed on
entire data set as whole. It can not be computed by
partitioning the give data set into subset and merging the
values obtained for the major in each subset
• E.g., median(), mode(), rank().
19
DATA CUBE OF WEB LOG
Pages
Result
Total Visit
20
Star Schema
sale
orderId
date customer
product
custId custId
prodId
prodId name
name
storeId address
price
qty city
amt
store
storeId
city
Star
product prodId name price store storeId city
p1 bolt 10 c1 nyc
p2 nut 5 c2 sfo
c3 la
21
Example of Star Schema
time
time_key item
day item_key
day_of_the_week Sales Fact Table item_name
month brand
quarter time_key type
year supplier_type
item_key
branch_key
branch location
location_key
branch_key location_key
branch_name units_sold street
branch_type city
dollars_sold province_or_street
country
avg_sales
Measures
Example of Snowflake
time
Schema
time_key item
day item_key supplier
day_of_the_week Sales Fact Table item_name supplier_key
month brand supplier_type
quarter time_key type
year item_key supplier_key
branch_key
location
branch location_key
location_key
branch_key
units_sold street
branch_name
city_key city
branch_type
dollars_sold
city_key
avg_sales city
province_or_street
Measures country
22
Example of Fact
time
Constellation
time_key item Shipping Fact Table
day item_key
day_of_the_week Sales Fact Table item_name time_key
month brand
quarter time_key type item_key
year supplier_type shipper_key
item_key
branch_key from_location
23
Defining a Star Schema in DMQL
24
Defining a Fact Constellation in
DMQL
define cube sales [time, item, branch, location]:
dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars),
units_sold = count(*)
define dimension time as (time_key, day, day_of_week, month, quarter,
year)
define dimension item as (item_key, item_name, brand, type,
supplier_type)
define dimension branch as (branch_key, branch_name, branch_type)
define dimension location as (location_key, street, city,
province_or_state, country)
define cube shipping [time, item, shipper, from_location, to_location]:
dollar_cost = sum(cost_in_dollars), unit_shipped = count(*)
define dimension time as time in cube sales
define dimension item as item in cube sales
define dimension shipper as (shipper_key, shipper_name, location as
location in cube sales, shipper_type)
define dimension from_location as location in cube sales
define dimension to_location as location in cube sales
• OLAP
• https://www.youtube.com/watch?v=kFQRr
gHeiOo
25