2.2.6 Multidimensional Analysis
Multidimensional analysis refers to the process commonly used in data warehousing applications of examining data using various combinations of dimensions. Dimensions are categories used to classify data such as time, geography, a company’s departments, product lines, and so forth. The results associated with a particular set of dimensions are called facts. Facts are typically figures associated with product sales, profits, volumes, counts, etc.In order to obtain these facts according to a set of dimensions in a relational database system, SQL aggregation is typically used. SQL aggregation basically means data is grouped according to certain criteria (dimensions) and the result set consists of aggregates of facts such as counts, sums, and averages of the data in each group.The GROUP BY clause of the SQL SELECT command supports the following extensions that simplify the process of producing aggregate results.
• ROLLUP extension
• CUBE extension
• GROUPING SETS extensionIn addition, the GROUPING function and the GROUPING_ID function can be used in the SELECT list or the HAVING clause to aid with the interpretation of the results when these extensions are used.Note: The sample dept and emp tables are used extensively in this discussion to provide usage examples. The following changes were applied to these tables to provide more informative results.The following rows from a join of the emp and dept tables are used:The loc, dname, and job columns are used for the dimensions of the SQL aggregations used in the examples. The resulting facts of the aggregations are the number of employees obtained by using the COUNT(*) function.A basic query grouping the loc, dname, and job columns is given by the following.The rows of this result set using the basic GROUP BY clause without extensions are referred to as the base aggregate rows.The ROLLUP and CUBE extensions add to the base aggregate rows by providing additional levels of subtotals to the result set.The GROUPING SETS extension provides the ability to combine different types of groupings into a single result set.The GROUPING and GROUPING_ID functions aid in the interpretation of the result set.