Databas – Part V
Chapters 33 OLAP
The OLAP cube shown in Figure 33.1 has been prepared for a car rental company that rents out cars to
customers throughout the UK. The company wishes to explore which manufacturer, model, engine size and
trim (interior finish) generates the most rental income in each location of the UK. For example, the
Figure 33.1
33.1 Present typical dimensional hierarchies (with 4 levels of aggregation) for each dimension shown in
Figure 1. The highest level of aggregation for each dimension is shown in Figure 33.1.
33.2 Describe the four common OLAP operations for querying data. Provide an example of each operation
using the OLAP cube in Figure 33.1 and your answer to question 33.1.
OLAP operations include:
Country
Country
Year
Car manufacturer
Databas – Part V
Roll-up, drill-down, slice and dice, pivoting.
Roll-up performs aggregations on the data by moving up the dimensional hierarchy or by dimensional
reduction e.g. 3-D to 2-D.
33.3 Describe how SQL has been extended to include OLAP-type analysis of relational data.
Extended Grouping Capabilities
Databas – Part V
Aggregation is a fundamental part of OLAP. To improve aggregation capabilities the SQL standard
provides extensions to the GROUP BY clause such as the ROLLUP and CUBE functions. ROLLUP
Elementary OLAP Operators
Supports a variety of operations such as rankings and window calculations. Ranking functions include
_________________________________________________________________________________
The OLAP cube shown in the figure below has been prepared for a media (TV programmes and films) streaming
service.
33.4 Describe the four common OLAP operations for querying data. Provide an example of each operation
using the OLAP cube in Figure 33.2.
Common OLAP operations include:
Roll-up performs aggregations on the data by moving up the dimensional hierarchy or by dimensional
Country
Databas – Part V
For example, analysis of weekly media streaming to analysis of monthly streaming.
33.5 What are the three key features that all OLAP applications share?
33.6 Explain the difference between the output produced by SQL ROLLUP and CUBE queries.
The ROLLUP and CUBE
Databas – Part V
capabilities by generated subtotal rows for every combination of GROUP BY columns. ROLLUP and
CUBE also generate a grand total row.