Chapter 19: Exploring the Data: Multidimensional Analysis

---

In Chapter 18, you added many meaningful breaks, sorts, and calculations. With multidimensional analysis, you look at the same data but from different viewpoints. The viewpoints may be from different dimensions or different levels of detail, often called granularity. Multidimensional analysis requires the BusinessObjects Explorer module, licensed as a separate product.

BusinessObjects Explorer first became available with version 4 of the product and, at the time, was a breakthrough in the market place. Prior to Explorer, companies could accomplish multidimensional analysis only through dedicated MOLAP or ROLAP servers. As these servers require a DBA or Administrator to build the cube in advance of the analysis, one had to predict the user requirements in advance. With version 4, Business Objects overhauled the product and changed the file formats from multiple files with results stored in a plain text file to a microcube. BusinessObjects builds this microcube as a file format, seamlessly and regardless of whether you plan to drill in the cube. With this breakthrough approach, users no longer had to define their requirements in advance; multiple users can create multiple personalized queries and drill within their own microcubes.

What Is Multidimensional Analysis?

As discussed in previous chapters, a dimension is a kind of object by which you analyze numeric measures. Dimensions often have different levels or groupings associated with them called hierarchies. Multidimensional analysis is the process of analyzing data by different dimensions and levels within the dimensions. Within BusinessObjects, one can perform multidimensional analysis only with Dimension objects, not with objects the designer has created as Detail objects (see Chapter 8).

Hierarchies allow you to analyze data by different levels of detail. Some hierarchies are very clear-cut, such as Time, going from Year to Quarter to Month to Week to Day. There is a natural order. Geography hierarchies may also be predetermined, running from Continent to Country to State. When the geography applies to a marketing region, however, each company introduces its own variation. One company may group the Middle East and Africa together; another company may include Mexico as part of North America because it is part of NAFTA or will group it under a different management region such as Latin America. Many of these groupings are fixed as part of your company’s reference data and built into the ERP or data warehouse. The universe designer uses the hierarchies to build the default drill paths you use for multidimensional analysis. You can also override the standard drill paths and create your own (see the later section “Hierarchy Editor”).

With BusinessObjects Explorer, you can drill down within a hierarchy, for example from year to quarter to month. You also can drill across by analyzing the current year or the past year; you are at the same level of detail, year, but you are changing the selection value that you are analyzing. You can analyze data by one dimension at a time (Time) or by several at once (Time, Geography, Product). You can also drill against multiple report formats including tables, crosstabs, and charts.

Fixed reports often deal with standard, recurring information needs and give you an overview of what is going on in your business. Multidimensional analysis is more exploratory and answers why, where, and when. For example, you may start with a standard management report that shows product sales for this year and last year. Sales for one product is lower this year than last year. You need to know why. So you begin to explore: were the sales bad for a particular region, salesperson, or quarter?

Understanding the Data

You can easily get lost when drilling within a report. Therefore, it’s helpful to first understand how the different universe objects relate to one another and where you are drilling from and to. Figure 19-1 shows two sample hierarchies for the Wine universe. Compare that with earlier Figure 11-1, which shows two hierarchies for a product dimension and time dimension used in the EFASHION universe.

Figure 19-1: Understand the hierarchical nature of your dimensions before drilling within a report.

It’s also important to understand that most drilling occurs within a local microcube. You are not requerying the database. Figure 19-2 shows how this works. For simplicity, I will use an example of a Time dimension that contains Year, Quarter, Month. When you construct a query, you first specify which columns of data you want to retrieve from the database. Initially, the default report displays all columns of data. However, as you format the report and/or slice and dice the data, you may hide certain columns. These columns still exist in the query and therefore in the microcube, but they are not displayed in the initial report. In Figure 19-2, Quarter and Month exist in the microcube but are not displayed in the report. This allows you to drill from Year to Quarter to Month without having to execute a new query.

Figure 19-2: The document or microcube may contain more data than what is initially displayed in the report, allowing you to explore the data without generating a new query.

How Is the Data Aggregated?

BusinessObjects aggregates measures in two ways: SQL functions and projection aggregates. SQL functions affect the number of rows returned in the initial query. When you drill within a document, BusinessObjects uses projection aggregates. Projection aggregates are set by the universe designer (see Chapter 8, “About Projection Aggregates,” for more information). Figure 19-3 shows a summary table of wine ratings and prices by Color and Country. The averages for Australian Red wines are 91.86 and $51, respectively. When drilling, BusinessObjects calculates these two numbers from all the details in the document. The document or microcube contains additional details by Dryness, Type, Vintage, Varietal, Producer, Label Name. The bottom table in Figure 19-3 displays seven rows of detail numbers for Australian wines that were used to calculate the one row in the summary table. For these particular objects, the universe designer specified a projection aggregate of Average, as it would be incorrect from a business viewpoint to sum prices and ratings. Often, projection aggregates use the SUM function—for example, sales, expenses, and available inventory are summed across various dimensions.

Figure 19-3: Projection aggregates recalculate summary and detail numbers with each drill. Projection aggregates are set by the universe designer for each measure.

---