Utilizing Concept Hierarchies in OLAP Operations

CHAPTER 4_PART 2 DATA WAREHOUSE DATA WAREHOUSE Assistant Prof. Dr Karim Hashim Kraidi 2021

4.2.5 TYPICAL OLAP OPERATIONS How are concept hierarchies useful in OLAP? In the multidimensional model, data are organized into multiple dimensions, and each dimension contains multiple levels of abstraction defined by concept hierarchies. This organization provides users with the flexibility to view data from different perspectives. A number of OLAP data cube operations exist to materialize these different views, allowing interactive querying and analysis of the data at hand. Hence, OLAP provides a user-friendly environment for interactive data analysis. Example 4.4 OLAP operations. Let s look at some typical OLAP operations for multidimensional data. Each of the following operations described is illustrated in Figure 4.12. At the center of the figure is a data cube for AllElectronics sales. The cube contains the dimensions location, time, and item, where location is aggregated with respect to city values, time is aggregated with respect to quarters, and item is aggregated with respect to item types. To aid in our explanation, we refer to this cube as the central cube. The measure displayed is dollars sold (in thousands). (For improved readability, only some of the cubes cell values are shown.) The data examined are for the cities Chicago, New York, Toronto, and Vancouver.

4.2.5 TYPICAL OLAP OPERATIONS 1. Roll-up:The roll-up operation (also called the drill-up operation by some vendors) performs aggregation on a data cube, either by climbing up a concept hierarchy for a dimension or by dimension reduction. Figure 4.12 shows the result of a roll-up operation performed on the central cube by climbing up the concept hierarchy for location given in Figure 4.9. This hierarchy was defined as the total order street < city < province or state < country. The roll-up operation shown aggregates the data by ascending the location hierarchy cube containing only the location and time dimensions. Roll-up may be performed by removing, say, the time dimension, resulting in an aggregation of the total sales by location, rather than by location and by time. from the level of city to the level of country. In other words, rather than grouping the data by city, the resulting cube groups the data by country. When roll-up is performed by dimension reduction, one or more dimensions are removed from the given cube. For example, consider a sales data

4.2.5 TYPICAL OLAP OPERATIONS 2. Drill-down: Drill-down is the reverse of roll-up. It navigates from less detailed data to more detailed data. Drill-down can be realized by either stepping down a concept hierarchy for a dimension or introducing additional dimensions. Figure 4.12 shows the result of a drill-down operation performed on the central cube by stepping down a concept hierarchy for the time defined as day < month < quarter < year. Drill- down occurs by descending the time hierarchy from the level of the quarter to a more detailed level of the month. The resulting data cube details the total sales per month rather than summarizing them by quarter. Because a drill-down adds more detail to the given data, it can also be performed by adding new dimensions to a cube. For example, a drill-down on the central cube of Figure 4.12 can occur by introducing an additional dimension, such as a customer group.

4.2.5 TYPICAL OLAP OPERATIONS 3. Slice and dice: The slice operation performs a selection on one dimension of the given cube, resulting in a subcube. Figure 4.12 shows a slice operation where the sales data are selected from the central cube for the dimension time using the criterion time D Q1. The dice operation defines a subcube by performing a selection on two or more dimensions. Figure 4.12 shows a dice operation on the central cube based on the following selection criteria that involve three dimensions: (location D Toronto or Vancouver ) and (time D Q1 or Q2 ) and (item D home entertainment or computer ). 4. Pivot (rotate): Pivot (also called rotate) is a visualization operation that rotates the data axes in view to provide an alternative data presentation. Figure 4.12 shows a pivot operation where the item and location axes in a 2-D slice are rotated. Other examples include rotating the axes in a 3-D cube or transforming a 3-D cube into a series of 2-D planes.

End Chpter4_Part 2