13 Creating and Maintaining Hierarchies

This chapter explains how to implement and maintain hierarchies using Discoverer Administrator, and contains the following topics:

13.1 What are hierarchies?

Hierarchies are default drill paths between items that you define in Discoverer Administrator. You create hierarchies between items in a business area to provide Discoverer end users with a default drill hierarchy.

There are two kinds of hierarchy:

  • item hierarchies

  • date hierarchies

Discoverer end users can use hierarchies to:

  • drill up to a more general level of detail (for example, Store to City to Region to Country)

  • drill down to a finer level of detail (for example, Country to Region to City to Store)

Hierarchies can link items in a business area where no relationship is defined in the database. For more information about business areas, see Chapter 5, "Creating and Maintaining Business Areas".

13.2 What are item hierarchies?

Item hierarchies are relationships between items other than dates.

An example of an item hierarchy:

Figure 13-1 Sales item hierarchy

Surrounding text describes Figure 13-1 .

The Sales item hierarchy links a country with its regions, cities and stores.

To use this hierarchy a Discoverer end user could use a report that shows sales from a country perspective. The Discoverer end user could then drill down from country to see sales per region, sales per city or sales per store, and then drill back up to the country level.

The Sales item hierarchy from a Discoverer end user perspective is shown below.

Figure 13-2 Sales item hierarchy - Discoverer end user perspective

Surrounding text describes Figure 13-2 .

The figure below shows the Sales item hierarchy from a database perspective.

Figure 13-3 Sales item hierarchy - database perspective

Surrounding text describes Figure 13-3 .

Note that you do not have to specify that Los Angeles is in the West. You only have to specify that City is under Region in your item hierarchy.

13.3 What are date hierarchies?

Date hierarchies are relationships between date items.

An example of a date hierarchy:

Figure 13-4 Sales date hierarchy

Surrounding text describes Figure 13-4 .

The Sales date hierarchy links a year with its quarters, months, weeks and days.

To use this hierarchy a Discoverer end user could use a report that shows total sales for each year. The Discoverer end user could then drill down from year to show sales per quarter, sales per month, sales per week and the sales per day, and then drill back up to sales per year.

A section of the Sales date hierarchy from a Discoverer end user perspective is shown below.

Figure 13-5 Sales date hierarchy - Discoverer end user perspective

Surrounding text describes Figure 13-5 .

The figure below shows the Sales date hierarchy from a database perspective.

Figure 13-6 Sample date hierarchy - database perspective

Surrounding text describes Figure 13-6 .

Note: Each level in the date hierarchy is a calculation based on the Sales_Orders.SALES_DATE column. The calculations are produced by a date hierarchy template (for more information, see "What are date hierarchy templates?").

13.3.1 What are date hierarchy templates?

Date hierarchy templates enable you to define a date hierarchy that you can apply to date items. A date item uses information that specifies the date, month, year and time. Discoverer uses this information to calculate for example, quarter, week and days of the week. A date hierarchy template automatically creates items based on a date item, for example to represent the year or month

You will find it more efficient to reuse a date hierarchy template by applying it to date items rather than redefining the same date hierarchy repeatedly for each date item.

You can use the date hierarchy templates (supplied with Discoverer Administrator) to define many common date hierarchies, or you can create your own customized date hierarchies.

Discoverer Administrator includes a default date hierarchy template (see the Date hierarchy template figure below) that enables you to drill from year to quarter to month to day:

Figure 13-7 Date hierarchy template

Surrounding text describes Figure 13-7 .

13.3.2 About date hierarchies and performance

If you apply a date hierarchy to a date item from an indexed table, a query that includes one of these date items will not use the indexes (which can reduce performance). You can optimize performance in Discoverer Plus by applying date hierarchies to date items from tables that do not rely on indexes.

13.3.2.1 Example of how a date hierarchy can affect performance in Discoverer Plus

When you load a large fact table (that is, a table with many rows) that contains a date column (for example, transaction_date) Discoverer applies the default date hierarchy to the date item (for more information, see "Load Wizard: Step 4 dialog").

Discoverer creates a folder containing date items such as Year, Quarter and Month using the EUL_DATE_TRUNC function (for more information, see "About truncating date items and the EUL_DATE_TRUNC function"). When a Discoverer end user runs queries that include these items, any indexes that include the date item in the fact table are not used. Where indexes are not used, performance can be affected.

It is recommended therefore that you do not apply date hierarchies to date items in folders based on fact tables, as fact tables are likely to have indexes.

13.3.2.2 Example of how performance can be retained

To retain performance you should apply date hierarchies to a separate dimension table.

For example, a transaction_date item in a fact table might join to another dimension table (for example, Time Period) that specifies time periods. You can load this dimension table using the Load Wizard and apply date hierarchies to it. You can then create a complex folder containing items from both the dimension and fact tables, including the items created by the date hierarchy such as Year or Quarter. When a Discoverer end user uses queries that include date hierarchy items Discoverer can use the date column indexes on the fact table. This can considerably improve performance.

13.4 How to create item hierarchies

To create an item hierarchy:

  1. Choose Insert | Hierarchy to display the "Hierarchy Wizard: Step 1 dialog".

  2. Select the Item Hierarchy option and click Next to display the "Hierarchy Wizard: Step 2 dialog".

    Figure 13-8 Hierarchy Wizard: Step 2 dialog

    Surrounding text describes Figure 13-8 .

    Here you select which items to use in this item hierarchy.

  3. Move the items that you want to include in this item hierarchy from the list on the left to the list on the right.

    You can select more than one item at a time by holding down the Ctrl key and clicking another item.

    Note: The order of items in the hierarchy list determines the drill down sequence that the Discoverer end user will use to analyze the data. The item hierarchy is arranged in the order that you include the items. For example, Region - City - Store.

    Note: You can select items from multiple folders but the folders must be joined. If the folders are joined with more than one join, Discoverer will prompt you with the Choose Join dialog to select the correct join to use.

  4. (optional) To move an item in the hierarchy to a different position, select the item in the right-hand list and click:

    • Promote to move the item higher in the hierarchy

    • Demote to move the item lower in the hierarchy

  5. (optional) To group two or more items so that they appear on the same level of the hierarchy, select the items and click Group (for more information, see "Hierarchy Wizard: Step 2 dialog").

    You can select more than one item at a time by holding down the Ctrl key and clicking another item.

    Note: To ungroup a group of items in the hierarchy, select the group and click Ungroup.

  6. (optional) To rename an item, click the item in the right hand list and specify a name in the Description field.

    The description you specify is the label the user sees in Discoverer Plus. If you don't specify a name, by default each level of the item hierarchy uses the item name.

  7. Click Next to open the "Hierarchy Wizard: Step 3 dialog (name hierarchy)".

    Figure 13-9 Hierarchy Wizard: Step 3 dialog

    Surrounding text describes Figure 13-9 .

  8. Specify a name and description for the new item hierarchy.

  9. Click Finish to create the item hierarchy.

Discoverer displays the item hierarchy in the Hierarchies tab of the Workarea.

13.5 How to create date hierarchies and templates

To create a date hierarchy:

  1. Choose Insert | Hierarchy to display the "Hierarchy Wizard: Step 1 dialog".

  2. Select the Date Hierarchy option and click Next to open the "Hierarchy Wizard: Step 2 dialog".

    Figure 13-10 Hierarchy Wizard: Step 2 dialog

    Surrounding text describes Figure 13-10 .

  3. (optional) To create a new date format click New Format... to display the "New Date Format dialog".

  4. Move the date formats to include in this date hierarchy from the list on the left to the list on the right.

    You can select more than one date format at a time by holding down the Ctrl key and clicking another date format.

  5. (optional) To move a date format in the hierarchy to a different position, select the date format in the list on the right and click:

    • Promote to move the date format higher in the hierarchy

    • Demote to move the date format lower in the hierarchy

  6. (optional) To rename a date format, click the date format in the list on the right and specify the new name in the Description field.

    The description you specify is the label the user sees in Discoverer Plus.

  7. Click Next to display the "Hierarchy Wizard: Step 3 dialog".

    Figure 13-11 Hierarchy Wizard: Step 3 dialog

    Surrounding text describes Figure 13-11 .

    Here you select which date items will use this date hierarchy.

  8. Move the date items that will use this date hierarchy from the Available Items list to the Selected Items list.

    You can select more than one item at a time by holding down the Ctrl key and clicking another item.

    Tip: To create just a date hierarchy template, do not select any date items on this page. Later, you can apply this template to date items by modifying their item properties (for more information, see "How to apply a date hierarchy template to a date item").

  9. Click Next to open the "Hierarchy Wizard: Step 4 dialog (name hierarchy)".

    Figure 13-12 Hierarchy Wizard: Step 4 dialog

    Surrounding text describes Figure 13-12 .

  10. Specify a name and description for the new date hierarchy.

  11. (optional) To make this date hierarchy template the default, select the Set as default date hierarchy check box.

    If you select the Set as default date hierarchy check box, the date hierarchy template is displayed as the default in the "Load Wizard: Step 4 dialog" in the drop down list under the Date hierarchies check box.

  12. Click Finish to create the date hierarchy and apply it to the date items that you selected in the Hierarchy Wizard: Step 3 dialog.

    The date hierarchies and the date hierarchy template now appear on the "Workarea: Hierarchies tab".

13.6 How to edit item hierarchies

To edit an existing item hierarchy:

  1. Select a hierarchy on the Hierarchies tab and choose Edit | Edit…to display the "Edit Hierarchy dialog: Name tab".

    Figure 13-13 Edit Hierarchy dialog: Name tab

    Surrounding text describes Figure 13-13 .

  2. Edit the Hierarchy as required.

    The Edit Hierarchy dialog is divided into two tabs.

    • Items

      Use this tab to add or remove the items that use this hierarchy.

    • Name

      Use this tab to edit the hierarchy's name and description.

    For more information about the above tabs see "Edit Hierarchy dialog: Name tab" or "Edit Hierarchy dialog: Items tab (select date items that will use this date hierarchy)".

  3. Click OK.

13.7 How to edit date hierarchy templates

This section describes how to edit an existing date hierarchy template. When you edit a date hierarchy template, all date items that use the date hierarchy template are modified to reflect the changes.

  1. Click the date hierarchy template on the Hierarchies page and choose Edit | Edit… to display the "Edit Hierarchy dialog: Name tab".

    Figure 13-14 Edit Hierarchy dialog: Name tab

    Surrounding text describes Figure 13-14 .

  2. Edit the hierarchy as required.

    The Edit Hierarchy dialog is divided in to two tabs:

    • Date formats

      Use this tab to change the date formats and their position in this date hierarchy template.

    • Name

      Use this tab to edit the date hierarchy template's name and description.

    For more information about the above tabs see "Edit Hierarchy dialog: Name tab" or "Edit Hierarchy dialog: Items tab (select date items that will use this date hierarchy)".

  3. Click OK.

13.8 How to apply a date hierarchy template to a date item

This section describes how to apply a date hierarchy template to an existing date item.

When you apply a date hierarchy template to an existing date item, Discoverer Administrator automatically creates all the date items that are required to complete the date hierarchy. These new date items appear in the same folder as the original date item (prefixed with the name of the original date item). If you change the date hierarchy template that is applied to a date item, Discoverer creates new date items to reflect the new date hierarchy template. However Discoverer does not remove the date items from the previous date hierarchy. If you want to remove date items from a previous date hierarchy you must remove them manually.

For example, if you create the date hierarchy YY/QQ/MM and assign it to the date item Transaction Date, Discoverer creates the following items in the folder:

  • Transaction Date YY

  • Transaction Date QQ

  • Transaction Date MM

If you then create a new date hierarchy WW/DD and assign it to Transaction Date, Discoverer creates the following additional items in the folder:

  • Transaction Date WW

  • Transaction Date DD

Discoverer does not delete the other three date items created previously.

To apply a date hierarchy template to a date item:

  1. Click the date item on the Data tab and choose Edit | Properties to display the "Item Properties dialog".

    Tip: To apply a single date hierarchy template to more than one date item at a time, select all the date items before opening the Properties dialog. For more information, see "How to edit item properties".

  2. Select the date hierarchy template for this date item to use from the drop-down list in the Date Hierarchy field.

    Select None to make the date item not use a date hierarchy template.

  3. Click OK.

13.9 How to set the default date hierarchy template

The default date hierarchy template is displayed as the default selection in the Load Wizard step 4 in the Date hierarchies, using: drop down list. For more information see "Load Wizard: Step 4 dialog".

To set the default date hierarchy template:

  1. Expand the Date hierarchy templates on the "Workarea: Hierarchies tab" to display all the date hierarchy templates.

  2. Select the date hierarchy template you want to set as the default.

  3. Choose Edit | Edit…to display the "Edit Hierarchy dialog: Name tab".

  4. Select the Set as default date hierarchy check box on the Name tab.

  5. Click OK.

13.10 How to delete item hierarchies or date hierarchy templates

To delete item hierarchies or date hierarchy templates:

  1. Select the item hierarchy or date hierarchy template on the "Workarea: Hierarchies tab".

    You can select more than one hierarchy at a time by holding down the Ctrl key and clicking another hierarchy.

  2. Choose Edit | Delete to open the "Confirm Delete dialog".

  3. (optional) To see the objects that might be affected by deleting this hierarchy or hierarchy template:

    1. Click Impact to display the "Impact dialog".

      Figure 13-15 The Impact dialog

      Surrounding text describes Figure 13-15 .

      The Impact dialog enables you to review the other EUL objects that might be affected when you delete a hierarchy or hierarchy template.

      Note: The Impact dialog does not show the impact on workbooks saved to the file system (that is, in .dis files).

    2. (optional) Select a row to view text at the bottom of the list, indicating what affect the current action will have on the EUL object.

    3. When you have finished reviewing the impact of deleting the hierarchy or hierarchy template, click Close to close the Impact dialog.

  4. Click Yes to delete the selected hierarchies or hierarchy templates.

13.11 How to modify default date hierarchies that use date format 'RR' following upgrade to Oracle Business Intelligence Discoverer Administrator

After upgrading to Oracle Business Intelligence Discoverer Administrator, if a date format in a default date hierarchy uses the date format 'RR' you must modify it to 'YYYY' and then remove any affected date items (that is, date items that use the 'RR' date format) from the business area. This action is necessary to ensure that materialized views can be successfully created (for more information about materialized views, see "What are materialized views?").

To modify a default date hierarchy that uses the date format 'RR' so that it uses 'YYYY' you must complete the following tasks:

Modify default date hierarchy replacing the date format 'RR' with 'YYYY'

To modify a default date hierarchy to use the date format 'YYYY':

  1. Click the "Workarea: Hierarchies tab".

  2. Expand Date hierarchy templates then highlight the default date hierarchy that uses the date format 'RR'.

  3. Choose Edit | Edit... to display the "Edit Hierarchy dialog: Name tab".

  4. Click the "Edit Hierarchy dialog: Items tab (select date items that will use this date hierarchy)" and replace each 'RR' date format with a 'YYYY' date format.

    Note: Repeat the above steps for each affected default date hierarchy.

  5. Click OK to save your changes.

After you apply the 'YYYY' format to the affected default date hierarchy, you can remove remaining date items that use the date format 'RR'.

Remove remaining date items that use the date format 'RR'

To remove date items that use the date format 'RR':

  1. Click the "Workarea: Data tab".

  2. Highlight a date item that uses the date format 'RR'.

  3. Choose Edit | Delete to delete the affected date item.

  4. Repeat the above steps for each date item that still uses the date format 'RR'.

13.12 About creating a hierarchy using the CONNECT BY clause in a custom folder

A database table might contain a recursive hierarchy, that is where a relationship exists between different records in one table. This form of hierarchy is not unlike a series of self joins and it cannot be used directly by Discoverer (sometimes described as a value hierarchy). However, you can make Discoverer use a recursive hierarchy if you create a custom folder and use the CONNECT BY clause.

For example, if you look at the emp table in the Scott schema (supplied with Oracle databases), some of the numbers appear in both of the columns empno and mgr. The numbers appear in both columns because an employee's manager is identified by the employee number. This illustrates how a recursive hierarchy exists in the emp table.

The table below illustrates the relationships that exist between rows in the columns empno, ename and mgr from the emp table.

EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING  
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782

Discoverer cannot create a hierarchy directly using the above table. However, you can create a hierarchy from this table if you first use the CONNECT BY clause of the SELECT statement to create a custom folder. You can then use the custom folder as the basis of a hierarchy that Discoverer can use.

To create a hierarchy in Discoverer that uses the information in the above table, you would need a table that is similar to the following table that Discoverer might use to create a hierarchy:

TOP_LEVEL 2nd_LEVEL Nth_LEVEL EMPNO ENAME
KING CLARK   7934 MILLER

The above table displays the name of an employee (ename) and all the managers for that employee. In this table Miller has two levels of manager, his immediate manager being Clark.

To create a table like the one above (that is, based on columns from the emp table) and subsequently create a hierarchy between the levels, complete the following task:

13.13 How to create a hierarchy with a custom folder and the CONNECT BY clause, using the emp table from the scott schema

You might want to create a hierarchy based on values in a table (for example, the emp table in the scott schema). Discoverer Plus users will be able to create workbooks and use this hierarchy to drill up and down between the different levels of employees.

To create a hierarchy, with a custom folder and the CONNECT BY clause, using the emp table from the scott schema:

  1. Create a custom folder and name it Recursive Hierarchy using the following SQL statement (for more information, see "How to create custom folders"):

    SELECT DISTINCT empno, mgr, level
FROM scott.emp
CONNECT BY mgr = PRIOR empno
START WITH mgr IS NULL

    The above SQL statement creates a table that contains the following result set:

    EMPNO MGR LEVEL
    7369 7902 4
    7499 7698 3
    7521 7698 3
    7566 7839 2
    7654 7698 3
    7698 7839 2
    7782 7839 2
    7788 7566 3
    7839   1
    7844 7698 3
    7876 7788 4
    7900 7698 3
    7902 7566 3

    Note the following points about the SQL statement in the custom folder you created in the above step:

    • the column named Level

      This is a pseudo-column generated by the CONNECT BY function that is used to define the hierarchy in Discoverer.

    • the word PRIOR used in the CONNECT BY statement

      This makes the SQL look for the next row in which the mgr column equals the value in the empno column in the prior row.

    • the START WITH statement

      This instructs the SQL to start where mgr is null (that is, with the top manager).

    For more information about the CONNECT BY statement, see the Oracle Database SQL Reference Guide.

  2. Set the custom folder property Visible to user to No (for more information, see "How to edit folder properties").

    This makes sure that end users cannot access this custom folder.

    Note: Although the Level column provides the information needed to create a hierarchy, you first need to separate the rows by Level. You achieve this by creating a complex folder for each level.

  3. Create a complex folder for each level (for more information, see "How to create complex folders").

    In this example, you create four complex folders.

    Tip: To create each complex folder, drag all three items (Empno, Mgr and Level) from the custom folder Recursive Hierarchy into each new complex folder in turn.

  4. Name each of the four new complex folders Level1, Level2, Level3, Level4 respectively (for more information, see "How to edit folder properties")

  5. Apply a mandatory condition to each complex folder (for more information, see "How to create simple conditions").

    Tip: For example, the condition you apply to the complex folder containing data at level 3 is:

    Level = 3.

  6. Create joins to join the complex folders to each other (for more information, see "How to create joins").

    Tip: Use the empno and mgr items to do this. Create a join between the complex folder for Level1 and the folder for Level2 as follows:

    Level1.empno = Level2.mgr.

    Follow the above rule for joining the Level2 folder to the Level3 folder and the Level3 folder to the Level4 folder. These joins reflect the hierarchy you want to create.

  7. Set the folder property Visible to user for each of these complex folders to No (for more information, see "How to edit folder properties").

  8. Create another complex folder and give it an appropriate name (for example, Chain of Command).

    Note: This is the complex folder that end users will see.

  9. Drag across into the Chain of Command folder, the Empno item from each of the four complex folders created in step 3 above and rename each one as soon as you have dragged it across.

    Tip: For each Empno item that you drag from one of the folders Level1, Level2, Level3 and Level4 you must rename each appropriately (for example, Empno1, Empno2 Empno3, Empno4).

    This gives you the following result set that Discoverer can use to build a hierarchy:

    Empno1 Empno2 Empno3 Empno4
    7839 7698 7499  
    7839 7698 7521  
    7839 7698 7654  
    7839 7698 7788  
    7839 7698 7844  
    7839 7782 7900  
    7839 7566 7902 7369

  10. Create a hierarchy based on the items in this folder (for more information, see "How to create item hierarchies") represented by the columns in the table above.

    Discoverer Plus users will be able to create workbooks and use this hierarchy to drill up and down between the different levels of employees.