Chapter 7: Data Analytics
Database System Concepts, 7th Ed.
©Silberschatz, Korth and Sudarshan
See [Link] for conditions on re-use
Chapter 7: Data Analytics
Overview
Data Warehousing
Building a modern Data Warehouse from scratch
[Link]
d18d346a7118
SQL for Aggregation in Data Warehouses
[Link]
Online Analytical Processing
Data Mining
Database System Concepts - 7th Edition 11.2 ©Silberschatz, Korth and Sudarshan
Overview
Data analytics: the processing of data to infer patterns, correlations, or
models for prediction
Primarily used to make business decisions
• Per individual customer
E.g., what product to suggest for purchase
• Across all customers
E.g., what products to manufacture/stock, in what quantity
Critical for businesses today
Database System Concepts - 7th Edition 11.3 ©Silberschatz, Korth and Sudarshan
Overview (Cont.)
Common steps in data analytics
• Gather data from multiple sources into one location
Data warehouses also integrated data into common schema
Data often needs to be extracted from source formats, transformed
to common schema, and loaded into the data warehouse
• Can be done as ETL (extract-transform-load), or ELT (extract-
load-transform)
• Generate aggregates and reports summarizing data
Dashboards showing graphical charts/reports
Online analytical processing (OLAP) systems allow interactive
querying
Statistical analysis using tools such as R/SAS/SPSS
• Including extensions for parallel processing of big data
• Build predictive models and use the models for decision making
Database System Concepts - 7th Edition 11.4 ©Silberschatz, Korth and Sudarshan
Overview (Cont.)
Predictive models are widely used today
• E.g., use customer profile features (e.g. income, age, gender,
education, employment) and past history of a customer to predict
likelihood of default on loan
and use prediction to make loan decision
• E.g., use past history of sales (by season) to predict future sales
And use it to decide what/how much to produce/stock
And to target customers
Other examples of business decisions:
• What items to stock?
• What insurance premium to change?
• To whom to send advertisements?
Database System Concepts - 7th Edition 11.5 ©Silberschatz, Korth and Sudarshan
Overview (Cont.)
Machine learning techniques are key to finding patterns in data and making
predictions
Data mining extends techniques developed by machine-learning
communities to run them on very large datasets
The term business intelligence (BI) is synonym for data analytics
The term decision support focuses on reporting and aggregation
Database System Concepts - 7th Edition 11.6 ©Silberschatz, Korth and Sudarshan
DATA WAREHOUSING
Database System Concepts - 7th Edition 11.7 ©Silberschatz, Korth and Sudarshan
Data Warehousing
Data sources often store only current data, not historical data
Corporate decision making requires a unified view of all organizational data,
including historical data
A data warehouse is a repository (archive) of information gathered from
multiple sources, stored under a unified schema, at a single site
• Greatly simplifies querying, permits study of historical trends
• Shifts decision support query load away from transaction processing
systems
Database System Concepts - 7th Edition 11.8 ©Silberschatz, Korth and Sudarshan
Data Warehousing
Database System Concepts - 7th Edition 11.9 ©Silberschatz, Korth and Sudarshan
Design Issues
When and how to gather data
• Source driven architecture: data sources transmit new information to
warehouse
either continuously or periodically (e.g., at night)
• Destination driven architecture: warehouse periodically requests new
information from data sources
• Synchronous vs asynchronous replication
Keeping warehouse exactly synchronized with data sources (e.g.,
using two-phase commit) is often too expensive
Usually OK to have slightly out-of-date data at warehouse
Data/updates are periodically downloaded form online transaction
processing (OLTP) systems.
What schema to use
• Schema integration
Database System Concepts - 7th Edition 11.10 ©Silberschatz, Korth and Sudarshan
More Warehouse Design Issues
Data transformation and data cleansing
• E.g., correct mistakes in addresses (misspellings, zip code errors)
• Merge address lists from different sources and purge duplicates
How to propagate updates
• Warehouse schema may be a (materialized) view of schema from data
sources
View maintenance
What data to summarize
• Raw data may be too large to store on-line
• Aggregate values (totals/subtotals) often suffice
• Queries on raw data can often be transformed by query optimizer to use
aggregate values
Database System Concepts - 7th Edition 11.11 ©Silberschatz, Korth and Sudarshan
Multidimensional Data and
Warehouse Schemas
Data in warehouses can usually be divided into
• Fact tables, which are large
E.g, sales(item_id, store_id, customer_id, date, number, price)
• Dimension tables, which are relatively small
Store extra information about stores, items, etc.
Attributes of fact tables can be usually viewed as
• Measure attributes
measure some value, and can be aggregated upon
e.g., the attributes number or price of the sales relation
• Dimension attributes
dimensions on which measure attributes are viewed
e.g., attributes item_id, color, and size of the sales relation
Usually small ids that are foreign keys to dimension tables
Database System Concepts - 7th Edition 11.12 ©Silberschatz, Korth and Sudarshan
Data Warehouse Schema
Database System Concepts - 7th Edition 11.13 ©Silberschatz, Korth and Sudarshan
Multidimensional Data and
Warehouse Schemas
Resultant schema is called a star schema
• More complicated schema structures
Snowflake schema: multiple levels of dimension tables
May have multiple fact tables
Typically
• fact table joined with dimension tables and then
• group-by on dimension table attributes, and then
• aggregation on measure attributes of fact table
Some applications do not find it worthwhile to bring data to a common
schema
• Data lakes are repositories which allow data to be stored in multiple
formats, without schema integration
• Less upfront effort, but more effort during querying
Database System Concepts - 7th Edition 11.14 ©Silberschatz, Korth and Sudarshan
Database Support for Data Warehouses
Data in warehouses usually append only, not updated
• Can avoid concurrency control overheads
Data warehouses often use column-oriented storage
• E.g., a sequence of sales tuples is stored as follows
Values of item_id attribute are stored as an array
Values of store_id attribute are stored as an array,
And so on
• Arrays are compressed, reducing storage, IO and memory costs
significantly
• Queries can fetch only attributes that they care about, reducing IO and
memory cost
• More details in Section 13.6
Data warehouses often use parallel storage and query processing
infrastructure
• Distributed file systems, Map-Reduce, Hive, …
Database System Concepts - 7th Edition 11.15 ©Silberschatz, Korth and Sudarshan
OLAP
Data Analysis and OLAP
Online Analytical Processing (OLAP)
• Interactive analysis of data, allowing data to be summarized and viewed
in different ways in an online fashion (with negligible delay)
We use the following relation to illustrate OLAP concepts
• sales (item_name, color, clothes_size, quantity)
This is a simplified version of the sales fact table joined with the dimension
tables, and many attributes removed (and some renamed)
Database System Concepts - 7th Edition 11.17 ©Silberschatz, Korth and Sudarshan
Example sales relation
... ... ... ...
... ... ... ...
Database System Concepts - 7th Edition 11.18 ©Silberschatz, Korth and Sudarshan
Cross Tabulation of sales by item_name and color
The table above is an example of a cross-tabulation (cross-tab), also
referred to as a pivot-table.
• Values for one of the dimension attributes form the row headers
• Values for another dimension attribute form the column headers
• Other dimension attributes are listed on top
• Values in individual cells are (aggregates of) the values of the
dimension attributes that specify the cell.
Database System Concepts - 7th Edition 11.19 ©Silberschatz, Korth and Sudarshan
Data Cube
A data cube is a multidimensional generalization of a cross-tab
Can have n dimensions; we show 3 below
Cross-tabs can be used as views on a data cube
Database System Concepts - 7th Edition 11.20 ©Silberschatz, Korth and Sudarshan
Online Analytical Processing Operations
Pivoting: changing the dimensions used in a cross-tab
• E.g., moving colors to column names
Slicing: creating a cross-tab for fixed values only
• E.g., fixing color to white and size to small
• Sometimes called dicing, particularly when values for multiple
dimensions are fixed.
Rollup: moving from finer-granularity data to a coarser granularity
• E.g., aggregating away an attribute
• E.g., moving from aggregates by day to aggregates by month or year
Drill down: The opposite operation - that of moving from coarser-
granularity data to finer-granularity data
Database System Concepts - 7th Edition 11.21 ©Silberschatz, Korth and Sudarshan
Hierarchies on Dimensions
Hierarchy on dimension attributes: lets dimensions be viewed at different
levels of detail
E.g., the dimension datetime can be used to aggregate by hour of day,
date, day of week, month, quarter or year
Database System Concepts - 7th Edition 11.22 ©Silberschatz, Korth and Sudarshan
Cross Tabulation With Hierarchy
Cross-tabs can be easily extended to deal with hierarchies
Can drill down or roll up on a hierarchy
E.g. hierarchy: item_name category
Database System Concepts - 7th Edition 11.23 ©Silberschatz, Korth and Sudarshan
Relational Representation of Cross-tabs
Cross-tabs can be
represented as relations
We use the value all to
represent aggregates.
The SQL standard actually
uses null values in place of
all
• Works with any data
type
• But can cause confusion
with regular null values.
Database System Concepts - 7th Edition 11.24 ©Silberschatz, Korth and Sudarshan
OLAP IN SQL
Database System Concepts - 7th Edition 11.25 ©Silberschatz, Korth and Sudarshan
Pivot Operation
select *
from sales
pivot (
sum(quantity)
for color in ('dark','pastel','white')
)
order by item name;
Database System Concepts - 7th Edition 11.26 ©Silberschatz, Korth and Sudarshan
Cube Operation
The cube operation computes union of group by’s on every subset of the
specified attributes
E.g., consider the query
select item_name, color, size, sum(number)
from sales
group by cube(item_name, color, size)
This computes the union of eight different groupings of the sales relation:
{ (item_name, color, size), (item_name, color),
(item_name, size), (color, size),
(item_name), (color),
(size), ()}
where ( ) denotes an empty group by list.
For each grouping, the result contains the null value for attributes not
present in the grouping.
Database System Concepts - 7th Edition 11.27 ©Silberschatz, Korth and Sudarshan
Online Analytical Processing Operations
Relational representation of cross-tab that we saw earlier, but with null in
place of all, can be computed by
select item_name, color, sum(number)
from sales
group by cube(item_name, color)
The function grouping() can be applied on an attribute
• Returns 1 if the value is a null value representing all,
and returns 0 in all other cases.
select case when grouping(item_name) = 1 then 'all’
else item_name end as item_name,
case when grouping(color) = 1 then 'all’
else color end as color,
'all' as clothes size, sum(quantity) as quantity
from sales
group by cube(item name, color);
Database System Concepts - 7th Edition 11.28 ©Silberschatz, Korth and Sudarshan
Online Analytical Processing Operations
Can use the function decode() in the select clause to replace such nulls by
a value such as all
• E.g., replace item_name in first query by
decode( grouping(item_name), 1, ‘all’, item_name)
Database System Concepts - 7th Edition 11.29 ©Silberschatz, Korth and Sudarshan
Extended Aggregation (Cont.)
The rollup construct generates union on every prefix of specified list of
attributes
select item_name, color, size, sum(number)
from sales
group by rollup(item_name, color, size)
Generates union of four groupings:
{ (item_name, color, size), (item_name, color), (item_name), ( ) }
Rollup can be used to generate aggregates at multiple levels of a
hierarchy.
E.g., suppose table itemcategory(item_name, category) gives the category
of each item. Then
select category, item_name, sum(number)
from sales, itemcategory
where sales.item_name = itemcategory.item_name
group by rollup(category, item_name)
would give a hierarchical summary by item_name and by category.
Database System Concepts - 7th Edition 11.30 ©Silberschatz, Korth and Sudarshan
Extended Aggregation (Cont.)
Multiple rollups and cubes can be used in a single group by clause
• Each generates set of group by lists, cross product of sets gives overall
set of group by lists
E.g.,
select item_name, color, size, sum(number)
from sales
group by rollup(item_name), rollup(color, size)
generates the groupings
{item_name, ()} X {(color, size), (color), ()}
= { (item_name, color, size), (item_name, color), (item_name),
(color, size), (color), ( ) }
select item_name, color, clothes_size, sum(quantity)
from sales
group by grouping sets ((color, clothes_size),
(clothes_size, item_name));
Database System Concepts - 7th Edition 11.31 ©Silberschatz, Korth and Sudarshan
OLAP Implementation
The earliest OLAP systems used multidimensional arrays in memory to
store data cubes, and are referred to as multidimensional OLAP (MOLAP)
systems.
OLAP implementations using only relational database features are called
relational OLAP (ROLAP) systems
Hybrid systems, which store some summaries in memory and store the base
data and other summaries in a relational database, are called hybrid OLAP
(HOLAP) systems.
Database System Concepts - 7th Edition 11.32 ©Silberschatz, Korth and Sudarshan
OLAP Implementation (Cont.)
Early OLAP systems precomputed all possible aggregates in order to
provide online response
• Space and time requirements for doing so can be very high
2n combinations of group by
• It suffices to precompute some aggregates, and compute others on
demand from one of the precomputed aggregates
Can compute aggregate on (item_name, color) from an aggregate
on (item_name, color, size)
• For all but a few “non-decomposable” aggregates such as
median
• is cheaper than computing it from scratch
Several optimizations available for computing multiple aggregates
• Can compute aggregate on (item_name, color) from an aggregate on
(item_name, color, size)
• Can compute aggregates on (item_name, color, size),
(item_name, color) and (item_name) using a single sorting
of the base data
Database System Concepts - 7th Edition 11.33 ©Silberschatz, Korth and Sudarshan
Reporting and Visualization
Reporting tools help create formatted reports with tabular/graphical
representation of data
• E.g., SQL Server reporting services, Crystal Reports
Data visualization tools help create interactive visualization of data
• E.g., Tableau, FusionChart, plotly, Datawrapper, Google Charts, etc.
• Frontend typically based on HTML+JavaScript
Database System Concepts - 7th Edition 11.34 ©Silberschatz, Korth and Sudarshan
Database
1. sales table
This is the fact table, containing transaction-level data.
Likely Columns:
time_id – foreign key referencing the times table
cust_id – foreign key referencing the customers table
channel_id – foreign key referencing the channels table
amount_sold – the amount/value of the sale (likely a number or currency field)
2. customers table
This is a dimension table holding customer information.
Likely Columns:
cust_id – primary key
country_id – indicates which country the customer is from (e.g., 'UK', 'US')
Possibly other columns like cust_name, cust_gender, cust_marital_status, etc.
3. times table
This is a time dimension table.
Likely Columns:
time_id – primary key
calendar_month_desc – human-readable month-year label (e.g., '2000-09')
Possibly day, month, quarter, year, etc.
Database System Concepts - 7th Edition 11.35 ©Silberschatz, Korth and Sudarshan
4. channels table
This is a sales channel dimension table.
Likely Columns:
channel_id – primary key
channel_desc – description of the channel (e.g., 'Direct Sales', 'Internet')
Possibly channel_class, channel_type, etc.
Database System Concepts - 7th Edition 11.36 ©Silberschatz, Korth and Sudarshan