CP 322 – DATA MINING AND WAREHOUS-

ING

Data Warehouse

Dept of Comp Sc and Engineering

CIVE-UDOM

2025-04-10

1
Cloud AI

Big Data Analyt-

ics

Contents
1. Data Warehouse: Basic Concepts

2. Data Warehouse Modeling: Data Cube and OLAP

3. Data Warehouse Design and Usage

4. Data Warehouse Implementation

5. Summary

2
Data Warehouse: Basic Concepts

 Defined in many different ways, but not rigorously.

 A decision support database that is maintained separately from the
organization’s operational database
 Support information processing by providing a solid platform of
consolidated, historical data for analysis.

 “A data warehouse is a subject-oriented, integrated, time-variant,

and nonvolatile collection of data in support of management’s decision-
making process.”—W. H. Inmon

 Data warehousing:
 The process of constructing and using data warehouses

3
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

4
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,
attribute measures, etc. among different data sources
 E.g., Hotel price: currency, tax, breakfast covered, etc.
 When data is moved to the warehouse, it is converted.

5
Data Warehouse: Time Variant

 The time horizon for the data warehouse is significantly longer than that
of operational systems
 Operational database: current value data
 Data warehouse data: provide information from a historical
perspective (e.g., past 5-10 years)

 Every key structure in the data warehouse

 Contains an element of time, explicitly or implicitly
 But the key of operational data may or may not contain “time
element”

6
Data Warehouse: Non-Volatile

 A physically separate store of data transformed from the operational

environment

 Operational update of data does not occur in the data warehouse

environment
 Does not require transaction processing, recovery, and concurrency
control mechanisms
 Requires only two operations in data accessing:
 initial loading of data and access of data

7
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

8
Why a 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
 Note: There are more and more systems which perform OLAP analysis
directly on relational databases

9
Data Warehouse – A Multi-Tiered Architecture

Monitor
Metadata & OLAP Server
Other
sources Integrator

Analysis
Operational Extract Query
DBs Transform Data Serve Reports
Load
Refresh
Warehouse Data mining

Data Marts

Data Sources Data Storage OLAP Engine Front-End Tools

10
Three Data Warehouse Models
 Enterprise warehouse
 collects all of the information about subjects spanning the entire
organization.

 Data Mart
 a subset of corporate-wide data that is of value to a specific groups of
users. Its scope is confined to specific, selected groups, such as
marketing data mart
 Independent vs. dependent (directly from warehouse) data mart

 Virtual warehouse
 A set of views over operational databases
 Only some of the possible summary views may be materialized

11
Extraction, Transformation and Loading
 Data extraction
 get data from multiple, heterogeneous, and external sources

 Data cleaning
 detect errors in the data and rectify them when possible

 Data transformation
 convert data from legacy or host format to warehouse format

 Load
 sort, summarize, consolidate, compute views, check integrity, and
build indicies and partitions

 Refresh
 propagate the updates from the data sources to the warehouse

12
 Metadata Repository

 Meta data is the data defining warehouse objects. It stores:

 Description of the structure of the data warehouse
 schema, view, dimensions, hierarchies, derived data defn, data mart
locations and contents
 Operational meta-data
 data lineage (history of migrated data and transformation path),
currency of data (active, archived, or purged), monitoring information
(warehouse usage statistics, error reports, audit trails)
 The algorithms used for summarization
 The mapping from operational environment to the data warehouse
 Data related to system performance
 warehouse schema, view and derived data definitions
 Business data
 business terms and definitions, ownership of data, charging policies

13
Cloud AI

Big Data Analyt-

ics

Contents
1. Data Warehouse: Basic Concepts

2. Data Warehouse Modeling: Data Cube and OLAP

3. Data Warehouse Design and Usage

4. Data Warehouse Implementation

5. Summary

14
 From Tables and Spreadsheets to
Data Cubes
 A data warehouse is based on a multidimensional data model which
views data in the form of a data cube.

 A data cube, such as sales, allows data to be modeled and viewed in

multiple dimensions
 Dimension tables, such as item (item_name, brand, type), or
time(day, week, month, quarter, year)
 Fact table contains measures (such as dollars_sold) and keys to
each of the related dimension tables

 In data warehousing literature, an n-D base cube is called a base

cuboid. The top most 0-D cuboid, which holds the highest-level of
summarization, is called the apex cuboid. The lattice of cuboids forms
a data cube.

15
 Cube: A Lattice of Cuboids

all
0-D (apex) cuboid

time item location supplier

1-D cuboids

time,location item,location location,supplier

2-D cuboids
time,item time,supplier item,supplier

time,location,supplier
3-D cuboids
time,item,location time,item,supplier
item,location,supplier

4-D (base) cuboid

time, item, location, supplier

16
Conceptual Modelling of Data Warehouse

 Modeling data warehouses: dimensions & measures

 Star schema: A fact table in the middle connected to a set of
dimension tables.

 Snowflake schema: A refinement of star schema where some

dimensional hierarchy is normalized into a set of smaller
dimension tables, forming a shape similar to snowflake

 Fact constellations: Multiple fact tables share dimension

tables, viewed as a collection of stars, therefore called galaxy
schema or fact constellation

17
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 state_or_province
country
avg_sales
Measures

18
Example of Snowflake Schema

time
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
branch location
location_key
location_key
branch_key
units_sold street
branch_name
city_key
branch_type
dollars_sold city
city_key
avg_sales city
state_or_province
Measures country

19
Example of Fact Constellation

time
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

branch location_key location to_location

branch_key location_key dollars_cost
branch_name units_sold
street
branch_type dollars_sold city units_shipped
province_or_state
avg_sales country shipper
Measures shipper_key
shipper_name
20 location_key
shipper_type
20
A Concept Hierarchy: Dimension (Location)

all all

region Europe ... North_America

country Germany ... Spain Canada ... Mexico

city Frankfurt ... Vancouver ... Toronto

office L. Chan ... M. Wind

21
Data Cubes 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 fixed integer), each of which is obtained by
applying a distributive aggregate function
 E.g., avg(), min_N(), standard_deviation()

 Holistic: if there is no constant bound (fixed integer) on the storage

size needed to describe a subaggregate.
 E.g., median(), mode(), rank()

22
View of Warehouses and Hierarchies

Specification of hierar-
chies
• Schema hierarchy
day < {month <
quarter; week} <
year
• Set_grouping hierar-
chy
{1..10} < inexpen-
sive
23
 Multidimensional Data

 Sales volume as a function of product, month, and region

Dimensions: Product, Location, Time

Hierarchical summarization paths
o n
gi

Industry Region Year

Re

Category Country Quarter

Product

Product City Month Week

Office Day

Month

24
A Sample Data Cube

Total annual sales

Date of TVs in U.S.A.
1Qtr 2Qtr 3Qtr 4Qtr sum
t
uc

TV
od

PC U.S.A
Pr

VCR

Country
sum
Canada

Mexico

sum

25
Cuboids Corresponding to the Cube

all
0-D (apex) cuboid
product date country
1-D cuboids

product,date product,country date, country

2-D cuboids

3-D (base) cuboid

product, date, country

26
Typical OLAP Operations

 Roll up (drill-up): summarize data

– by climbing up hierarchy or by dimension reduction

 Drill down (roll down): reverse of roll-up

– from higher level summary to lower level summary or detailed data,
or introducing new dimensions
 Slice and dice: project and select

 Pivot (rotate):
– reorient the cube, visualization, 3D to series of 2D planes

 Other operations
– drill across: involving (across) more than one fact table
– drill through: through the bottom level of the cube to its back-end
relational tables (using SQL)
27
 Typical OLAP
erations

28
28
Cloud AI

Big Data Analyt-

ics

Contents
1. Data Warehouse: Basic Concepts

2. Data Warehouse Modeling: Data Cube and OLAP

3. Data Warehouse Design and Usage

4. Data Warehouse Implementation

5. Summary

29
Design of Data Warehouse: A Business Analysis Framework

 Four views regarding the design of a data warehouse

 Top-down view
 allows selection of the relevant information necessary for the
data warehouse
 Data source view
 exposes the information being captured, stored, and managed
by operational systems
 Data warehouse view
 consists of fact tables and dimension tables
 Business query view
 sees the perspectives of data in the warehouse from the view
of end-user

30
Data Warehouse Design Process
 Top-down, bottom-up approaches or a combination of both
 Top-down: Starts with overall design and planning (mature)
 Bottom-up: Starts with experiments and prototypes (rapid)

 From software engineering point of view

 Waterfall: structured and systematic analysis at each step before
proceeding to the next
 Spiral: rapid generation of increasingly functional systems, short
turn around time, quick turn around

 Typical data warehouse design process

 Choose a business process to model, e.g., orders, invoices, etc.
 Choose the grain (atomic level of data) of the business process
 Choose the dimensions that will apply to each fact table record
 Choose the measure that will populate each fact table record

31
Data Warehouse Usage

 Three kinds of data warehouse applications

 Information processing
 supports querying, basic statistical analysis, and reporting
using crosstabs, tables, charts and graphs
 Analytical processing
 multidimensional analysis of data warehouse data
 supports basic OLAP operations, slice-dice, drilling, pivoting
 Data mining
 knowledge discovery from hidden patterns
 supports associations, constructing analytical models,
performing classification and prediction, and presenting the
mining results using visualization tools

32
From On-Line Analytical Processing (OLAP)
to On Line Analytical Mining (OLAM)

 Why online analytical mining?

 High quality of data in data warehouses
 DW contains integrated, consistent, cleaned data
 Available information processing structure surrounding data
warehouses
 ODBC, OLEDB, Web accessing, service facilities, reporting
and OLAP tools
 OLAP-based exploratory data analysis
 Mining with drilling, dicing, pivoting, etc.
 On-line selection of data mining functions
 Integration and swapping of multiple mining functions,
algorithms, and tasks

33
Cloud AI

Big Data Analyt-

ics

Contents
1. Data Warehouse: Basic Concepts

2. Data Warehouse Modeling: Data Cube and OLAP

3. Data Warehouse Design and Usage

4. Data Warehouse Implementation

5. Summary

34
Data Warehouse Implementation

 Data cube can be viewed as a lattice of cuboids

 The bottom-most cuboid is the base cuboid
 The top-most cuboid (apex) contains only one cell
 How many cuboids in an n-dimensional cube with L levels?

 Materialization of data cube

 Materialize every (cuboid) (full materialization), none (no
materialization), or some (partial materialization)
 Selection of which cuboids to materialize
 Based on size, sharing, access frequency, etc.

35
The “Compute Cube” Operator

 Cube definition and computation in DMQL

define cube sales [item, city, year]: sum
(sales_in_dollars)
compute cube sales
 Transform it into a SQL-like language (with a new operator
cube by, introduced by Gray et al.’96) ()
SELECT item, city, year, SUM (amount)
FROM SALES (city) (item) (year)

CUBE BY item, city, year

 Need compute the following Group-Bys
(city, item) (city, year) (item, year)
(date, product, customer),
(date,product),(date, customer), (product, customer),
(date), (product), (customer) (city, item, year)
()

36
OLAP Server Architectures
 Relational OLAP (ROLAP)
 Use relational or extended-relational DBMS to store and manage
warehouse data and OLAP middle ware
 Include optimization of DBMS backend, implementation of
aggregation navigation logic, and additional tools and services
 Greater scalability
 Multidimensional OLAP (MOLAP)
 Sparse array-based multidimensional storage engine
 Fast indexing to pre-computed summarized data
 Hybrid OLAP (HOLAP)
 The hybrid OLAP approach combines ROLAP and MOLAP
technology, benefiting from the greater scalability of ROLAP and the
faster computation of MOLAP.
 For example, a HOLAP server may allow large volumes of detailed
data to be stored in a relational database, whereas aggregations are
kept in a separate MOLAP store.
37
Cloud AI

Big Data Analyt-

ics

Contents
1. Data Warehouse: Basic Concepts

2. Data Warehouse Modeling: Data Cube and OLAP

3. Data Warehouse Design and Usage

4. Data Warehouse Implementation

5. Summary

38
Concept Description vs. Cube-Based OLAP
 Data warehousing: A multi-dimensional model of a data warehouse
 A data cube consists of dimensions & measures
 Star schema, snowflake schema, fact constellations
 OLAP operations: drilling, rolling, slicing, dicing and pivoting

 Data Warehouse Architecture, Design, and Usage

 Multi-tiered architecture
 Business analysis design framework
 Information processing, analytical processing, data mining, OLAM
(Online Analytical Mining)

39
Concept Description vs. Cube-Based OLAP
 Implementation: Efficient computation of data cubes
 Partial vs. full vs. no materialization
 OLAP query processing
 OLAP servers: ROLAP, MOLAP, HOLAP

40
Thank You
41