data cube and olap server l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Cube and OLAP Server PowerPoint Presentation
Download Presentation
Data Cube and OLAP Server

Loading in 2 Seconds...

play fullscreen
1 / 23

Data Cube and OLAP Server - PowerPoint PPT Presentation


  • 341 Views
  • Uploaded on

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.

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

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

Data Cube and OLAP Server

Madhavi Gundavarapu

outline
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
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
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
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
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
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
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
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
Multidimensional Data
  • Measure Attributes
  • Dimension Attributes
  • Example

Data Cube and OLAP Server

olap system
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: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
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
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
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
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
Example Contd.

CUBE

Data Cube and OLAP Server

grouping function
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
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
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 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
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
Questions

Data Cube and OLAP Server