OLAP Reporting Requirements

In this section:

OLAP reporting requires some preparation both of the data to be reported against and of the report itself. In many instances, this preparation is entirely transparent, having been done before a user encounters an OLAP report. However, for developers who are charged with OLAP-enabling data and reports and for users who wish, and are authorized, to OLAP-enable their personal reports, the following summary will be useful.

OLAP-Enabling Data

Behind the scenes of any Db2 Web Query OLAP report is a hierarchical data structure. For example, a typical hierarchy of sales regions might contain a GEOGRAPHY category including the fields (in descending order) Region, State, and City. Region, the highest level in this hierarchy, would contain a list of all available regions within GEOGRAPHY. State, the second highest level in the hierarchy, would contain a list of all available states within those regions, and so on.

In Db2 Web Query, the hierarchical structure is generally built into the Master File for a data source, where it becomes active for any report that uses that data source. Developers or administrators who are responsible for describing data in a Master File can use the underlying language. The keyword WITHIN defines the elements in each dimension in the hierarchy.

In addition, those working in Developer Workbench have access to a variety of graphical tools that make it easy to drag and drop fields into position to form a hierarchy. The hierarchy may be global to all procedures or local to one procedure. To define a:

OLAP-Enabling a Report

Reference:

In addition to using OLAP-enabled data, a report must be enabled to support OLAP analysis. OLAP-enabling a report consists of specifying how a user will interact with and drill down on OLAP data.

The primary interactions occur in the report itself. In addition, you can choose to expose two supplementary tools, the OLAP Selections pane and the Control Panel.

Reference: Setting OLAP Reporting Options

OLAP Interface Options

Users can control the OLAP interfaces and following drill-down options.

  • OFF. Provides an OLAP button to open the OLAP Control Panel.
  • Columns only. Turns off the OLAP Control Panel and the OLAP Selections pane, but allows OLAP functionality from the report itself. You can access options on right-click menus, drag and drop columns within the report, and use up and down arrows to sort columns from high to low or vice versa. This is the default.
  • Columns with panel. Provides access to the OLAP Selections pane from a square button to the left of the column titles.
  • Show filters on top. Opens the OLAP Selections pane above the report. The Measures, Graph, and Dimension controls, as well as the band containing the OLAP, Run, and Reset buttons appear above the report output. You can open the Control Panel by clicking the OLAP button on the Selection pane.
  • Show filters on bottom. Opens the OLAP Selections pane below the report. The Measures, Graph, and Dimension controls, as well as the band containing the OLAP, Run, and Reset buttons appear below the report output. You can open the Control Panel by clicking the OLAP button on the Selection pane.
  • Show Panel in Report. Opens the OLAP report with the OLAP Selections pane hidden. You can perform a variety of analytic tasks from the report itself. Selection Criteria is shown next to the OLAP button.
  • Show Tabbed. For OLAP reports that have multiple dimensions, this option groups the dimension elements under a tab labeled with the dimension name.

Drill Down options

These options enable you to sort instantly from high to low or low to high for selected report columns:

  • None

    Disables automatic drill downs.

  • Dimensions

    Enables automatic drill downs on dimensions in both reports and graphs.

  • Dimensions and Measures

    Enables automatic drill downs on dimensions in both reports and graphs and, also, on measures in reports.

Note: Explicit drill downs in a StyleSheet (if they exist) take precedence over OLAP-enabled hyperlinks. If you click a hyperlink associated with an explicit drill-down, the behavior will be defined by the StyleSheet rather than by the AutoDrill On or All settings.

OLAP Terminology

The following table describes OLAP terms that may be useful as you work in the Db2 Web Query OLAP tools. Some of these terms are directly reflected in the interfaces of the OLAP Selections pane and the OLAP Control Panel. Others provide useful background information.

The first column of the following table provides the term and the second column provides the definition.

Term

Definition

Dimension

Group or list of related elements, usually structured in a hierarchy. For example, a Location dimension could include the elements Country, Region, State, and City arranged in a hierarchy where Country is the top level and City is the base level. Dimensional data usually describes the measured item.

Hierarchy

Logical parent-child structure of elements within a dimension.

Measure

Type of item that specifies the quantity of another element with which it is associated. A measure typically defines how much or how many. For example, Units, Revenue, and Gross Margin are measures in the Account dimension and specify how many units were sold, how much revenue was generated, and at what profit margin, respectively.

Pivot

Manipulating (or rotating) the view of a report by moving a field (or a group of fields) from a column to a row, or vice versa.

WebFOCUS