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.
Reference: |
Behind the scenes of any WebFOCUS 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 others.
In WebFOCUS, 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 WebFOCUS language. The keyword WITHIN defines the elements in each dimension in the hierarchy.
For those interested in the underlying code, see OLAP Hierarchy Syntax Summary and the Describing Data With WebFOCUS Language manual for details.
In addition, those working in Developer Studio 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:
For details about these related tools, see the Describing Data With Graphical Tools manual.
For those interested in the underlying code, the following syntax applies:
WITHIN='*dimensionname' WITHIN=field
where:
Is the name of the dimension and can include up to 66 characters. The dimension is defined in the field declaration for the field that is at the top of the hierarchy. The name must be preceded by an asterisk and enclosed within single quotation marks. The name must start with a letter and can consist of any combination of letters, digits, underscores, or periods. Avoid using special characters and embedded blanks.
Is used to define the hierarchical relationship among additional elements to be included in a given dimension. After the dimension name is defined at the top of the hierarchy, each element (field) uses the WITHIN attribute to link to the field directly above it in the hierarchy. The WITHIN attribute can refer to a field either by its field name or its alias. Note that a given field may participate in only one dimension, and two fields cannot reference the same higher level field.
For example,
FILENAME=OSALES, SUFFIX=FOC SEGNAME=SALES01, SEGTYPE=S1 FIELD=PRODCAT, ALIAS=PCAT, FORMAT=A11, WITHIN='*PRODUCT',$ FIELD=PRODNAME, ALIAS=PNAME, FORMAT=A16, WITHIN=PRODCAT,$
OLAP DIMENSIONShierarchy1: field1,field2,...fieldn;hierarchy2: field1,field2,...fieldn; . . .hierarchy3: field1,field2,...fieldn; END
For example,
OLAP DIMENSIONS Time Period: YEAR, QUARTER, MONTH; Products: PRODCAT, PROD_NUM, PRODNAME; END
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.
Developer Studio
In Developer Studio, OLAP options are available on the Options Features tab in Report Painter. The relevant options (Enable OLAP and Automatic Drill Down) are located in the OLAP section of the tab, as shown in the following image.
Tip: In the Report Painter, you can also make OLAP selections from the OLAP option on the Report menu. For more information about using Report Painter, see the Creating Reports With Report Painter manual.
OLAP Interface Options
The Enable OLAP options in Developer Studio control how users can interact with an OLAP report and access OLAP tools.
For Standard Reports delivered to Managed Reporting users, these decisions are made by Managed Reporting content developers. However, users who are creating their own reports can OLAP enable them and control the OLAP interfaces and following drill-down options.
Drill Down options
These options enable you to sort instantly from high to low or low to high for selected report columns:
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.
For those interested in the underlying code, the following syntax applies:
-OLAP ON
where:
Turns on the OLAP Control Panel.
ON TABLE SET AUTODRILL {ON|ALL|OFF}
where:
Enables automatic drill downs on dimensions.
Enables automatic drill downs on dimensions and measures.
Disables automatic drill downs.
ON TABLE SET OLAPPANE {TOP|BOTTOM|HIDDEN|NONE|CONTROL}
where:
Exposes the Selections pane above or below the report.
Hides the Selection pane. You can open it from the report.
Restricts analysis to the report. The Selections pane and Control Panel are not available.
Provides access to the OLAP Control Panel from the report.
With this setting, the Report Painter generates the -OLAP ON command in the procedure, which turns on the OLAP Control Panel.
ON TABLE SET OLAPDRAGDROP {ON|OFF}
where:
Enables Internet Explorer users to move report columns to different positions within the report. This is the default.
Disables drag and drop functionality within the report when using browsers other than Internet Explorer. OFF should be set for Netscape and Firefox users.
For more information about these options, see Setting OLAP Reporting Options.
The following code example turns OLAP on, calls a customer help file, sets drill-down capabilities for dimensions and measures, and sets the OLAP Selections pane to open the report:
-OLAP ON -OLAP HELP http://webserver/olaphelp/olaphelp.htm TABLE FILE CENTORD SUM COST PRICE BY PLANT ON TABLE SET AUTODRILL ALL ON TABLE SET OLAPPANE TOP ON TABLE SET ONLINE-FMT HTML END
The following table describes OLAP terms that may be useful as you work in the WebFOCUS 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 row to column. |
WebFOCUS |