OLAP Data Source

 < Day Day Up > 



When you create a new document, you can choose to access an OLAP data source. If this is the first time you are using WebI to access the OLAP data source, you need to define a connection, as shown in the next screen and described in the subsequent table.

click to expand

Prompt

Explanation

OLAP Data Source Name

The name that appears to you when you choose to create a new document from an OLAP server.

OLAP Server

The type of OLAP database or server you are accessing—for example, Essbase or Microsoft OLAP Services. The corresponding OLAP connectivity packs must be installed on the WebI server.

Server Name

The name of the MOLAP server.

Description

Optional description. You can leave this blank, as it does not appear elsewhere in WebI.

As discussed in Chapter 13, “MOLAP Cubes,” and Chapter 21, “OLAP Access,” BusinessObjects uses the OLAP Panel to build the query; once you build the query, you perform most of the sorting and formatting offline in BusinessObjects. The workflow for WebI is significantly different and better leverages the power of the MOLAP server. All your sorts, ranking, and filters are server-based. You drill within a formatted grid or chart. Also, WebI allows you to drill from a cube through to the details in a relational database, a functionality not yet available in the full client.

Figure 23-18 shows the Hyperion Essbase Sample database. The OLAP Report shows a grid of the first two dimensions in the outline (Year and Product). The grand total for the Year has been explicitly added to the grid. The Query Panel shows the current grid selections and filters. When you first launch an OLAP Report, by default, the numbers displayed in the grid are for the first child in the Measure dimension. In Figure 23-18, this selection has been changed to display the Sales measure. Figure 23-18 also shows the more robust formatting available via WebI’s OLAP as you can have both a table and a chart displayed simultaneously.

To drill down, you click the + next to a member. For example, to drill from Qtr1 down to Jan, Feb, Mar, click the + next to Qtr1. To drill up, click the – next to any member. The grid and the chart drills are synchronized. When you drill to month in the grid, the chart reflects the same monthly details. Beneath the chart, you also can filter the data via the Row drop-down box. For example, in Figure 23-18, Sales for all products for the year are displayed in the bar chart. To focus on Qtr4, select the Row drop-down and choose Qtr4. The bar chart will display Qtr4 data, while the grid will continue to display details for all four quarters.

click to expand
Figure 23-18: WebI OLAP Report

You add additional dimensions to the grid by dragging them from the Query Panel to the grid. As you drag dimensions, watch the status bar to ensure you are dragging them to the appropriate place in the grid. The status bar will display either Move To Row or Move To Column. You also can add a dimension to the table by selecting the children of a particular dimension, as explained in the later section “Filtering Data by Members and Values.”

Table 23-5 describes the purpose of each button on the OLAP toolbar. Several buttons allow you to select from an additional drop-down menu. If you click the button, the first option in the menu is the default and is selected.

Table 23-5: OLAP Panel Buttons

Button

Name

Description

 

Create Printable Version

Opens a second browser session to print the currently displayed grid or chart.

 

Clear Query

Resets all filters and displays only the first two dimensions in the grid.

 

View

Allows you to close the Query Panel in the left or to suppress the display of a report title.

 

Drill Through

Allows you to drill from the summary MOLAP cube to details in a relational database. The universe designer must enable drill-through from a MOLAP cube to a detailed universe and reports.

 

Swap Axes

Switches the columns and rows in a tabular report.

 

Value Filter

Allows you to filter measure values via different operators. You filter dimension members via selections in the Query Panel.

 

Rank

Provides a drop-down box to perform server-based rankings according to top or bottom performers.

 

Sort

Provides a drop-down menu to sort values.

 

Turn Empty Cell Suppression On or Off

Toggle to suppress empty cells.

 

Layout Menu

Allows you to display a chart, mixed chart, or grid, and to select additional chart styles.

 

Formatting Menu

Format measures, parent/child indents, aliases.

 

Report Information

Displays database and query selections in the left frame. To close the information frame and redisplay the Query Panel, click the X in the Report Information.

Figure 23-18 shows the numeric codes for each product. To display the longer alias names:

  1. Select the Formatting menu from the toolbar and choose Alias from the drop- down box.  

  2. This opens a frame on the left titled Format Alias Table. Use the drop-down box to select the alias table; this is typically Default, but your Essbase Administrator may create additional alias tables.

  3. Click OK to close the dialog box.

Now the product description, Colas, appears rather than the numeric ID 100.

Filtering Data by Members and Values

You can filter your data according to individual members, their relationships, or values for measures. You can filter data even when it does not appear in the grid or chart. In Figure 23-18, Sales (under the Measures dimension) and Actual (under the Scenario dimension) were used as filters; however, only the children of product and year are displayed in the report. To add a filter on the Market dimension to show data for the East region:

  1. In the Query Panel, double-click Market. WebI displays the Select Members dialog box. The first drop-down box in this dialog allows you to view the outline by members or levels. When you view it by levels, you also see a count for the number of members within a particular level.

  2. Highlight East.

  3. From the second drop-down box, you can specify the relationship. Leave the selection as Member and click Add.

This filter selection will now appear in the Query Panel.

Value-based filters allow you to filter individual columns by values. When you have multiple rows, the filter applies to the innermost row. For example, in Figure 23-18, you want to see only Colas, Product 100, with Sales greater than 26000. The quarters appear as the row dimension. To filter Colas according to Sales for a particular Quarter:

  1. Select the Colas or 100 column by clicking one of the values in this column. Do not select the column heading.

  2. Click the Value Filter drop-down menu from the toolbar and select Custom. You also could select an operator from the drop-down menu to filter the values according to the cell value you select in a grid. If you click the Value Filter icon without selecting the drop-down menu, then your table is filtered for greater than the selected cell or first cell in the column.

  3. WebI opens a Value Filter dialog box. Select > in the first drop-down and then enter 26000.

  4. Click OK.

The rows for Qtr1 and Qtr4 will be suppressed, as these are < 26000. A filter statement is added to the Year dimension in the Query Panel. The filter does not apply to the months but only to the quarters.

Note 

Even though the filter applies to the Colas-100 column, the quarters for the other products are also suppressed.

Rank

Rank allows you to select the top or bottom values according to a particular column. Like the Value Filter, Rank looks at the innermost row in the table. In the following example, Product is the innermost row, so the rank will select the top or bottom products. If Market were the innermost column, rank would select the top- or bottom-performing markets. The Rank default is Top 10. If you click Rank, it ranks the data according to the Top 10 of whatever column you selected. For more ranking options, click the drop-down arrow next to the Rank button.

click to expand

To rank products according to the two most profitable products for Qtr1 as shown in the preceding illustration:

  1. Select the column Qtr1. Be sure to select the values and not the column name.

  2. Click the Rank menu. Select Custom from the drop-down menu.  

  3. This opens the Rank dialog. In the first drop-down, select Top. In the second box, enter a value 2.

  4. Click OK to close the dialog box and rank the products. A rank icon appears in the Query Panel beneath the Product dimension.

Notice in the preceding report that the top two products in the East region are Cola and Birch Beer, whereas the top two products in the West region are different; in Qtr1, the profit for Grape soft drink was higher than Birch Beer.

Drill Through

Drill through is a powerful feature available only via WebI and not available in BusinessObjects. It allows you to explore the data in a summary MOLAP cube and then drill through to retrieve details from a relational database. The details are presented to you in a formatted WebI report. The universe designer must enable drill through for each cube (see Chapter 11). You specify the drill through intersection, which gets passed as a condition or WHERE clause into the WebI query. If you want to use all the member values for the intersection, select the numeric measure in the table and then click Drill Through. If you want to select only one member value, select the row or column heading and then click Drill Through.

Drill Through makes sense only when you are at the lowest level of detail in the cube; there are now + signs next to the dimension members to indicate you can drill down further. In the next example, the lowest members for the Time, Product, and Market dimensions are displayed and there is no + for drilling. If you select the intersection of July, 100-10, and New York by placing your mouse on the cell value 912, then all three of these member names are used as conditions in the query. If you click in the cell containing New York, then only this member value is passed through to the conditions.

To drill through to details:

  1. Select either the member name or the numeric measure for which you want to retrieve details.

  2. Click Drill Through from the toolbar.

  3. You are prompted to select a WebI report called a Drill Through Target. Each report may contain different result columns or block styles.

    click to expand

  4. Click OK.

  5. WebI displays the details for the drill through.

You may be able to use your browser Back button to return to the OLAP Panel, but I have never had this work successfully with Essbase.

Caution 

When you drill through to details, be careful in using either alias names or IDs. Ideally, the universe designer has enabled the drill-through to work with either values, but this may not be possible for all aliases. The member names—either aliases or codes—displayed in the OLAP Panel get passed through as the conditions.



 < Day Day Up > 



Business Objects(c) The Complete Reference
Cisco Field Manual: Catalyst Switch Configuration
ISBN: 72262656
EAN: 2147483647
Year: 2005
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net