86 Dimensional Modeling: In a Business Intelligence Environment

4.5 Multidimensional analysis techniquesMultidimensional analysis has become a popular way to extend the capabilities of query and reporting. That is, rather than submitting multiple queries, data is structured to enable fast and easy access to answers to the questions that users typically ask. For example, the data would be structured to include answers to the question, "How much of each of our products was sold on a particular day, by a particular salesperson, in a particular store?" Each separate part of that query is called a dimension. By precalculating answers to each subquery within the larger context, many answers can be readily available because the results have been precalculated for each query; they are simply accessed and displayed. For example, by having the results to the above query, one would automatically have the answer to any of the subqueries. That is, we would already know the answer to the subquery, "How much of a particular product was sold by a particular salesperson?" Having the data categorized by these different factors, or dimensions, makes it easier to understand, particularly by business-oriented users of the data. Dimensions can have individual entities, or a hierarchy of entities, such as region, store, and department.

Multidimensional analysis enables users to look at a large number of interdependent factors involved in a business problem and to view the data in complex relationships. Users are interested in exploring the data at different levels of detail, which is determined dynamically. The complex relationships can be analyzed through an iterative process that includes drilling down to lower levels of detail or rolling up to higher levels of summarization and aggregation.

Figure 4-7 on page 87 demonstrates that the user can start by viewing the total sales for the organization, then drill-down to view the sales by continent, region, country, and finally by customer. Or, the user could start at customer and roll-up through the different levels to finally reach total sales. Pivoting in the data can also be used. This is a data analysis operation where the user takes a different viewpoint than is typical on the results of the analysis, changing the way the dimensions are arranged in the result. Like query and reporting, multidimensional analysis continues until no more drilling down or rolling up is performed.

Chapter 4. Data analysis techniques 87

Figure 4-7 Drill-down and roll-up analysis

Multidimensional analysis enables you to look at the business problem by large number of interdependent factors describing the matter. In other words, multidimensional analysis enables you to view the information at different levels of detail or to analyze complex relationships.

The following are multidimensional techniques that we discuss in more detail:

� Slice and dice� Pivoting� Drill-down, drill-up, and drill-across� Roll-down and roll-up

4.5.1 Slice and diceWe start by discussing slice and dice analysis as individual activities.

SliceThe term slice in multidimensional terminology is used to define a member or a group of members that are separated (from ALL other dimensions) and then evaluated across all the dimensions. A member of a dimension means a value inside a column. Slicing is slightly difficult to understand on a two-dimensional paper. In order to understand the slicing concept, consider a dimensional model example. Assume that we have only three dimensions named product, store, and date in a simple dimensional model. In this simple dimensional model, we just have one fact table with a fact called sales.

88 Dimensional Modeling: In a Business Intelligence Environment

Assume that we isolate three members from the product dimension. The three members we isolated for the product dimension are soda, milk, and juice. This is shown in Figure 4-8. If we measure the SUM of sales quantity for ALL stores and for ALL dates across one or more members of one dimension (product in our case), then this concept is called slicing. The arrow in Figure 4-8 shows that the sum is across all dates and all stores.

This slice of the product dimension lets us to select our concerned members (soda, milk, and juice) from the product dimension. The slicing of the members allows us to focus only on these three members across all other dimensions. This concept is called slicing.

Figure 4-8 Slice for product

The slice in Figure 4-8 shows that soda generates the smallest sales amount, milk second, and juice third.

DiceThe dicing concept means that you put multiple members from a dimension on an axis and then put multiple members from a different dimension on another axis. This allows you to view the interrelationship of members from different dimensions.

Dicing is analysis of interrelationships among different dimensions or their members. Figure 4-9 on page 89 and Figure 4-10 on page 89 show examples of dicing.

In Figure 4-9 on page 89, we see multiple members listed vertically for the store dimension in one axis. These members are CA, OR, and LA. Similarly, we have multiple members for the date dimension which are listed horizontally. We are able to view the interrelationship of members from different dimensions. In other

Note: When you slice, you choose one or more members of a dimension and consolidate (or summarize) across all other dimensions (in our example, the other dimensions were store and date.)

(For ALL Stores and Dates)

Sales in USDProductSodaMilkJuiceTotal

2,5303,858

15,39621,784

Chapter 4. Data analysis techniques 89

words, we are able to see the relationship between CA and dates 1/1/2005, 1/2/2005, 1/3/2005, and vice versa.

Figure 4-9 Dice for store and date

Another example of dicing is shown in Figure 4-10.

Figure 4-10 Dice of store and product dimension

In this example, we can see the interrelationship between the members of the store and product dimensions. Here we analyze:

� How each store contributes to total sales amounts for each product (Soda, Milk, and Juice).

� How a particular product contributes to total sales for each store location.

Note: You dice when you choose one or more members of same dimension on one axis and on the other axis you choose a member or members from another dimension. Now you can analyze interrelationships of those dimensions.

LA

Milk

LA

90 Dimensional Modeling: In a Business Intelligence Environment

4.5.2 PivotingPivoting in multidimensional modeling means exchanging rows with columns and vice versa. Figure 4-11 on page 90 shows an example of pivoting. We exchange the store rows with columns of the product dimension members. It is simply a quick way to view the same data from a different perspective.

Figure 4-11 Pivoting

4.5.3 Drill-down and drill-upDrilling in multidimensional terminology means going from one hierarchy level to another. In other words, drill-down can be defined as the capability to browse through information, following a hierarchical structure.

In the example shown in Figure 4-12 on page 91, we show drilling down through a simple three level hierarchy present in the product dimension. The hierarchy is

Note: You pivot when you exchange the axes of the report.

Pivot

Milk

Milk

LA

LA

Chapter 4. Data analysis techniques 91

‘Group Class’ → ‘Group’ → ‘Product’. When we drill-down the Group Class attribute, we reach the Group. Finally by drilling down on the Group attribute, we reach the lowest detail present inside the product dimension (which is the individual product) as shown in Figure 4-12 on page 91.

Figure 4-12 Drill-down on product dimension

Another example of drill-down is shown in Figure 4-13 on page 92. Here we drill down from total sales in the US to sales at a particular store in Buffalo.

Note: We consider drilling up and drilling down when we want to analyze the subject at different levels of detail. Drilling is possible if a dimension contains a multiple level hierarchy.

Beverage ->Group

Beverage ->Group -> Pop

Milk

LA

LA

LA

92 Dimensional Modeling: In a Business Intelligence Environment

Figure 4-13 Drill-down example

Drill-up is exactly the opposite of drill-down.

4.5.4 Drill-acrossDrill-across is a method where you drill from one dimension to another. You must define the drill-across path. This function is often used in ROLAP. In Figure 4-14, you see the result of drill-across from store CA to the product dimension. The first chart depicts the sales in stores in three different states. And, in particular, we have focused on CA (California).

Figure 4-14 Drill-across result

CA Product

Milk

LA

Chapter 4. Data analysis techniques 93

By drilling across to the product dimension, we can see the details about which products comprised the sales for the store CA.

4.5.5 Roll-down and Roll-upRoll-down and roll-up are OLAP functions that give the higher or lower aggregate over whole dimension at a given hierarchy level.

In the example in Figure 4-15, we roll-down the product dimension from level 3, to level 2, and to level 1. This is done through the product hierarchy level: “Group class” → “Group” → “Product”.

The roll-down concept is the same as a drill-down.

Figure 4-15 Roll-down, Roll-up

Roll-up is exactly opposite to roll-down. The arrows in Figure 4-15 show the roll directions. The roll-up concept is the same as drill-up.

2

1

3

Rol

l-Up

Rol

l-Dow

n

Milk