Data Warehousing & OLAP Motivation: Business Intelligence - - PowerPoint PPT Presentation

▶
data warehousing olap motivation business intelligence
SMART_READER_LITE
LIVE PREVIEW

Data Warehousing & OLAP Motivation: Business Intelligence - - PowerPoint PPT Presentation

Dec 14, 2022 187 likes •465 views

Data Warehousing & OLAP Motivation: Business Intelligence Customer information Product information (customer-id, gender, age, home- (Product-id, category, address, occupation, income, manufacturer, made-in, stock- family-size, )

slide-1
SLIDE 1

Data Warehousing & OLAP

slide-2
SLIDE 2

Motivation: Business Intelligence

Jian Pei: Big Data Analytics -- Multidimensional Analysis 2

Customer information (customer-id, gender, age, home- address, occupation, income, family-size, …) Product information (Product-id, category, manufacturer, made-in, stock- price, …) Sales information (customer-id, product-id, #units, unit-price, sales- representative, …) Business queries:

  • Which categories of products are most popular for customers in Vancouver?
  • Find pairs (customer groups, most popular products)
slide-3
SLIDE 3
  • J. Pei: Finding Outstanding Aspects and Contrast Subspaces

3

Symptoms:

  • verweight,

high blood pressure, back pain, short of breadth, chest pain, cold sweat … In what aspect is he most similar to cases of coronary artery disease and, at the same time, dissimilar to adiposity?

slide-4
SLIDE 4

Don’t You Ever Google Yourself?

  • Big data makes one know oneself better
  • 57% American adults search themselves on

Internet

– Good news: those people are better paid than those who haven’t done so! (Investors.com)

  • Egocentric analysis becomes

more and more important with big data

  • J. Pei: Finding Outstanding Aspects and Contrast Subspaces

4

slide-5
SLIDE 5

Egocentric Analysis

  • How am I different from (more often than

not, better than) others?

  • In what aspects am I good?
  • J. Pei: Finding Outstanding Aspects and Contrast Subspaces

5

http://img03.deviantart.net/a670/i/2010/219/a/e/glee___egocentric_by_gleeondoodles.jpg

slide-6
SLIDE 6

Dimensions

  • “An aspect or feature of a situation, problem, or

thing, a measurable extent of some kind” – Dictionary

  • Dimensions/attributes are used to model

complex objects in a divide-and-conquer manner

– Objects are compared in selected dimensions/ attributes

  • More often than not, objects have too many

dimensions/attributes than one is interested in and can handle

Jian Pei: Big Data Analytics -- Multidimensional Analysis 6

slide-7
SLIDE 7

Multi-dimensional Analysis

  • Find interesting patterns in multi-dimensional

subspaces

– “Michael Jordan is outstanding in subspaces (total points, total rebounds, total assists) and (number of games played, total points, total assists)”

  • Different patterns may be manifested in

different subspaces

– Feature selection (machine learning and statistics): select a subset of relevant features for use in model construction – a set of features for all objects – Different subspaces may manifest different patterns

Jian Pei: Big Data Analytics -- Multidimensional Analysis 7

slide-8
SLIDE 8

Jian Pei: Big Data Analytics -- Multidimensional Analysis 8

OLAP

  • Conceptually, we may explore all possible

subspaces for interesting patterns

  • What patterns are interesting?
  • How can we explore all possible subspaces

systematically and efficiently?

  • Fundamental problems in analytics and data

mining

slide-9
SLIDE 9

Jian Pei: Big Data Analytics -- Multidimensional Analysis 9

OLAP

  • Aggregates and group-bys are frequently used in

data analysis and summarization

SELECT time, altitude, AVG(temp) FROM weather GOUP BY time, altitude; – In TPC, 6 standard benchmarks have 83 queries, aggregates are used 59 times, group-bys are used 20 times

  • Online analytical processing (OLAP): the

techniques that answer multi-dimensional analytical (MDA) queries efficiently

slide-10
SLIDE 10

Jian Pei: Big Data Analytics -- Multidimensional Analysis 10

OLAP Operations

  • Roll up (drill-up): summarize data by

climbing up hierarchy or by dimension reduction

– (Day, Store, Product type, SUM(sales) à (Month, City, *, SUM(sales))

  • Drill down (roll down): reverse of roll-up,

from higher level summary to lower level summary or detailed data, or introducing new dimensions

slide-11
SLIDE 11

Roll Up

Jian Pei: Big Data Analytics -- Multidimensional Analysis 11

http://www.tutorialspoint.com/dwh/images/rollup.jpg

slide-12
SLIDE 12

Drill Down

Jian Pei: Big Data Analytics -- Multidimensional Analysis 12

http://www.tutorialspoint.com/dwh/images/drill_down.jpg

slide-13
SLIDE 13

Other Operations

  • Dice: pick specific values or ranges on

some dimensions

  • Pivot: “rotate” a cube – changing the order
  • f dimensions in visual analysis

Jian Pei: Big Data Analytics -- Multidimensional Analysis 13

http://en.wikipedia.org/wiki/File:OLAP_pivoting.png

slide-14
SLIDE 14

Dice

Jian Pei: Big Data Analytics -- Multidimensional Analysis 14

http://www.tutorialspoint.com/dwh/images/dice.jpg

slide-15
SLIDE 15

Jian Pei: Big Data Analytics -- Multidimensional Analysis 15

Relational Representation

  • If there are n dimensions, there are 2n

possible aggregation columns

Roll up by model by year by color in a table

slide-16
SLIDE 16

Jian Pei: Big Data Analytics -- Multidimensional Analysis 16

Difficulties

  • Many group bys are needed

– 6 dimensions à 26=64 group bys

  • In most SQL systems, the resulting query

needs 64 scans of the data, 64 sorts or hashes, and a long wait!

slide-17
SLIDE 17

Jian Pei: Big Data Analytics -- Multidimensional Analysis 17

Dummy Value “ALL”

slide-18
SLIDE 18

Jian Pei: Big Data Analytics -- Multidimensional Analysis 18

CUBE

SALES Model Year Color Sales

Chevy 1990 red 5 Chevy 1990 white 87 Chevy 1990 blue 62 Chevy 1991 red 54 Chevy 1991 white 95 Chevy 1991 blue 49 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 blue 71 Ford 1990 red 64 Ford 1990 white 62 Ford 1990 blue 63 Ford 1991 red 52 Ford 1991 white 9 Ford 1991 blue 55 Ford 1992 red 27 Ford 1992 white 62 Ford 1992 blue 39

DATA CUBE Model Year Color Sales

CUBE

Chevy 1990 blue 62 Chevy 1990 red 5 Chevy 1990 white 95 Chevy 1990 ALL 154 Chevy 1991 blue 49 Chevy 1991 red 54 Chevy 1991 white 95 Chevy 1991 ALL 198 Chevy 1992 blue 71 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 ALL 156 Chevy ALL blue 182 Chevy ALL red 90 Chevy ALL white 236 Chevy ALL ALL 508 Ford 1990 blue 63 Ford 1990 red 64 Ford 1990 white 62 Ford 1990 ALL 189 Ford 1991 blue 55 Ford 1991 red 52 Ford 1991 white 9 Ford 1991 ALL 116 Ford 1992 blue 39 Ford 1992 red 27 Ford 1992 white 62 Ford 1992 ALL 128 Ford ALL blue 157 Ford ALL red 143 Ford ALL white 133 Ford ALL ALL 433 ALL 1990 blue 125 ALL 1990 red 69 ALL 1990 white 149 ALL 1990 ALL 343 ALL 1991 blue 106 ALL 1991 red 104 ALL 1991 white 110 ALL 1991 ALL 314 ALL 1992 blue 110 ALL 1992 red 58 ALL 1992 white 116 ALL 1992 ALL 284 ALL ALL blue 339 ALL ALL red 233 ALL ALL white 369 ALL ALL ALL 941

SELECT Model, Year, Color, SUM(sales) AS Sales FROM Sales WHERE Model in {'Ford', 'Chevy'} AND Year BETWEEN 1990 AND 1992 GROUP BY CUBE(Model, Year, Color);

slide-19
SLIDE 19

Jian Pei: Big Data Analytics -- Multidimensional Analysis 19

Semantics of ALL

  • ALL is a set

– Model.ALL = ALL(Model) = {Chevy, Ford } – Year.ALL = ALL(Year) = {1990,1991,1992} – Color.ALL = ALL(Color) = {red,white,blue}

slide-20
SLIDE 20

Jian Pei: Big Data Analytics -- Multidimensional Analysis 20

OLTP Versus OLAP

OLTP OLAP users clerk, IT professional knowledge worker function day to day operations decision support DB design application-oriented subject-oriented data current, up-to-date, detailed, flat relational Isolated historical, summarized, multidimensional integrated, consolidated usage repetitive ad-hoc access read/write, index/hash on prim. key lots of scans unit of work short, simple transaction complex query # records accessed tens millions #users thousands hundreds DB size 100MB-GB 100GB-TB metric transaction throughput query throughput, response

slide-21
SLIDE 21

Jian Pei: Big Data Analytics -- Multidimensional Analysis 21

What Is a Data Warehouse?

  • “A data warehouse is a subject-oriented,

integrated, time-variant, and nonvolatile collection of data in support of management’s decision-making process.” – W. H. Inmon

  • Data warehousing: the process of

constructing and using data warehouses

slide-22
SLIDE 22

Jian Pei: Big Data Analytics -- Multidimensional Analysis 22

Subject-Oriented

  • Organized around major subjects, such as

customer, product, sales

  • Focusing on the modeling and analysis of

data for decision makers, not on daily

  • perations or transaction processing
  • Providing a simple and concise view around

particular subject issues by excluding data that are not useful in the decision support process

slide-23
SLIDE 23

Jian Pei: Big Data Analytics -- Multidimensional Analysis 23

Integrated

  • Integrating multiple, heterogeneous data sources

– Relational databases, flat files, on-line transaction records

  • Data cleaning and data integration

– Ensuring consistency in naming conventions, encoding structures, attribute measures, etc. among different data sources

  • E.g., Hotel price: currency, tax, breakfast covered, etc.

– When data is moved to the warehouse, it is converted

slide-24
SLIDE 24

Jian Pei: Big Data Analytics -- Multidimensional Analysis 24

Time Variant

  • The time horizon for the data warehouse is

significantly longer than that of operational systems

– Operational databases: current value data – Data warehouse data: provide information from a historical perspective (e.g., past 5-10 years)

  • Every key structure in the data warehouse contains

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

slide-25
SLIDE 25

Jian Pei: Big Data Analytics -- Multidimensional Analysis 25

Nonvolatile

  • A physically separate store of data

transformed from the operational environment

  • Operational updates of data do not occur in

the data warehouse environment

– Do not require transaction processing, recovery, and concurrency control mechanisms – Require only two operations in data accessing

  • Initial loading of data
  • Access of data
slide-26
SLIDE 26

Jian Pei: Big Data Analytics -- Multidimensional Analysis 26

Why Separate Data Warehouse?

  • High performance for both

– Operational DBMS: tuned for OLTP – Warehouse: tuned for OLAP

  • Different functions and different data

– Historical data: data analysis often uses historical data that operational databases do not typically maintain – Data consolidation: data analysis requires consolidation (aggregation, summarization) of data from heterogeneous sources

slide-27
SLIDE 27

To-Do List

  • Read Section 4.1

Jian Pei: CMPT 741/459 Data Warehousing and OLAP (1) 27