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 manufacturer Ford has various models including Mondeo, Fiesta and Ka and each model comes in various size of engine such as 1.8 or 1.6 with each available in one of three trim levels of high, medium and low.

-Describe how SQL has been extended to include OLAP-type analysis of relational data.

Question

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 manufacturer Ford has various models including Mondeo, Fiesta and Ka and each model comes in various size of engine such as 1.8 or 1.6 with each available in one of three trim levels of high, medium and low. 
 
-Describe how SQL has been extended to include OLAP-type analysis of relational data.

Quizplus · Accepted Answer

The Answer is The extensions to SQL are collectively referred to as the 'OLAP package' and includes:
• Extended Grouping capabilities such as ROLLUP and CUBE.
• Extended OLAP operators such as Ranking and Window calculations.
Extended Grouping Capabilities
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 supports calculations using aggregations such as SUM, COUNT, MAX, MIN, and AVG at increasing levels of aggregation, from the most detailed up to a grand total. CUBE is similar to ROLLUP, enabling a single statement to calculate all possible combinations of aggregations. CUBE can generate the information needed in cross-tabulation reports with a single query.
Elementary OLAP Operators
Supports a variety of operations such as rankings and window calculations. Ranking functions include cumulative distributions, percent rank, and N-tiles. Windowing allows the calculation of cumulative and moving aggregations using functions such as SUM, AVG, MIN, and COUNT.

The OLAP Cube Shown in Figure 33