Oracle Hyperion: Pivots

Exclusive offer: get 50% off this eBook here
The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11

The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11 — Save 50%

Quickly master the extremely robust and powerful Oracle Hyperion Interactive Reporting 11 tool with this book and eBook

$29.99    $15.00
by Edward J. Cody | September 2010 | Enterprise Articles Oracle

Pivots are one of the most widely used tools for analyzing and presenting data. Interactive Reporting Pivots provide the business analyst with a very effective method to summarize and analyze a dataset, providing the ability to quickly modify, compute, and rearrange data while easily repopulating the dataset with new data. Pivots are very useful in presenting data, where the sections can be analyzed individually or included in Dashboards and Reports. The focus of this article is to demonstrate the features of the Pivot used to effectively analyze and present content.

This article by Edward J. Cody, author of The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11, covers:

  • Creating pivots
  • Methods for adding content
  • Managing pivot headings
  • Sorting
  • Analyzing content

(For more resources on Oracle, see here.)

Creating Pivot sections

Pivots are added to a document by selecting the New Pivot item from the Insert Menu. When a new Pivot section is added the document, the Pivot will appear in the Section Catalogue with the default name Pivot. Renaming the section is consistent with other sections, where the section is renamed using one of the three following options:

Right-click method

Edit menu method

Double-click method

Right-click the section in the Section Catalogue and select Rename Section

Select Rename Section from the Edit menu while viewing the section

Double-click the name of the section in the Section Title Bar

Change the section to the desired name and select OK

Change the section to the desired name and select OK

Change the section to the desired name and select OK

 

Pivot this Chart

Interactive Reporting provides the ability to create a Pivot from the data contained in a Chart section. While viewing a chart, the Pivot this Chart menu option in the Insert menu can be selected. Upon selecting the menu item, a new Pivot section is added to the document and is activated in the main window. Interactive Reporting translates the axis information of the Chart to the Pivot row and column labels and the Fact elements to the Facts in the Pivot. This feature can be used with all chart types except the Scatter and Bubble charts.

Pivots created using results or tables

Pivot sections can be added underneath a Results section or Table section to allow for reporting on subsets of data. When a Pivot is added to the document and a Query section, Results section, or a section reporting off of a Results section is active (displayed in the main window and highlighted in the Section Catalogue), the Pivot will be allowed access to all of the elements and will reflect all of the data stored in the Results section. However, a Pivot may also be added to report off a subset of results contained in a Table section. When a Pivot is added to the document and the parent section is a Table section or presentation section with a Table section as the parent, the Pivot appears in the Section Catalogue indented underneath the Table section to denote the section is reporting off of the data contained in a table. Notice how the US Sales Pivot and US Sales Chart in the following screenshot appear indented underneath the US Sales Table. This indentation denotes that the Sales Pivot and Chart are both presenting information from the US Sales Table. Notice also how the Sales Pivot and Cost Pivot appears underneath the Results section and not indented. This shows that the Sales Pivot and the Cost Pivot present information from the Results section.

Oracle Hyperion: Pivots

Removing Pivot sections

Pivot sections are removed from the document using one of the two options shown in the following table:

Right-click method

Edit menu method

Right-click the section in the Section Catalogue and select Delete Section

Select Delete Section from the Edit menu

Confirm section delete by selecting Delete from the Delete Section confirmation window

Confirm section delete by selecting Delete from the Delete Section confirmation window

Adding content

Once a Pivot is added to the document, data from the parent Results or Table section may be added to the Pivot by adding elements to the Data Layout window in the section. The Data Layout section is displayed at the bottom of the main window and can be shown or hidden by clicking on the Data Layout button in the Section Title Bar. An example of the Data Layout window is shown in the following screenshot:

Oracle Hyperion: Pivots

The Data Layout window contains three different areas controlling the display of content in the Pivot named Row Labels, Column Labels, and Facts. Items are added to the different areas by the following three methods:

Drag-and-drop method

Right-click method

Pivot menu method

Highlight the column(s) in the Elementswindow

Highlight and right-click the column(s) in the Elements window

Highlight the column(s) in the Elements Window

Left-click and drag the column(s) to the Row Labels, Column Labels, or Fact area in the Data Layout window

Select from the menu the desired location to add the column(s) to the pivot

Click on the Pivot menu and highlight the Add Select Items menu item

 

 

Select from menu the desired location to add the column(s) in the pivot

Row and column labels

Row labels are the items displayed on the left-hand side of the Pivot, and column labels are the items displayed on the top of the Pivot. Multiple elements may be added to both the row and column labels, where the order of the columns drives the order and summarization of the data in the Pivot.

Facts

Facts are elements of data summarized by the elements added to the row and column labels. Facts are typically numeric data elements, but Interactive Reporting also allows for text columns to be added as a Fact. While numeric items are aggregated by a data function, text items are separated by commas creating a string of values.

Data functions

The Pivot differentiates the aggregation of each Fact by the Data function. The default is the Sum function and can be changed to another preset configuration. Other functions include Average, Count, Count Distinct, Maximum, Minimum, % of Column, % of Row, and % of Grand. The data function of a fact item can easily be adjusted by one of the following methods:

Right-click method

Pivot menu method

Highlight the Fact column of data in the pivot.

Highlight the Fact column of data in the pivot.

Right-click and highlight the Data Function menu item

Open the Pivot menu and highlight the Data Function menu item

Select the Data Function of interest

Select the Data Function of interest

Removing content

Items in the Row labels, Column labels, and Facts are easily removed. Use one of the following steps to remove a column from the Data Layout:

Right-click method

Keyboard method

Pivot menu method

Highlight and right-click on the column(s) in the Data Layout

Highlight the column(s) in the Data Layout or the entire column in the Pivot

Highlight the column(s) in the Data Layout

Select Remove

Press the Delete key on the keyboard

Open the Pivot menu and select the Remove Selected Items menu item

Arranging label content

The Pivot section provides flexibility to easily modify and rearrange content while performing analysis. The first and most commonly used method of rearranging content is the ability to easily move row and column element in the pivot. Row and column elements are rearranged by dragging-and-dropping elements in the Data Layout, or by highlighting and moving the column of data in the physical pivot.

A tab exists at the end of each row and column label allowing for the highlighting of the entire column as shown in the following screenshot. When the element is highlighted, the column can be pivoted from the row labels to the column labels, and the row and column labels can be rearranged respectively by dragging the element in front or behind other sections. When the element is dragged, a graphical outline appears as shown in the following screenshot:

Oracle Hyperion: Pivots

The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11 Quickly master the extremely robust and powerful Oracle Hyperion Interactive Reporting 11 tool with this book and eBook
Published: September 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

Read more about this book

(For more resources on Oracle, see here.)

Managing label and data headings

When row and column labels are added to a Pivot section, the row and column headings are not shown by default. However, when an element is added to the Fact section, the heading of the fact is shown above every column of facts. The Pivot provides the ability to manage the headings for both the labels and facts, named Corner Labels and Data Labels.

Data labels

Data labels are used to add or remove fact headings and to select the position of the fact columns in the Pivot. Three options can be selected for managing data labels including:

  • None: The Fact records are shown as columns and no header record is included.
  • Column: The Fact records are shown as columns and column headings are added above the fact columns.
  • Row: The Fact records are moved to the rows and the column headings appear as row labels.

The names of the Data labels can be modified by double-clicking on the label name and entering the desired name into the pop-up window. The original name can be restored by right-clicking on the heading label and selecting Restore Name from the menu.

Corner labels

Corner labels are used to manage the addition or removal of headings to the row and column elements. Four options can be selected for managing corner labels including:

  • None: Removes both the row and column headings.
  • Column: Adds headings for all column labels to the left of the column above the final row element, where a row element must exist to display the column names.
  • Row: Adds headings for all row elements above each column.
  • Both: Adds headings for both row and column elements, however a row element must exist for the column headings to appear. In addition, a Data label must be selected for the final column of row labels to appear.

The names of Corner labels may not be edited directly in the pivot. Corner label names must be renamed in the section's results.

Sorting

Each row and column label can be sorted in ascending or descending order by either the name of the label or by any Fact column in the pivot, providing the flexibility to sort items in a row and column by any column in the dataset. The Sort line in the Pivot section appears different compared to the previously discussed sections due to the additional flexibility provided by the Pivot Sort line for sorting the aggregated information.

The Sort line is toggled to show or hide by clicking on the Sort button at the top of the Section Title Bar. An example of the Sort line is shown in the following screenshot:

Oracle Hyperion: Pivots

Notice the Sort line contains three drop-down boxes. The first is for selecting the row or column label to sort and the second is the column to use to sort the label. The default sort column is Label, which refers to sorting the data by the physical label name. The third drop-down box is for the selection of the data function to use when sorting the data. If the Label option is selected, the third drop-down box is disabled. However, if a Fact column is selected, the third drop-down box is enabled and a data function must be selected for the aggregation of data in the sort with Sum as the default. Finally, the last two buttons are used to toggle the sort in ascending or descending order.

Manual sorting

Data items in the rows and columns can also be manually sorted by highlighting and dragging-and-dropping the individual data items in the Pivot to reorder the element in the row or column. The manual sort of data elements will continue to hold through data refreshes until the element is removed or sorted.

Analyzing content

The analysis features of the Pivot section are the most commonly used data analysis features in Interactive Reporting. The Pivot section provides the ability to Focus, Hide, and Drill into columns of data to easily locate and explore datasets. The following screenshot shows a Pivot configured with sales and cost across regions.

Oracle Hyperion: Pivots

Drill

The concept of drilling into data has become of one of the most commonly used terms in data analysis, where drilling provides the ability to start at a high-level dataset and explore the details of a particular section of data. The Interactive Reporting Pivot concept of drilling is similar, where the software allows the ability to drill into another data element available in the parent section of data. When a Drill is initiated, Interactive Reporting freezes the data item(s) selected for the drill and removes all of the other items in the particular row or column label. Then an addition element selected during the Drill is added to the row or column label to show the additional detail.

One of the following two steps can be used to drill into a Pivot of data:

Right-click method

Pivot menu method

Highlight the item(s) in a row or column label and right-click

Highlight the item(s) in a row or column label

Highlight Drill Anywhere

Open the Pivot menu and highlight Drill Anywhere

Select the element to drill into

Select the element to drill into

 

Using the previous sales model example, the outcome of drilling into the Americas region to the Country Name element is shown in the following screenshot. Notice how the other previously shown regions are now hidden from view and the additional data element (Country Name) has been added as a row label to the data layout. Notice also the drill bit icon next to the Country Region row label denoting the Country Region column is focused on a particular set of items.

Oracle Hyperion: Pivots

Multiple drills can take place in a Pivot, and items in the Pivot may be rearranged as desired.

Drillup

Interactive Reporting provides a very simple way of reversing the Drill behavior. The following steps are used to Drillup on a column of data.

Right-click method

Pivot menu method

Click on the tab at the bottom of the element that was selected from the Drill Anywhere menu

Click on the tab at the bottom of the element that was selected from the Drill Anywhere menu

Right-click and select Drillup

Open the Pivot menu and select the Drillup menu item

In continuing with the previous example, selecting Drillup on the Country Name field in the pivot will return the pivot to the original example starting point.

Summary

The focus of this article was to provide the business analyst the features necessary to effectively analyze and present content in a Pivot section. The article started with the methods for creating and adding content to a Pivot and continued with an overview to managing Pivot headings and labels. The article addressed the methods for sorting a Pivot using the text labels and Fact elements and also explored the data analysis capabilities of the tool and the concept of drilling.


Further resources on this subject:


The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11 Quickly master the extremely robust and powerful Oracle Hyperion Interactive Reporting 11 tool with this book and eBook
Published: September 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Edward J. Cody

Edward J. Cody is an accomplished data warehouse and business intelligence consultant with over eight years of experience with Hyperion products. Mr. Cody was a speaker at Oracle OpenWorld 2008 on Essbase, and he has spoken at local user groups on ETL software. His experience with Interactive Reporting began with Brio v6 and has continued through the most recent version. He also has extensive experience with Essbase and Financial Reporting.

Mr. Cody has consulted both private and Government organizations throughout his career. He has a Bachelor of Science in Systems Engineering from the George Washington University, School of Engineering and Applied Science, and he has a Master of Science in the Management of Information Technology from the University of Virginia, McIntire School of Commerce. His experience includes managing large data warehouse and business intelligence projects and providing senior advisory consulting services. He is well known for his efforts to centralize metrics, reduce BI complexity, leverage process automation and product integration.

Mr. Cody is the author of The Business Analyst’s Guide to Oracle Hyperion Interactive Reporting 11 and decided to author a second book with Ms. Vose on Oracle Hyperion Interactive Reporting to better educate the user on advanced topics in the software. They identified that an advanced guide on the advanced features of Interactive Reporting would be beneficial to all software users.

Books From Packt


Oracle Application Express 3.2 - The Essentials and More
Oracle Application Express 3.2 - The Essentials and More

Oracle SOA Suite 11g R1 Developer's Guide
Oracle SOA Suite 11g R1 Developer's Guide

BPEL PM and OSB operational management with Oracle Enterprise Manager 10g Grid Control
BPEL PM and OSB operational management with Oracle Enterprise Manager 10g Grid Control

Oracle Coherence 3.5
Oracle Coherence 3.5

The Oracle Universal Content Management Handbook
The Oracle Universal Content Management Handbook

Oracle JRockit: The Definitive Guide
Oracle JRockit: The Definitive Guide

Oracle Siebel CRM 8 Installation and Management
Oracle Siebel CRM 8 Installation and Management

Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators


Your rating: None Average: 5 (3 votes)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
1
q
b
1
t
j
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software