Before You Begin

This tutorial shows you how to narrow the members of a hierarchy structure to use in Oracle Analytics visualizations.

Background

In Oracle Analytics, you can use selection steps to specify the levels or members of a hierarchy. You can use a table or pivot visualization for the results of the selection steps. Oracle Analytics supports using selection steps with level-based hierarchies, parent-child hierarchies, and skip-level or ragged hierarchy structures.

Selection steps are top-down, for example, from the parent to the child node or from year to quarter. You would need to select the higher level and then define a lower-level step next.

Selection steps only apply to hierarchy structures and impact visualizations that support hierarchy structures such as tables and pivots.

What Do You Need?

  • Access to Oracle Analytics
  • Access to a dataset with hierarchy structures

Create a Workbook

In this section, you create a workbook with a local subject area to use as the data source and then create a table visualization with a level hierarchy and a fact column.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create. In Create, click Workbook. In Add Data select a dataset, and the click Add to Workbook.

    This tutorial uses a local subject area Local subject area icon, Sample Sales.

  3. In the Data Data pane icon pane, expand a data element containing a hierarchy.


    This tutorial expands the Time data element which contains two hierarchy hierarchical icon structures.

    Description of time_data_element.png follows
    Description of the illustration time_data_element.png
  4. In the Data pane, select a hierarchy and drag it to the canvas.


    This tutorial drags the Time Hierarchy to the canvas.

    Description of time_hierarchy_tablepng follows
    Description of the illustration time_hierarchy_table.png
  5. In the Data pane, drag a fact or measure related to the hierarchy data element to the table.


    This tutorial expands Facts, expands Base Facts, and then drags Revenue to Rows in the Grammar pane under Time Hierarchy.

    Description of table_wo_selection_steps.png follows
    Description of the illustration table_wo_selection_steps.png

Refine Your Selection Steps

In this section, you use selection steps to choose the levels or members of the hierarchy to reveal in the table visualization.

  1. In the Data pane, select a hierarchy Hierarchical column icon and drag it to the filter bar.


    This tutorial drags the Time Hierarchy to the filter bar. The Selection Steps dialog opens with Keep Only as the default action and Members as the default operator. The selection value is empty.

    Description of selection_steps_dialog.png follows
    Description of the illustration selection_steps_dialog.png
  2. In Selection Steps, use the default Keep Only in Actions. Click list list icon in Operators, and then select Levels.
  3. In value, click list list icon and select a value.


    This tutorial expands Total Time and selects Year. In this example, query results don't change in the table with Levels as the operator.

    Description of time_level_year.png follows
    Description of the illustration time_level_year.png
  4. Click the hierarchy Hierarchical column icon in the filter bar. In hierarchy selection steps, click Add Step Add step icon.
  5. Click Keep Only in the Actions list list icon and select Remove. Keep Members in Operators. In value, click list list icon and select a value. Hold down the Ctrl key to select multiple values.


    This tutorial adds a Remove action step in selection steps to exclude members 2009 and 2010.

    Description of remove_members.png follows
    Description of the illustration remove_members.png


    The table visualization shows revenue for 2008 by quarter, month, and day.

    Description of just_2008_rev.png follows
    Description of the illustration just_2008_rev.png

Choose Members as the Operator in Selection Steps

In this section, you start with Members as the operator in the first selection step to change the query.

  1. Click the hierarchy Hierarchical column icon in the filter bar. Click Remove Step Remove step icon to delete the actions, operators, and values in each selection step.


    Oracle Analytics updates the table to show the Time Hierarchy table without selection steps.

    Description of no_select_steps.png follows
    Description of the illustration no_select_steps.png
  2. In Selection Steps, use Keep Only as the action and keep Members as the operator. In value, click list list icon and select a value.


    This tutorial selects 2008 as the value.

  3. In Selection Steps, click Add Step Add step icon. In Selection Steps, use Keep Only as the action, click Members list icon in Operators, and then select Levels. In value, click list list icon and select a value.


    This tutorial selects Month as the value. The result shows the values for each day of the month.

    Description of time_year_month_day.png follows
    Description of the illustration time_year_month_day.png
  4. Click the hierarchy Hierarchical column icon in the filter bar.
  5. In Selection Steps, click Add Step Add step icon. In Selection Steps, click Keep Only in the Actions list list icon and select Remove.
  6. In Operators, click Members and select Levels. Click list list icon to select a value.


    This tutorial selects Day as the values to remove from the query. The table updates to show the total revenue by month in 2008.

    Description of year_month_rev2008.png follows
    Description of the illustration year_month_rev2008.png

Select Members in a Parent-child Hierarchy

In this section, you use a parent-child hierarchy to demonstrate how to use selection steps. This example uses the same Sample Sales local subject area as in the previous sections.

  1. In the Data Data pane icon pane, select a parent-child hierarchy Hierarchical column icon and drag it to the canvas to create a new visualization.


    This tutorial expands Sales Person in the Data panel and drags the Sales Rep Hierarchy to the canvas creating a new table visualization in the workbook.

  2. Expand the rows in the table to show the parent-child relationships.


    The canvas shows that the Time Hierarchy in the filter bar doesn't change the Sales Rep Hierarchy table.

    Description of sales_rep_hier.png follows
    Description of the illustration sales_rep_hier.png
  3. In the Data pane, drag a fact or measure related to the Sales Rep element to the table.


    This tutorial expands Facts, expands Base Facts, and then drags Revenue to Rows in the Grammar pane under Sales Rep Hierarchy.

  4. In the Data pane, drag the same parent-child hierarchy to the filter bar.


    This tutorial drags the Sales Rep Hierarchy to the filter bar.

    When defining selection steps for a parent-child hierarchy, you can choose nodes in the hierarchy. Levels is not available as an operator.

    Description of parent_child_operators.png follows
    Description of the illustration parent_child_operators.png
  5. In Selection Steps, use Keep Only as the action and Member as the operator, and in value, click list list icon. In value, click list list icon and select a value.


    This tutorial uses Keep Only as the action, Members as the operator, and selects Monica Velasquez as the value.

    Description of monica_v_children.png follows
    Description of the illustration monica_v_children.png
  6. Click the parent-child hierarchy Hierarchical column icon in the filter bar. Click Add Step Add step icon. In Actions, click Keep Only and select Remove.
  7. Click Members in Operators and select Children. In value, click list list icon, hold down the Ctrl key, and the select the children to remove from the query.


    This tutorial uses the Ctrl key to select all of Monica's subordinate sale representatives.

    Description of monica_no_children.png follows
    Description of the illustration monica_no_children.png

Learn More