Scope of Analysis

---

As shown in Figures 19-2 and 19-3, the initial drill screen does not necessarily contain all the dimensions or levels by which you can drill; more data may exist within the microcube. Ideally, these additional drill-by details are still at a reasonably aggregated level for the most frequently performed analysis. With Scope of Analysis, you can further expand the data in the microcube while drilling. In this respect, Scope of Analysis lets you expand the cube with still more detail rows or less frequently used drill-by dimensions than what was stored in the original cube. Scope of Analysis modifies the query and retrieves a new full set of data; the microcube is rebuilt. Therefore, you want to reserve Scope of Analysis for retrieving less frequently used details and dimensions.

To invoke the Scope of Analysis, right-click within the drill table and select Scope of Analysis from the pop-up menu.

Figure 19-5 shows the Scope of Analysis dialog box and how it relates to the data in the drill table as well as the microcube. Note that only dimension objects appear under Scope of Analysis; detail and measure objects do not appear. Any objects in the existing query definition and microcube have a check mark next to them. Objects that are currently displayed in the table are dimmed; Country and Color are in the drill table and so are dimmed under Scope of Analysis. Region is included in the query and the microcube but not in the current table; this appears in black.

Figure 19-5: Scope of Analysis modifies the query to include more data in the microcube for drilling.

When you modify the Scope of Analysis, you can choose whether or not the query should return all rows for all selections within the dimension or if the selection should get translated as a condition in the query. The current drill selections are marked with filter icons, similar to condition objects. The filter values come from either the Drill toolbar or the position of your mouse within the drill table. This is a wonderful new feature that is not available from the BusinessObjects Query Panel. For example, in Figure 19-5, the mouse selection is on Red wines. Color=Red appears as a filter in the Scope of Analysis. If additional selections existed on the Drill toolbar, these also would appear as filters in the Scope of Analysis. Varietal is the next level of detail within the dimension and has been added to the Scope of Analysis. If you want to limit the rows returned, and thus the size of the microcube, click the filter object to retrieve Varietals for Red wines only. Compare the difference in the SQL generated. The first SQL statement has no WHERE clause and with large databases can return a large number of rows.

Caution 

Be careful about including too much data in your Scope of Analysis; use filters for large dimensions. Drilling times within a cube will vary depending on your computer’s resources but will become slower with larger result sets. Selecting the lowest level within a product or customer dimension without additional conditions may return millions of rows of data.

SELECT   WINE_FACT_PRICE_RATE.Country,   WINE_FACT_PRICE_RATE.ColorClass,   AVG(WINE_FACT_PRICE_RATE.Rating),   avg(WINE_FACT_PRICE_RATE.SalesPrice),   WINE_FACT_PRICE_RATE.Region,   WINE_FACT_PRICE_RATE.Variety FROM   WINE_FACT_PRICE_RATE GROUP BY   WINE_FACT_PRICE_RATE.Country,   WINE_FACT_PRICE_RATE.ColorClass,   WINE_FACT_PRICE_RATE.Region,   WINE_FACT_PRICE_RATE.Variety

This second SQL statement includes the Filter Color=Red in the Scope of Analysis. For extremely large databases, and in particular, product and customer dimensions that may contain thousands and millions of rows of data, select the Filter objects.

SELECT   WINE_FACT_PRICE_RATE.Country,   WINE_FACT_PRICE_RATE.ColorClass,   AVG(WINE_FACT_PRICE_RATE.Rating),   avg(WINE_FACT_PRICE_RATE.SalesPrice),   WINE_FACT_PRICE_RATE.Region,   WINE_FACT_PRICE_RATE.Variety FROM   WINE_FACT_PRICE_RATE WHERE   WINE_FACT_PRICE_RATE.ColorClass  =  'Red' GROUP BY   WINE_FACT_PRICE_RATE.Country,   WINE_FACT_PRICE_RATE.ColorClass,   WINE_FACT_PRICE_RATE.Region,   WINE_FACT_PRICE_RATE.Variety

Note 

Changing the Scope of Analysis does not affect the columns displayed in the table. You must use Drill By to insert a new column in the table.

---