www.HadoopExam.com

HadoopExam Learning Resources

Question 15: When would you use GROUP BY ROLLUP clause in your OLAP query?

1. where only the subtotals are to be included in the output

2. where only the grand totals are to be included in the output

3. where only specific subtotals and grand totals for a combination of variables are to be included in the output

4. where all subtotals and grand totals are to be included in the output

Correct Answer : 4 Exp: The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The ROLLUP, CUBE, or GROUPING SETS operators can generate the same result set as when you use UNION ALL to combine single grouping queries; however, using one of the GROUP BY operators is usually more efficient.
The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want. The GROUPING SETS list can contain duplicate groupings; and, when GROUPING SETS is used with ROLLUP and CUBE, it might generate duplicate groupings. Duplicate groupings are retained as they would be by using UNION ALL. Queries that use the ROLLUP and CUBE operators generate some of the same result sets and perform some of the same calculations as OLAP applications. The CUBE operator generates a result set that can be used for cross tabulation reports. A ROLLUP operation can calculate the equivalent of an OLAP dimension or hierarchy. A query with a GROUP BY ROLLUP clause returns the same aggregated data as an equivalent query with a GROUP BY clause. It also returns multiple levels of subtotal rows. You can include up to three fields in a comma-separated list in a GROUP BY ROLLUP clause.

The GROUP BY ROLLUP clause adds subtotals at different levels, aggregating from right to left through the list of grouping columns. The order of rollup fields is important. A query that includes three rollup fields returns the following rows for totals:

First-level subtotals for each combination of fieldName1 and fieldName2. Results are grouped by fieldName3.
Second-level subtotals for each value of fieldName1. Results are grouped by fieldName2 and fieldName3.
One grand total row

You have no rights to post comments

You are here: Home EMC Certification EMC Data Science EMC Data Science Question 15: When would you use GROUP BY ROLLUP clause in your OLAP query?