Data Cube and OLAP Server

1. Data Cube and OLAP Server Madhavi Gundavarapu

2. 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

3. 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

4. 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

5. Steps in Data Analysis • Formulate query • Extract aggregated data • Visualize results • Analyze Analyze & Formulate Extract Visualize Data Cube and OLAP Server

6. 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

7. 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

8. 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

9. 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

10. Multidimensional Data • Measure Attributes • Dimension Attributes • Example Data Cube and OLAP Server

11. 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

12. 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

13. 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

14. 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

15. 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

16. 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

17. Example Contd. CUBE Data Cube and OLAP Server

18. 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

19. 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

20. 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

21. 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

22. 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

23. Questions Data Cube and OLAP Server