In this section: |
|
WebFOCUS Online Analytical Processing (OLAP) enables you to view and quickly analyze data in order to make critical business decisions.
In this section: |
Suppose that you own a small business in New York and are exploring a partnership with a company in Oakland, California. You need to get to a Monday morning meeting. How do you go about arranging your flight?
Most likely, you go online.
First, you check available flights on the airline that holds your frequent flyer miles. You discover that your frequent flyer carrier requires a change of planes and you would prefer a direct flight, so you look at routes and fares for other airlines.
In New York, you can get to LaGuardia, JFK, and Long Island MacArthur Airport. In California, you can fly into Oakland or San Francisco.
While you would prefer to fly out on Sunday and return Tuesday morning, you could consider a Saturday flight to California and a return flight on the red-eye Monday night, if fares and schedules are better.
You begin your search by airline and then look at options for each departure point and destination by day, time, and price.
Another approach is to start with an online consolidator, enter the times you can fly, and see what flights and fares are available.
There are a lot of variables to play with, but in a half hour, you have done your research and can make a good decision based on all available factors.
The websites you access are designed to facilitate your queries. Various menus and selection panes make it easy to pursue each line of inquiry. Required and optional information is identified for you. You can move forward down a path of choices, backtrack and start down a different path, or resume the original path with different selections.
You need to keep track of the question you want to answer, but a well-designed site makes your investigation easy. For most of us, this process has become intuitive.
The same process works when analyzing the data in an OLAP-enabled WebFOCUS report.
You can run all of the examples in this chapter using OLAP-enabled published content. If the reports are not already available in your sample repository, ask your WebFOCUS administrator to provide them for your use. There are nine reports, named olaprep1.fex through olaprep9.fex, located on the Reporting Server in the \ibinccen demo directory.
Each example indicates which published content to run. After the report appears in your browser, you can perform the analytic task shown, or pursue your own line of inquiry.
Suppose that you are an analyst for Century Corporation, which manufactures electronics equipment. You need to determine which of the stores that sells your products had the highest sales in 2002, and whether there is a pattern in sales periods and/or best selling products that should be considered when planning manufacturing schedules and parts inventories.
You have created a base report that shows sales data only for 2002. You have also OLAP enabled the report to permit quick analysis of the data.
Before you begin your analysis, the OLAP report looks like the following image.
The quarterly information is spread out over the left-most column. You can try a horizontal display to make comparison easier.
The report changes immediately and appears, as shown in the following image, across the top of the report with the Quantity and Line Cost of Goods Sold columns repeating for each quarter.
The store information is more compact, but it is not easier to identify the store with the best sales record, so drag QUARTER back to its original position.
Note: The options available may vary, depending on your OLAP format settings. For more information, see Setting OLAP Reporting Options.
The display changes, as shown in the following image.
The bar graphs still do not reveal a trend.
As shown in the following image, the report shows that Audio Expert has the highest sales in the digital product lines in Quarters 1 and 2, with eMart trailing slightly. Each value under the QUARTER, Store Name, and Product Type column is hyperlinked for more details.
In the monthly report, both stores recorded their highest sales in June (06), as shown in the following image.
You now see information for digital and analog sales at Audio Expert. Since the significant sales for Audio Expert are in the digital area, let us see which digital products contributed to the June figures.
The breakdown shows clearly that PDAs drove Audio Expert digital sales.
As shown in the following image, ZT Digital PDA - Commercial was by far the top selling PDA in June for Audio Expert.
Let us now see what drove digital sales at eMart, the second highest producer.
This time, click eMart next to MONTH 06, to see the June sales information for eMart.
Once again, the Digital category leads sales, as shown in the following image.
PDA is the strong seller for eMart too, as shown in the following image
The report shows sales figures for the two PDA models, as shown in the following image.
ZT Digital PDA - Commercial far outsells ZC Digital PDA - Standard.
This information from the two top selling stores suggests that Century Corporation should evaluate and adjust available parts inventories for each model and consider shifting production schedules of plants to produce more Commercial units.
You have done all of your data manipulation from the report. But, because of the options you selected when OLAP-enabling this report, it is easy to expose the OLAP Selections panel where you can review the selections that are currently in effect, and make additional selections if you like. For details on OLAP set-up options, see OLAP-Enabling a Report.
Note: The options available may vary, depending on your OLAP format settings. For more information, see Setting OLAP Reporting Options.
The Selections panel appears above the report, as shown in the following image.
Notice that Store Name is eMart, Product Type is Digital, and Product Category is PDA Devices.
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.
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 fields into position to form a hierarchy. The hierarchy may be global to all procedures or local to one procedure.
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
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 panel and the OLAP 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 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.
Enable OLAP
The Enable OLAP options in Developer Studio control how users can interact with an OLAP report and access OLAP tools.
For published content 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.
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.
This option opens the Dimension Builder using the Master File you selected for the report.
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. ON is the default value.
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 panel above or below the report.
Hides the Selections panel. You can open it from the report.
Restricts analysis to the report. The Selections panel and Control Panel are not available. NONE is the default value.
Provides access to the OLAP Control Panel from the report.
With this setting, 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. ON is the default value.
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 panel 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 panel 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. |
An OLAP-enabled report has a number of features that distinguish it from other WebFOCUS reports.
A basic OLAP report is shown in the following image.
Every OLAP user can take advantage of the analytic features that are built into the OLAP report:
Depending on your OLAP settings, the hyperlinks may be active for both the dimension fields (by which the report is sorted) and the measures fields (which display quantitative data), or only for the dimension fields. For related information, see OLAP-Enabling a Report.
Beyond the features in the report itself, your OLAP options depend on the interface and drill-down settings that are in effect for a particular report. Those choices determine whether you have access to the following tools:
In this section: |
There are three ways to work with OLAP data, from the report itself, from the Selections panel, and from the OLAP Control Panel. This documentation is organized to help you understand what you can do from each location and which method is most suitable and efficient for your particular OLAP settings.
You can perform a wide range of basic analytic functions from the report itself. Changes you make in the report are implemented instantly. Every OLAP user can perform these tasks:
For an illustration of report-powered OLAP analysis, see We Do It Every Day: A Typical Web Query.
When the OLAP Selections panel is turned on, you can quickly limit the data in the report by selecting specific values for the dimensions in the hierarchy. A drop-down list is available for each dimension. You can multiselect values from one or more dimension lists to refine your report output.
If you wish to add a dimension element to the report, you can drag it from the Selections panel into the report frame. (The cursor changes to a plus sign (+) to indicate an acceptable location.)
Each dimension has a relational operator button located to its left. This button toggles through a selection of basic numeric operators that enable you to quickly define your selection criteria. The operators are:
For details, see Selection Criteria Relational Operators.
The following image shows the Equal to operator as the selection for each dimension in the Selections panel.
The name of the dimension field appears as defined in the Master File, even if an alternate column title has been specified.
In addition, you can customize the display of the measures in your report from the Selections panel. You can click either the Measures or the Graph arrow in the upper-left corner of the pane to list the measures.
Note that the Selections panel is resizable. The controls for dimensions, measures, and graphs float as you resize the report window, so that they continue to be visible in the frame.
Five buttons appear below the Selections panel: OLAP, Run, Reset, Save, and Help.
From the OLAP Control Panel, you can perform every analytic function available to a WebFOCUS OLAP user, as shown in the following image.
The main window of the OLAP Control Panel contains the following components:
Although the most frequently used functions are available directly from an OLAP report and/or from the Selections panel, several can only be performed from the OLAP Control Panel.
OLAP Control Panel operations include:
You can drill down on dimensions in OLAP reports and graphs and on measures in reports. The settings activate the required hyperlinks:
In Developer Studio, you can set drill-down options from the Report Options Features tab. For details about this setting, see Setting OLAP Reporting Options.
This report you are about to run uses data from a hierarchy that contains three dimensions, each of which has three elements. The report is sorted by the specified field from each dimension. The following table outlines three dimensions, Time Period, Location, and Product, each containing three elements.
Time Period |
Location |
Product |
---|---|---|
Year |
Manufacturing Plant |
PRODTYPE |
Quarter |
State |
PRODCAT |
Month |
Store Name |
PRODNAME |
The report will show data at different levels in each dimension. Quarter is down one level in its dimension, Store Name is at the lowest level in its dimension, Product Type is the top level in its dimension. This determines how much farther you can drill down within each dimension. If you drill down on a value of Quarter, the report shows information broken down by Month within that Quarter. The Quarter column itself will no longer appear.
In this quarterly report, drill-down hyperlinks are active for both dimensions and measures.
Since Month is the bottom level in its dimension, if you drill down on a month value, you will no longer see the month column. However, you will see the data that relates to the selected month in subsequent columns.
As shown in the following image, the January report displays Product Type, Quantity, and Line Cost Of Goods Sold for each store.
Next, see what happens when you drill down in the Location dimension (in this case, on a value of Store Name in the second column of the report). When you drill down on a dimension column other than the first, the output is affected to the right and left of that column.
Since Store Name is the lowest level in its dimension, the Store Name column no longer appears, nor does the Time Period column to its left. Nevertheless, both the Store Name (AV VideoTown) and the current time period (January) set the context for the information you see, which now consists of types of Product Type, Quantity, and Line Cost of Goods Sold for AV VideoTown in January, as shown in the following image.
By drilling down on a measure, you expose the next level of detailed information associated with that measure for each displayed dimension in the hierarchy. In other words, when you drill down on a measure, the current dimension is used as a limiting criterion. The rest of the hierarchy is then expanded based on that limitation.
Remember that a measure contains quantitative information about fields in each dimension.
In this example, Quantity and Line Cost of Goods Sold provide data about products at particular stores during particular time periods.
Notice that quantity of sales for all digital products at AV VideoTown in the first quarter of the year is 22,206. You want to find out how much each digital product contributed to the total quantity.
As shown in the following image, the report now displays total quantity for digital products sold at AV VideoTown broken out by MONTH, Product Category, and Product Name. Notice that Store Name no longer appears. Since it is the lowest level of the Location dimension, there is no lower level of detail.
Since all relevant information is now visible, no further drill downs are possible and the measure is no longer represented as a hyperlink.
Next, verify this behavior at another level in the hierarchy.
Note: When you drill down on a measure value, results may differ depending on the combination of sort fields in the report. The examples that follow show several variations.
Product Type: Digital serves as the limiting criterion. Therefore, the expanded hierarchy shows the next level of detail for each digital product, as shown in the following image.
This level is composed of digital product categories and the names of the products in each category. The report displays the detailed data for each element in the Product Type dimension (in this case, the product categories and product names that compose the quantity figure of 1,426). The total Quantity and the Line Cost Of Goods Sold are now broken down by product.
When you drill down on a measure in a report with at least one dimension Across field and no By fields, all Across fields are removed from the report and all of the dimension elements under the removed Across fields become By fields from left to right in the resulting report. This convention ensures that the maximum number of Across values supported by WebFOCUS is not exceeded.
The values that appear for the new By fields are controlled by internally generated selection criteria. The measure values in the resulting report depend on the values of the new By fields.
In the report, RISK_CLASS and Continent are dimension Across fields on which you can drill down.
The report now looks like the following image.
In the new report, the RISK_CLASS and Continent fields are removed based on two internally generated criteria: IF RISK_CLASS EQ 'Low' and IF Continent EQ 'AMERICAS'.
The only dimension element under RISK_CLASS is Risk_Factor. The dimension elements under Continent are Region and Country. These become By fields in the new report, from left to right. The data displayed for the measures in the resulting report are those that satisfy the values in the current By fields.
When you drill down on a measure in a report with at least one By and one Across dimension field under the same root dimension, both the By and Across fields are hidden and the subordinate elements in the same dimension become By fields in the new report. In effect, the report is filtered based on the values of the dimensions. As a result, the sorting controlled by both hidden and visible dimensions remains in effect.
In the report, Continent is a By field and Region is an Across field. Both are in the Geographic Area dimension.
The report now looks like the following image.
In the new report, data is filtered based on the internally generated criteria: IF Continent EQ 'AMERICAS' and IF REGION EQ 'CENTRAL AMERICA.' (Continent and Region are no longer visible.)
REGION is replaced by the last element in the Geographic Area dimension, Country, which becomes the controlling By field in the report. The data displayed for the measures are those that satisfy the values in the current By field.
When you drill down on a measure in a report with at least one By and one Across dimension field from different root dimensions, the By fields are broken down to their last dimension level, then the Across fields are broken down.
The original By and Across fields are removed. The dimension elements under the removed By fields become the first set of By fields from left to right. The dimension elements under the removed Across fields follow the first set of By fields from left to right.
In the report, Continent is a By field from the Geographic Area dimension and RISK_CLASS is an Across field from the Risk dimension.
The report looks like the following image.
In the new report, the Continent and RISK_CLASS fields are removed based on the internally generated criteria: IF CONTINENT EQ 'EUROPE' and IF RISK_CLASS EQ 'High'.
The By field (Continent) is broken down to its last dimension element. Then, the Across field (RISK_CLASS) is broken down to its last dimension level. The resulting By fields in the report, from left to right, are Region, Country, and Risk Factor. The data displayed for the measures satisfy the values in the current By fields.
In this section: |
You can sort the data in an OLAP report based on the values of dimensions in the hierarchy and/or the values of the quantitative measures that constitute the body of the report. Sorting options vary depending on the nature of the data being sorted. For details, see Sorting Measures and Sorting Dimensions.
You can also group numeric data into any number of tiles (percentiles, quartiles, deciles, and so on). See Grouping Numeric Data Into Tiles.
You can apply aggregation and sorting simultaneously to a numeric measure in an OLAP report, and sort the data from high to low (descending order) or from low to high (ascending order). All other columns are sorted correspondingly.
For the measure being sorted, you can restrict the report to a specified number of highest values (when sorting high to low) or lowest values (when sorting from low to high).
When you sort a measure, any subtotals, subheadings, or subfootings in the report are automatically suppressed since these elements relate to a specific sort field and are not meaningful when the report is resorted by the values in a measure column. For an illustration, see Applying a Percent Calculation to a Measure.
Note: Sorting by measures is not available in a report in which measures have been stacked. See Hiding and Displaying Measures.
To sort the values of a measure from high to low:
or
The report runs automatically. The highest value is now first in the column. The top of the diamond button becomes solid blue to indicate the current sort direction.
To sort the values of a measure from low to high:
or
The lowest value is first in the column. The bottom of the diamond button becomes solid blue.
Tip: After a measure has been sorted once, clicking the upper or lower half of the diamond button inverts the sort order of that measure. Place your mouse pointer over either half of the diamond to see a message that indicates the next sort order that will occur if you click the diamond.
The OLAP report shows sales information sorted by quarter, store, and Product Type.
You are interested in seeing where the greatest quantity of goods has been sold.
As shown in the following image, the report now displays data values for the Quantity measure in descending order. The top half of the diamond next to Quantity is blue and solid to indicate the current sort order of the measure. This is now the controlling sort in the report. All other values are reordered correspondingly.
Tip: To invert the sort order, click the diamond button again.
Do not click the Stack Measures check box, which controls the display of a measure, not its sorting.
The sort pane is replaced by the Measures pane, where the measure becomes blue to indicate that sorting specifications have been defined.
The diamond button next to the sorted measure changes to reflect the sort order. If the sort order is high to low, the top half of the diamond is solid blue. If the sort order is low to high, the bottom half is solid blue.
Note:
You can select to view only a subset of the total number of records in your report.
Do not click the Stack Measures check box, which controls the display of a measure, not its sorting.
or
The default number of sort fields values is 5.
The sort pane is replaced by the Measures pane, where the measure becomes blue to indicate that sorting specifications have been defined.
The report shows sales information sorted by quarter, store, and Product Type.
The sort pane opens, as shown in the following image.
High to Low sorting is selected by default.
Because the report is being sorted from high to low, you can indicate the number of values you wish to see, beginning with the highest.
The main OLAP Control Panel window appears. In the Measures pane the Quantity measure is blue, indicating that sorting specifications have been defined.
As shown in the following image, the report now displays Quantity sorted from high to low with the highest four values appearing.
You can remove sorting specifications for a measure whether the measure appears or is hidden.
There are several ways in which you can sort dimensions in an OLAP hierarchy:
The sort pane opens.
The main OLAP Control Panel window reopens.
In the report, the values of both sort fields (Continent and Region) are sorted from low to high (A to Z), as shown in the following image.
The sort pane opens.
The main OLAP Control Panel window reopens.
The main OLAP Control Panel window opens.
Both dimensions are now sorted in reverse alphabetical order (Z to A), as shown in the following image.
The sorting pane opens.
The main OLAP Control Panel window reopens.
The sort pane opens.
The main OLAP Control Panel window reopens.
Information for all stores is shown for each quarter. You want to see quarterly information for only the first two stores in alphabetical order (low to high).
The sort pane opens.
The following image shows these three selections on the OLAP Control Panel.
Notice that only two values now appear for each Quarter and they are ranked low to high within each group, as shown in the following image.
You can change the order in which data is sorted and presented in the report. For example, you can change from sorting by State and then by Product to sorting by Product and then by State. If you want to reposition:
In each case, the cursor changes to a plus sign (+) to indicate acceptable places into which you can drop the field. Unacceptable positions are shown by a circle with a slash across the center.
The dimension values adjust accordingly. The report now shows the Quantity values from high to low, but according to the QUARTER sort order, as shown in the following image.
You would like to change the sort order in the report, making Store Name the first sort field, followed by Product Type and QUARTER.
The cursor changes to a plus sign (+) to indicate acceptable places into which you can drop the field.
The report changes immediately, as shown in the following image, with the Store Name being the first sort order.
Repeat for other fields as needed.
The dimension values adjust accordingly. The report now shows the Quantity values from high to low, but according to the QUARTER sort order, as shown in the following image.
You would like to change the sort order in the report, making Store Name the first sort field, followed by Product Type and QUARTER.
QUARTER is now the third item in the Drill Down list, as shown in the following image.
QUARTER appears in the third column of the report, as shown in the following image.
In OLAP, you can hide a sort field by clicking the Hide check box in a report.
Note: Hidden sort fields are indicated by reversing the color of the icon that appears at the left of the field name.
-OLAP ON TABLE FILE CAROLAP SUM CAROLAP.BODY.DEALER_COST CAROLAP.BODY.RETAIL_COST BY CAROLAP.ORIGIN.COUNTRY BY CAR END
Notice that the color of the sort icon has been reversed. The Drill Down pane now appears, as shown in the following image.
You can quickly change a field from one that sorts data vertically, creating rows, to one that sorts data horizontally, creating columns, or vice versa.
To change a:
In each case, the cursor changes to a plus sign (+) to indicate acceptable places where you can drop the field. Unacceptable places have a circle with a slash across the center.
The report is now sorted vertically, by month, store, and Product Type, as shown in the following image.
You want to create a matrix in which data is sorted horizontally by month, and vertically by store and Product Type.
The cursor changes to a plus sign (+) to indicate acceptable places where you can drop the field.
In the new report, Quantity and Line Cost of Goods Sold are repeated horizontally for each month, as shown in the following image.
You can change a field from one that sorts data vertically, creating rows, to one that sorts data horizontally, creating columns, or vice versa.
The report is now sorted vertically, by month, store, and Product Type, as shown in the following image.
You want to create a matrix in which data is sorted horizontally by month, and vertically by store and Product Type.
MONTH moves into the Drill Across pane, as shown in the following image.
In the new report, Quantity and Line Cost of Goods Sold are repeated horizontally for each month, as shown in the following image.
The sort pane opens.
The main OLAP Control Panel window reopens.
Tip: To expose the hidden sort field, repeat the process and deselect the Hide check box.
The first sort field in the report is QUARTER. You want to retain the sorting but not display this field.
The sort pane opens.
The main OLAP Control Panel window reopens.
Report sorting is unchanged, but the QUARTER column no longer appears, as shown in the following image.
You can group numeric data into any number of tiles (percentiles, deciles, quartiles, and so on) in tabular reports. For example, you can group student test scores into deciles to determine which students are in the top ten percent of the class.
Grouping is based on the values in the selected vertical (BY) field and data is apportioned into the number of tile groups you specify.
The following occurs when you group data into tiles:
1
5
5
5
8
9
In this case, dividing the instances into groups containing an equal number of records produces the following table:
Group |
Data Values |
---|---|
1 |
1,5 |
2 |
5,5 |
3 |
8,9 |
However, because all of the same data values must be in the same tile, the fives (5) that are in group 2 are moved to group 1. Group 2 remains empty. The final tiles look like the following table:
Tile Number |
Data Values |
---|---|
1 |
1,5,5,5 |
2 | |
3 |
8,9 |
How to: Reference: |
You can perform standard calculations, such as average, percent, and summarize, on the numeric data in measures on an OLAP report.
Note: Do not click the Stack Measures check box, which controls the display of a measure, not its sorting.
The sort options pane opens.
None is the default value. For details, see Calculations You Can Perform on a Measure.
The sort pane is replaced by the Measures pane, where the selected calculation appears as a prefix to the measure.
The following table lists the types of calculations in the first column and describes their functions in the second column.
Calculation |
Function |
---|---|
Average Sum of Squares |
Computes the average sum of squares for standard deviation in statistical analysis. |
Average |
Computes the average value of the field. |
Count |
Counts the number of occurrences of the field. |
Count Distinct |
Counts the number of distinct values within a field when using -REMOTE. For other modes of operation, this behaves like Count. |
Maximum |
Generates the maximum value of the field. |
Minimum |
Generates the minimum value of the field. |
Percent |
Computes the percent of a field based on the total values for the field. The Percent can be used with detail, as well as summary fields. |
Percent of a Count |
Computes the percent of a field based on the number of instances found. |
Row Percent |
Computes the percent of a field based on the total values for the field across a row. |
Summarize |
Sums the number of occurrences of the field. |
Total |
Counts the occurrences of the field for use in a heading (includes footings, subheads, and subfoots). |
The following is an example of applying a percent calculation to a measure.
The report shows Quantity and Line Cost of Goods Sold sorted by plant and product category, with a subtotal at each sort break.
You want to create a report column that shows the percent of total sales for each plant.
The sort pane opens.
The report now breaks down sales for each product at each plant as a percentage of total sales, as shown in the following image.
Notice that the subtotals have been removed from the report because the breakdown by plant is no longer suitable for the data.
An OLAP report is limited to values belonging to the parent categories in the dimensions hierarchy. There are several ways to further limit the data that appears in the report.
The Selections panel provides the easiest approach since you can choose both dimension values and relational operators with a few mouse clicks, while the report is fully exposed to view.
Changes made in the Selections panel are implemented immediately in the OLAP Control Panel (even if the Control Panel is closed), and changes made in the OLAP Control Panel are reflected immediately in the Selections panel.
You can define selection criteria in the Selections panel or in the OLAP Control Panel using several relational operators, which are shown in the following tables. The first column displays the operator and the second column provides a description of the operator.
Operator |
Icon |
Displays Records That... |
---|---|---|
Is Equal To |
|
Are equal to the criteria you specified. This is the default operator. |
Is Not Equal To |
|
Are not equal to the criteria you specified. |
Is Greater Than |
|
Are greater than, but not equal to, the criteria you specified. |
Is Greater Than or Equal To |
|
Are greater than or equal to the criteria you specified. |
Is Less Than |
|
Are less than, but not equal to, the criteria you specified. |
Is Less Than or Equal To |
|
Are less than or equal to the criteria you specified. |
Contains |
|
Contain the criteria you specified. Note: This operator is available only for alphanumeric fields. |
Does Not Contain |
|
Do not contain the criteria you specified. Note: This operator is available only for alphanumeric fields. |
Note: You can select more than one value using the same relational operator.
The following table lists and describes relational operators for selecting a range of dates.
Operator |
Icon |
Displays Records Where... |
---|---|---|
From (within range) |
|
The value in the indicated date field falls within the specified range. Note: To use this relational operator, you must select the Range check box in the Date Selection panel. |
Not From (not within range) |
|
The value in the indicated date field does not fall within the specified range. Note: To use this relational operator, you must select the Range check box in the Date Selection panel. |
When the Selections panel is turned on, there is one control (drop-down list) for every dimension in the OLAP hierarchy. Note that the name of the dimension field appears as defined in the Master File, even if an alternate column title has been specified.
To limit data for the dimensions that are included in the report:
To select multiple values, click the desired values while holding the Ctrl key on the keyboard.
You can toggle through a list of operators. See Selection Criteria Relational Operators.
Tip: To change or eliminate selection criteria, reopen the values list and choose another value or choose All.
The Selections panel above the report shows that the controls for Continent and Region are set to All to show all values of each dimension.
You wish to focus on the data for one continent and one region.
The output is now limited to data for the selected continent and region, as shown in the following image.
The Selection Criteria pane opens.
A secondary window opens. Select one or more values (press the Ctrl key to multiselect).
Tip: If you have access to the Selections panel, it provides the quickest way to limit data. For an illustration, see Limiting Continents and Regions From the Selections Panel.
The report shows data for continents and countries. You want to restrict the information to the Countries ARGENTINA and BRAZIL in the Continent AMERICAS.
A secondary window lists the acceptable values.
The selected values now appear in the drop-down lists in the Selection Criteria pane, as shown in the following image.
The new report displays the data by Continent, AMERICAS followed by Country, as shown in the following image.
Tip: If you have access to the Selections panel, it provides the easiest way to adjust or remove selection criteria. See How to Apply Selection Criteria From the Selections Panel.
From the OLAP Control Panel:
The Selection Criteria pane opens.
A secondary pane opens.
To change a value: Type the new value in the text pane or select one or more values from the list. (The value you type must be in the same case as the value in the data source.)
You can input only one value in the text pane. If you select more than one value from the list, only the first value appears. However, all values appear in your report.
Tip: If you have access to the Selections panel, it provides the easiest way to adjust or remove selection criteria. See How to Apply Selection Criteria From the Selections Panel.
From the OLAP Control Panel:
The Selection Criteria pane opens.
The selection category is removed from the list.
You can apply selection criteria to date elements just as you apply them to other types of elements. The results are limited by the dates you select. For example, you can select to view data associated with a particular date or to exclude data from the specified date.
Note: Like other dimension elements, date fields must have been defined in the Master File by a Managed Reporting developer. The Master File specifies the date formats available for selection criteria.
In the OLAP Control Panel, you can choose the selection criteria from a Date selection pane that contains the appropriate controls for the date format.
You can also select a range of dates in a designated year by specifying a From and To date. Two relational operators are available for selecting a range of dates:
For more information on supported date formats, see Date Format Limitations. For more information on specifying date formats, see the Describing Data With WebFOCUS Language manual.
Note: The Date selection pane appears only when a supported date format is provided. See Date Format Limitations.
From the OLAP Control Panel:
The Selection Criteria pane opens.
A secondary window displays controls for the date format of the dimension. For example, if the date format is YYM, only the year and month controls appear. If the format is YYMD, year, month, and day controls appear.
If your date format includes edit masking, such as Y.M.D, the date appears with forward slashes (/) in the Date selection list pane, the Selection Criteria pane, and the drop-down list at the bottom of the report. However, the date edit mask appears as specified within the body of the report.
As shown in the following images, the multi-page OLAP report includes several years of data about reported problems falling into five categories: incorrect labeling, missing components, physical damage, power failure, and remote failure.
You want to investigate problems reported on June 6, 2001. You can limit data based on a single date from the OLAP Control Panel.
Note: To show the selection of a particular date, a dimension component has been added to the procedure. This dimension places Date Problem Reported in the Time Period dimension hierarchy directly below the root.
Note: The OLAP button appears at the bottom of this report because the OLAP CONTROL setting was selected. For details, see Setting OLAP Reporting Options.
The Selection Criteria pane opens.
A new pane appears for Date Problem Reported, replacing the Selection Criteria pane. The pane includes a drop-down list for each selectable value (Year, Month, and Date), as shown in the following image.
The relational operator to the left of the Date pane indicates that your report will contain data only for those rows where date is equal to (=) the values you entered. This default operator is correct for this example.
Your selection criteria are listed beside the OLAP button at the bottom of the report, as shown in the following image.
The Selection Criteria pane opens.
A secondary window displays controls for the date format of a dimension. For example, if the date format is YYM, only the year and month controls appear. If the format is YYMD, year, month, and day controls appear.
Note: The Date selection pane appears only when a supported date format is provided. See Date Format Limitations.
Inclusive and Exclusive option buttons appear:
Note:
From and To drop-down lists open for all selectable options. By default, the current date appears.
As shown in the following images, the report shows problem information reported over the course of several years.
The information falls into the following categories: incorrect labeling, missing components, physical damage, power failure, and remote failure.
You want to restrict the information to problems reported between June 6, 2001 and July 6, 2001. From the OLAP Control Panel, you can limit data based on a range of dates.
The Selection Criteria pane opens.
A new pane appears for Date Problem Reported, replacing the Selection Criteria pane. The pane includes a drop-down list for each selectable value (Year, Month, and Date).
The following image shows the selections.
The date element appears at the bottom of the window.
From the OLAP Control Panel:
The Selection Criteria pane opens.
The date appears inside the Selections list pane.
From the OLAP Control Panel:
The Selection Criteria pane opens.
Note the following limitations when applying selection criteria to date elements:
How to: |
To make your reports more powerful, you can insert visual representations of selected data directly into the report output. These visual representations, which appear as a column of vertical or horizontal bar graphs adjacent to the numeric data, make relationships and trends among data more obvious.
You can apply data visualization graphs to selected measures from:
This is the quickest way to apply data visualization bar graphs to numeric measures.
The quickest way to apply data visualization graphics is from the report itself:
The report runs automatically, displaying a column of bar graphs following the selected measures column.
Tip: To remove the bar graphs, right-click the measure column title and choose Remove Visualize from the menu.
How to:
Reference: |
When you graph a measure in an OLAP report, you select the specific data elements to include and then view the tabular report and a graphical representation of the identical information simultaneously in a split window. The graph appears in a frame in the top half of the window to facilitate comparison.
To create a graph, the data in the report must include at least one numeric measure and one sort field (By or Across). The Graph control is activated in the Selections panel or the OLAP Control Panel when these basic requirements are met.
The following image includes three sort fields (Store Name, Manufacturing Plant, and PRODCAT) and three numeric measures (Quantity, Our Cost, and Price), displayed as horizontal bar charts for quick comparison.
You can request a graph from an OLAP report, from the Selections panel, or from the OLAP Control Panel:
If you choose to graph more than one measure, you can employ different graph types to suit the data in each column, with the following restrictions:
For details about supported combinations, see Combining Graph Styles and Measure Styles in OLAP Graphs.
Note: If drill-down capability has been enabled for the dimensions in a report, the same functionality is automatically enabled for graphs. You can drill down from one graphical representation of your data to another.
The following table lists the available style combinations in the second column for each graph style in the first column.
Controlling Graph Style |
Potential Measure Styles |
---|---|
Vertical Bar (default) |
Vertical Bar (default) |
Vertical Line | |
Vertical Area | |
Vertical Line |
Vertical Line (default) |
Vertical Bar | |
Vertical Area | |
Vertical Area |
Vertical Area (default) |
Vertical Bar | |
Vertical Line | |
Horizontal Bar |
Horizontal Bar (default) |
Horizontal Line | |
Horizontal Area | |
Horizontal Line |
Horizontal Line (default) |
Horizontal Bar | |
Horizontal Area | |
Horizontal Area |
Horizontal Area (default) |
Horizontal Line | |
Horizontal Area | |
Pie |
Pie |
There is a check box to the left of each measure and a graph button to the right of each measure. All check boxes are unchecked by default and all graph buttons are grayed (inactive) by default.
The graph button to the right of the measure becomes active. The default graph style is Vertical bar.
For a list of graph types that can be defined, see Combining Graph Styles and Measure Styles in OLAP Graphs.
The graph opens in a separate frame above the report and Selections panel.
This example contains two measures, Balance and CANADA_DOLLAR, sorted by Continent. You would like to see graphical representations of both measures. To contrast the graphical information, you use a different graph type for each one.
As shown in the following image, the Selections panel has the Graph control listing Balance represented as a vertical bar and CANADA_DOLLAR represented as a vertical area.
The following image shows the results of the graph selections.
The report shows order information for stores that sell electronic products from Century Corporation. Audio Expert shows the highest numbers, with orders of digital products significantly exceeding analog.
You want a clearer picture of how the digital orders break down by product so you decide to create a pie chart.
The report now shows the Quantity and Line Cost of Goods Sold for several digital products sold at Audio Expert in Q2, as shown in the following image.
As shown in the following image, the graph appears in a pane above the report. You can see at a glance that PDA Devices constituted about 1/3 of digital sales at the Audio Expert store in Q2.
Note that the contents of the Drill Down and Drill Across panes determine the X-axis fields. When there are multiple drill (X-axis) fields, multiple graphs appear vertically stacked in the same frame. The measures appear as Y-axis fields on the graphs you display.
The Measures and Graph-Style pane opens.
Check boxes associated with the available measures are checked by default.
The graph icon corresponding to the controlling graph style appears next to each selected measure.
Note:
In this section: |
While you cannot add new measures to an OLAP report without returning to the original report request, you can adjust the display of the measures in the report in several ways. You can:
When you have more than one measure in an OLAP report, you can stack the measures in separate rows within the same column to reduce the width of the report.
You cannot apply data visualization bar graphs to stacked measures.
Tip: To restore the standard display, deselect the Stack Measures check box and rerun the report.
Initially, this report is sorted vertically by Continent and Risk_Class and horizontally by Region, and the measures (Balance and CANADA_DOLLAR) appear as separate columns.
The report now displays data by Continent followed by Country, as shown in the following image.
You wish to show the measure titles and data values in rows.
You can change the order in which measure columns are presented in the report.
To reposition a numeric column, drop the field into a new column position.
The cursor changes to a plus sign (+) to indicate acceptable places into which you can drop the field. (Unacceptable positions are indicated by a circle with a slash cross the center.)
As shown in the following image, the column for the Quantity measure precedes the column for the Line Cost of Goods Sold measure.
The cursor changes to a plus sign (+) to designate where you can drop the field. The report, as shown in the following image, now displays Quantity as the last column.
You can hide and expose measures from an OLAP report, the Selections panel, or the OLAP Control Panel.
Choose the one you want to re-expose in the report.
Tip: If you want to add a new measure to the report, you must return to the original request and add the field there.
The report includes two measures: Quantity and Line Cost of Goods Sold.
Note: The options available may vary, depending on your OLAP format settings. For more information, see Setting OLAP Reporting Options.
The report runs and displays only the Quantity measure.
A secondary menu displays the hidden measure.
Note: The options available may vary, depending on your OLAP format settings. For more information, see Setting OLAP Reporting Options.
The report now displays the Line Cost of Goods Sold column.
The check box toggles through three positions:
Because of the OLAP settings selected for this report, the Selections panel is hidden. For this example, you will need to expose it.
As shown in the following image, two measures (Quantity and Line Cost of Goods Sold) appear in the report.
Only Quantity now appears, as shown in the following image.
The output now looks as it originally did.
The check box toggles through three positions:
How to: |
Since all of the values in a dimensions hierarchy are available in an OLAP report, you can add dimensions to the OLAP report at any time, without returning to the original report request. You can add dimensions from an OLAP report and from the OLAP Control Pane.
Initially, the report is sorted by quarter, store, and Product Type, as shown in the following image.
You want to sort by month within each quarter.
The report is now sorted by quarter, month, store, and Product Type, as shown in the following image.
Right-click the dimension column you wish to remove and choose Delete from the menu.
The report runs automatically.
Initially, the report is sorted by quarter, store, and Product Type. You wish to remove Product Type as a sort category.
Note: The options available may vary, depending on your OLAP format settings. For more information, see Setting OLAP Reporting Options.
The report runs automatically. The new report is sorted by quarter and store, as shown in the following image.
The following is an example of deleting a dimension element from the OLAP Control Panel.
Initially, the report is sorted by quarter, store, and Product Type. You wish to remove Product Type as a sort category.
The new report is sorted by quarter and store, as shown in the following image.
In this section: |
The following are related to saving OLAP reports:
The manner in which a developer designs a report with regard to field referencing carries through to both the OCP and the OLAP Selections panel. Field referencing does not differ between the report and the OCP and OLAP Selections panel. Field references by AS, TITLE, or field name, are uniform in the report output and OLAP controls.
In this section: How to: |
OLAP reports and graphs appear in your browser in HTML format. You can display the report and corresponding graph in PDF, Excel, and active report formats, and in folders within Managed Reporting.
The following save and/or display options are available:
When you choose PDF format, the report appears in Adobe Acrobat Reader and the graph continues to appear above it in a browser window. If you print from Acrobat, only the report will be printed.
Drill-downs of any kind are not supported.
When you choose Excel 2000, the report and graph are displayed in the same tool where you can manipulate the data using Excel options. From Excel you can print both the report and the graph.
When you save in Excel 2000 format, only explicit drill-downs (based on parameters passed from the base report to the drill-down report) continue to work. Automatic drill downs on Dimensions and Measures are not supported in Excel.
In Managed Reporting:
The graph appears in the browser above the report, while a second browser opens and launches the report output in Adobe Acrobat, as shown in the following image.
Tip: If you wish, you can save and print the PDF report from Adobe Acrobat.
In Managed Reporting, you can save an OLAP report and graph in your private content folder.
A secondary window opens.
Note: There is no limit to the number of characters in the label legend of a graph, but long labels may appear truncated.
This topic describes common problems that you might encounter when working with OLAP-enabled reports.
The report is not OLAP enabled. Ensure that Run with OLAP is selected in the Properties window for the report
The OLAP Control Panel does not display dimensions. Ensure that the metadata for the report uses the WITHIN attribute to create one or more hierarchical dimensions.
You cannot drill-down or roll up a dimension. Ensure that the metadata for the report uses the WITHIN attribute to create one or more hierarchical dimensions.
You cannot include additional measures in the report. You cannot use the OLAP Control Panel to include measures that are not included in the original report request.
Tip:
When you click the OLAP button in a graph, the OLAP Control Panel does not open. OLAP currently is not implemented for GRAPH requests. Deselect Run with OLAP in the Report Properties window.
If you use Print *, an error occurs. If you create a procedure that uses Print *, and you then OLAP enable and run the procedure (selecting Save output as PDF file from the OLAP Control Panel), an error is returned. OLAP does not support Print *. Instead, it prints the entire contents.
WebFOCUS |