Therefore, analytical tasks require convenient and efficient data aggregation. Subtotals across many dimensions are vital to multi-dimensional analyses.
Because the flood of detailed data generated by large organizations cannot be interpreted at the lowest level, aggregated views of the information are essential. An ad hoc user might work with a wide variety of constraints, working in a subset cube.Īnswering multi-dimensional questions often involves huge quantities of data, sometimes millions of rows. In contrast, product managers might compare slices that apply to different products. Regional managers might study the data by comparing slices of the cube applicable to different markets. These correspond to cross-tabular reports such as the one shown in Table 20-1. We can retrieve "slices" of data from the cube. The cube stores sales data organized by the dimensions of Product, Market, and Time.įigure 20-1 Cube and Views by Different Users Figure 20-1 shows a data cube and how it could be used differently by various groups. To visualize data that has many dimensions, analysts commonly use the analogy of a data "cube," that is, a space where facts are stored at the intersection of n dimensions. Many multi-dimensional questions require aggregated data and comparisons of data sets, often across time, geography or budgets.
This chapter presents concepts, syntax, and examples of CUBE, ROLLUP and Top-N analysis. To enhance performance, both CUBE and ROLLUP are parallelized: multiple processes can simultaneously execute both types of statements.įor information on parallel execution, see Oracle8i Concepts.Įnhanced Top-N queries enable more efficient retrieval of the largest and smallest values of a data set. CUBE can generate the information needed in cross-tab reports with a single query. CUBE is an extension similar to ROLLUP, enabling a single statement to calculate all possible combinations of subtotals. ROLLUP creates subtotals at any level of aggregation needed, from the most detailed up to a grand total.
ROLLUP and CUBE are simple extensions to the SELECT statement's GROUP BY clause. These enhancements make important calculations significantly easier and more efficient, enhancing database performance, scalability and simplicity. Oracle also provides optimized performance and simplified syntax for Top-N queries. Oracle expands its long-standing support for analytical applications in Oracle8i release 8.1.5 with the CUBE and ROLLUP extensions to SQL. Enterprises exploring new markets and facing greater competition expect these tools to provide the maximum possible decision-making value from their data resources. The last decade has seen a tremendous increase in the use of query, reporting, and on-line analytical processing (OLAP) tools, often in conjunction with data warehouses and data marts. Overview of CUBE, ROLLUP, and Top-N Queries
0 kommentar(er)
