Data Cube and OLAP Server

1 / 23

# Data Cube and OLAP Server - PowerPoint PPT Presentation

Data Cube and OLAP Server. Madhavi Gundavarapu. Outline. What is Data Analysis? Steps in Data Analysis SQL-92 Aggregate Functions Limitations of GROUP BY OLAP Server CUBE Operator ROLLUP Operator. What is Data Analysis?. query. DATA ANALYSIS. exact response.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Data Cube and OLAP Server' - niveditha

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Data Cube and OLAP Server

Outline
• What is Data Analysis?
• Steps in Data Analysis
• SQL-92 Aggregate Functions
• Limitations of GROUP BY
• OLAP Server
• CUBE Operator
• ROLLUP Operator

Data Cube and OLAP Server

What is Data Analysis?

query

DATA

ANALYSIS

exact

response

• User issues a query, receives a response and formulates the next query based on the response
• This process repeats until the user gets the required result
• Fundamentally an iterative process

Data Cube and OLAP Server

Why Data Analysis?
• Search for unusual patterns of data
• Summarize data values
• Extract statistical information
• Contrast one category with another
• Provide a consolidated view of enterprise data buried in OLTP databases
• Help Decision makers understand business trends
• Derive intelligible results from ad hoc, voluminous and scattered data

Data Cube and OLAP Server

Steps in Data Analysis
• Formulate query
• Extract aggregated data
• Visualize results
• Analyze

Analyze &

Formulate

Extract

Visualize

Data Cube and OLAP Server

Overview of SQL-92
• SQL has several aggregate operators:
• sum(), count(), avg(), min(), max()
• The basic idea is:
• Combine all values in a column
• into a single scalar value
• Syntax
• SELECT sum(units)

FROM inventory;

Data Cube and OLAP Server

Overview of SQL-92 (contd.): Distinct Clause
• DISTINCT
• Allows aggregation over distinct values
• Example
• SELCT COUNT(DISTINCT locations)
• FROM inventory;

Data Cube and OLAP Server

Overview of SQL-92 (contd.): GROUP BY Clause
• Group By allows aggregates over table sub-groups
• Result is a new table
• Syntax:

SELCT location, sum(units)

FROM inventory

GROUP BY location

HAVING nation = “USA”;

Data Cube and OLAP Server

Limitations of GROUP BY

sum

M T W T F S S



AIR

HOTEL

FOOD

MISC



• Users want CrossTabs
• GROUP BY is limited to 0-D and 1-D aggregates
• Users want sub-totals and totals
• drill-down & roll-up reports

Data Cube and OLAP Server

Multidimensional Data
• Measure Attributes
• Dimension Attributes
• Example

Data Cube and OLAP Server

OLAP System
• On-Line Analytical Processing System
• Interactive system
• Permits analysts to view summaries of multidimensional data
• On-Line indicates
• No long waits to see result of a query
• response times within a few seconds for new summaries
• View data at different levels of granularity

Data Cube and OLAP Server

SQL:1999 OLAP Extensions
• SQL-92 functionality was limited
• SQL:1999 standard defines
• CUBE
• ROLLUP
• as generalizations of GROUP BY clause

Data Cube and OLAP Server

CUBE : Relational Aggregate Operator

Aggregate

Group By

Sum

(with total)

By Color

RED

Cross Tab

WHITE

BLUE

By Color

Chevy

Ford

RED

The Data Cube and

WHITE

Sum

The Sub-Space Aggregates

BLUE

By Make

1990

FORD

1991

CHEVY

1992

Sum

1993

By Year

By Make

By Make & Year

RED

WHITE

BLUE

By Color & Year

By Make & Color

Sum

By Color

• N-dimensional generalization of simple aggregate functions

Data Cube and OLAP Server

CUBE : The Idea
• 0-dimensional Aggregate (sum(), max(),...)
• a1, a2, ...., aN, f()
• Super-aggregate over 1-Dimensional sub-cubes
• ALL, a2, ...., aN , f()
• a1, ALL, a3, ...., aN , f()
• ...
• a1, a2, ...., ALL, f()
• Super-aggregate over 2-Dimensional sub-cubes
• ALL, ALL, a3, ...., aN , f()
• ...
• a1, a2 ,...., ALL, ALL, f()

Data Cube and OLAP Server

An Example

SELECT model, year, color, sum(sales) as sales

FROM sales

WHERE model in (‘Chevy’)

AND year BETWEEN 1990 AND 1992

GROUP BY CUBE (model, year, color);

Data Cube and OLAP Server

CUBE Contd.

SELECT model, year, color, sum(sales) as sales

FROM sales

WHERE model in (‘Chevy’)

AND year BETWEEN 1990 AND 1992

GROUP BY CUBE (model, year, color);

• Computes union of 8 different groupings:
• {(model, year, color), (model, year), (model, color), (year, color), (model), (year), (color), ()}

Data Cube and OLAP Server

Example Contd.

CUBE

Data Cube and OLAP Server

GROUPING Function
• SQL:1999 uses NULL to represent both ALL and regular null values
• GROUPING function
• Can be applied to an attribute
• Returns 1 if NULL value represents ALL
• Returns 0 in all other cases

Data Cube and OLAP Server

GROUPING Example

SELECT model, year, color, sum(sales) as sales,

GROUPING(model) as model_flag,

GROUPING(year) as year_flag,

GROUPING(color) as color_flag

FROM sales

WHERE model in (‘Chevy’)

AND year BETWEEN 1990 AND 1992

GROUP BY CUBE (model, year, color);

Data Cube and OLAP Server

Rollup and Drill down
• Allow analysts to view data at any desired level of granularity
• Rollup
• Operation of moving from finer-granularity of data to a coarser granularity
• Drill Down
• Operation of moving from coarser-granularity of data to a finer granularity
• Cannot be generated from coarse-granularity data
• Has to be computed from original data

Data Cube and OLAP Server

ROLLUP Operator
• Rollup example

SELECT model, year, color, sum(sales) as sales

FROM sales

WHERE model in (‘Chevy’)

AND year BETWEEN 1990 AND 1992

GROUP BY ROLLUP (model, year, color);

• Only 4 groupings are generated
• {(model, year, color), (model, year), (model), ()}

Data Cube and OLAP Server

Summary
• SQL-92 has limited functionality to support OLAP operations
• SQL:1999 has introduced extensions to address these limitations
• provides operators such as CUBE, GROUPING and ROLLUP

Data Cube and OLAP Server

Questions

Data Cube and OLAP Server