Skip Headers

Oracle Discoverer Administrator Administration Guide
10g (9.0.4)

Part Number B10270-01
Go To Documentation Library
Home
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

12
Creating and maintaining hierarchies

Creating and maintaining hierarchies

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

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:

Discoverer end users can use hierarchies to:

Hierarchies can link items in a business area where no relationship is defined in the database. For more information about business areas, see Chapter 4, "What are business areas?".

What are item hierarchies?

Item hierarchies are relationships between items other than dates.

An example of an item hierarchy:

Figure 12-1 Sales item hierarchy


Text description of hiecsale.gif follows.
Text description of the illustration hiecsale.gif

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 12-2 Sales item hierarchy - Discoverer end user perspective


Text description of hiuser.gif follows.
Text description of the illustration hiuser.gif

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

Figure 12-3 Sales item hierarchy - database perspective


Text description of hidb.gif follows.
Text description of the illustration hidb.gif

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.

What are date hierarchies?

Date hierarchies are relationships between date items.

An example of a date hierarchy:

Figure 12-4 Sales date hierarchy


Text description of hiedtex.gif follows.
Text description of the illustration hiedtex.gif

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 12-5 Sales date hierarchy - Discoverer end user perspective


Text description of hiedtexu.gif follows.
Text description of the illustration hiedtexu.gif

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

Figure 12-6 Sample date hierarchy - database perspective


Text description of hidtdb.gif follows.
Text description of the illustration hidtdb.gif

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?").

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 re-use 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 12-7 Date hierarchy template


Text description of hidtmpl.gif follows.
Text description of the illustration hidtmpl.gif

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.

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

When you load a large fact table (i.e. a table with many rows) that contains a date column (e.g. 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 Chapter 8, "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.

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 (e.g. 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.

How to create item hierarchies

To create an item hierarchy:

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

  2. Select the Item Hierarchy radio button and click Next to display the "Edit Hierarchy dialog: Items tab".

    Figure 12-8 Hierarchy Wizard: Step 2 dialog


    Text description of hwz21.gif follows.
    Text description of the illustration hwz21.gif

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

  1. 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.

  2. (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

  3. (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").

    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.

  4. (optional) To rename an item, click on 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.

  5. Click Next to open the "Hierarchy Wizard: Step 3".

    Figure 12-9 Hierarchy Wizard: Step 3 dialog


    Text description of hwz31.gif follows.
    Text description of the illustration hwz31.gif
  6. Specify a name and description for the new item hierarchy.

  7. Click Finish to create the item hierarchy.

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

How to create date hierarchies and templates

Note: You can create date hierarchies only if you use an Oracle database.

To create a date hierarchy:

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

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

    Figure 12-10 Hierarchy Wizard: Step 2 dialog


    Text description of hwz22.gif follows.
    Text description of the illustration hwz22.gif
  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 on 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".

    Figure 12-11 Hierarchy Wizard: Step 3 dialog


    Text description of hwz32.gif follows.
    Text description of the illustration hwz32.gif

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

  1. 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.

    Hint: 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").

  2. Click Next to open the "Hierarchy Wizard: Step 4".

    Figure 12-12 Hierarchy Wizard: Step 4 dialog


    Text description of hwz42.gif follows.
    Text description of the illustration hwz42.gif
  3. Specify a name and description for the new date hierarchy.

  4. (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.

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

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

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 the "Edit Hierarchy dialog: Name tab".

    Figure 12-13 Edit Hierarchy dialog: Name tab


    Text description of hiedn1.gif follows.
    Text description of the illustration hiedn1.gif
  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: Items tab" or "Edit Hierarchy dialog: Name tab".

  3. Click OK.

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 12-14 Edit Hierarchy dialog: Name tab


    Text description of hiednd.gif follows.
    Text description of the illustration hiednd.gif
  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: Date formats tab" or "Edit Hierarchy dialog: Name tab".

  3. Click OK.

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:

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:

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".

    Hint: 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 Chapter 8, "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.

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.

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 12-15 The Impact dialog


      Text description of impacta.gif follows.
      Text description of the illustration impacta.gif

      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 (i.e. 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.

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

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

After upgrading to Oracle 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 (i.e. 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 Chapter 13, "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: Date formats tab".

  4. 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.

Once you have applied 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'.

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 (i.e. based on columns from the emp table) and subsequently create a hierarchy between the levels, complete the following task:

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 (e.g. 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 

7499 

7698 

7521 

7698 

7566 

7839 

7654 

7698 

7698 

7839 

7782 

7839 

7788 

7566 

7839 

 

7844 

7698 

7876 

7788 

7900 

7698 

7902 

7566 

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

For more information about the CONNECT BY statement, refer to the Oracle 8i SQL Reference, Volume 2.

  1. 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.

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

    In this example, you create four complex folders.

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

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

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

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

    Level = 3.

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

    Hint: 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.

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

  6. Create another complex folder and give it an appropriate name (e.g. Chain of Command).

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

  7. 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.

    Hint: For each Empno item that you drag from one of the folders Level1, Level2, Level3 and Level4 you must rename each appropriately (e.g. 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 

  8. 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.


Go to previous page Go to next page
Oracle
Copyright © 1996, 2003 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Table Of Contents
Contents
Go To Index
Index