13
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?"

• "What are item hierarchies?"

• "What are date hierarchies?"

• "How to create item hierarchies"

• "How to create date hierarchies and templates"

• "How to edit item hierarchies"

• "How to edit date hierarchy templates"

• "How to apply a date hierarchy template to a date item"

• "How to set the default date hierarchy template"

• "How to delete item hierarchies or date hierarchy templates"

• "How to modify default date hierarchies that use date format 'RR' following upgrade to Oracle9i Discoverer Administrator"

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 (e.g. Store to City to Region to Country)

• drill down to a finer level of detail (e.g. 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 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 13-1 Sales item hierarchy

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

Text description of the illustration hiuser.gif

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

Figure 13-3 Sales item hierarchy - database perspective

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 13-4 Sales date hierarchy

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

Text description of the illustration hiedtexu.gif

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

Figure 13-6 Sample date hierarchy - database perspective

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 13-7 Date hierarchy template

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 9, "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 13-8 Hierarchy Wizard: Step 2

   
   
   Text description of the illustration hwz21.gif

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

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

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

   Figure 13-9 Hierarchy Wizard: Step 3

   
   
   Text description of the illustration hwz31.gif
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.

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 13-10 Hierarchy Wizard: Step 2

   
   
   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 13-11 Hierarchy Wizard: Step 3

   
   
   Text description of the illustration hwz32.gif

   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.

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

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

   Figure 13-12 Hierarchy Wizard: Step 4

   
   
   Text description of the illustration hwz42.gif
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 under the Automatically generate: option. For more information, see "Load Wizard: Step 4 dialog".

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

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

   
   
   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:

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

   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 9, "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) You can review the objects that might be affected by deleting this hierarchy or hierarchy template. To see the potential impact of the deletion:
   1. Click Impact to display the "Impact dialog".

      The Impact dialog shows other objects that might be affected by the deletion and helps you to make the right choice.

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

      Figure 13-15 The Impact dialog

      
      
      Text description of the illustration impacta.gif
   3. When you have finished reviewing the impact of deleting the hierarchy or hierarchy template, click OK to close the Impact dialog.

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

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

After upgrading to Oracle9i 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 14, "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'"

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

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