SQL extensions for OLAP

• On-Line Analytical Processing (OLAP) provides a advanced set of BI

techniques to analyze data in data warehouse
• OLAP allows interactive analysis of data, summarize it and
visualize it in various ways
• goal of OLAP is to provide the business-user with a powerful tool
for ad-hoc querying
• ISO SQL:2003 has added a lot of support for OLAP operations
• Various database vendors like Oracle and Microsoft provide their
own support for extending the functionality of SQL for use in Data
Warehouse, OLAP and Analytics
• To facilitate the execution of OLAP queries and data aggregation,
SQL has some extensions to GROUP BY statement – such as, CUBE,
ROLLUP and GROUPING SETS operators

27-11-2024 CC ZG515 Data Warehousing 1

 SQL extensions for OLAP
GROUP BY Extensions

• ROLLUP calculates aggregations such as SUM, COUNT, MAX, MIN, and

AVG at increasing levels of aggregation, from the most detailed up to
a grand total
• CUBE is similar to ROLLUP, enabling a single statement to calculate all
possible combinations of aggregations
• Computing a CUBE creates a heavy processing load!
• The GROUPING SETS extension lets you specify just the needed
groupings in the GROUP BY clause
• This allows efficient analysis across multiple dimensions without
performing a CUBE operation
• Replacing cubes with grouping sets can significantly increase
performance
27-11-2024 CC ZG515 Data Warehousing 2
 SQL extensions for OLAP
CUBE operator computes union of
GROUP BY’s on every subset of
specified attribute types
• Result set represents multi-
dimensional cube based upon
source table
• Example SALES TABLE ->

SELECT QUARTER, REGION,

SUM(SALES)
FROM SALESTABLE
GROUP BY CUBE (QUARTER, REGION)

27-11-2024 CC ZG515 Data Warehousing 3

 SQL extensions for OLAP
• query computes union of 2n (n Result from SQL query with Cube operator
is no. of attribute parameters
for CUBE, here n=2) 2² = 4
groupings of SALESTABLE
• {(quarter,region), (quarter),
(region), ()}
• () denotes empty group list
representing total aggregate
across entire SALESTABLE
• CUBE is typically most suitable
in queries that use columns
from multiple dimensions
rather than columns
representing different levels of
a single dimension
• e.g., subtotals for all
combinations of month, state,
and product

27-11-2024 CC ZG515 Data Warehousing 4

 SQL extensions for OLAP
Grouping() function
• NULL values have been added in the dimension columns Quarter and
Region to indicate aggregation
• Can be replaced by more meaningful ‘ALL’ by adding 2 CASE clauses:
SELECT
CASE WHEN grouping(QUARTER) = 1
THEN 'ALL' • Output shown on next slide
ELSE QUARTER
END AS QUARTER,
• grouping() function returns
CASE WHEN grouping(REGION) = 1 1 in case NULL value is
THEN 'ALL' generated during the
ELSE REGION aggregation and 0
END AS REGION, otherwise
SUM(SALES)
FROM SALESTABLE
GROUP BY CUBE (QUARTER, REGION)

27-11-2024 CC ZG515 Data Warehousing 5

 SQL extensions for OLAP
• NULL value for
Sales in 5th row
• Because no
products were
sold in Q3 in
Europe
• Besides SUM()
other SQL
aggregator
functions like
MIN(), MAX(),
COUNT(), AVG()
can be used in
SELECT statement
27-11-2024 CC ZG515 Data Warehousing 6
 SQL extensions for OLAP
Example-2 Sales Schema

27-11-2024 CC ZG515 Data Warehousing 7

 SQL extensions for OLAP
Example Sales view

CREATE OR REPLACE VIEW [Link] AS

SELECT *
FROM bi.s, bi.p, bi.c, bi.t, bi.m, bi.n
WHERE s_t_id = t_id
AND s_p_id = p_id
AND s_c_id = c_id
AND s_m_id = m_id
AND c_n_id = n_id;

27-11-2024 CC ZG515 Data Warehousing 8

 SQL extensions for OLAP
CUBE Example-2
SELECT m_desc, t_cal_month_desc, n_iso_code, SUM(s_amount_sold)
FROM [Link]
WHERE m_desc IN ('Direct Sales', 'Internet')
AND t_cal_month_desc IN ('2000-09', '2000-10')
AND n_iso_code IN ('GB', 'US')
GROUP BY CUBE(m_desc, t_cal_month_desc, n_iso_code);

Produces all possible roll-up combinations:

(m_desc, t_cal_month_desc, n_iso_code)
(m_desc, t_cal_month_desc)
(m_desc, n_iso_code)
(t_cal_month, n_iso_code)
(m_desc)
(t_cal_month_desc)
(n_iso_code)
-
27-11-2024 CC ZG515 Data Warehousing 9
 SQL extensions for OLAP

27-11-2024 CC ZG515 Data Warehousing 10

 SQL extensions for OLAP
CUBE
• CUBE creates 2n combinations of subtotals (i.e.,
groupings), where n is number of grouping columns
• Includes all rows produced by ROLLUP
• CUBE is most suitable in queries that use columns
from multiple dimensions rather than columns
representing different levels of a single dimension
• e.g., subtotals for all combinations of period,
location, and product

27-11-2024 CC ZG515 Data Warehousing 11

 SQL extensions for OLAP
ROLLUP operator computes union on
every prefix of the list of specified
attribute types, from most detailed
up to grand total
• Useful to generate reports
containing both subtotals & totals
• Key difference between the
ROLLUP and CUBE operator is
ROLLUP generates result set
showing aggregates for a hierarchy
of values of the specified attribute
types
• CUBE generates result set showing
aggregates for all combinations of
values of selected attribute types
27-11-2024 CC ZG515 Data Warehousing 12
 SQL extensions for OLAP
• Order of attribute types important for
ROLLUP (Hierarchy) but not for CUBE
operator

SELECT QUARTER, REGION, SUM(SALES)

FROM SALESTABLE
GROUP BY ROLLUP (QUARTER, REGION)
• query generates union of three
groupings
• { (quarter,region)
• (quarter}
• () }
• () represents full aggregation
• region dimension is first rolled up
followed by quarter dimension
27-11-2024 CC ZG515 Data Warehousing 13
 SQL extensions for OLAP
• Result from SQL query
with ROLLUP operator
• Note two rows left out
when compared to result
of CUBE operator
• ROLLUP generates results
at n + 1 increasing levels of
aggregation

27-11-2024 CC ZG515 Data Warehousing 14

 SQL extensions for OLAP
• GROUP BY ROLLUP
can also be applied
to attribute types
that represent
different
hierarchical
aggregation levels
(different levels of
detail) along the
same dimension
• SALESTABLE has 3
location-related
columns: (City,
Country, Region)
27-11-2024 CC ZG515 Data Warehousing 15
 SQL extensions for OLAP
SELECT REGION, COUNTRY,
CITY, SUM(SALES)
FROM SALESTABLE
GROUP BY ROLLUP
(REGION, COUNTRY, CITY)
• three attribute types
represent different
hierarchical levels of
detail in the same
dimension
• transitively dependent
on one another

27-11-2024 CC ZG515 Data Warehousing 16

 SQL extensions for OLAP
ROLLUP Example 2:
SELECT m_desc, t_cal_month_desc, n_iso_code,
SUM(s_amount_sold)
FROM [Link]
WHERE m_desc IN ('Direct Sales', 'Internet')
AND t_cal_month_desc IN ('2000-09', '2000-10')
AND n_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(m_desc, t_cal_month_desc, n_iso_code);

• Rollup from right to left

• Computes and combines the following 4 groupings:
– (m_desc, t_cal_month_desc, n_iso_code)
– (m_desc, t_cal_month_desc)
– (m_desc)
– (ALL)
27-11-2024 CC ZG515 Data Warehousing 17
 SQL extensions for OLAP

27-11-2024 CC ZG515 Data Warehousing 18

 SQL extensions for OLAP
GROUPING SETS operator generates a result set equivalent to that generated by
a UNION ALL of multiple simple GROUP BY clauses

SELECT QUARTER, REGION, SUM(SALES)

FROM SALESTABLE
GROUP BY
GROUPING SETS ((QUARTER), (REGION))

This query is equivalent to:

SELECT QUARTER, NULL, SUM(SALES)

FROM SALESTABLE
GROUP BY QUARTER
UNION ALL
SELECT NULL, REGION, SUM(SALES)
FROM SALESTABLE
GROUP BY REGION
27-11-2024 CC ZG515 Data Warehousing 19
 SQL extensions for OLAP
GROUPING SETS Example-2

SELECT m_desc, t_cal_month_desc, n_iso_code, SUM(s_amount_sold)

FROM [Link]
WHERE m_desc IN ('Direct Sales', 'Internet')
AND t_cal_month_desc IN ('2000-09', '2000-10')
AND n_iso code IN ('GB', 'US')
GROUP BY GROUPING SETS ((m_desc, t_cal_month_desc, n_iso_code),
(m_desc, n_iso_code), (t_cal month_desc, n_iso_code));

• GROUPING SETS produce just the specified groupings

• No (automatic) rollup is performed

27-11-2024 CC ZG515 Data Warehousing 20

 SQL extensions for OLAP
GROUPING SETS Example-2

27-11-2024 CC ZG515 Data Warehousing 21

 SQL extensions for OLAP
Equivalences:

SELECT QUARTER, REGION, SUM(SALES)

FROM SALESTABLE
GROUP BY CUBE (QUARTER, REGION)

This query is equivalent to:

SELECT QUARTER, REGION, SUM(SALES)

FROM SALESTABLE
GROUP BY GROUPING SETS ((QUARTER, REGION), (QUARTER),
(REGION), ())

27-11-2024 CC ZG515 Data Warehousing 22

 SQL extensions for OLAP
Equivalences:

SELECT QUARTER, REGION, SUM(SALES)

FROM SALESTABLE
GROUP BY ROLLUP (QUARTER, REGION)

is identical to:

SELECT QUARTER, REGION, SUM(SALES)

FROM SALESTABLE
GROUP BY
GROUPING SETS ((QUARTER, REGION), (QUARTER),())

27-11-2024 CC ZG515 Data Warehousing 23

 SQL extensions for OLAP
Equivalences:

• CUBE(a,b) ≡ GROUPING SETS ((a,b), (a), (b), ())

• ROLLUP(a,b,c) ≡ GROUPING SETS ((a,b,c), (a,b), (a), ())
• GROUP BY GROUPING SETS(a,b,c) ≡
GROUP BY a UNION ALL GROUP BY b UNION ALL
GROUP BY c
• GROUP BY GROUPING SETS((a,b,c)) ≡ GROUP BY a, b, c
• GROUP BY GROUPING SETS(a,b,(b,c)) ≡
GROUP BY a UNION ALL GROUP BY b UNION ALL
GROUP BY b, c
• GROUP BY GROUPING SETS(a,ROLLUP(b,c)) ≡
GROUP BY a UNION ALL GROUP BY ROLLUP(b, c)

27-11-2024 CC ZG515 Data Warehousing 24

 SQL extensions for OLAP
Composite Columns:

• A composite column is a collection of columns that are

treated as a unit for the grouping
• Allows to skip aggregation across certain levels

Example: ROLLUP(year,(quarter,month),day)

• (quarter,month) is treated as a unit

• Produces the following groupings:

(year,quarter,month,day)
(year,quarter,month)
(year)
()
27-11-2024 CC ZG515 Data Warehousing 25
 SQL extensions for OLAP
Tabular representation with Crosstab
totals
• Cross-tabular report with
• More space-efficient than (sub)totals
crosstab for sparse data • Space-efficient for dense
data (few dimensions)

27-11-2024 CC ZG515 Data Warehousing 26

 SQL extensions for OLAP
• If amount of data to be aggregated and retrieved is large,
OLAP SQL queries may take long time to execute
• To speed up performance -> change OLAP queries into
materialized views
• E.g., SQL query with CUBE operator can be used to
precompute aggregations on a selection of dimensions -
whose results can be stored as materialized view for
ready use
• Disadvantage of view materialization - extra efforts needed
to regularly refresh materialized views
• Many organizations are fine with near current data –
enough to update at regular time intervals

27-11-2024 CC ZG515 Data Warehousing 27

 SQL extensions for OLAP
Materialized Views - Aggregates

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID

(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;
• creates a materialized view
that contains aggregates on
CREATE MATERIALIZED VIEW sum_sales
a single table
PARALLEL • Because the materialized
BUILD IMMEDIATE view log has been created
REFRESH FAST ON COMMIT AS with all referenced columns
SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp, in the materialized view's
SUM(s.amount_sold) AS sum_dollar_sales, defining query, the
materialized view is fast
COUNT(s.amount_sold) AS count_dollar_sales, refreshable
SUM(s.quantity_sold) AS sum_quantity_sales, • If sales table is updated,
COUNT(s.quantity_sold) AS count_quantity_sales then the changes are
FROM sales s reflected in the materialized
GROUP BY s.prod_id, s.time_id; view when the commit is
issued
27-11-2024 CC ZG515 Data Warehousing 28
 SQL extensions for Analytics
Analytic Functions and their Uses

27-11-2024 CC ZG515 Data Warehousing 29

 SQL extensions for Analytics
Analytic Functions in Oracle
• Rankings and percentiles
• Moving window calculations
• Lag/lead analysis
• First/last analysis
• Linear regression statistics

27-11-2024 CC ZG515 Data Warehousing 30

 SQL extensions for Analytics
Elements of SQL Analytic Processing
• Processing Order
• Result set Partitions
• Window
• Current Row
Processing Order:

27-11-2024 CC ZG515 Data Warehousing 31

 SQL extensions for Analytics
Ranking Functions

RANK function allows ranking of items in a group, for

example, finding the top three products sold in a city in a
particular year. It computes the rank of a record compared
to other records in the data set based on the values of a set
of measures.

Types of Ranking functions:

• RANK and DENSE_RANK

• CUME_DIST
• PERCENT_RANK
• NTILE
• ROW_NUMBER
27-11-2024 CC ZG515 Data Warehousing 32
 SQL extensions for Analytics
RANK and DENSE_RANK Functions

Two main functions that perform ranking:

•RANK ( ) OVER ( [query_partition_clause] order_by_clause
)
•DENSE_RANK ( ) OVER ( [query_partition_clause]
order_by_clause )

DENSE_RANK leaves no gaps in ranking sequence when there

are ties

27-11-2024 CC ZG515 Data Warehousing 33

 SQL extensions for Analytics
SQL – Ranking Order

27-11-2024 CC ZG515 Data Warehousing 34

 SQL extensions for Analytics
Moving Aggregate Function

• Time-series technique for analyzing and determining

trends in data
• Computed based on current and specified number of
immediately preceding values for each point in time
• To examine how these aggregates behave over time
instead of examining the behavior of original or raw data
points
• Working with moving aggregates gives better
representation of the time series
• since longer-term trends are much easier to see with
moving aggregates than with raw data points
• Moving aggregates are often used in financial analysis
27-11-2024 CC ZG515 Data Warehousing 35
 SQL extensions for Analytics
Moving Aggregate Function
• Moving average of sales for one customer for current month
and preceding 2 months

27-11-2024 CC ZG515 Data Warehousing 36

 SQL extensions for Analytics
Moving Aggregate Function (cont’d)

27-11-2024 CC ZG515 Data Warehousing 37

 SQL extensions for Analytics
WINDOW

• A window is basically a set of rows or observations in a table

or result set
• A window function performs a calculation across a set of table
rows that are somehow related to the current row
• Window functions applies aggregate and ranking functions
over a particular window (set of rows)
• OVER clause is used with window functions to define that
window
• OVER clause does two things:
• Partitions rows into form set of rows. (PARTITION BY
clause is used)
• Orders rows within those partitions into a particular order.
(ORDER BY clause is used)

27-11-2024 CC ZG515 Data Warehousing 38

 SQL extensions for Analytics
WINDOW

SELECT [Link], [Link], AVG([Link]) OVER W AS movavg

FROM Sales S, Times T, Locations L
WHERE [Link]=[Link] AND [Link]=[Link]
WINDOW W AS (PARTITION BY [Link] ORDER BY [Link] RANGE
BETWEEN INTERVAL `1’ MONTH PRECEDING AND INTERVAL `1’
MONTH FOLLOWING)

• Answer rows to each row is constructed first by identifying its

WINDOW
• Then, for each answer column defined using a window aggregate
function, we compute the aggregate using the rows in the WINDOW
• Each row of TEMP is a row of sales, tagged with extra details about
time & location dimensions
• One partition for each state and every row of temp belongs to
exactly one partition
27-11-2024 CC ZG515 Data Warehousing 39
 SQL extensions for Analytics
WINDOW

• Define partitions of the table (Partitions are similar to

groups created by GROUP BY)
• Specify the ordering of rows within a partition
• Frame WINDOW: establish the boundaries of the
window associated with each row in terms of ordering
of rows within partitions
• Window for each row includes the row itself, plus all
rows whose month values are within a month before or
after
• A row whose month value is June 2006 has a window
containing all rows with month = May, June, or July
2006
27-11-2024 CC ZG515 Data Warehousing 40
 SQL extensions for Analytics
WINDOW

• Answer rows to each row are constructed first by

identifying its WINDOW
• Then, for each answer column defined using a
window aggregate function, we compute the
aggregate using the rows in the WINDOW
• Each row of sales tagged with extra details
about time & location dimensions
• One partition for each state and every row of
temp belongs to exactly one partition

27-11-2024 CC ZG515 Data Warehousing 41

 SQL extensions for Analytics
List of functions supported by Extended SQL
• Ranking Functions • Inverse Percentile functions
• CUME_Dist (inv percentile) • Hypothetical Rank &
• PERCENT_RANK Distribution
• NTILE • Linear Regression functions
• ROW_NUMBER • Linear Algebra
• Windowing Aggregate • Other Statistical functions:
functions • Descriptive Statistics
• Centred Aggregate functions • Hypothesis Testing
• FIRST_VALUE & LAST_VALUE (Parametric & Non-Parametric
• Reporting Aggregate tests)
functions • User Defined Aggregate
• SUM/AVG/MAS/MIN/COUNT/ functions
• STDDEV/VARIANCE….. • Data Densification for
Reporting
27-11-2024 CC ZG515 Data Warehousing 42
 MDX
• Multidimensional Expressions (MDX) is a standard query language
• Derived from SQL but geared specifically for OLAP databases
• Also includes a calculation language, with syntax similar to
spreadsheet formulas
• Based upon the XML for Analysis (XMLA) specification, with
specific extensions for Analysis Services
• specially designed to retrieve multidimensional data
• Supported by Microsoft in SQL Server
• Basic SELECT statement syntax:

[ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ]

SELECT [ * | ( <SELECT query axis clause>
[ , <SELECT query axis clause> ... ] ) ]
FROM <SELECT subcube clause>
[ <SELECT slicer axis clause> ]
[ <SELECT cell property list clause> ]

27-11-2024 CC ZG515 Data Warehousing 43

 Getting Started with MDX
• Simple MDX expression returning two cube dimensions:
SELECT axis specification ON COLUMNS,
axis specification ON ROWS
FROM cube_name
WHERE slicer_specification
• The axis specification - member selection for the axis
• The slicer specification on the WHERE clause is actually optional
– If not specified, the returned measure will be the default for the
cube
• Simplest axis specification or member selection - taking
MEMBERS of the required dimension, including those of the
special Measures dimension:
SELECT [Link] ON COLUMNS,
[Store].MEMBERS ON ROWS
FROM [Sales]
27-11-2024 CC ZG515 Data Warehousing 44
 MDX SELECT Example
• Query result set contains years 2006 and 2007 Internet Sales Amount and
Internet Order Quantity for Australian customer base:
SELECT
{ [Measures].[internet Sales Amount],
[Measures].[Internet Order Quantity] } ON COLUMNS,
{ [Date].[Calendar].[Calendar Year].[CY 2006],
[Date]. [Calendar].[Calendar Year].[CY 2007] } ON ROWS
FROM
[Adventure Works]
WHERE
([Customer].[Customer Geography].[Country].[Australia])
• SELECT clause sets the query axes: Internet Sales Amount and Internet
Order Quantity members of the Measures dimension (on columns of the
results dataset), and the 2006 and 2007 members of the Date dimension (on
rows of the results dataset)
• FROM clause indicates data source is Adventure Works cube
• WHERE clause defines the slicer axis as the Australia member of Customer
dimension (Customer Geography hierarchy)
27-11-2024 CC ZG515 Data Warehousing 45
 MDX SELECT Example
Query result:

27-11-2024 CC ZG515 Data Warehousing 46

 MDX SELECT Example
• Client organization (Adventure Works) asks to provide the total Internet
Sales Amounts and Internet Order Quantities, for years 2006 and 2007
individually, for all customers in U.K.
• To provide the information in a two-dimensional grid, with the Internet Sales
Amount and Internet Order Quantity measures in the columns, and the
calendar years (2006 and 2007) in the rows

SELECT
{[Date].[Calendar].[Calendar Year].&[2006],
[Date].[Calendar].[Calendar Year].&[2007]}
ON COLUMNS,
{[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity]}
ON ROWS
FROM
[Adventure Works]
WHERE
[Customer].[Customer Geography].[Country].&[United Kingdom]
27-11-2024 CC ZG515 Data Warehousing 47
 Labelled Parts of Basic MDX Query

27-11-2024 CC ZG515 Data Warehousing 48

 Home Exercises
References

• Oracle Data Warehousing Guide 10.2

• SQL Analytics for Analysis, Reporting and Modeling -

Oracle 12c

• Advanced Data Management Technologies by J. Gamper

• MDX SQL Server 2012 Reference

27-11-2024 CC ZG515 Data Warehousing 49

 Data Warehouse Architecture

27-11-2024 CC ZG515 Data Warehousing 1

 Metadata
• Data about data
• Metadata element describes all entities of data warehouse
• Created for data names and definitions of data warehouse
• Additional metadata created and captured for
timestamping any extracted data, source of extracted data,
and missing fields added by data cleaning or integration
• Proper metadata necessary for using, building, and
administering data warehouse
• Used as directory to help decision support system analyst
locate contents of data warehouse
• Should be stored and managed persistently (i.e., on disk)

27-11-2024 CC ZG515 Data Warehousing 2

 Metadata repository contents
• Description of data warehouse structure
– includes warehouse schema, view, dimensions, hierarchies,
and derived data definitions, as well as data mart locations
and contents
• Operational metadata
– include data lineage (history of migrated data and the
sequence of transformations applied to it), currency of data
(active, archived, or purged), monitoring information
(warehouse usage statistics, error reports, audit trails)
• Algorithms used for summarization
– include measure and dimension definition algorithms, data on
granularity, partitions, subject areas, aggregation,
summarization, predefined queries / reports

27-11-2024 CC ZG515 Data Warehousing 3

 Metadata repository contents
• Mapping from operational environment to data warehouse
– includes source databases and their contents, gateway
descriptions, data partitions, data extraction, cleaning,
transformation rules and defaults, data refresh and purging
rules, security (user authorization, access control)
• Data related to system performance
– include indices and profiles that improve data access and
retrieval performance, rules for timing and scheduling of
refresh, update, and replication cycles
• Business metadata
– include business terms and definitions, data ownership
information, charging policies

27-11-2024 CC ZG515 Data Warehousing 4

 Metadata element for entity Customer

27-11-2024 CC ZG515 Data Warehousing 5

 Who needs Metadata

27-11-2024 CC ZG515 Data Warehousing 6

 Metadata – its positioning in DWH
• Various processes during building and administering of data
warehouse generate parts of metadata
• Parts of metadata generated by one process are used by another
• Acts like nerve
center in data
warehouse
• Enables
communication
among various
processes
• Parts of metadata
needed by:
– end-users
– IT developers &
administrators
27-11-2024 CC ZG515 Data Warehousing 7
 Metadata vital for end-users

27-11-2024 CC ZG515 Data Warehousing 8

 Metadata need for IT

27-11-2024 CC ZG515 Data Warehousing 9

 Automation of Warehousing Tasks

27-11-2024 CC ZG515 Data Warehousing 10

 Metadata Types by Functional Areas
• Data acquisition
• Data storage
• Information delivery

• As processes take place, appropriate tools record

metadata elements relating to them

• Tools record metadata elements during development

phases as well as while data warehouse is in
operation after deployment

27-11-2024 CC ZG515 Data Warehousing 11

 Data Acquisition – Metadata types

27-11-2024 CC ZG515 Data Warehousing 12

 Data Acquisition – Metadata types
• IT professionals and part of DWH project team will be using
development tools that record metadata relating to this area
• Tools we use for other processes either in this area or in some
other area may use the metadata recorded in this area
• IT professionals will also use metadata recorded by processes in
data acquisition area for administering and monitoring the ongoing
functions of the DWH after deployment
– will use metadata from this area to monitor ongoing data
extraction and transformation
• Users of DWH for queries will also use metadata recorded in this
area
– E.g., when the user wants to know how profit margin has been
calculated and stored in the data warehouse, user will look up
the derivation rules in the metadata recorded in the data
acquisition area
27-11-2024 CC ZG515 Data Warehousing 13
 Data Storage – Metadata types

27-11-2024 CC ZG515 Data Warehousing 14

 Data Storage – Metadata types
• Functional areas -
– Data loading
– Data archiving
– Data management
• As processes take place in data storage functional area, appropriate tools record metadata
elements relating to processes
• Metadata recorded by processes in data storage area is used for development,
administration, and by users
• Metadata from this area used for designing full data refreshes and incremental data loads
• DBA will be using metadata for backup, recovery, and tuning database
• DWH Admin will use metadata from this area for purging data warehouse and for
archiving of data
• E.g., a user wants to create a query breaking total quarterly sales down by sale districts
– Before the user runs query, he would like to know the last time data on district
delineation was loaded
– Metadata recorded by data loading process in data storage functional area will give
user latest load date for district delineation

27-11-2024 CC ZG515 Data Warehousing 15

 Information Delivery – Metadata types
• Functional areas –
– Report generation
– Query processing
– Complex analysis
• Processes in this area meant for end-users – who use metadata recorded in
processes of other two areas of data acquisition and data storage
• When a user creates a query with query processing tool, he can refer back to
metadata recorded in the data acquisition and data storage areas and can look
up source data configurations, data structures, and data transformations from
the metadata recorded in data acquisition area
• Likewise, from metadata recorded in data storage area, user can find date of
last full refresh and incremental loads for various tables in DWH database
• Metadata recorded in information delivery functional area relate to predefined
queries, predefined reports, input parameter definitions for queries and
reports
• Metadata recorded here also include information for OLAP - developers and
administrators involved in these processes
27-11-2024 CC ZG515 Data Warehousing 16
 Information Delivery – Metadata types

27-11-2024 CC ZG515 Data Warehousing 17

 Business Metadata
• Metadata types may also be classified as
– business metadata
– technical metadata
• another effective method of classifying metadata types because nature and format of metadata
markedly different in each group
• Business users need to know what is available in the data warehouse from a perspective
different from that of IT professionals
• Business metadata is like a roadmap or an easy-to-use information directory showing the
contents and how to get there - tour guide for executives and a route map for managers and
business analysts
• must describe the contents in plain language, giving information in business terms
• less structured than technical metadata - originates from textual documents, spreadsheets, and
even business rules and policies not written down completely
• All informal metadata must be captured, put in a standard form, and stored as business
metadata in the data warehouse
• business users do not have technical expertise to create their own queries or format their own
reports - need to know what predefined queries are available and what preformatted reports
can be produced
• must be able to identify the tables and columns in the data warehouse by referring to them by
business names
27-11-2024 CC ZG515 Data Warehousing 18
 Business Metadata - Examples
• Connectivity procedures • Data ownership
• Security and access privileges • Query and reporting tools
• Overall structure of data in • Predefined queries
business terms • Predefined reports
• Source systems • Report distribution
• Source-to-target mappings information
• Data transformation business • Common information
rules access routes
• Summarization and • Rules for analysis using
derivations OLAP
• Table names and business • Currency of OLAP data
definitions • Data warehouse refresh
• Attribute names and schedule
business definitions
27-11-2024 CC ZG515 Data Warehousing 19
 Business Metadata - Contents
Info. from business metadata for end-users
• How to sign on to and connect with data warehouse
• Which parts of data warehouse to access
• To see all attributes from a specific table
• Definitions of attributes needed in queries
• Any queries and reports already predefined to give needed results
• Source system of needed data
• Default values were used for data items retrieved by query
• Types of aggregations available for needed metrics
• How the value in the needed data item is derived from other data
items
• When was last update for the data items in user query
• On which data items to perform drill-down analysis
• How old is OLAP data? Should we wait for the next update?
27-11-2024 CC ZG515 Data Warehousing 20
 Business Metadata - Benefits
Info. from business metadata – who benefit:
• Managers
• Business analysts
• Power users
• Regular users
• Casual users
• Senior managers/junior executives

27-11-2024 CC ZG515 Data Warehousing 21

 Technical Metadata
• Meant for IT staff responsible for development and administration of
data warehouse
• Technical personnel need information to design each process in every
functional area of data warehouse
• Technical group on the project team must know proposed structure and
content of DWH
• Technical staff need to understand data extraction, data transformation,
and data cleansing processes
• IT staff require technical metadata for three distinct purposes
– for initial development of data warehouse
– for ongoing growth and maintenance of DWH
– for continuous administration of production data warehouse
– Administrator has to monitor ongoing data extractions
– ensure incremental loads are completed correctly and on time
– Do database backups and archiving of old data
27-11-2024 CC ZG515 Data Warehousing 22
 Technical Metadata - Examples
• Data models of source systems
• Record layouts of outside sources
• Source to staging area mappings
• Staging area to data warehouse mappings
• Data extraction rules and schedules
• Data transformation rules and versioning
• Data aggregation rules
• Data cleansing rules
• Summarization and derivations
• Data loading and refresh schedules and controls
• Job dependencies

27-11-2024 CC ZG515 Data Warehousing 23

 Technical Metadata - Contents
• databases and tables • default values used for data items
• columns for each table while cleaning up missing data
• keys and indexes • types of aggregations available
• physical files • derived fields and their rules for
• business descriptions derivation
correspondence with technical • When was the last update for the
ones data items in my query?
• last successful update • What are the load and refresh
• source systems and their data schedules?
structures • How often is data purged or
• data extraction rules for each data archived? Which data items?
source • What is the schedule for creating
• source to target mapping for each data for OLAP?
data item in DWH • What query and report tools are
• data transformation rules available?
27-11-2024 CC ZG515 Data Warehousing 24
 Metadata Sources
Source Systems
• Data models of operational systems (manual or with CASE tools)
• Definitions of data elements from system documentation
• Physical file layouts and field definitions
• Program specifications
• File layouts and field definitions for data from outside sources
• Other sources such as spreadsheets and manual lists
Data Extraction
• Data on source platforms and connectivity
• Layouts and definitions of selected data sources
• Definitions of fields selected for extraction
• Criteria for merging into initial extract files on each platform
• Rules for standardizing field types and lengths
• Data extraction schedules
• Extraction methods for incremental changes
• Data extraction job streams
27-11-2024 CC ZG515 Data Warehousing 25
 Metadata Sources
Data Transformation and Cleansing
• Specifications for mapping extracted files to data staging files
• Conversion rules for individual files
• Default values for fields with missing values
• Business rules for validity checking
• Sorting and resequencing arrangements
• Audit trail for the movement from data extraction to data staging
Data Loading
• Specifications for mapping data staging files to load images
• Rules for assigning keys for each file
• Audit trail for the movement from data staging to load images
• Schedules for full refreshes
• Schedules for incremental loads
• Data loading job streams
27-11-2024 CC ZG515 Data Warehousing 26
 Metadata Sources
Data Storage
• Data models for centralized data warehouse and dependent
data marts
• Subject area groupings of tables
• Data models for conformed data marts
• Physical files
• Table and column definitions
• Business rules for validity checking
Information Delivery
• List of query and report tools
• List of predefined queries and reports
• Data model for special databases for OLAP
• Schedules for retrieving data for OLAP
27-11-2024 CC ZG515 Data Warehousing 27
 Metadata Repository

27-11-2024 CC ZG515 Data Warehousing 28

 Metadata Repository
Functions of information navigator
• Interface from Query Tools
– attaches data warehouse data to third-party query tools so
that metadata definitions inside technical metadata may
be viewed from these tools
• Drill Down for Details
– User of metadata can drill down and proceed from one
level of metadata to a lower level for more information.
E.g., we can first get the definition of a data table, then go
to the next level for seeing all attributes, and go further to
get the details of individual attributes
• Review Predefined Queries and Reports
– user is able to review predefined queries and reports, and
launch selected ones with proper parameters
27-11-2024 CC ZG515 Data Warehousing 29
 Physical Design of Data Warehouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 1

 Develop Standards
• Standards take on greater importance in the data
warehouse environment
• Standards range from how to name fields in
database to how to conduct interviews with user
departments for requirements definition
• Standards ensure consistency across the various
areas

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 2

 Create Aggregates Plan
• Building aggregate or summary tables -
comprehensive plan
• Types of aggregates that must be built for each
level of summarization
• Many aggregates may be present in the OLAP
system
• Aggregate database tables must be laid out and
included in the physical model

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 3

 Determine Data Partitioning Scheme
• Large tables are not easy to manage
• Back up and recovery of large tables difficult due to large sizes
• Partitioning divides large database tables into manageable
parts
• Partitioning options for fact tables & dimension tables
• Partitioning scheme must include:
– fact tables and the dimension tables selected for partitioning
– type of partitioning for each table—horizontal or vertical
– number of partitions for each table
– criteria for dividing each table (e.g., by product groups)
– description of how to make queries aware of partitions

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 4

 Establish Clustering Options
• In data warehouse, many of the data access patterns rely on sequential access of
large quantities of data
• Hence much performance improvement from clustering -
– involves placing and managing related units of data in the same physical
block of storage
• Causes related units of data to be retrieved together in a single input operation
• To establish proper clustering before completing physical model
• Examine the tables, find pairs that are related
• Rows from related tables are usually accessed together for processing in many
cases
• Make plans to store the related tables close together in the same file on the
storage medium
– For two related tables, you may want to store the records from both files
interleaved
– A record from one table is followed by all the related records in the other
table while storing in the same physical file

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 5

 Prepare an Indexing Strategy

• Crucial step in physical design

• Unlike OLTP systems, DWH is query-centric - so
indexing does improve performance
• Indexing plan for each table -> indicating columns
selected for indexing
• sequence of attributes in each index also affects
performance
• Scrutinize the attributes in each table to determine
which attributes qualify for bitmapped indexes

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 6

 Assign Storage Structures
• Plan for assigning each table to specific files
• Divide each physical file into blocks of data
• Storage for physical files of data warehouse
tables alone not enough
• Storage assignment plan must include
other types of storage such as the
temporary data extract files, the staging
area, and any storage needed for front-end
applications

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 7

 Complete Physical Model
• Final step - reviews and confirms completion of
activities and tasks
• Have standards for naming database objects
• Determined which aggregate tables are necessary
and how to partition large tables
• Completed indexing strategy and planned for
other performance options
• Know where to put physical files
• Result is creation of physical schema
• Create physical structure in data dictionary

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 8

 Physical Design Objectives
Improve Performance
• In data warehouse and OLAP environments, response time
beyond a few minutes is not acceptable
• To improve performance to keep the response time at this
level
• To ensure that performance is monitored regularly and the
data warehouse is kept fine-tuned
• Monitoring and improving performance must happen at
different levels
• Performance of DBMS
• Higher levels of logical database design, application design,
and query formatting also contribute to overall performance

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 9

 Physical Design Objectives
Ensure Scalability
• usage of the data warehouse escalates over time, with a
sharper increase during the initial period
• usage increases on two counts
• number of users increases rapidly and the complexity of the
queries intensifies
• As number of users increases, the number of concurrent
users of the data warehouse also increases proportionately
• Adopt methods to address the escalation in the usage of the
data warehouse on both counts

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 10

 Physical Design Objectives
Manage Storage
• Proper management of stored data will boost performance
• improve performance by storing related tables in same file
• manage large tables more easily by storing parts of tables at
different places in storage
• set the space management parameters in DBMS to optimize
use of file blocks

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 11

 Physical Design Objectives
Ease of Administration
• includes methods for proper arrangement of table
rows in storage so that frequent reorganization is
avoided
• back up and recovery of database tables
• Make administration of storage or DBMS easy

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 12

 Physical Design Objectives
Design for Flexibility
• In terms of physical design, flexibility implies
keeping design open
• it must be easy to propagate changes to data
model to physical model
• physical design must have built-in flexibility to
satisfy future requirements

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 13

 From Logical Model to Physical Model
Activities that transform a logical model into a physical model

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 14

 Physical Model Components
• physical model represents info content at level closer to
hardware
• Model has details such as file sizes, field lengths, data types,
primary keys, and foreign keys

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 15

 Logical model and physical model
• components of logical model related to those of physical model

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 16

 Physical storage
Data structures in the warehouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 17

 Optimizing Physical storage
• Structure is stored as files in physical storage medium
• A collection of records in a file forms a block
• A file comprises blocks - each block contains records
• Set Correct Block Size
• Set Proper Block Usage Parameters
• Manage Data Migration
• Manage Block Utilization
• Resolve Dynamic Extension
• Employ File Striping Techniques
• Using RAID Technology
• Estimating Storage Sizes
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 18
 Query Processing
• Pre-computed views / Aggregates
• SQL Extensions
• Indexing

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 19

 Pre-computed views / Aggregates
• Pre-computed aggregates -- special case of materialized views
• Keep aggregated data for efficiency (pre-computed queries)
• Questions
– Which aggregates to compute?
– How to update aggregates?
– How to use pre-computed aggregates in queries?

• Aggregated table can be maintained by the

– warehouse server
– middle tier
– client applications

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 20

 SQL Extensions
• Extended family of aggregate functions
– rank (top 10 customers)
– percentile (top 30% of customers)
– median, mode
– Object Relational Systems allow addition of new
aggregate functions
• Reporting features
• running total, cumulative totals
• Cube operator
• group by on all subsets of a set of attributes (month,
city)
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 21
 Indexing the Data Warehouse
• Indexes and Loading
• Indexing for Large Tables
• Index-Only Reads
• Selecting Columns for Indexing
• Staged Approach

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 22

 Indexing the Data Warehouse
B-Tree Index
• Default indexing method of DBMSs
• indexes automatically on primary key values
• superior to other techniques due to data retrieval speed, ease of
maintenance, and simplicity
• Tree structure with root at the top
• Index has B-Tree (a balanced binary tree) structure based on values of the
indexed column (in the shown Example, Name)
• Here, B-Tree is created using all existing names that are values of the
indexed column
• If a column in a table has many unique values, then the selectivity of the
column is said to be high
• B-Tree indexing most suitable for highly selective columns
• Indexes grow direct proportional to growth of indexed data table
• B-Tree indexes do not work well with data whose selectivity is low

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 23

 Indexing the Data Warehouse
B-Tree Index

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 24

 Indexing the Data Warehouse
Bitmapped Index
• Ideally suitable for low-selectivity data
• popular in OLAP products because it allows quick searching in data
cubes
• A bitmap is an ordered series of bits, one for each distinct value of the
indexed column
• Figure (Example next) presents an extract of Sales table and bitmapped
indexes for three columns
• Each entry in an index contains ordered bits to represent distinct values
in the column
• An entry is created for each row in the base table
• Each entry carries the address of the base table row
• Take significantly less space than B-Tree indexes for low-selectivity
columns
• More suitable for a data warehouse environment than for OLTP system
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 25
 Indexing the Data Warehouse
Bitmapped Index
Example-1

CSI ZG515 / SS ZG515/SE ZG515 Data

27-11-2024 26
Warehousing
 Indexing the Data Warehouse
Bitmapped Index – Data Retrieval – Example 1

How Boolean logic is

applied to find the result
set based on the
bitmapped indexes

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 27

 Indexing the Data Warehouse
Bitmapped Index Example-2
• In the AllElectronics data warehouse, suppose the dimension item at the top level
has four values (representing item types): “home entertainment,” “computer,”
“phone,” and “security.”
• Each value (e.g., “computer”) is represented by a bit vector in the item bitmap
index table
• Suppose that the cube is stored as a relation table with 100,000 rows
• Because the domain of item consists of four values, the bitmap index table requires
four bit vectors (or lists), each with 100,000 bits.
Bitmapped Index Benefits
• Bitmap indexing is advantageous compared to hash and tree indices
• Especially useful for low-cardinality domains because comparison, join, and
aggregation operations are then reduced to bit arithmetic, which substantially
reduces processing time
• Significant reductions in space and input/output (I/O) since a string of characters
can be represented by a single bit
• For higher-cardinality domains, the method can be adapted using compression
techniques
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 28
 Indexing the Data Warehouse
Indexing OLAP data using bitmap indices- Example 2
• a base (data) table containing the dimensions item and city, and its
mapping to bitmap index tables for each of the dimensions.

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 29

 Indexing the Data Warehouse
Join Indexing
• Traditional indexing maps the value in a given column to a list
of rows having that value
• In contrast, join indexing registers the joinable rows of two
relations from a relational database
• E.g., if two relations R(RID, A) and S(B, SID) join on the
attributes A and B, then the join index record contains the pair
(RID, SID), where RID and SID are record identifiers from the R
and S relations, respectively
• Hence the join index records can identify joinable tuples
without performing costly join operations
• Join indexing is especially useful for maintaining the
relationship between a foreign key and its matching primary
keys, from the joinable relation
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 30
 Indexing the Data Warehouse
Join Indexing
• The star schema model of data warehouses makes join
indexing attractive for cross table search, because the linkage
between a fact table and its corresponding dimension tables
comprises the fact table’s foreign key and the dimension
table’s primary key
• Join indexing maintains relationships between attribute values
of a dimension (e.g., within a dimension table) and the
corresponding rows in the fact table
• Join indices may span multiple dimensions to form composite
join indices
• We can use join indices to identify subcubes that are of
interest

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 31

 Indexing the Data Warehouse
Join Indexing
Star schema for AllElectronics of the form “sales star [time, item, branch,
location]: dollars sold D sum (sales in dollars).”

Star schema of
sales data
warehouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 32

 Indexing the Data Warehouse
Join Indexing
• An example of a join index relationship between the sales fact table and
the location and item dimension tables is shown in Figure below
• For example, the “Main Street” value in the location dimension table joins
with tuples T57, T238, and T884 of the sales fact table
• Similarly, the “Sony-TV” value in the item dimension table joins with tuples
T57 and T459 of the sales fact table

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 33

 Indexing the Data Warehouse
Join Indexing
The corresponding join
index tables

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 34

 Indexing the Data Warehouse
Join Indexing
• Suppose that there are 360 time values, 100 items, 50
branches, 30 locations, and 10 million sales tuples in the sales
star data cube
• If the sales fact table has recorded sales for only 30 items, the
remaining 70 items will obviously not participate in joins
• If join indices are not used, additional I/Os have to be
performed to bring the joining portions of the fact table and
the dimension tables together
• To further speed up query processing, the join indexing and
the bitmap indexing methods can be integrated to form
bitmapped join indices

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 35

 Indexing the Data Warehouse
Clustered Indexes
• In sequential indexing method, separate data segment
(where the values of all columns are stored) and index
segment (where index entries are kept)
• In Clustered Indexing, both segments are combined
• Clustered tables improve performance considerably
because in one read you get the index and the data
segments
• Queries run faster with clustered tables when you are
looking for exact matches or searching for a range of
values
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 36
 Indexing the Data Warehouse
Indexing the Fact Table
• If there is no index on the primary key, then we may create a
B-Tree index on the full primary key
• Order of individual key elements in the full concatenated key
for indexing should be in the order of the frequency of keys of
the dimension tables frequently queried
• As required, we can also create indexes on each individual
component of the concatenated key for query performance
• We can also index the metrics columns if frequently queried
upon
• Bitmapped indexing does not apply to fact tables, as there are
hardly any low-selectivity columns

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 37

 Indexing the Data Warehouse
Indexing the Dimension Tables
• Columns in the dimension tables are used in the predicates of queries, so query
performance based on them
• Example query - How much are the sales of product A in the month of March for
the northern division?
• Here the columns product, month, and division from three different dimension
tables are candidates for indexing
• Create a unique B-Tree index on the single-column primary key
• Columns that are commonly used to constrain the queries are candidates for
bitmapped indexes
• Look for columns that are frequently accessed together in large dimension tables.
Determine how these columns may be arranged and used to create multicolumn
indexes
• columns that are more frequently accessed or the columns that are at the higher
hierarchical levels in the dimension table are placed at the high order of the
multicolumn indexes
• Individually index every column likely to be used frequently in join conditions

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 38

 Performance Enhancement Techniques
Data Partitioning
• Splitting of a table and its index data into manageable parts
• effective technique for storage management and improving performance
• DBMS supports and provides mechanism for partitioning
• split a large table vertically or horizontally
• In vertical partitioning, you separate out the partitions by grouping selected
columns together
– Each partitioned table contains the same number of rows as the original
table
– Usually, wide dimension tables are candidates for vertical partitioning
• Horizontal partitioning - divide the table by grouping selected rows together
• In DWH, horizontal partitioning based on calendar dates - split a table into
partitions of recent events and past history
• Horizontal partitioning of the fact tables produces great benefits

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 39

 Performance Enhancement Techniques
Partitioning Benefits
• A query needs to access only the necessary partitions. Applications can be
given the choice to have partition transparency or they may explicitly
request an individual partition. Queries run faster when accessing smaller
amounts of data
• An entire partition may be taken off-line for maintenance. You can
separately schedule maintenance of partitions. Partitions enable
concurrent maintenance operations
• Index building is faster
• Loading data into the data warehouse is easy and manageable
• Data corruption affects only a single partition
• Backup and recovery on a single partition reduces downtime
• The input–output load gets balanced by mapping different partitions to
the various disk drives

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 40

 Performance Enhancement Techniques
Data Clustering
• In the data warehouse, many queries require sequential
access of huge volumes of data
• Clustering fosters sequential prefetch of related data
• Cluster data by physically placing related tables close to each
other in storage
• When you declare a cluster of tables to the DBMS, the tables
are placed in neighboring areas on the disk
• DBMS provides data clustering features

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 41

 Performance Enhancement Techniques
Parallel Processing
• Consider a query that accesses large quantities of data, performs summations,
and then makes a selection based on multiple constraints
• major performance improvement if processing is split into components which
are executed in parallel
• The simultaneous concurrent executions will produce the result faster
• Several DBMS vendors offer parallel processing features that are transparent to
users
• As a designer of the query, the user need not know how a specific query must be
broken down for parallel processing - DBMS will do that for the user
• parallel architecture of server hardware also affects the way parallel processing
options work - physical options critical for effective parallel processing
• Parallel processing techniques may be applied to data loading, data
reorganization, data partitioning schemes
• Parallel processing and partitioning together provide great potential for
improved performance, if used effectively

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 42

 Performance Enhancement Techniques
Summary Levels
• Data warehouse must keep both detailed and summary data
• Select levels of granularity to optimize I/O operations – keep
summary and detail levels based on user needs
• Rolling summary structures are especially useful in a data
warehouse
• E.g., if we need to keep hourly data, daily data, weekly data,
and monthly summaries, create mechanisms to roll the data
into next higher levels automatically with passage of time
– i.e., hourly data automatically gets summarized into the
daily data, daily data into the weekly data, and so on

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 43

 Performance Enhancement Techniques
Referential Integrity Checks
• Referential integrity constraints ensure validity
between two related tables
• Referential integrity verification is critical in OLTP,
but reduces performance
• Loading of data into data warehouse
– During ETL itself, necessary verification done – no
further need for referential integrity verification
while loading the data
• Turning off referential integrity verification improves
performance
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 44
 Performance Enhancement Techniques
Initialization Parameters
• Setting initialization parameters appropriately also important for
good performance
• E.g., maximum number of concurrent users
• checkpoint frequency – DBMS writing checkpoint records
Data Arrays
• E.g., financial data mart you need to keep monthly balances of
individual line accounts
– users query the balances for all months together
• data array or repeating group with 12 slots, each to contain the
balance for one month
• E.g., request for monthly sales figures for 24 months for each
salesperson

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 45

 Data Warehousing Quality
What is Data Quality?

• Fitness for use – means required level of quality of

data depends on the context

• Data quality in a data warehouse is not just the

quality of individual data items but the quality of the
full, integrated system as a whole

• Data quality is a multidimensional concept involving

various aspects or criteria by which to assess the
quality of a dataset or individual data record
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 1
 Data Warehousing Quality
Indicators of high-quality data:
• Accuracy
• Domain integrity
• Data type
• Consistency
• Redundancy
• Completeness
• Duplication
• Conformance to Business Rules
• Structural Definiteness
• Data Anomaly
• Clarity
• Timely
• Usefulness
• Adherence to Data Integrity Rules
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 2
 Data Quality Dimensions
• Data accuracy - whether data values stored are correct
• e.g., name of customer should be spelled correctly
• Data completeness - whether both metadata and values
are represented to the degree required and are not missing
• e.g., a date of birth should be filled out for each
customer
• Data consistency – (1) between redundant or duplicate
values (2) among different data elements referring to same
or related concept
• e.g., name of city and postal code should be consistent
• Data accessibility - ease of retrieving data
• Timeliness – extent to which data are sufficiently up-to-
date for task at hand

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 3

 Data Warehousing Quality
Data accuracy vs. data quality

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 4

 Data Warehousing Quality
Why Data Quality is critical

Improved data quality:

• improves decision making
• enables better customer service
• increases opportunity to add better value to the services
• reduces risk from disastrous decisions
• reduces costs, especially of marketing campaigns
• enhances strategic decision making
• improves productivity by streamlining processes
• avoids compounding the effects of data contamination
• ----------------------
• Bad data leads to bad decisions
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 5
 Data Warehousing Quality
• Data quality is biggest challenge in data warehouse
development and usage
• not just because of complexity and extent of data pollution
• effect of polluted data on strategic decisions made based
on such data

Benefits of Improved Data Quality

• Analysis with Timely Information
• Better Customer Service
• Newer Opportunities
• Reduced Costs and Risks
• Improved Productivity
• Reliable Strategic Decision Making
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 6
 Types of Data Quality Problems
• Dummy Values in Fields
• Absence of Data Values
• Unofficial Use of Fields
• Cryptic Values
• Contradicting Values
• Violation of Business Rules
• Reused Primary Keys
• Non-unique Identifiers
• Inconsistent Values
• Incorrect Values
• Multipurpose Fields
• Erroneous Integration

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 7

 Sources of Data Pollution
• System Conversions
• Data Aging
• Heterogeneous System Integration
• Poor Database Design
• Incomplete Information at Data Entry
• Input Errors
• Internationalization/Localization
• Fraud
• Lack of Policies
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 8
 Validation of Names and Addresses
Data entry: name and address formats

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 9

 Data Quality Tools
Error Discovery Features of data cleansing tools

• Quickly and easily identify duplicate records

• Identify data items whose values are outside the range of
legal domain values
• Find inconsistent data
• Check for range of allowable values
• Detect inconsistencies among data items from different
sources
• Allow users to identify and quantify data quality problems
• Monitor trends in data quality over time
• Report to users on the quality of data used for analysis
• Reconcile problems of RDBMS referential integrity

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 10

 Data Quality Tools
Data Correction Features of data cleansing tools

• Normalize inconsistent data

• Improve merging of data from dissimilar data sources
• Group and relate customer records belonging to the
same household
• Provide measurements of data quality
• Standardize data elements to common formats
• Validate for allowable values

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 11

 Quality Control in DBMS
Database management system itself is used as a tool for
data quality control in many ways such as:

• Domain Integrity
• Update Security
• Entity Integrity Checking
• Minimize missing values
• Referential Integrity Checking
• Conformance to Business Rules

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 12

 Data Quality Framework

• Need to establish a data quality framework

• provides a basis for launching data quality initiatives

• embodies a systematic plan for action

• identifies the players, their roles, responsibilities

• guides data quality improvement effort

• major functions carried out within the framework ->

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 13

 Data Quality Framework

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 14

 Data quality: participants and roles

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 15

 Data Purification Process

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 16

 Data Warehouse Quality Guidelines
• Identify high-impact pollution sources and begin
purification process
• Do not try to do everything with in-house programs
• Tools are good and useful. Select the proper tools
• Agree on standards and reconfirm these
• Link data quality with specific business objectives
• Get senior officials actively involved in data cleansing
• Get users totally involved and keep them trained and also
constantly informed
• Wherever needed, bring in outside experts for specific
assignments

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 17

 Master Data Management (MDM)
• Many Organizations initiative to Data Quality by adopting
an overall master data management approach
• MDM is an umbrella approach to provide consistent and
comprehensive core information across the organization
• It strives for a single version of high quality master data
• Focus is on unifying companywide reference data types
such as customers and products
• Master data may also include data about other entities
such as business partners, employees, sales contacts, and
physical assets
• MDM is as much about information strategy as it is about
software
• It cuts across business processes, software, data
stewardship, data governance, and business information
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 18
 Master Data Management (MDM)
• comprises a series of processes, policies, standards, and
tools to help organizations to define and provide single
point of reference for all data that are “mastered”
• key concern is to provide a trusted, single version of the
truth on which to base decisions
• to ensure that organizations do not use multiple,
potentially inconsistent versions of same concept in
different parts of their operations
• Modern information systems can be very complicated and
entangled constructs, which should emphasize need for
master data management

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 19

 Master Data Management (MDM)
• MDM initiatives provide a means for ensuring data
quality in data warehouse
• Setting up a master data management initiative involves
many steps and tools, including
• data source identification
• mapping out the systems architecture
• constructing data transformation
• cleansing and normalization rules
• providing data storage capabilities
• monitoring and governance facilities
• Another key element is a centrally governed data model
and metadata repository

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 20

 MDM Categories

MDM solutions – 3 broad categories:

• categorization may vary from organization to organization

• In some organizations, all three categories may not be
relevant
• Operational MDM - integrated with and used with
operational applications for CRM, ERP, financial systems, etc.
• Analytic MDM - predominant in data warehousing, useful for
obtaining quality master data.
• Enterprise MDM - much wider in scope than other
categories, covers all aspects of master data within
enterprise

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 21

 MDM Benefits
• Reduction in cost and complexity of processes
• Improvement in ability to consolidate, share, and analyze
business information in a timely manner
• Possibility to rapidly assemble new, composite applications with
accurate master data and reusable business processes
• Reduction in time to market by having a single system for creating
and maintaining product information, promotions, and consumer
communications
• Improvements to supply chain with single, accurate, well-defined
definitions of products and suppliers, eliminating duplications
• Enhanced customer service, with a complete view of each
customer designed to better anticipate customer needs and
provide targeted offers
• Better overall integration, eliminating information silos present
across divisions within organization
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 22
 MDM and Data Warehousing
• Organizations tend to address data quality problems in data
warehouse only by rectifying problems downstream, NOT at source
• Neither do organizations back- propagate the corrections made in
data staging area or data warehouse
• MDM provides a way to correct bad master data at source so that
data will be high quality when it reaches data warehouse
• Leads to more accurate business intelligence
• Creation of a system of record (a trusted single data source) is
underlying theme of MDM
• Aim is to establish an authenticated master copy from which entity
definitions and physical data can flow among all applications
integrated through the MDM initiative
• Many enterprises build a central data warehouse or an operational
data store as a hub through master data definitions, metadata, and
content synchronized for all applications
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 23
 M10: DATA WAREHOUSING AND THE WEB

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 24

 Web-Enabled Data Warehouse
• A Web-enabled data warehouse uses Web for information delivery and
collaboration among users
• In a maturing data warehousing environment, more and more data
warehouses have been connected to Web
• Essentially, this means an increase in access to information in data warehouse
• Increase in information access, in turn, means increase in knowledge level of
enterprise
• even before connecting to Web, you could give access for information to more
of your users, but with much difficulty and a proportionate increase in
communication costs
• Web has changed all that. It is now a lot easier to add more users
• communications infrastructure is already there
• Almost all users have Web browsers. No additional client software is required
• We can leverage the Web that already exists
• exponential growth of Web, with its networks, servers, users, and pages, has
brought about adoption of Internet, intranets, and extranets as information
transmission media
• Web-enabled data warehouse takes center stage in Web revolution
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 25
 Web-Enabled Data Warehouse
• 3 information delivery mechanisms that companies have adopted
based on Web technology
• In each case, users access information with Web browsers
Internet
• Internet provides low-cost transmission of information
• exchange information with anyone within or outside company
• Because information is transmitted over public networks, security
concerns must be addressed
Intranet
• private computer network based on data communications standards
of public Internet
• applications posting information over intranet all reside within firewall
and hence more secure
• Have all benefits of Web technology
• In addition, we can manage security better on intranet

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 26

 Web-Enabled Data Warehouse
Extranet
• Not completely open like Internet, nor restricted for internal use like
intranet
• intranet that is open to selective access by outside parties
• From intranet, in addition to looking inward and downward, we can
look outward and upward to your customers, suppliers, and business
partners
• Figure illustrates how information from data warehouse may be
delivered over these information delivery mechanisms
• how data warehouse may be deployed over Web
• to restrict data warehouse to internal users, use intranet
• To open up to outside parties with proper authorization -> extranet
• In both cases, information delivery technology and transmission
protocols are same

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 27

 Web-Enabled Data Warehouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 28

 Intranet & Extranet - Advantages
• With a universal browser, users will have a single point of entry for
information
• Minimal training required to access information
• Users already know how to use a browser
• Universal browsers will run on any systems
• Web technology opens up multiple information formats to users
• They can receive text, images, charts, even video and audio
• easy to keep intranet/extranet updated so there will be one source of
information
• Opening up data warehouse to business partners over extranet fosters
and strengthens these partnerships
• Deployment and maintenance costs are low for Web-enabling data
warehouse
• network costs are less, Infrastructure costs low

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 29

 Convergence of Technologies
• Web technology and data warehousing have converged
• Web-enabled versions of data warehousing products increased
• We need to carry out a no. of tasks to adapt data warehouse for Web
• requisites for adapting data warehouse to Web:
• Information “Push” Technique
• data warehouse was designed and implemented using the “pull”
technique
• information delivery system pulls information from data warehouse based
on requests, and then provides it to users
• But Web can “push” information to users without their asking for I
• Datawarehouse must be able to adopt “push” technique
• Ease of Usage
• With availability of clickstream data, we can very quickly check behavior of
user at site
• Among other things, clickstream data reveals how easy or difficult it is for
users to browse pages
• Ease of use tops list of requirements

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 30

 Convergence of Technologies
• Speedy Response
• Some data warehouses allow jobs to run long to produce
the desired results. In the Web model, speed is expected
and cannot be negotiated or compromised.
• No Downtime
• Web model is designed so that the system is available all
the time. Similarly, the Web-enabled data warehouse has
no downtime
• Multimedia Output
• Web pages have multiple data types: textual, numeric,
graphics, sound, video, animation, audio, and maps
• These types are expected to show as outputs in information
delivery system of Web-enabled data warehouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 31

 Convergence of Technologies
• Market of One
• Web information delivery is tending to become highly
personalized, with dynamically created XML pages replacing
static HTML coding
• Web-enabled data warehouses will have to follow suit
• Scalability
• More access, more users, and more data—results of Web
enabling data warehouse.
• Therefore, scalability becomes a primary concern

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 32

 Web as a Data Source
• Web content is a valuable and potent data source for data
warehouse
• Information content on Web is so disparate and fragmented
• need to build special search and extract system to sift through
information and pick up what is relevant for data warehouse
• Assume that project team is able to build such an extraction
system, then selection and extraction consists of a few distinct
steps
• Before extraction, we must verify accuracy of source data
• Just because data was found on Web, we cannot assume it is
accurate
• We can get clues to accuracy from types of sources
• Figure shows an arrangement of components for data selection
and extraction from Web

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 33

 Web as a Data Source

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 34

 Web as a Data Source
• How to use Web content to enrich data warehouse
• Add more descriptive attributes to business dimensions
• Include nominal or ordinal data about a dimension so that
more options are available for pivoting and cross-tabulations
• Add linkage data to a dimension so that correlation analysis
with other dimensions can be performed
• Create new dimension and fact tables as necessary
• Data selection and data extraction from Web is a radically new
paradigm

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 35

 Usefulness of Clickstream Data
• Clickstream data most important source for identifying and retaining e-
commerce customers
• list of useful information derivable from clickstream data:
• Effectiveness of sales promotions
• Affinities between products that are likely to be bought together
• Customer demographics
• General buying patterns of customers
• Referring partner links
• Site statistics
• Site navigations resulting in sales
• Site navigations not producing sales
• Ability to differentiate between customer types

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 36

 Web-based information delivery
• convergence of Web technology and data warehousing is inevitable
• The two technologies deal with providing information
• Web technology is able to deliver information more easily & always
• companies want to Web enable data warehouses
• advantages and possibilities when we connect warehouse to Web
• ability to come up with newer ways of making data warehouse more
effective through extranet data marts
• better information delivery remains most compelling reason for adapting
data warehouse for Web
• Web brings new outlook on information delivery
• Expanded Usage - Users can use browser to perform queries easily always
• No synchronizing distributed data warehouses in client/server
environments
• Use of data warehouse expands beyond internal users
• parties from outside can now be granted access to use warehouse content
• As usage expands, no scalability issue
• User training costs are minimal because of use of Web browser
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 37
 Growth of a Web-enabled data warehouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 38

 Growth of a Web-enabled data warehouse
• supergrowth occurs only in very initial stages
• Afterwards, usage curve seems to level off, or at least rate of increase
becomes manageable
• no industry-standard charts to predict supergrowth pattern
• supergrowth pattern of data warehouse depends entirely on
circumstances and conditions of environment
• We have to define graph for our environment by using best techniques
for estimation

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 39

 OLAP and WEB
• For maximizing value potential, we need to cater to large user group and tap into
potential of warehouse
• includes extension of OLAP capabilities to a larger group of analysts
Enterprise OLAP
• Web-enabled data warehouses can open their doors to a large group of users
both within and outside enterprise
• OLAP services can be extended to more than a select group of analysts
Web-OLAP Approaches
• Web technology, data warehouse with OLAP system, thin-client architecture
• How to implement OLAP in such an environment
• How will OLAP system work in Web-enabled data warehouse
• What kind of client and Web architecture will produce optimum results
• Browser plug-ins - more like thin client
• Pre-created HTML documents - users cannot do typical online analytical
processing
• OLAP server - use server to do all online analytical processing and present
results on a true thin-client information interface
• provides integrated server environment irrespective of client machines
• Everyone shares same components: server, metadata, and reports
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 40
 OLAP Engine Design
• When data warehouse is Web-enabled and level of OLAP operations elevated,
design of OLAP engine determines possibilities for scaling up
• In OLAP product chosen for Web-enabled data warehouse, OLAP engine design ranks
high in criticality
• properly designed engine produces a performance curve that stays linear as number
of concurrent users increases
• Dependence on RDBMS
• OLAP engine relies completely on RDBMS to perform multidimensional
processing, generating complex, multi-pass SQL to access summary data
• Joins, aggregations, and calculations are all done within database, posing
serious problems for Web-enabled systems
• High overhead for creating, inserting, dropping, allocating disk space, checking
permissions, and modifying system tables for each calculation
• Dependence on Engine
• engine has intelligence to determine type of request, able to distribute joins,
aggregations, and calculations between engine component and RDBMS
• We can separate presentation, logic, and data layers both logically and
physically
• So system processing is balanced and network traffic is optimized
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 41
 Building a Data Webhouse

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 42

 Features of Data Webhouse
• fully distributed Web-enabled system
• Consists of many independent nodes
• distribution of tasks and arrangement of components radically different
• Web browser is key to information delivery
• system delivers results of requests for information through remote browsers
• Web supports all data types, including textual, numeric, graphical, photographic,
audio, video, and more
• data Webhouse supports many forms of data
• provides results to information requests within reasonable response times
• User interface design is of paramount importance for ease of use and for
effective publication on Web
• Unlike interfaces in other configurations, Web has a definite method to measure
effectiveness of user interface
• data Webhouse has nicely distributed architecture comprising small-scale data
marts
• Because arrangement of components is based on “bus” architecture of linked
data marts, it is important to have fully conformed dimensions and completely
conformed or standardized facts
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 43
 Web Processing Model

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 44

 Web architecture configuration
• Figure above shows overall arrangement
• architecture is more complex than two-tier or three-tier client/
server architecture
• We need additional tiers to accommodate requirements of Web
computing
• Web server needed between browser clients and database
• firewall to protect corporate applications from outside intrusions
• covers overall architecture

27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 45

 Web processing model

• model for delivering information

• illustrates how HTML pages are translated into SQL queries passed
on to DBMS using CGI scripts
• shows components for information delivery through HTML pages
27-11-2024 CSI ZG515 / SS ZG515/SE ZG515 Data Warehousing 46
• Data warehouse is an information system that stores and organizes
data from multiple sources for business intelligence purposes
• Real-time data warehouse enables storage of real-time data to
analyze it nearly instantaneously upon arrival
• Fusion of real-time activity and data warehousing
27-11-2024 CC ZG515 Data Warehousing 1
 Real-time Data Warehousing
• Processes data in real time, giving quick insights into business operations
• Data arrives in WH warehouse faster and transformed to make querying more
efficient
• Fast processing due to real-time data pipelines

27-11-2024 CC ZG515 Data Warehousing 2

 Real-Time Data Warehousing Benefits
• Timely insights: By processing and analyzing data in near real-
time, organizations can make faster and more informed
decisions based on latest information
• Improved data accuracy: Real-time data integration ensures
that data in warehouse is always up-to-date and reflects most
recent changes in source systems
• Enhanced operational efficiency: Real-time data warehousing
enables businesses to monitor key metrics and performance
indicators in real-time, allowing for proactive identification and
resolution of issues
• Advanced analytics: By combining real-time data with historical
data, organizations can perform advanced analytics, such as
predictive modeling and machine learning, to uncover valuable
insights and drive innovation
27-11-2024 CC ZG515 Data Warehousing 3
 Applications of Real-Time Data Warehouses
• Banking and Finance - fraud detection, real-time risk analysis, and
personalized customer services
• e-Commerce & Retail – real-time inventory management, dynamic
pricing, prediction of customer behavior and targeted marketing
efforts
• Delivery & Logistics operations to support route planning
• Healthcare - real-time patient monitoring, early disease detection,
and predictive analytics for better patient treatment outcomes
• Security Analytics to detect and respond to potential threats in
real time
• Supply Chain Optimization to increase operational efficiency
• Customer Relationship Management (CRM) by providing real-time
insights into customer behavior and interactions to quickly respond
to customer needs, improve customer satisfaction, and increase
loyalty
27-11-2024 CC ZG515 Data Warehousing 4
27-11-2024 CC ZG515 Data Warehousing 5
 Real-time data warehouse architecture

27-11-2024 CC ZG515 Data Warehousing 6

 Layers of Real-time Data Warehouse
Combination of streaming and traditional batch processing mechanisms.
Three main layers:
• Ingestion and Streaming Layer
– Captures real-time data from various sources and ingests into DWH
– handles data ingestion, transformation, and loading, delivers clean and
consistent data to subsequent layers
• Processing Layer
– processes ingested data in real time, performing necessary
computations, validations, and aggregations
– utilizes streaming frameworks or complex event processing engines to
analyze the streaming data and generate actionable insights on the fly
• Analytics and Visualization Layer
– Lets explore and analyze processed data, derive insights from real-time
data and make informed decisions, providing powerful data visualization
tools, reporting capabilities, and ad-hoc query functionalities

27-11-2024 CC ZG515 Data Warehousing 7

 Key processes that happen in an RTDW
Data ingestion
• Ingests real-time data with high throughput
Real-time storage
• Acts as buffer that ensures reliable queuing logic, e.g., record
ordering, scaling resources, delivering messages with minimal
latency
• Enables pre-analytics processing

27-11-2024 CC ZG515 Data Warehousing 8

 Key processes that happen in an RTDW
Real-time processing and analytics
• Most RTDW solutions rely on AI to enhance real-time streaming data
analysis and provide intelligent insights on events as they happen
• software instantly notifies users about events that require manual
settlement and can automatically trigger immediate actions (e.g.,
block a credit card in case of fraud detection or stop the machine
that reported a critical event)
• AI-powered predictive analytics enables accurate forecasting of the
required metrics, while prescriptive analytics offers intelligent
recommendations on the proper actions
Data access and reporting
• An RTDW makes processed data immediately available as short-term
insights and event-based alerts or automated action triggers
• enable comprehensive analytics of accumulated historical data and
ad hoc generation of custom reports
27-11-2024 CC ZG515 Data Warehousing 9
 Real-time Data Integration Techniques
• Ensure seamless and timely extraction, transformation, and loading of data into
real-time data warehouse
• Changed Data Capture (CDC): captures and tracks changes made to source data,
enabling the extraction of only modified data for loading into warehouse
– Oracle Data Integrator (ODI)’s Changed Data Capture (CDC) technology
identifies and captures data as it is being inserted, updated, or deleted from
datastores, and i makes changed data available for integration processes
• Data Replication: This technique involves replicating data from source systems
to the data warehouse in real time, ensuring the availability of the most updated
data for analysis
• Streaming Data Ingestion: This technique streams data directly from various
sources into the warehouse, eliminating the need for batch processing and
enabling real-time analytics
• Event-Driven Architecture: This technique structures the real-time data
warehouse around events, allowing for the immediate processing of incoming
data streams and triggering actions based on event-driven triggers

27-11-2024 CC ZG515 Data Warehousing 10

 Real- Time Data Warehouse Architecture Modules

27-11-2024 CC ZG515 Data Warehousing 11

 Working of Real-Time Data Warehouse Architecture
• Continuous loading as opposed to periodic in traditional approaches
• Three main parts:
▪ Data source hosting data production systems that populate data warehouse
▪ Intermediate Data Processing Area (DPA) where cleaning, extraction and
transformation of data take place
▪ Data warehouse
• Each data source has Source Flow Regulator (SFlowR) module – identifies
relevant changes and propagates them towards DWH
• Data Processing Flow Regulator (DPFlowR) module decides which source is
ready to transmit data
• Warehouse Flow Regulator (WFlowR) orchestrates propagation of data from DPA
to warehouse
– propagation based on current work load from end users posing queries and
requirements for data freshness, ETL throughput and query response time

27-11-2024 CC ZG515 Data Warehousing 12

 Techniques used in Real-time Data Warehousing
• Near Real- time ETL

• Direct Trickle Feed

• Trickle and Flip

• External Real- Time data Cache

• Real-time Partitions

• ETL vs ELT

27-11-2024 CC ZG515 Data Warehousing 13

 Near Real-Time ETL
• Increasing frequency of existing data load
• Change would enable users of DW to have access to more
recent data without having to make major modifications to
loading process or data model

Direct Trickle Feed

• continuously feed data warehouse with new data from the
source system
• can be done by either directly inserting or updating data in
the warehouse fact tables, or by inserting data into separate
fact tables in a real-time partition

27-11-2024 CC ZG515 Data Warehousing 14

 Trickle & Flip
• Instead of loading data in real-time into actual fact tables,
data is continuously fed into staging tables that are exactly in
same format as target tables

• Periodically, staging table is duplicated and copy is swapped

with fact table, bring data warehouse instantly up-to-date

• Thus searchable fact table is updated less frequently to limit

performance loss

27-11-2024 CC ZG515 Data Warehousing 15

 External Real-Time Data Cache (RTDC)
• Store real-time data in an external Real-Time Data Cache (RTDC) outside
of traditional DWH, completely avoiding any potential performance
problems and leaving existing warehouse largely as-is
• RTDC can simply be another dedicated database server (or a separate
instance of a large database system) dedicated to loading, storing, and
processing real-time data
• All real-time data is loaded into cache as it arrives from source system
• Real-time data required to answer any particular query is seamlessly
merged with regular data warehouse on a temporary basis to process
query
• Further, queries that access real-time data will be extremely fast, as they
execute in their own environment separate from existing data warehouse
• By using just-in-time data merging from RTDC into DW queries can access
both real-time and historical data seamlessly
27-11-2024 CC ZG515 Data Warehousing 16
 Real-time Partitions
• To support real-time requirements, data status should be current
– Need to see orders placed by customer even in last hour, Need to track
hourly status of latest order during the day
• But in a relatively large retail environment experiencing 10 million
transactions per day, static fact table would be pretty big
• Design solution -> building a real-time partition as an extension of
conventional, static data warehouse
• Real-time partition has exactly same dimensional structure as static fact
table
• contains only latest or today’s transactions that have occurred since
midnight when you last loaded regular fact tables
– Facts are trickle-fed into RTFTs throughout the day
• Caching entire RTFT in memory
– Create view to combine data from both static & real-time FT, providing a
virtual star schema to simplify queries that demand views of historical
measures that extend to moment
27-11-2024 CC ZG515 Data Warehousing 17
 ETL vs ELT
• Extract, transform, and load (ETL) is a data integration
methodology that extracts raw data from sources, transforms
data on a secondary staging server, and then loads data into
target data warehouse
• In ETL, transformations are processed by the ETL tools (e.g.,
Talend, DataStage, Informatica, SQL Server Integration
Services (SSIS))
• Unlike ETL, Extract, Load, and Transform (ELT) does not
require data transformations to take place before loading
process
• ELT loads raw data directly into target data warehouse and
transforms it by the target data sources at query processing
time

27-11-2024 CC ZG515 Data Warehousing 18

 [Link]

27-11-2024 CC ZG515 Data Warehousing 19

 [Link]

27-11-2024 CC ZG515 Data Warehousing 20

 ETL vs ELT: Comparison

27-11-2024 CC ZG515 Data Warehousing 21

 Future Trends in Real-time Data Warehousing
• Real-time AI and Machine Learning: The integration of AI and
machine learning capabilities into real-time data warehousing will
enable more advanced data analysis, automated decision-making,
and real-time predictive analytics
• Edge Computing: Edge computing, combined with real-time data
warehousing, will enable the processing and analysis of data closer
to its source, reducing latency and enabling faster insights
• Blockchain-Based Data Integration: The use of blockchain
technology for secure and transparent data integration in real-time
data warehousing will enhance data privacy, integrity, and
auditability
• IoT and Realtime Data Fusion: The fusion of data from IoT devices
with other real-time data sources will open up new opportunities
for organizations to harness the power of connected devices and
real-time insights
27-11-2024 CC ZG515 Data Warehousing 22
 Enterprise Data Warehousing

Data warehousing is a powerful solution that helps organizations

store, manage, and analyze data effectively, enabling informed
decision-making
27-11-2024 CC ZG515 Data Warehousing 1
 Empowering Enterprise Data Warehouse
• In today’s data-driven world, managing and analyzing vast amounts of
data is a critical aspect of business success
• Data warehouses are no longer simple decision-support databases fed by
batch extract, transform and load (ETL) processes - have evolved into
dynamic analytical warehouses
• technological innovations are rapidly changing data warehouse
architectures and providing potential for substantial performance
improvements
• several recent developments have made advanced analytics an
increasingly important, and affordable, element of corporate BI initiatives
• drop in storage, computing and memory prices -> emergence of scaled-
down, low-cost database engines, data warehousing platforms and
appliances
• 64-bit memory enables large volumes of data needed for predictive
analytics to reside in main memory instead of on disk
– eliminates time-consuming I/O transfers
27-11-2024 CC ZG515 Data Warehousing 2
 Empowering Enterprise Data Warehouse
• Parallel processing enables multiple analytic processes to run
in tandem
• Virtualization enables companies to allocate computing
resources to analytic and database querying functions on a
prioritized and as-needed basis
• To compete effectively in the age of big data, the enterprise
needs to switch their approach from traditional data
warehousing by offloading appropriate data to a cost effective
big data platform
– and then leveraging the big data platform to enable the
wealth of analytics that can be developed on both
structured and unstructured data

27-11-2024 CC ZG515 Data Warehousing 3

 Empowering Enterprise Data Warehouse
• Emergence of two industry standards for advanced analytics
– MapReduce, a vendor-neutral programmability framework for
complex information types, has gained traction among data
warehouse and advanced analytics software vendors
– Hadoop, defines an open analytic processing pushdown
workflow model and distributed analytic object-file store
• has growing support from database, data warehouse and
cloud computing platform vendors
• MapReduce and Hadoop can work with unstructured as well as
structured data residing in a database
• This will be critical for current generation of analytic applications,
which will be mining complex patterns in diverse and distributed
information generated by Web 2.0 applications, social
networking, clickstream analysis, etc.
27-11-2024 CC ZG515 Data Warehousing 4
Criteria for data warehouse platform selection
Potential requirements include:
• Active loading of data and immediate access to
loaded data
• mixed processing workload against the data
• Cross-functional complexity
• desired level of query concurrency
• organization’s platform scalability needs
• required DBMS functionality

27-11-2024 CC ZG515 Data Warehousing 5

 Criteria for data warehouse platform selection
Architecture for a data warehouse platform should be:
• Scalable - in both performance capacity and incremental data volume growth.
• Powerful - should be designed for complex decision-support activity in a
multiuser, mixed-workload environment
• Manageable - technology you choose should need only minimal support tasks
requiring database administrator (DBA) or systems administrator intervention
• Extensible - flexible database design and system architecture in tune with
evolving business requirements, leveraging existing investments in hardware
and applications
• Available - support mission-critical business applications with minimal
downtime
• Interoperable - allows for integrated access to data on the Web, internal
networks and corporate mainframes
• Affordable - low total cost of ownership (TCO) over a multiyear period
• Flexible - provide optimal performance across the full range of normalized, star
and hybrid data schemas with large numbers of tables
27-11-2024 CC ZG515 Data Warehousing 6
 Recent Trends in Data Warehousing
Why Data Warehousing Is Critical to Company’s Success
• Data warehousing is the secure electronic information storage by a
company or organization
• creates a trove of historical data that can be retrieved, analyzed, and utilized
to create reports designed to provide insight or predictive analysis into an
organization’s performance and operations
• Data warehousing solutions drive business efficiency, build future analysis
and predictions, enhance productivity, and improve business success
• These solutions categorize and convert data into readable dashboards that
anyone in a company can analyze
• Data is reported from one central repository to enable management to
make meaningful business insights and faster and better decisions
• By running reports on historical data, a data warehouse can clarify what
systems and processes are working and what methods need improvement
• Data warehouses also provide the base architecture for artificial intelligence
(AI) and machine learning (ML) solutions
27-11-2024 CC ZG515 Data Warehousing 7
 Recent Trends in Data Warehousing
• Virtual Data Warehousing
• cloud-based data warehouse
• Big data technologies
• Columnar storage
• In-Memory Computing
• In-Database Analytics
• Data Compression
• self-service data warehousing
• Machine Learning and Artificial Intelligence
• Analytics on Demand
• Integration Platform-as-a-Service (IPaaS)
• Data lake and data warehouse convergence
• Data Warehouse as A Service (DWaaS)
• Data Warehouse Appliances
27-11-2024 CC ZG515 Data Warehousing 8
 Virtual Data Warehousing
• A virtual warehouse is a data warehouse that has no physical
data but provides a uniform and consolidated single point of
access to a set of underlying physical data stores
• a set of views over operational databases
• For efficient query processing, only some of the possible
summary views may be materialized
• virtual warehouse is easy to build but requires excess capacity
on operational database servers
• Virtual data warehouses are sets of separate databases that
can be queried simultaneously by means of middleware
• Virtual data warehousing is trending because it’s cost-effective
and can be deployed faster than physical solutions
• By foregoing physical data replication, virtualization can
improve operating speeds and reduce operating costs
27-11-2024 CC ZG515 Data Warehousing 9
 Virtual Data Warehousing
A virtual data warehouse can be built as a set of SQL views either
(a) directly on underlying operational data sources or
(b) as extra layer on top of a set of physical independent data marts

27-11-2024 CC ZG515 Data Warehousing 10

 Virtual Data Warehousing
• A Virtual Data Warehouse should provide a uniform and consistent metadata
model and data manipulation language (e.g., SQL)
• metadata model contains mappings between schemas of underlying data
stores and schema of virtual data warehouse
• Queries are then reformulated and decomposed using these schema
mappings on the fly, whereby the underlying data are fetched and
consolidated on demand
• This provides queries with a real-time perspective on the underlying
evolving data
• The wrappers are dedicated software components that receive queries from
the upper level, execute them on the underlying data store, and convert the
result to a format (e.g., relational tuples) that can be understood by the
query processor
• complexity of the wrapper depends upon data source
• In case of RDBMS, wrapper can make use of a database API such as JDBC
• In case of semi-structured data such as an HTML webpage, wrapper needs to
parse HTML code into a set of tuples
27-11-2024 CC ZG515 Data Warehousing 11
 Columnar storage
• Columnar storage systems allow for faster querying and
improved compression
• important to store data from different sources in data storage
so that it is effective for analytical purposes to query
• Columnar storage can increase disc performance when
retrieving complex analytical queries compared to row-based
storage
• When it comes to advanced analytics, column-based storage is
a preferred choice to store data in a data warehouse, as data
can be more easily compressed, with less disk space, and the
query may take less time to compute

27-11-2024 CC ZG515 Data Warehousing 12

 Columnar Databases
• A columnar database is a DBMS that stores data in columns instead of rows
• purpose is to efficiently write and read data to and from hard disk storage in
order to speed up the time it takes to return a query
• Columnar databases store data that greatly improves disk I/O performance
• can store more data in smaller amount of memory
• because initial data retrieval is done on a column-by-column basis, only columns
that need to be used are retrieved
• makes it possible for a columnar database to scale efficiently and handle large
amounts of data
• Reading and writing data is much more efficient in a columnar database than a
row-oriented one
• Scope for data compression
• Both columnar and row databases can use traditional database query languages
like SQL to load data and perform queries
• particularly helpful for data analytics and data warehousing
• examples for Columnar Database - Monet DB, Apache Cassandra, SAP Hana,
Amazon Redshift
27-11-2024 CC ZG515 Data Warehousing 13
 Columnar Databases - Benefits
• Suited for modern business applications, e.g., data analytics, business intelligence,
data warehousing
• Multipurpose. Columnar databases increasingly used with big data applications
• running OLAP cubes, storing metadata and doing real-time analytics
• Columnar databases efficient for such tasks because they excel at loading new data
quickly
• Compressible data. Data can be highly compressed in a columnar database
• compression permits columnar operations -- like MIN, MAX, SUM, COUNT and AVG
-- to be performed fast
• Self-indexing. Uses less disk space than RDBMS with same data
• Speed and efficiency
• Columnar databases perform analytical queries faster than other database
methodologies
• quick and efficient at performing joins, a way of combining data from two tables in
a relational database
• In the traditional row-order database, a join can be inefficient with slow
performance
• A columnar database can join any number of data sets quickly, and it can aggregate
the results of a query into a single output
27-11-2024 CC ZG515 Data Warehousing 14
 Data warehouse appliances
• emerged as viable short-list solutions for new deployments or
refurbished data warehousing installations
• combination of hardware, software, operating system, DBMS and
storage pre-configured for data management requirements and
uses
• Many utilize commodity components, and some include open
source DBMS software
• open source technology provides a starting point for basic database
functionality, and appliance vendors focus on necessary
functionality enhancements
• Query performance, especially against large volumes of data, is
distinctively impressive thanks to automatic parallelism that many
appliances provide
• Low TCO for a mixed-workload data warehouse environment is also
possible, and consequential, with appliances
27-11-2024 CC ZG515 Data Warehousing 15
 Data Warehouse Appliances - Benefits
• Data Warehouse Appliance is designed specifically to take care of workload of
business intelligence
• built with hardware and software components specifically architected
• integrates hardware, storage, and DBMS into one unified device
• combines best elements of SMP and MPP to enable queries to be processed
in the most optimal manner
• Appliances from most vendors are designed to interface seamlessly with
standardized applications and tools available on business intelligence market
• scalable, because all parts of the appliance, hardware and software, come
from the same vendor, homogeneous and reliable
• For administrator, appliance provides simplicity because of its integrated
nature
• Data warehouse appliances are already supporting data warehouse and
business intelligence deployments at major corporations
• especially true in telecommunications and retail where data volumes are
enormous and queries and analysis more complex and demanding
27-11-2024 CC ZG515 Data Warehousing 16
 Cloud-based Data Warehouse
• Increased adoption of cloud-based data warehouses
• cloud data warehouse is a database provided as a management
solution in the public cloud and is optimized for analytics,
scalability, and simplicity of use
• Companies are increasingly turning to cloud-based data
warehouses, such as Amazon Redshift and Google BigQuery
• Due to scalability, flexibility, and cost-effectiveness
• These platforms allow companies to quickly and easily spin up and
down data warehouse capacity
• Making them ideal for businesses that experience fluctuating data
storage and processing needs
• Due to advantages of cloud data warehouse, many businesses are
implementing cloud data warehouses in their data analytics and
business intelligence initiatives
27-11-2024 CC ZG515 Data Warehousing 17
 Cloud-based Data Warehouse - Advantages
• Scalability and flexibility
• quick adaptability to change data volumes and processing capacity
needs
• Flexible pricing options
• Cloud providers provide flexible pricing options for supplied resources
to meet customers’ technological demands and budgets
• Improved Performance
• Cloud data warehouses often have multiple servers that share workload
• servers can handle massive volumes of data concurrently without
interruption
• cloud-based data warehouse allows all departments in a business to
access relevant data and make evidence-based choices, which can help
boost overall productivity
• Data availability
• cloud data warehouses automatically make consistent backups,
resulting in 99.99 percent data availability and fault tolerance
27-11-2024 CC ZG515 Data Warehousing 18
27-11-2024 CC ZG515 Data Warehousing 19
 Data Warehouse as A Service (DWaaS)
• Outsourcing model where service provider configures and operates hardware and
software resources needed for a data warehouse, while consumer provides data and
pays for managed service
• Using DWaaS will give following businesses improvements:
• Cost: purchase only services and capacity needed
• So you can avoid initial hardware expenses, unused capacity, and maintenance costs
• Performance: Accelerating time required to turn raw data into meaningful insights
directly contributes to business agility and operational process performance
• Workloads should be distributed among locations and clusters to improve processing
performance of complicated queries
• Time-to-value: Your data warehouse can be created in minutes and put into production
as soon as your data is imported
• Compared to purchase and setup time for on-premises infrastructure, DWaaS lets
you recognize value quickly, rather than waiting weeks or months to deploy an on-
premises data warehouse
• Scalability: Cloud services have a limitless capacity, so you don’t have to worry about
running out of storage space
• Increasing no. of computational resources available to handle complicated data
enables speedier decision-making
27-11-2024 CC ZG515 Data Warehousing 20
 Big Data Technologies
• Greater integration with big data technologies
• Data warehouses are being used in conjunction with big data
technologies, such as Hadoop and Spark
• to enable processing of large volumes of structured and
unstructured data
• more and more companies look to leverage power of big data to
gain insights from their data
• Big data integration is coordinated use of people, processes,
suppliers, and technology to collect, reconcile, and improve data
usage from different sources for decision support
• Integrating historical company data with less structured data from
big data sources enables discovery of hidden data patterns and
correlations, as well as generation of valuable insights
• leads to business-improving actions, a significant step toward
predicting and increasing revenue
27-11-2024 CC ZG515 Data Warehousing 21
 Big Data Technologies
NoSQL for Big Data
• Sometimes database isn’t organized and data in various forms like
text, pictures, videos and becomes impossible to build schema
• hence to process an unstructured data or big data led to development
of schema-less alternative that provides greater flexibility than SQL
solution
• more flexible than traditional databases as they are document-
oriented instead of tables (like in SQL)
• Especially when data warehouse is comprised of unstructured and
frequently changing data
• What makes NoSQL well suited for big data, high volume databases,
and high variety online applications -
• NoSQL is not constrained by a fixed schema model and its horizontal
scalability that increases storage capacity and compute capacity for big
data
27-11-2024 CC ZG515 Data Warehousing 22
 Self-Service Data Warehousing
• Self-service data warehousing allows business users to access
and analyze data without the need for IT involvement
• Companies look to empower their business users with ability
to extract insights from their data

27-11-2024 CC ZG515 Data Warehousing 23

 Machine Learning and Artificial Intelligence
• Increased use of machine learning and artificial intelligence
• to automate and optimize various aspects of data warehousing process,
from data ingestion and preparation to modeling and analysis
• AI and ML can help data warehouse development with tasks such as data
integration, data quality, data modeling, data analysis, data visualization, and
data governance
• AI and ML can also enable data warehouse development to generate more
insights, predictions, and recommendations from data, and support more
complex and sophisticated decision support scenarios
• increase in use of these technologies in data warehouse space, as companies
look to gain a competitive edge through more advanced data analytics
• As growth of volume of data being processed is expected to spike,
businesses increasingly tend to offload data operations to faster machine
learning-enabled AI systems
• With trend and pattern analysis capabilities improving by leaps and bounds,
businesses that integrate AI into their data warehousing solutions can reduce
operational costs and perform data operations more efficiently
27-11-2024 CC ZG515 Data Warehousing 24
 In-Memory Computing
• With growth of big data and live analytics, in-memory
processing capabilities are becoming essential for data
warehouse solutions
• to manage large volumes of data with low latency

27-11-2024 CC ZG515 Data Warehousing 25

 In-Database Analytics
• Analysis that takes place within confines of a database or data
warehouse
• In-database analytics are built into storage architecture and
replace use of separate applications after transfers
• Performing analytical processes on the interior
• minimizes data movement
• reduces bandwidth overhead requirements
• eliminates security risks of distributing sensitive data across
multiple sites and devices
• In-database analytics is an emerging practice that experts say
can significantly cut cost and time it takes to do complex and
data-intensive analytic processes

27-11-2024 CC ZG515 Data Warehousing 26

 In-Database Analytics
• approach in which developers embed application logic into data
warehouse and database systems
• In a traditional setup, predictive analytics, data mining and
other compute-intensive analytic functions are part of separate
applications or data marts, each typically with its own system,
set of data, analytic tools and programmers
• In contrast, with in-database analytics, the analytic functions
reside on same centralized enterprise data warehouse (EDW)
• This eliminates I/O-intensive extract, transform and load (ETL)
operations that can consume as much as 75% of cycle time in
predictive analytics applications
• also enables developers to exploit powerful data warehouse
platform technologies, such as parallel processing

27-11-2024 CC ZG515 Data Warehousing 27

 How In-database Analytics works

• By doing operations on database rather than BI tool, we

eliminate resource-intensive pull phase, and we now have
portable code within database that we can transfer to another
tool
• In terms of security, just modeling, query, and results are
communicated between database and BI tool
• Performing data analytics procedures on inside decreases data
movement, bandwidth overheads
27-11-2024 CC ZG515 Data Warehousing 28
 Data Compression
• As companies accumulate more data over time, need
to compress data and save on storage space
• Data compression reduces number of bits necessary
to store data
• Compressing data frees up storage capacity,
accelerates data transfers, and reduces overall
storage costs

27-11-2024 CC ZG515 Data Warehousing 29

 Analytics on Demand
• In a SaaS-heavy work environment, users may be
extracting data from warehouses through dozens of
different applications
• Analytics on demand has thus become a trend in
marketing agencies
• On-demand analytics refers to IT architectures that
allow users to access data in sandboxes—a virtual
machine host—using a wide variety of software
platforms
• helping many companies meet growing need for
better and faster analytics processes

27-11-2024 CC ZG515 Data Warehousing 30

 Integration Platform-as-a-Service (IPaaS)
• used by large companies to combine data and
applications that reside on-premises as well as in public
and private clouds
• enables development and deployment of complicated
integration projects involving two or more connections to
SaaS as a technology connector for common DBs
• Traditional ETL – combines data from many systems into
single database, data store, or data warehouse for
analysis and decision making
• iPaaS systems communicate data through API endpoints
and provide security through API rules such as data and
authorization

27-11-2024 CC ZG515 Data Warehousing 31

 Data lake
• Data lake is a vast pool of raw enterprise data
• Data lake enables organizations to store and consume diverse
enterprise data to make informed business decisions
• fundamental concept of data lake is to establish single source of
truth inside organization for decision making
• Data lakes do not impose any rigid schema on data ingested from
various sources
• enable physical and logical separation of data
• encourage a ‘Schema-On-Read’ policy rather than a ‘Schema-On-
Write’ policy of traditional data warehouses
• Flexibility in ingesting large volumes enterprise data enables
organizations to collect and store data and derive insights later on

27-11-2024 CC ZG515 Data Warehousing 32

 Data lake
Fundamental design principles in a modern data lake:
• Single Source of Truth: A centralized repository for all kinds of
enterprise data in raw form
• Diversity of Data: Design to ingest, store and process structured,
semi-structured and unstructured data
• Schema-On-Read: Design to avoid a strict schema while writing
data to storage, kept in pristine form.
• Apply schema as it is pulled from store for consumption
• Decoupled Compute and Storage: Design to decouple storage
and compute so that both can be scaled independently
• Data Security: Design to ensure security of data gathered at a
single place
• Ensure data security, network security, access control,
governance
27-11-2024 CC ZG515 Data Warehousing 33
 Data lake and data warehouse convergence
• The maxim that data warehouses hold structured data while
data lakes hold unstructured data is quickly breaking down
• Both have expanded their capabilities to support the other
• Data warehouses like Snowflake or Google BigQuery have
made improvements integrating streaming data capabilities
while Databricks has added ACID properties via delta tables
and its new Unity Catalog
• result will be a convergence toward a data lakehouse
• best-of-both-worlds approach that will allow many more
organizations to benefit from business intelligence to be
found within their data footprint

27-11-2024 CC ZG515 Data Warehousing 34

 Data lake and data warehouse convergence

27-11-2024 CC ZG515 Data Warehousing 35

 Lakehouses
• Lakehouses combine best elements of data warehouses and
data lakes, and seamlessly support data consumption for
business intelligence, reporting, data science, data engineering,
machine learning and artificial intelligence
• Lakehouse architecture similar to data warehouse but uses a
low cost cloud storage, open format of a data lake
• Lakehouses also support:
• decoupled storage and compute
• open and standardized storage formats
• support for structured and unstructured data
• Indexing
• support for diverse workloads like data science
• machine learning, SQL and analytics
• makes data consumption more efficient, reliable and effective

27-11-2024 CC ZG515 Data Warehousing 36

 Lakehouses

27-11-2024 CC ZG515 Data Warehousing 37

 What is Big Data?
• Due to advent of new technologies, devices, and communication
means like social networking sites, amount of data produced by
mankind is growing rapidly every year
• term used to describe collection of data that is huge in size and yet
growing exponentially with time
• includes data produced by different devices and applications
• Normally we work on data of size MB(Word Doc, Excel) or maximum
GB(Movies, Codes) but data in Peta bytes i.e. 10^15 byte size is
called Big Data
• 90% of today's data has been generated in past 3 years
• data so large and complex that none of traditional data
management tools are able to store it or process it efficiently
• Big Data generate value from storage and processing of very large
quantities of digital information that cannot be analysed with
traditional computing techniques
• Big data technologies are helping to build and use Data Warehouses
as ‘single version of truth’
27-11-2024 CC ZG515 Data Warehousing 1
 Big Data Tech Evolution
• Common thread in current trends points toward two things:
• volume of data needed for using the Web 2.0 platform is far greater than
anything that has been used in enterprises today
• need for using statistical models and analytics is now much more than
ever before in history of computing
•Above facts demonstrated in success stories by companies like Facebook,
Google, Yahoo, Apple, and other Fortune 500 companies
•Along with data came problem of how to compute all this volume and
variety, and how to handle volume of data
•Google, Facebook, and Yahoo clearly showed the way
•Google created new computing model based on a file system and a
programming language called MapReduce
• that scaled up search engine and could process multiple queries
simultaneously
•This led to development of Hadoop, Apache project under open source,
which has created a slew of companies that do true collaboration-based
software and framework development
27-11-2024 CC ZG515 Data Warehousing 2
 What Comes Under Big Data?
• some data that come under the umbrella of Big Data:
• Black Box Data: It is a component of helicopter, airplanes, and jets, etc. It
captures voices of the flight crew, recordings of microphones and earphones,
and the performance information of the aircraft
• Social Media Data: Statistic shows that 500+terabytes of new data gets
ingested into the databases of social media site Facebook, Google, LinkedIn,
every day. This data is mainly generated in terms of photo and video uploads,
message exchanges, putting comments etc.
• Stock Exchange Data: The New York Stock Exchange generates about one
terabyte of new trade data per day
• E-commerce site: Sites like Amazon, Flipkart, Alibaba generates huge amount
of logs from which users buying trends can be traced
• Weather Station: All weather stations and satellites give very huge data which
are stored and manipulated to forecast weather
• Telecom company: Telecom giants like Airtel, Vodafone study the user trends
and accordingly publish their plans and for this they store the data of its
million of users
• Search Engine Data: Search engines retrieve lots of data from different DBs
27-11-2024 CC ZG515 Data Warehousing 3
 Sources of Big Data

27-11-2024 CC ZG515 Data Warehousing 4

 Integration of Big Data and Data Warehousing
Components of next-generation data warehouse

27-11-2024 CC ZG515 Data Warehousing 5

 Big Data Types
• next-generation data warehouse will have data from across
enterprise that will be integrated and presented to users for
business decision-making and analysis
• data layer in new platform includes following:
• Legacy data
• Transactional (OLTP) data
• Unstructured data
• Video
• Audio
• Images
• Numerical/patterns/graphs
• Social media data

27-11-2024 CC ZG515 Data Warehousing 6

 Big Data Types
[Link] Data
• Any data that can be stored, accessed and processed in fixed
format
• E.g., Data stored in a relational database management
system, e.g., 'Employee' table in a database
[Link]-structured Data
• Semi-structured data can contain both the forms of data
• E.g., data represented in XML file, JSON data or CSV file
[Link] Data
• Any data with unknown form or structure is unstructured
• E.g., heterogeneous data source containing a
combination of simple text files, images, videos etc.
• E.g., Output returned by 'Google Search'

27-11-2024 CC ZG515 Data Warehousing 7

 Big Data Processing
Big Data processing needs to happen in the order shown:

27-11-2024 CC ZG515 Data Warehousing 8

 Big Data Processing Technologies
Technologies mixed and integrated into heterogeneous architecture include:
● RDBMS
● Hadoop
● NoSQL
● MDM solutions
● Metadata solutions
● Semantic technologies
● Rules engines
● Data mining algorithms
● Text mining algorithms
● Data discovery technologies
● Data visualization technologies
● Reporting and analytical technologies
27-11-2024 CC ZG515 Data Warehousing 9
 3Vs of Big Data
Volume
• Organizations and firms gather as well as pull together different data from
different sources
• includes business transactions and data, data from social media,
login data, as well as information from the sensor as well as machine-
to-machine data
• tools like Apache Spark, Hadoop help to handle voluminous Big data
Velocity
• Data is now streaming at an exceptional speed, which has to be dealt with
suitably
• Sensors, smart metering, user data as well as RFID tags are lashing
the need for dealing with an inundation of data in near real-time
Variety
• data from various systems have diverse types and formats
• range from structured to unstructured, numeric data of traditional
databases to non-numeric or text documents, emails, audios and
videos, stock ticker data, login data, Blockchains' encrypted data, or
even financial transactions
27-11-2024 CC ZG515 Data Warehousing 10
 Benefits of Big Data
• Using information in social networks like Facebook,
marketing agencies are learning about response for their
campaigns, promotions, and other advertising media
• Using information in social media like preferences and
product perception of their consumers, product companies
and retail organizations are planning their production
• Using data regarding previous medical history of patients,
hospitals are providing better and quick service
• Risk-management can be done in minutes by calculating
risk portfolios
• Detection of fraud before it affects

27-11-2024 CC ZG515 Data Warehousing 11

 Big Data Challenges
• Rapid Data Growth: growth velocity at such a high rate creates
a problem to look for insights using it
• No foolproof efficient way to filter out relevant data
• Storage: generation of massive amount of data needs space for
storage that poses challenges for organizations to handle it
• Unreliable Data: big data collected and analyzed not totally
accurate
• Redundant data, contradicting data, or incomplete data
• Data Security: Handling massive data can pose cyber threat and
risk
• encrypting such humungous data is also a challenge

27-11-2024 CC ZG515 Data Warehousing 12

 NoSQL
• technology that has evolved into a powerful platform is NoSQL (not only
SQL) movement
• difficulties in scaling up SQL database systems for on-line, web scale
processing led to NoSQL systems
• built on non-relational models not conforming to ACID properties
(Atomicity, Consistency, Isolation, Durability)
• NoSQL systems include key/value, documents, columnar, graphs and
streams
• rigid relational schema was inflexible, particularly for unstructured data
• high availability is paramount
• SQL is not the most suitable programming language for handling big data
• Facebook one of earliest evangelists of NoSQL architecture, as they needed
to solve scalability and usability demands of a huge user population
• popular NoSQL database Cassandra developed and used at Facebook for a
long time
• Now used by many companies along with Hadoop
27-11-2024 CC ZG515 Data Warehousing 13
 MapReduce
Two other contemporaneous developments contributed to
growth and popularization of NoSQL systems:
• MapReduce, Google's divide-and-conquer software framework
for distributed systems
• Facilitate programming of distributed applications using
two functions Map and Reduce

27-11-2024 CC ZG515 Data Warehousing 14

 Hadoop
• Hadoop a distributed file system from Apache
• An open source system with goals of performance,
availability and scalability
• modeled on MapReduce
• made MapReduce style application development popular
• Relieves programmer of managing a parallel application
running in a distributed environment of clusters, with fault
tolerance
• contributed to meteoric rise and adoption of MapReduce
for solving web scale data processing problems
• several NoSQL products incorporate MapReduce model

27-11-2024 CC ZG515 Data Warehousing 15

 Big Data Types
Operational Big Data
• Captured from real-time, interactive operational sources
• NoSQL Big Data systems use new cloud computing architectures
to do massive computations inexpensively and efficiently
• Some NoSQL systems can provide insights into patterns and
trends based on real-time data with minimal coding and no
additional infrastructure
Analytical Big Data
• includes systems like Massively Parallel Processing (MPP)
database systems and MapReduce that provide analytical
capabilities
• MapReduce complements SQL for analyzing data
• system based on MapReduce is scalable from single servers to
thousands of high and low end machines
27-11-2024 CC ZG515 Data Warehousing 16
 Big Data In Business Intelligence

27-11-2024 CC ZG515 Data Warehousing 17

 Types of Tools Used in Big Data
• Where processing is hosted?
Distributed Servers/ Cloud (e.g. Amazon EC2)
• Where data is stored?
Distributed Storage (e.g. Amazon S3)
• What is the programming model?
Distributed Processing (e.g. MapReduce)
• How data is stored & indexed?
High- performance schema-free databases (e.g.
MongoDB)
• What operations are performed on data?
Analytic/ Semantic Processing
27-11-2024 CC ZG515 Data Warehousing 18
 Hadoop
• Hadoop runs applications using the MapReduce algorithm,
where data is processed in parallel
• Hadoop is used to develop applications that could perform
complete statistical analysis on huge amounts of data

27-11-2024 CC ZG515 Data Warehousing 19

 Hadoop - Introduction
• Hadoop framework application Hadoop is an Apache open source
works in an environment that framework written in java that allows
provides distributed storage and distributed processing of large
computation across clusters of datasets across clusters of computers
using simple programming models
computers
• Hadoop is designed to scale up
from single server to thousands
of machines, each offering local
computation and storage
• At its core, Hadoop has two
major layers namely:
(a) Processing/Computation
layer (MapReduce)
(b) Storage layer (Hadoop
Distributed File System)

27-11-2024 CC ZG515 Data Warehousing 20

 Hadoop Distributed File System
• The Hadoop Distributed File System (HDFS) is based on
Google File System (GFS) and provides a distributed file
system that is designed to run on commodity hardware
• highly fault-tolerant and is designed to be deployed on
low-cost hardware
• provides high throughput access to application data and
is suitable for applications having large datasets
• Hadoop framework also includes two modules:
• Hadoop Common: Java libraries and utilities required
by other Hadoop modules
• Hadoop YARN: framework for job scheduling and
cluster resource management

27-11-2024 CC ZG515 Data Warehousing 21

 Hadoop Distributed File System (HDFS)
• Hadoop HDFS has Master/Slave architecture where Master is
NameNode and Slave is DataNode
• single NameNode and all other nodes are DataNodes
• HDFS NameNode:
• NameNode works as a Master in a Hadoop cluster that guides
Datanode (Slaves)
• Namenode is mainly used for storing Metadata
• Meta Data include transaction logs that keep track of user activity in
Hadoop cluster
• Meta Data include file name,file, size, and location (Block number,
Block ids) of Datanode that Namenode stores to find closest
DataNode for faster communication
• Namenode instructs DataNodes regarding operations like delete,
create, Replicate, etc.

27-11-2024 CC ZG515 Data Warehousing 22

 Hadoop Distributed File System
HDFS DataNode
• DataNodes works as a Slave
• DataNodes mainly used to store data in Hadoop
cluster
• number of DataNodes - 1 to 500 or more
• more number of DataNodes -> Hadoop cluster can
store more data
• So DataNode should have high storing capacity to
store large number of file blocks

27-11-2024 CC ZG515 Data Warehousing 23

 Hadoop High Level Architecture

27-11-2024 CC ZG515 Data Warehousing 24

 How Hadoop Works
• Expensive to build bigger servers with heavy
configurations that handle large scale processing
• Alternative –tie together many commodity computers
with single-CPU, as a single functional distributed
system and practically, the clustered machines can
read the dataset in parallel and provide much higher
throughput
• Hadoop runs code across a cluster of computers

27-11-2024 CC ZG515 Data Warehousing 25

 How Hadoop Works
• Process includes following core tasks:
• Data is initially divided into directories and files
• Files are divided into uniform sized blocks of 128M and 64M
(preferably 128M)
• files are then distributed across various cluster nodes for
further processing
• HDFS, being on top of the local file system, supervises the
processing
• Blocks are replicated for handling hardware failure
• Checking that the code was executed successfully
• Performing sort operation that takes place between map and
reduce stages
• Sending sorted data to a certain node
• Writing debugging logs for each job
27-11-2024 CC ZG515 Data Warehousing 26
 HIVE
• A data warehouse infrastructure built on top of
Hadoop
• Enables easy data summarization, adhoc querying and
analysis of large datasets data stored in Hadoop files
• Provides a mechanism to put structure on this data
• Hive QL - A simple query language (based on SQL)
which enables users familiar with SQL to query data
• HIVE QL allows plug-in custom mappers and reducers
to do advanced analysis

27-11-2024 CC ZG515 Data Warehousing 27

 MapReduce
▪ MapReduce is a parallel programming model for writing
distributed applications devised at Google for efficient
processing of large amounts of data (multi-terabyte data-sets),
on large clusters (thousands of nodes) of commodity hardware
in a reliable, fault-tolerant manner
▪ MapReduce program runs on Hadoop, an Apache open-source
framework
▪ provides high level of abstraction
▪ hides parallel/distributed computing concepts from users/
programmers
▪ users/programmers can leverage cluster computing for data-
intensive problems
▪ Cluster, Grid, & MapReduce are platforms for general purpose
computing

27-11-2024 CC ZG515 Data Warehousing 28

 MapReduce - Map and Reduce Function
MapReduce works by breaking processing into 2 phases:
• Map (inherently parallel)
• Reduce (inherently sequential)

Map Function
• Applies to a list
• map(function, list) calls function(item) for each of the list’s
items and returns a list of the return values
• E.g., to compute cubes:
>>> def cube(x): return x*x*x
...
>>> map(cube, range(1, 11))
[1, 8, 27, 64, 125, 216, 343, 512, 729, 1000, 1331]

27-11-2024 CC ZG515 Data Warehousing 29

 MapReduce - Map and Reduce Function
Reduce Function
• Applies to a list
• reduce(function, list) returns a single value
constructed by calling the binary function on the first
two items of the list, then on the result and the next
item, and so on…
• E.g., to compute sum of numbers 1 thru 11:
>>> def add(x,y): return x+y
...
>>> reduce(add, range(1, 11))
66

27-11-2024 CC ZG515 Data Warehousing 30

 Integration-driven approach to create next-
generation data warehouse
• To create next-generation data warehouse we combine Big Data processing platform
created in Hadoop or NoSQL and existing RDBMS-based data warehouse infrastructure
by deploying connector between two systems
• This connecter will be a bridge to exchange data between the two platforms
• most RDBMS, BI, analytics, and NoSQL vendors have Hadoop and NoSQL connectors

27-11-2024 CC ZG515 Data Warehousing 31

 Data virtualization to create next-generation data
warehouse platform
• Data virtualization technology can be used to create the next-
generation data warehouse platform
• Data virtualization–based Big Data integration
• biggest benefit of this deployment is reuse of existing
infrastructure for structured portion of data warehouse
• This approach also provides an opportunity to distribute
workload effectively across the platforms -> best optimization
to be executed in the architectures
• Data Virtualization coupled with strong semantic architecture
can create a scalable solution

27-11-2024 CC ZG515 Data Warehousing 32

Data virtualization–based Big Data integration

27-11-2024 CC ZG515 Data Warehousing 33

 Semantic framework
• When data from multiple sources and systems is integrated together, there are
multiple layers of hierarchies, data granularity at different levels, and data
quality issues especially with unstructured data, including text, image, video,
and audio data
• Processing data and presenting data for visualization at both ends requires a
more robust architecture, which is the semantic framework
• The framework consists of multiple layers of processing and data integration
techniques that will be deployed as a part of the next-generation data
warehouse
Lexical processing
• This layer can be applied to both input data processing of Big Data and the
processing of data exploration queries from the visualization layer. Lexical
processing includes processing tokens and streams of text
Clustering
• In this process all the data from lexical processing will be clustered to create a
logical grouping of data processed in the Big Data layers and from any data
exploration queries
27-11-2024 CC ZG515 Data Warehousing 34
 Semantic framework
Semantic knowledge processing
• This process consists of integrating the data outputs from lexical
and clustering layers to provide a powerful architecture for data
visualization
Information extraction
• In this process visualization tools extract data from prior layers of
processing and load it for visual exploration and analytics
• data at this stage can be loaded into technology like in-memory
for further analysis
Visualization
• In this process data can be visualized using new technologies like
Tableau and Spotfire, or with R, SAS, or traditional technologies
like Microstrategy, Business Objects, or Cognos
• These tools can directly leverage semantic architecture from their
integration layers and create a scalable interface
27-11-2024 CC ZG515 Data Warehousing 35
 Semantic framework

27-11-2024 CC ZG515 Data Warehousing 36

 Big Data appliances
• configured to handle the rigors of workloads and complexities of Big Data and RDBMS
architecture
• conceptual architecture of the Big Data appliance, which includes a layer of Hadoop and
a layer of RDBMS

27-11-2024 CC ZG515 Data Warehousing 37

 Big Data appliances
• Hadoop and/or NoSQL technologies will be used to acquire, preprocess,
and store Big Data
• RDBMS layers will be used to process the output from the Hadoop and
NoSQL layers
• In-database MapReduce, R, and RDBMS specific translators and
connectors used in integrated architecture for managing data movement
and transformation within appliance
• Big Data appliance addresses key issues when dealing with extremely
large data processing in areas including:
• data loading
• Availability
• data volume
• storage performance
• Scalability
• diverse and changing query demands against the data
• operational costs of the next-generation data warehouse platform
27-11-2024 CC ZG515 Data Warehousing 38
 What Is a Large Data Warehouse?
• A data warehouse centralizes and consolidates large
amounts of data from multiple sources
• Its analytical capabilities allow organizations to derive
valuable business insights from their data to improve
decision-making
• This large amount of data can be structured, semi-
structured, or unstructured which cannot be
processed by traditional data processing software and
databases
• Various operations like analysis, manipulation,
changes, etc. are performed on data and then it is
used by companies for intelligent decision making
 World’s Largest Data Warehouse
World’s largest Data Warehouse record- 12.1 PB(petabytes)
from SAP:
• SAP along with technology partners, from BMMsoft, HP,
Intel, NetApp and RedHat has created new world record
for world’s largest data warehouse @ SAP/Intel data
center in Santa Clara, California, USA
• SAP HANA platform and SAP IQ software
• contains 12.1 PB( 12,100 terabytes) of raw data, over 221
trillion transactional records, more than 100 billion
unstructured documents, including emails, SMS, and
images
• contains data from 30 billion sources, including users,
smart sensors, and mobile devices
 What is SAP HANA?
• SAP HANA (High-performance ANalytic Application)
• in-memory, column-oriented, relational database
management system developed by SAP
• functions as part of a large Data Warehouse
• performs advanced analytics (predictive analytics,
spatial data processing, text analytics, text search,
streaming analytics, graph data processing)
• includes extract, transform, load (ETL) capabilities as
well as an application server
 Features of SAP HANA
Architecture Overview

Key distinctions between HANA and previous generation SAP systems

• column-oriented, in-memory database
• combines OLAP and OLTP operations into a single system
• - "online transaction and analytical processing" (OLTAP) system (also
called hybrid transactional/analytical processing (HTAP))
• Storing data in main memory rather than on disk provides faster data
access and, by extension, faster querying and processing
• While storing data in-memory confers performance advantages, it is a
more costly form of data storage
• Observing data access patterns, up to 85% of data in an enterprise
system may be infrequently accessed
• therefore it can be cost-effective to store frequently accessed, or
"hot", data in-memory while the less frequently accessed "warm" data
is stored on disk
• - approach SAP calls ‘Dynamic tiering’
 Key Terminology
BICS: Business Intelligence Consumer Services (BICS) is SAP’s
proprietary interface for queries
Business Objects: One can visualize SAP BusinessObjects as “black
boxes” that encapsulate data and business processes, thus hiding
details of structure and implementation of underlying data
Business Warehouse(BW): SAP BW provides standard application
data for program usage over various systems
In Memory Computing Engine(IMCE): Heart of Hana solution is In-
memory Computing Engine(IMCE) allowing to create and perform
accelerated calculations on data
Multi-Dimensional Expressions(MDX): MDX is a language
developed by Microsoft for queries using multidimensional data
Data Striping: Technique of segmenting logically sequential data,
such as a file, in a way that accesses of sequential segments are
made to different physical storage devices
• Striping is useful when a processing device requests access to
data more quickly than a storage device can provide access
HANA Architecture
HANA Architecture
HANA Architecture
 HANA Architecture
Indexer components
• The index server performs session management, authorization,
transaction management and command processing
• database has both a row store and a columnar store
• Users can create tables using either store, but the columnar store
has more capabilities and is most frequently used
• The index server also manages persistence between cached
memory images of database objects, log files and permanent
storage files
• XS engine allows web applications to be built
• SAP HANA Information Modeling (also known as SAP HANA Data
Modeling) is a part of HANA application development
• Modeling is the methodology to expose operational data to the end
user
• Reusable virtual objects (named calculation views) are used in the
modelling process
 HANA Architecture
MVCC
• SAP HANA manages concurrency through the use of
multiversion concurrency control (MVCC)
• gives every transaction a snapshot of database at a point in time
• When an MVCC database needs to update an item of data, it will
not overwrite old data with new data
• but will instead mark old data as obsolete and add newer
version

Big data
• In a scale-out environment, HANA can keep volumes of up to a
petabyte of data in memory while returning query results in
under a second
• However, RAM is still much more expensive than disk space, so
the scale-out approach is feasible for only certain time-critical
use cases
HANA Architecture
The ‘Heart’ of SAP HANA
 In-Memory Computing Engine
Column Storage Multiple Engines
Compression

Partitioning

Data Striping

Parallel Processing

Calculation

SQL

OLAP / MDX
No Pre-Aggregation => Ad-hoc Queries
 SAP HANA: In-Memory Database
Delivering across 5 dimensions of decision processing
 SAP HANA: In-Memory Database
In-memory computing
 SAP HANA In-memory data platform
• SAP HANA is an implementation of in-memory database
technology, consisting of a database, a server for storage,
client software and extract/ transform/ load middleware
• allows processing of massive quantities of real-time data
in main memory of server
• data access is more than a hundred thousand times faster
than access from a hard disk, and a thousand times than
access from a flash technology storage
• SAP HANA integrates a number of SAP components including
the SAP HANA database, SAP LT (Landscape Transformation)
Replication Server, SAP HANA Direct Extractor Connection
(DXC) and Sybase Replication technology
• This preconfigured Appliance consists of in-memory software
bundled with hardware delivered from hardware partners
such as HP, IBM, CISCO, Fujitsu etc.
SAP HANA Column Storage Vs. Row-Based Storage
SAP HANA Column Storage Vs. Row-Based Storage
• Strong data in columns is not new technology, but not
fully leveraged yet
• Columnar storage is read-optimized, i.e., read
operations very fast
• Not write-optimized – new insert might move a lot of
data to create place for new data
• HANA handles this well with delta merge
• Columnar storage performs very well while reading
and write operations are handled by In-Memory
Computing Engine (IMCE) in some other ways
 Column-oriented systems
• Column-oriented systems store all data for a single column in
same location
• - rather than storing all data for a single row in same location
(row-oriented systems)
• -> performance improvements for OLAP queries on large
datasets and allows greater vertical compression of similar types
of data in a single column
• If read times for column-stored data is fast enough, consolidated
views of data can be performed on the fly
• - removing need for maintaining aggregate views and
associated data redundancy
• Although row-oriented systems have traditionally been favored
for OLTP, in-memory storage opens techniques to develop hybrid
systems suitable for both OLAP and OLTP capabilities
• - removing need to maintain separate systems for OLTP and
OLAP operations
 Column Storage Opportunities
Compressions: As the data written next
to each other is of some type, there is no Column Storage

Compression

Partitioning

Data Striping

Parallel Processing
need to write the same values again and
again
Partitioning: HANA supports two types of
partitioning
A single column can be partitioned to
many HANA servers, and different
columns of a table can be partitioned in
different HANA servers
Columnar storage easily enables this partitioning
Data Striping: When querying a table, there are often times where a lot of
columns are not used
Parallel Processing: It is always performance-critical to make full use of
resources available
With current boost in number of CPUs, the more work they can do in
parallel, the better the performance
 Multiple Engines
• HANA has multiple engines inside its
computing engine for better Column Storage
performance

Compression

Partitioning

Data Striping

Parallel Processing
• HANA supports both SQL & OLAP
reporting tools
• separate engines to perform
operations
• separate calculation engine to do
calculations
• planning engine used for functional reporting
• controller which breaks incoming requests into multiple pieces
and sends sub queries to these engines
• separate row end column engines to process operations between
tables stored in rows and tables stored in column format
 What is Ad Hoc Analysis?
• In traditional data warehouses, such as SAP BW, a lot
of pre-aggregation is done for quick results
• The IT administrator decides which information
might be needed for analysis and prepares the result
for end user
• Results in fast performance but end user has no
flexibility
• With SAP HANA and its speedy engine, no pre-
aggregation is required
• user can perform any kind of operation in reports
and does not have to wait hours to get the data
ready for analysis in real time
 Analytics
• SAP HANA includes a number of analytic engines for
various kinds of data processing
• Business Function Library includes a number of
algorithms made available to address common
business data processing algorithms such as asset
depreciation, rolling forecast and moving average
• Predictive Analytics Library includes native algorithms
for calculating common statistical measures in areas
such as clustering, classification and time series
analysis
• HANA incorporates open source statistical
programming language R as a supported language
within stored procedures
 Analytics
• column-store database offers graph database capabilities
• graph engine processes Cypher Query Language and also has a
visual graph manipulation via a tool called Graph Viewer
• Graph data structures are stored directly in relational tables in
HANA's column store
• Pre-built algorithms in Graph engine include pattern matching,
neighborhood search, single shortest path, and strongly
connected components
• Typical usage situations for Graph Engine include supply chain
traceability, fraud detection, and logistics and route planning
• HANA also includes a spatial database engine which implements
spatial data types and SQL extensions for CRUD operations on
spatial data
• HANA is certified by Open Geospatial Consortium and integrates
with ESRI's ArcGIS geographic information system
 Analytics
• In addition to numerical and statistical algorithms, HANA can perform
text analytics and enterprise text search
• HANA's search capability is based on ‘fuzzy’ fault-tolerant search,
much like modern web-based search engines
• Results include a statistical measure for how relevant search results
are, and search criteria can include a threshold of accuracy for results
• Analyses available include identifying entities such as people, dates,
places, organizations, requests, problems, and more
• Such entity extraction can be catered to specific use cases such as
• Voice of the Customer (customer's preferences and expectations)
• Enterprise (i.e. mergers and acquisitions, products, organizations)
• Public Sector (public persons, events, organizations)
• Custom extraction and dictionaries can also be implemented
 Application Development
• Besides the database and data analytics capabilities, SAP HANA is a web-
based application server
• hosts user applications tightly integrated with database and analytics
engines of HANA
• ‘XS Advanced Engine’ (XSA) natively works with [Link] and JavaEE
languages and runtimes
• XSA is based on Cloud Foundry architecture and thus supports notion of
‘Bring Your Own Language’
• allowing developers to develop and deploy applications written in
languages and in runtimes other than those XSA implements natively
• deploying applications as microservices
• XSA also allows server-side JavaScript (XSJS)
• Supporting application server is a suite of application lifecycle
management tools
• That allows development deployment and monitoring of user-facing
applications
 SAP HANA - Key Takeaways
• Empowers Your Organization
• Reduced reliance on IT resources
• Real-time visibility to complete data for transaction and
analytics processing
• Enable a 360 degree view of your business
• Real- Time Analytics for Operational Data
• Go from “ what happened yesterday” to real time
• Close to zero latency
• Ability to leverage and analyze large volumes of data
• Low Total Cost of Ownership (TCO)
• Non- disruptive to existing Enterprise Data Warehousing
(EDW) Strategy
• Low TCO by leveraging the latest technology and delivery are
pre- configured appliance
 Cloud-based Data Warehouses

27-11-2024 CC ZG515 Data Warehousing 28

 Oracle Data Warehousing
Autonomous Data Warehouse

• Oracle Autonomous Data Warehouse is optimized for

analytic workloads, including data marts, data warehouses,
data lakes, and data lakehouses
• data scientists, business analysts, and non-experts can
rapidly, easily, and cost-effectively discover business insights
using data of any size and type

27-11-2024 CC ZG515 Data Warehousing 29

27-11-2024 CC ZG515 Data Warehousing 30
 Oracle Data Warehousing
Boosted efficiency with data warehouse automation:

• Oracle Autonomous Data Warehouse automates manual

data warehouse tasks enabling IT teams to focus on
improving their business instead of managing databases
• Autonomous management enables IT to run a high-
performance, highly available, and secure enterprise data
warehouse
• while eliminating administrative complexity and reducing
costs
• Existing Oracle customers maintain same data models, tools,
and data engineering processes, making it simple to
modernize data warehouses

27-11-2024 CC ZG515 Data Warehousing 31

 Oracle Data Integrator
• Oracle’s Data Integration solutions provide continuous access to
timely, trusted, and heterogeneous data across the enterprise to
support both analytical and operational data integration on-
premises and in the cloud
• data integration platform focused on fast bulk data movement and
handling complex data transformations
• provides high-performance data movement and transformation
among enterprise platforms with its open and integrated E-LT
architecture and extended support for Big Data
• ODI is critical to leveraging data integration initiatives on-premise or
in the cloud, such as Big Data management, Service Oriented
Architecture and Business Intelligence
• easy-to-use user interface combined with a rich extensibility
framework
• helps improve productivity, reduce development costs and lower
total cost of ownership for data-centric architectures
27-11-2024 CC ZG515 Data Warehousing 32
 Oracle Data Integrator
with Autonomous Data Warehouse Cloud

27-11-2024 CC ZG515 Data Warehousing 33

 Oracle Data Integrator
• Can load data directly into Oracle Autonomous Data
Warehouse Cloud (ADW) and Oracle Autonomous
Transaction Processing (ATP)
• - Using native integration between Oracle Autonomous
Data Warehouse and Oracle Object Storage to enable
extremely fast data transfer into ADWC or ATP

27-11-2024 CC ZG515 Data Warehousing 34

 Enhanced Big Data Support
Introduction of Spark and Pig
• Oracle Data Integrator introduces execution of mappings using Spark or
Pig
• ODI allows defining of mappings through a logical design which is
independent of implementation language
• Users can select for Hadoop-based transformations between Hive,
Spark, and Pig as the generated transformation code, allowing users to
pick the best implementation based on the environment and use case
Spark
• Oracle Data Integrator mappings can generate PySpark, which exposes
the Spark programming model in the Python language
• Apache Spark is a transformation engine for large-scale data processing
• It provides fast in-memory processing of large data sets
• Custom PySpark code can be added through user- defined function or
table function component
27-11-2024 CC ZG515 Data Warehousing 35
 Big Data Support
Spark Streaming Support
• Oracle Data Integrator (ODI) now supports Spark Streaming to
fully enable the creation of Big Data streaming jobs easily
without requiring end users to write a single line of code
• In addition to Spark Streaming ODI already supports Hive, Pig and
batch Spark when it comes to data processing
• Through its unique decoupling of the Logical and Physical design
of Mappings Oracle Data Integrator gives developers the
flexibility to design Mappings with a generic business logic and
then generate code for several data processing technologies
(Hive, Spark, Spark Streaming etc.)

27-11-2024 CC ZG515 Data Warehousing 36

 Big Data Support
Support for Apache Kafka and Apache Cassandra
• Apache Kafka and Cassandra are certified with latest
version of Oracle Data Integrator as both sources and
targets

Hadoop Complex Types and Storage Format

• This release further extends market leading Hadoop
support in ODI with ability to natively access data
stored in various formats such as Avro, Parquet or JSON
• new features added to leverage complex types or
nested types in Mappings such as Array, Struct or Map

27-11-2024 CC ZG515 Data Warehousing 37

 Big Data Support
Pig
• Apache Pig is a platform for analyzing large data sets in Hadoop
and uses high-level language Pig Latin for expressing data analysis
programs
• Oracle Data Integrator mappings can leverage Pig Latin as a
transformation language and execution engine
• Any Pig transformation can be executed either in local or map-
reduce mode
• Custom Pig code can be added through user-defined function or
the table function component

27-11-2024 CC ZG515 Data Warehousing 38

 Big Data Support

27-11-2024 CC ZG515 Data Warehousing 39

 Cloud
RESTful Service Support
• Oracle Data Integrator can now invoke RESTful Service
• A RESTful Service connectivity, resource URI, methods and
parameters can be configured in Topology configurations like any
other data source connectivity
• number of parameters supported providing maximum flexibility to
support widespread RESTful services
• Data chunking and pagination are also supported for uploading or
downloading larger payloads
Business Intelligence Cloud Service
• now supported out of the box in Oracle Data Integrator
• We can define Business Intelligence Cloud Service connectivity in
Topology, reverse engineer metadata and load data into it just like
any other target data server
27-11-2024 CC ZG515 Data Warehousing 40
 Cloud
Support for Cubes and Dimensions
• Core ETL – ELT enhancements made
• ODI now provides support for two types of dimensional
objects: Cubes and Dimensions
• Users can create and use Cubes and Dimensions objects
directly in Mappings to improve developer productivity
with out of the box patterns that automate the loading
of dimensional objects
• allows for improved Type 2 Slowly Changing Dimensions
and brand new Type 3 Slowly Changing Dimensions
support with ODI

27-11-2024 CC ZG515 Data Warehousing 41

 Cloud
Easier, faster management
• Oracle Multitenant architecture for Data Warehouse
Management
• easier consolidation of data marts and data warehouses
by offering complete isolation, agility and economies of
scale
• Oracle Multitenant offers additional benefits by
providing fast and efficient management framework for
delivering sandboxes and data discovery platforms
within overall Oracle Big Data Management System

27-11-2024 CC ZG515 Data Warehousing 42

 Analytics
Smarter, faster analytic queries
• Database In-Memory delivers speed-of-thought processing
for sophisticated analytic queries
• Database In-Memory implements leading-edge columnar
data processing to accelerate your data warehouse
analytics by orders of magnitude
• Answers that used to take minutes to obtain are now
available instantly
• Included in Oracle Database is a compelling array of
analytical features and functions that are accessible
through SQL
• new fast and efficient way to organize data using a
dimensional model
27-11-2024 CC ZG515 Data Warehousing 43
 Oracle Analytic SQL
Overview of SQL for Analysis, Reporting and Modelling
• In-database analytical functions and features that are embedded inside
the Oracle Database can be used to answer a wide variety of business
problems
• Developers and business users can access a wide range of analytic
features and combine their results with other SQL queries and analytical
pipelines to gain deeper insights
Standards Based SQL
• ANSI SQL (since 1986) standardization two major benefits:
• provides a high degree of application portability across different
database systems without major code changes
• In data warehousing, BI tools can support multiple types of SQL
databases directly
• SQL standard has ensured continuity in application development
• A SQL statement written thirty years ago continues to run today, without
any modification to the SQL code
27-11-2024 CC ZG515 Data Warehousing 44
 Oracle Analytic SQL
• Oracle has a long history of embedding sophisticated
SQL-based analytics within Oracle Database
• Oracle 10g (2003) introduced the SQL Model clause,
which provides a spreadsheet–like what–if modeling
framework aimed at business users
• 12c introduced SQL pattern matching along with the
HyperLogLog based approximate count distinct function
• Database 18c further provides the ability to write self–
describing, reusable, fully dynamic table functions along
with extensions to approximate query processing

27-11-2024 CC ZG515 Data Warehousing 45

 Oracle Analytic SQL
Key Benefits of SQL for Analysis, Reporting and Modelling
Key benefits provided by Oracle's in-database analytical functions and
features are:
Enhanced Developer Productivity
• perform complex analyses with much clearer and more concise SQL code
• Complex tasks can now be expressed using single SQL statement which is
quicker to formulate and maintain, resulting in greater productivity.
Improved Query Speed
• processing optimizations supported by in-database analytics enable
significantly better query performance
• Actions which before required self-joins or complex procedural processing
may now be performed in native SQL
Improved Manageability
• ability to access a consolidated view of all data types and sources is
simplified when applications share a common relational environment rather
than a mix of calculation engines with incompatible data structures
27-11-2024 CC ZG515 Data Warehousing 46
 Oracle Analytic SQL
Minimized Learning Effort
• SQL analytic functions minimize the need to learn new
keywords because the syntax leverages existing well-
understood keywords
Industry standards based syntax
• Oracle's features conform to ANSI SQL standard and
are supported by a large number of independent
software vendors

27-11-2024 CC ZG515 Data Warehousing 47

 Oracle Analytic Views
Overview of Analytic Views
• Analytic views organize data using a dimensional
model
• They allow us to easily add aggregations and
calculations to data sets and to present data in views
that can be queried with relatively simple SQL
• Like standard relational views, analytic views are
metadata objects (that is, they do not store data)
which can be queried using SQL
• They access data from other database objects such as
tables, views, and external tables and can join
multiple tables into a single view

27-11-2024 CC ZG515 Data Warehousing 48

 Oracle Analytic Views
Analytic views also:
• Organize data using a rich business model that has
dimensional and hierarchical concepts
• Include system-generated columns with hierarchical
data
• Automatically aggregate data
• Include embedded measure calculations that are
easily defined using syntax based on the business
model
• Include presentation metadata
• Hierarchically aware calculation expressions

27-11-2024 CC ZG515 Data Warehousing 49

 Oracle Analytic Views – Benefits
Simplified and faster application development
• much easier to define calculations within analytic views
than it is to write or generate complex SELECT
statements
Calculation rules are stored once
• rules are stored in database which provides end-users
with greater freedom of choice in their use of reporting
tools
Calculation consistency
• because calculation rules are defined once and stored
inside the database, they can be re-used by any number
of applications
27-11-2024 CC ZG515 Data Warehousing 50
 Oracle Partitioning
• Oracle Partitioning enhances the manageability,
performance, and availability of large databases
• Partitioning is powerful functionality that allows
tables, indexes, and index-organized tables to be
subdivided into smaller pieces
• enabling these database objects to be managed and
accessed at a finer level of granularity
• Oracle provides a comprehensive range of partitioning
schemes to address every business requirement
• Moreover, since it is entirely transparent in SQL
statements, partitioning can be used with any
application, from packaged OLTP applications to data
warehouses
27-11-2024 CC ZG515 Data Warehousing 51
 Oracle Partitioning
Key Benefits of Partitioning:

• Increases performance
• by only working on the data that is relevant
• Improves availability
• through individual partition manageability
• Decreases costs
• by storing data in the most appropriate manner
• Easy as to implement
• requires no changes to applications and queries

27-11-2024 CC ZG515 Data Warehousing 52

 Oracle Partitioning Methods
Oracle supports a wide array of partitioning methods:
• Range Partitioning - data is distributed based on a range of values
• List Partitioning - data distribution is defined by a discrete list of
values
• One or multiple columns can be used as partition key
• Auto-List Partitioning - extends the capabilities of the list method by
automatically defining new partitions for any new partition key values
• Hash Partitioning - an internal hash algorithm is applied to the
partitioning key to determine the partition
• Composite Partitioning - combinations of two data distribution
methods are used
• First, the table is partitioned by data distribution method one
• then each partition is further subdivided into subpartitions using
the second data distribution method
27-11-2024 CC ZG515 Data Warehousing 53
 Oracle Partitioning Methods
• Multi-Column Range Partitioning - an option for when the
partitioning key is composed of several columns and subsequent
columns define a higher level of granularity than the preceding ones
• Interval Partitioning - extends the capabilities of the range method
by automatically defining equi-partitioned ranges for any future
partitions using an interval definition as part of the table metadata
• Reference Partitioning Partitions - a table by leveraging an existing
parent-child relationship
• primary key relationship is used to inherit partitioning strategy of
parent table to its child table
• Virtual Column Based Partitioning - allows partitioning key to be an
expression, using one or more existing columns of a table, and storing
expression as metadata only
• Interval Reference Partitioning - extension to reference partitioning
that allows use of interval partitioned tables as parent tables for
reference partitioning
27-11-2024 CC ZG515 Data Warehousing 54
 Oracle Partitioning Methods
Partitioned tables can be created as one of several
types:

• internal heap
• index organized tables
• external tables
• hybrid partitioned tables

• availability of individual partitioning methods varies

with type of table

27-11-2024 CC ZG515 Data Warehousing 55

 Amazon Redshift
• Data warehouse product which forms part of the
larger cloud-computing platform Amazon Web Services
• Built on top of technology from the massive parallel
processing (MPP) data warehouse company ParAccel (later
acquired by Actian)
• to handle large scale data sets and database migrations
• Redshift differs from Amazon's other hosted database
offering, Amazon RDS, in its ability to handle analytic
workloads on big data sets stored by a column-oriented
DBMS principle
• Redshift allows up to 16 petabytes of data on a
cluster compared to Amazon RDS Aurora's maximum size of
128 terabytes
• Amazon Redshift has largest number of Cloud data
warehouse deployments (>6,500)

27-11-2024 CC ZG515 Data Warehousing 1

 Amazon Redshift
• Amazon Redshift is a fully managed, petabyte-scale data warehouse service
in the cloud
• Amazon Redshift Serverless lets you access and analyse data without all of
the configurations of a provisioned data warehouse
• Resources are automatically provisioned and data warehouse capacity is
intelligently scaled to deliver fast performance for even the most demanding
and unpredictable workloads
• You can load data and start querying right away in the Amazon Redshift query
editor v2 or in your favourite business intelligence (BI) tool
• Regardless of the size of the dataset, Amazon Redshift offers fast query
performance using the same SQL-based tools and business intelligence
applications that you use today
• resources can be managed manually by creating provisioned clusters for data
querying needs
• As an application developer, you can use Amazon Redshift API or AWS
Software Development Kit (SDK) libraries to manage clusters
programmatically
27-11-2024 CC ZG515 Data Warehousing 2
 Amazon Redshift
• Amazon Redshift uses SQL to analyze structured and
semi-structured data across data warehouses,
operational databases, and data lakes
• using AWS-designed hardware and machine learning
to deliver the best price performance at any scale

27-11-2024 CC ZG515 Data Warehousing 3

 Amazon Redshift

27-11-2024 CC ZG515 Data Warehousing 4

 Amazon Redshift

27-11-2024 CC ZG515 Data Warehousing 5

 Amazon Redshift

27-11-2024 CC ZG515 Data Warehousing 6

 Amazon Redshift
• Redshift uses parallel-processing and compression to decrease
command execution time
• allows Redshift to perform operations on billions of rows at
once
• also makes Redshift useful for storing and analyzing large
quantities of data from logs or live feeds through a source such
as Amazon Kinesis Data Firehose
• Amazon has listed a number of business intelligence
software proprietors as partners and tested tools in their "APN
Partner" program
• including IBM Cognos, Infor, MicroStrategy, SiSense, Tableau,
Yellowfin
• Partner companies providing data integration tools
include Informatica and SnapLogic
• System integration and consulting partners
include Accenture, Deloitte, Capgemini and DXC Technology
27-11-2024 CC ZG515 Data Warehousing 7
 Google BigQuery
Overview of BigQuery architecture

• BigQuery is serverless, highly scalable, and cost

effective cloud data warehouse
• Its serverless architecture allows it to operate at scale
and speed to provide incredibly fast SQL analytics over
large datasets
• BigQuery is part of Google Cloud’s comprehensive data
analytics platform that covers the entire analytics
value chain including ingesting, processing, and storing
data, followed by advanced analytics and collaboration

27-11-2024 CC ZG515 Data Warehousing 8

 BigQuery Architecture
• BigQuery’s serverless architecture decouples storage and
compute and allows them to scale independently on demand
• This structure offers both immense flexibility and cost controls
for customers
• because they don’t need to keep their expensive compute
resources up and running all the time
• This is very different from traditional node-based cloud data
warehouse solutions or on-premise massively parallel
processing (MPP) systems
• This approach also allows customers of any size to bring their
data into the data warehouse and start analyzing their data
using Standard SQL without worrying about database
operations and system engineering

27-11-2024 CC ZG515 Data Warehousing 9

 BigQuery Architecture
12 Components of Google BigQuery
• Serverless Service Model • Streaming Ingest
• Opinionated Storage Engine • Batch Ingest
• Dremel Execution Engine & • Federated Query Engine
Standard SQL
• UX, CLI, SDK, ODBC/JDBC, API
• Separation of Storage and
Compute through Jupiter • Pay-Per-Query AND Flat Rate
Network Pricing

• Enterprise-grade Data Sharing • IAM, Authentication & Audit

Logs
• Public Datasets, Commercial
Datasets, Marketing Datasets,
and the Free Pricing Tier
27-11-2024 CC ZG515 Data Warehousing 10
 BigQuery Architecture

27-11-2024 CC ZG515 Data Warehousing 11

 BigQuery Architecture
• BigQuery runs on top of Dremel
• BigQuery executes its shuffle in-memory in a separate
sub-service
• Dremel does stuff like pipelined execution and smart
scheduling
• Dremel itself is a vast multi-tenant compute cluster
• Dremel supports its legacy SQL, as well as 2011 ANSI
Standard SQL

27-11-2024 CC ZG515 Data Warehousing 12

 BigQuery Architecture
The Jupiter Network and Separation of Storage &
Compute
• Jupiter is Google’s inter-data center network, capable
of a Petabit of bisectional traffic, and allowing
BigQuery to sling data from storage to compute
Enterprise-Grade Data Sharing
• BigQuery’s pure separation of storage and compute,
coupled with of Colossus allows us to share Exabyte-
scale datasets with each other, much like Google Docs
and Sheets are shared today

27-11-2024 CC ZG515 Data Warehousing 13

 Ingesting data into BigQuery
Streaming and Batch Ingest
BigQuery‘s Streaming API is a rather unique feature
• We can stream data into BigQuery to the tune of millions of rows per
second, and data is available for analysis almost immediately
• BigQuery supports several ways to ingest data into its managed storage
• - specific ingestion method depends on origin of data
• E.g., data sources like Cloud Logging and Google Analytics support
direct exports to BigQuery
• BigQuery Data Transfer Service enables data transfer to BigQuery from
Google SaaS apps (Google Ads, Cloud Storage), Amazon S3, and other
data warehouses (Teradata, Redshift)
• Streaming data, such as logs or IoT device data, can be written to
BigQuery using Cloud Dataflow pipelines, Cloud Dataproc jobs, or
directly using the BigQuery stream ingestion API
27-11-2024 CC ZG515 Data Warehousing 14
 BigQuery Architecture

27-11-2024 CC ZG515 Data Warehousing 15

 BigQuery Architecture

Under the hood, BigQuery employs a vast set of multi-tenant services

driven by low-level Google infrastructure technologies like Dremel,
Colossus, Jupiter and Borg
27-11-2024 CC ZG515 Data Warehousing 16
 BigQuery Architecture

27-11-2024 CC ZG515 Data Warehousing 17

 BigQuery Architecture
Compute is Dremel, a large multi-tenant cluster that
executes SQL queries

▪ Dremel turns SQL queries into execution trees

▪ leaves of the tree are called slots and do the heavy lifting
of reading data from storage and any necessary
computation
▪ branches of tree are ‘mixers’, which perform aggregation
▪ Dremel dynamically apportions slots to queries on an as-
needed basis, maintaining fairness for concurrent queries
from multiple users
▪ A single user can get thousands of slots to run their
queries
27-11-2024 CC ZG515 Data Warehousing 18
 BigQuery Architecture
Storage is Colossus, Google’s global storage system
• BigQuery leverages columnar storage format and compression algorithm to
store data in Colossus
• optimized for reading large amounts of structured data
• Colossus also handles replication, recovery (when disks crash) and
distributed management (so there is no single point of failure)
• Colossus allows BigQuery users to scale to dozens of petabytes of data
stored seamlessly
• without paying the penalty of attaching much more expensive
compute resources as in traditional data warehouses
Compute and storage talk to each other through petabit Jupiter network
• In between storage and compute is ‘shuffle’, which takes advantage of
Google’s Jupiter network to move data extremely rapidly from one place
to another
BigQuery is orchestrated via Borg, Google’s precursor to Kubernetes
• The mixers and slots are all run by Borg, which allocates hardware
resources
27-11-2024 CC ZG515 Data Warehousing 19
 BigQuery Architecture
• BigQuery is a serverless and cost-effective enterprise data
warehouse that works across clouds and scales with your data
• Use built-in ML/AI and BI for insights at scale
• BigQuery Studio provides a single, unified interface for all data
practitioners of various coding skills to simplify analytics
workflows from data ingestion and preparation to data
exploration and visualization to ML model creation and use
• It also allows you to use simple SQL to access Vertex AI
foundational models directly inside BigQuery for text processing
tasks such as
• - sentiment analysis, entity extraction, etc. without having to
deal with specialized models

27-11-2024 CC ZG515 Data Warehousing 20

 BigQuery Architecture
• An AI collaborator integrated into BigQuery, Duet AI in BigQuery
provides contextual code assistance for writing SQL and Python
• It auto-suggests functions, code blocks, and fixes
• With chat assistance, we can use natural language to get real-
time guidance on performing specific tasks, reducing your need
to search for documentation
• BigQuery's serverless architecture lets you use SQL queries to
analyze your data
• BigQuery ML enables data scientists and data analysts to build
and operationalize ML models on planet-scale structured, semi-
structured, and now unstructured data directly inside BigQuery,
using simple SQL
• Export BigQuery ML models for online prediction into Vertex AI
or your own serving layer

27-11-2024 CC ZG515 Data Warehousing 21

 BigQuery Architecture
• BigQuery Omni is a fully managed, multicloud analytics solution that allows
for cost-effective and secure data analysis across clouds and shares results
within a single pane of glass
• Within BigQuery Analytics Hub, securely exchange data assets internally
and across organizations and enhance analysis with commercial, public,
and Google datasets
• Create and manage data clean rooms for privacy-centric measurement,
data sharing, and collaboration across organizations without moving or
copying data
• BigQuery has built-in capabilities that ingest streaming data and make it
immediately available to query, along with native integrations to streaming
products, like Dataflow
• Analyze large datasets interactively with BigQuery BI Engine, an in-memory
analysis service that offers sub-second query response time and high
concurrency
• Accelerate query performance and reduce costs within your environment
with BigQuery materialized views
27-11-2024 CC ZG515 Data Warehousing 22
 BigQuery Architecture
• Query all data types with BigQuery: structured, semi-structured, and
unstructured
• Use BigLake to explore and unify different data types and build advanced
models
• Centrally discover, manage, monitor, and govern data across data lakes,
data warehouses, and data marts with consistent controls with Dataplex
• an intelligent data fabric that enables organizations to provide
access to trusted data
• With built-in business intelligence, create and share insights in a few clicks
with Looker Studio or build data-rich experiences that go beyond BI
with Looker
• Analyze billions of rows of live BigQuery data in Google Sheets with familiar
tools, like pivot tables, charts, and formulas, to easily derive insights from
big data with Connected Sheets
• BigQuery's integration with security and privacy services from Google
Cloud provides strong security and fine-grained governance controls, down
to column level and row level
• data is encrypted at rest and in transit by default
27-11-2024 CC ZG515 Data Warehousing 23
 BigQuery Architecture
• BigQuery geospatial uniquely combines serverless architecture of
BigQuery with native support for geospatial analysis, so you can
augment your analytics workflows with location intelligence
• Simplify your analyses, see spatial data in fresh ways, and unlock
entirely new lines of business with support for arbitrary points,
lines, polygons, and multi-polygons in common geospatial data
formats
• Synchronize data across heterogeneous databases, storage
systems, and applications reliably and with minimal latency
with Datastream
• Datastream integrates with purpose-built and extensible Dataflow
templates to pull change streams written to Cloud Storage
• creates up-to-date replicated tables in BigQuery for real-time
analytics

27-11-2024 CC ZG515 Data Warehousing 24

 BigQuery Architecture
Bring any data into BigQuery
• analytics easier by bringing together data from multiple sources into
BigQuery
• You can upload data files from local sources, Google Drive, or Cloud Storage
buckets, use BigQuery Data Transfer Service (DTS), Cloud Data Fusion plugins,
replicate data from relational databases with Datastream for BigQuery, or
leverage Google's industry-leading data integration partnerships
External data
• You can query various external data sources such other Google Cloud storage
services (like Cloud Storage) or database services (like Cloud Spanner or Cloud
SQL)
Multi-cloud data
• You can query data that is stored in other public clouds such as AWS or Azure
Public datasets
• If you don't have your own data, you can analyze any of the datasets that are
available in the public dataset marketplace
27-11-2024 CC ZG515 Data Warehousing 25
 BigQuery Architecture
Migrate data warehouses to BigQuery

• Solve for today’s analytics demands and seamlessly scale your

business by moving to Google Cloud’s enterprise data
warehouse
• Streamline your migration path from Netezza, Oracle, Redshift,
Teradata, or Snowflake to BigQuery using free and fully managed
BigQuery Migration Service
• BigQuery is optimized to run analytic queries on large datasets,
including terabytes of data in seconds and petabytes in minutes
• Understanding its capabilities and how it processes queries can
help you maximize your data analysis investments

27-11-2024 CC ZG515 Data Warehousing 26

 BigQuery Architecture
Analytic workflows
• BigQuery supports several data analysis workflows
Ad hoc analysis
• BigQuery uses GoogleSQL, the SQL dialect in BigQuery, to support ad hoc
analysis. You can run queries in the Google Cloud console or through third-
party tools that integrate with BigQuery
Geospatial analysis
• BigQuery uses geography data types and GoogleSQL geography functions to
let you analyze and visualize geospatial data. For information about these data
types and functions, see Introduction to geospatial analytics
Machine learning
• BigQuery ML uses GoogleSQL queries to let you create and execute machine
learning (ML) models in BigQuery
Business intelligence
• BigQuery BI Engine is a fast, in-memory analysis service that lets you build
rich, interactive dashboards and reports without compromising performance,
scalability, security, or data freshness
27-11-2024 CC ZG515 Data Warehousing 27
 BigQuery Architecture
Queries

• primary unit of analysis in BigQuery is SQL query

• BigQuery has two SQL dialects
• GoogleSQL
• legacy SQL
• GoogleSQL is the preferred dialect
• supports SQL:2011 and includes extensions that
support geospatial analysis or ML

27-11-2024 CC ZG515 Data Warehousing 28

 BigQuery Studio
Helps you discover, analyze, and run inference on data in BigQuery with
following features:
• A robust SQL editor that provides code completion, query validation, and
estimation of bytes processed
• Embedded Python notebooks built using Colab Enterprise
• Notebooks provide one-click Python development runtimes, and built-in
support for BigQuery DataFrames
• Asset management and version history for code assets such as notebooks
and saved queries, built on top of Dataform
• Assistive code development in the SQL editor and in notebooks, built on
top of Duet AI generative AI
• Dataplex features for data discovery, and data profiling and data
quality scans
• view job history on a per-user or per-project basis
• analyze saved query results by connecting to other tools such as Looker
and Google Sheets, and to export saved query results for use in other
applications
27-11-2024 CC ZG515 Data Warehousing 29
BigQuery ML
BigQuery Architecture
• lets you use SQL in BigQuery to perform machine learning (ML) and predictive
analytics
• In addition to running queries in BigQuery, you can analyze your data with
various analytics and business intelligence tools that integrate with BigQuery,
like:
Looker
• Looker is an enterprise platform for business intelligence, data applications,
and embedded analytics. The Looker platform works with many datastores
including BigQuery
Looker Studio
• After you run a query, you can launch Looker Studio directly from BigQuery in
the Google Cloud console. Then, in Looker Studio you can create visualizations
and explore the data that's returned from the query
Connected Sheets
• You can also launch Connected Sheets directly from BigQuery in the console.
Connected Sheets runs BigQuery queries on your behalf either upon your
request or on a defined schedule. Results of those queries are saved in your
spreadsheet for analysis and sharing
27-11-2024 CC ZG515 Data Warehousing 30
 BigQuery Architecture
Third-party tool integration
• Several third-party analytics tools work with BigQuery
• E.g., you can connect Tableau to BigQuery data and use its
visualization tools to analyze and share your analysis
• ODBC and JDBC drivers are available and can be used to
integrate your application with BigQuery
• intent of these drivers is to help users leverage the power
of BigQuery with existing tooling and infrastructure
• pandas libraries like pandas-gbq let you interact with
BigQuery data in Jupyter notebooks
• You can also use BigQuery with other notebooks and
analysis tools

27-11-2024 CC ZG515 Data Warehousing 31

 BigQuery Architecture
Data analytics features
• BigQuery supports both descriptive and predictive analytics
• To query your data directly to answer some statistical questions, you can
use the Google Cloud console
• To visually explore the data, such as for trends and anomalies, you can use
tools like Tableau or Looker that integrate with BigQuery
Real-time analytics
Event-driven analysis
• Gain a competitive advantage by responding to business events in real
time with event-driven analysis
• Built-in streaming capabilities automatically ingest streaming data and
make it immediately available to query
• This allows you to stay agile and make business decisions based on the
freshest data
• Or use Dataflow to enable fast, simplified streaming data pipelines for a
comprehensive solution
27-11-2024 CC ZG515 Data Warehousing 32
 BigQuery Architecture
Predictive analytics
Predict business outcomes with leading AI/ML
• Predictive analytics can be used to streamline operations, boost revenue,
and mitigate risk
• BigQuery ML democratizes the use of ML by empowering data analysts to
build and run models using existing business intelligence tools and
spreadsheets
• Predictive analytics can guide business decision-making across the
organization
Analyze log data
• Analyze and gain deeper insights into your logging data with BigQuery
• You can store, explore, and run queries on generated data from servers,
sensors, and other devices simply using GoogleSQL
• Additionally, you can analyze log data alongside the rest of your business
data for broader analysis all natively within BigQuery
27-11-2024 CC ZG515 Data Warehousing 33
 BigQuery Architecture
Marketing analytics
• Increase marketing ROI and performance with data and AI

• Bring power of Google AI to your marketing data by

unifying marketing and business data sources in BigQuery
• Get a holistic view of the business, increase marketing
ROI and performance using more first-party data, and
deliver personalized and targeting marketing at scale with
ML/AI built-in
• Share insights and performance with Looker Studio or
Connected Sheets

27-11-2024 CC ZG515 Data Warehousing 34