Data Warehouse and OLAP II Data Warehouse and OLAP II Week 6 1 - - PowerPoint PPT Presentation

Data Warehouse and OLAP II Data Warehouse and OLAP II

Week 6

1

Team Homework Assignment #8 Team Homework Assignment #8

• Using a data warehousing tool and a data set, play four OLAP

Using a data warehousing tool and a data set, play four OLAP

• perations (Roll‐up (drill‐up), Drill‐down (roll down), Slice and

dice, Pivot (rotate)) and show the results. i 3 3 2 d 3 3

• Exercise 3.11, 3,12 and 3.13.
• Due date

beginning of the lecture on Friday March11th – beginning of the lecture on Friday March11th.

T i l OLAP O ti Typical OLAP Operations

• Roll‐up (drill‐up)

Roll up (drill up)

• Drill‐down (roll down)
• Slice and dice
• Pivot (rotate)
• Drill‐across
• Drill‐through

Drill through

R ll Roll-up

• Perform aggregation on a data cube by

– Climbing up a concept hierarchy for a dimension Climbing up a concept hierarchy for a dimension – Dimension reduction

Ro ll-up Ro ll up

5

Drill-down Drill down

• Drill‐down is the reverse of roll‐up
• Navigates from less detailed data to more detailed data by

– Stepping down a concept hierarchy for a dimension – Introducing additional dimensions

Drill-do wn Drill do wn

7

Slice and Dice Slice and Dice

• The slice operation performs a selection on one dimension of

the given cube, resulting in a sub‐cube

• The dice operation defines a sub‐cube by performing a

selection on two or more dimensions

8

Slic e Slic e

9

Dic e Dic e

10

Pivot (Rotate) Pivot (Rotate)

• Visualization operation that rotate the data axes in view in
• rder to provide an alternative presentation of the data

11

Pivo t Pivo t

12

Drill-across Drill across

• An additional drilling operation
• Executes queries involving (i e across) more than one fact
• Executes queries involving (i.e., across) more than one fact

table

13

Drill-through Drill through

• An additional drilling operation
• Uses relational SQL facilities to drill through the bottom level
• Uses relational SQL facilities to drill through the bottom level
• f a data cube down to its back‐end relational tables

14

F ig

mu da

ure 3.10. E

ultidime nsio ta ware ho E xample s o

• nal data
• using
• f T

ypic al O c ube , c o m OL AP o pe r mmo nly us ratio ns o n e d fo r

15

Motivation for Building Data Warehouse Motivation for Building Data Warehouse

• Building and using a data warehouse is a complex, difficult,

and long‐term task

• The construction of a large and complex information system
• The construction of a large and complex information system

can be viewed as the construction of large and complex building

D t W h P j t P (1) Data Warehouse Project Process (1)

• Top‐down, bottom‐up approaches or a combination of both

– Top‐down: Starts with overall design and planning (mature) – Bottom‐up: Starts with experiments and prototypes (rapid)

Data Warehouse Project Process (2) Data Warehouse Project Process (2)

• Typical data warehouse design process

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

Th D t W h M d l Three Data Warehouse Models

• Enterprise warehouse
• Enterprise warehouse

– Collects all of the information about subjects spanning the entire

• rganization
• Data mart

– A subset of corporate‐wide data that is of value to a specific groups of users Its scope is confined to specific selected groups such as

• users. Its scope is confined to specific, selected groups, such as

marketing data mart

• Independent vs. dependent (directly from warehouse) data mart
• Virtual warehouse

– A set of views over operational databases – Only some of the possible summary views may be materialized – Only some of the possible summary views may be materialized

Data Ware ho use De ve lo pme nt: A R d d A h A Re c o mme nde d Appro ac h

F ig ure 3.13 A re c o mme nde d appro ac h fo r data ware ho use

de ve lo pme nt.

F ig ure 3.12 A thre e -tie r data ware ho using arc hite c ture .

OLAP S A hit t OLAP Server Architectures

• Relational OLAP (ROLAP)
• Multidimensional OLAP (MOLAP)

Multidimensional OLAP (MOLAP)

• Hybrid OLAP (HOLAP)

ROLAP ROLAP

• Advantages

C h dl l f d – Can handle large amounts of data – Can leverage functionalities inherent in the relational database database

• Disadvantages

– Performance can be slow – Limited by SQL functionalities

23

MOLAP MOLAP

• Advantages

– Excellent performance – Can perform complex calculations

• Disadvantages

– Limited in the amount of data it can handle – Requires additional investment

HOLAP HOLAP

• HOLAP technologies attempt to combine the advantages of

MOLAP and ROLAP.

25

Data Warehouse Vendors Data Warehouse Vendors

• IBM

– http://www‐ 306.ibm.com/software/data/informix/redbrick/

• Microsoft

– http://www.microsoft.com/sql/solutions/bi/default.mspx p // / q / / / p

• Oracle

– http://www.oracle.com/siebel/index.html

• Business Objects
• Business Objects

– http://www.businessobjects.com/

Data Warehouse Vendors (cont’d) Data Warehouse Vendors (cont d)

• Microstrategy

– http://www microstrategy com/ – http://www.microstrategy.com/

• Cognos

– http://www.cognos.com/ f

• Informatica

– http://www.informatica.com/

• Actuate

– http://www.actuate.com/home/index.asp

Open Source Data Warehousing Tools Open Source Data Warehousing Tools

• MySQL‐based data warehouse
• Open data warehouse

Open data warehouse

D t W h U (1) Data Warehouse Usage (1)

• Information processing

supports querying basic statistical analysis reporting using – supports querying, basic statistical analysis, reporting using cross‐tabs, tables, charts and graphs

• Analytical processing
• Analytical processing

– multidimensional analysis of data warehouse data supports basic OLAP operations slice dice drilling – supports basic OLAP operations, slice‐dice, drilling, pivoting

D t W h U (2) Data Warehouse Usage (2)

• Data mining
• Data mining

– knowledge discovery from hidden patterns Supports associations constructing analytical models – Supports associations, constructing analytical models, performing classification and prediction, and presenting the mining results using visualization tools presenting the mining results using visualization tools

From OLAP t OLAM to OLAM

• On‐Line Analytical Mining

y g

– High quality of data in data warehouses – Available information processing infrastructure surrounding data warehouses – OLAP‐based exploratory data analysis O li l ti f d t i i f ti – On‐line selection of data mining functions

F ig

arc

g ure 3.18

c hite c ture An inte gr e . rate d OL AM and OL AP