14
Hierarchies

This chapter consists of the following sections:

• 14.1 Introduction

• 14.2 Creating Hierarchies

• 14.3 Editing Hierarchies

• 14.4 Editing Date Hierarchy Templates

• 14.5 Applying Date Hierarchy Templates to Date Items

• 14.6 Setting the Default Date Hierarchy Template

• 14.7 Deleting Hierarchies

• 14.8 Date Hierarchies and Performance

14.1 Introduction

14.1.1 What is a Hierarchy?

A Hierarchy is a logical linking that you define between Items that enables Discoverer Plus users to:

• drill up (to a greater level of aggregation) and

• drill down (to a greater level of detail).

Hierarchical relationships are not defined in the database; you create them in the Business Area. Discoverer Hierarchies mimic data relationships from the end user's perspective instead of from the database perspective.

14.1.2 Types of Hierarchy

There are two types of Hierarchy in Discoverer Administration Edition:

• Item Hierarchies

• Date Hierarchies

14.1.2.1 Item Hierarchies

An example Item Hierarchy is:

Country > Region > District > Retail Store

If you implement this Item Hierarchy in a Business Area, and an end user has a report that looks at sales from a country perspective, they can drill down (using the Item Hierarchy) to get regional details, and so on right down to the retail store level.

Figure 14-1 shows this Item Hierarchy from the end user's perspective.

Figure 14-1 Sample Item Hierarchy--End User's Perspective

Figure 14-2 shows the same Hierarchy from the database perspective.

Figure 14-2 Sample Item Hierarchy--Database Perspective

When an Item Hierarchy is included in a query expressed in units such as Dollar Sales, the units are appropriately aggregated to reflect the value for that level of Hierarchy.

14.1.2.2 Date Hierarchies

An example Date Hierarchy is:

Year > Quarter > Month > Week > Day

If you implement this Date Hierarchy in a Business Area, and an end user has a report that looks at total sales for each year in their records, they can drill down (using the Date Hierarchy) to get sales per quarter, and so on right down to the sales per day level.

You can use Discoverer Administration Edition's existing Date Hierarchy templates to define many common Date Hierarchies, or you can create your own customized Date Hierarchies.

Figure 14-3 shows a Date Hierarchy from the end user's perspective and how the data could be viewed based on the Sales_Date.

Figure 14-3 Sample Date Hierarchy--Database Perspective

The date column in this sample is Sales_Date.

14.1.3 Date Hierarchy Templates

A Date Hierarchy Template enables you to define a Date Hierarchy which you can apply to Date Items as required. It is much faster to apply a Date Hierarchy Template to Date Items than to redefine the same Date Hierarchy for each Date Item.

Discoverer Administration Edition includes a default Date Hierarchy Template for drilling from year to quarter to month to day: YYYY > "Q"Q > MON > DD.

14.2 Creating Hierarchies

This section consists of the following topics:

• 14.2.1 Creating Item Hierarchies

• 14.2.2 Creating Date Hierarchies

14.2.1 Creating Item Hierarchies

This section describes how to create an Item Hierarchy.

1. Start the Hierarchy Wizard.

   There are three ways to do this:

   • Toolbar Icon
     Click the New Hierarchy toolbar icon ()

   • Menu
     Choose Insert | Hierarchy.

   • Popup Menu
     Right-click anywhere on the Hierarchies page of the work area and choose New Hierarchy... on the popup menu.

2. Select Item Hierarchy.

3. Click Next.

   This opens Hierarchy Wizard: Step 2 (see Figure 14-4).

Figure 14-4 Hierarchy Wizard: Step 2

4. Move the Items that you want to include in this Item Hierarchy from the list on the left to the list on the right.

   There are three ways of moving Items from one list to the other:

   • Drag & Drop
     Drag one or more Items from one list to the other.

   • Include / Exclude buttons
     Select one or more Items in a list and then click either the Include (right arrow) or Exclude (left arrow) button.

   • Double-click
     Double-click an Item to move it from one list to the other.

   To select more than one Item at once, hold down Ctrl while you click on the Items.

   You can select Items from within multiple Folders. However, the Folders must be joined. If the Folders are joined with more than one Join, the Choose Join dialog box opens. Use this dialog box to select the correct Join for the Item Hierarchy.

   The order of items in the hierarchy list determines the drilldown sequence that the end user will use to analyze the data. By default, the Item Hierarchy is arranged in the order that you include the Items.

5. If you need 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 up in the Hierarchy.

   • Click Demote to move the Item lower down in the Hierarchy.

   If you want to group two or more Items so that they appear on the same level of the Hierarchy, select the Items (To select more than one Item at once, hold down Ctrl when you click on the Items) and click Group.

6. If you want to rename the Item as it appears in the Item Hierarchy, click on the Item in the right-hand list and specify the new name in the Name field.

   The name specified here is the label the user sees in Discoverer Plus. By default, each level of the Item Hierarchy uses the Item name.

7. Click Next.

   This opens Hierarchy Wizard: Step 3 (see Figure 14-5).

Figure 14-5 Hierarchy Wizard: Step 3

8. Specify a name for the new Item Hierarchy.

9. Specify a description for the new Item Hierarchy.

10. Click Finish.

This creates the Item Hierarchy and lists it on the Hierarchies page.

14.2.2 Creating Date Hierarchies

This section describes how to create a Date Hierarchy. You can only create Date Hierarchies if you are using an Oracle database.

1. Start the Hierarchy Wizard.

   There are three ways to do this:

   • Toolbar Icon
     Click the New Hierarchy toolbar icon ()

   • Menu
     Choose Insert | Hierarchy.

   • Popup Menu
     Right-click anywhere on the Hierarchies page of the work area and choose New Hierarchy... on the popup menu.

2. Select Date Hierarchy.

3. Click Next.

   This opens Hierarchy Wizard: Step 2 (see Figure 14-4).

Figure 14-6 Hierarchy Wizard: Step 2

4. Move the Date Formats that you want to include in this Date Hierarchy from the list on the left to the list on the right.

   There are three ways of moving Date Formats from one list to the other:

   • Drag & Drop
     Drag one or more Date Format from one list to the other.

   • Include / Exclude buttons
     Select one or more Date Format in a list and then click either the Include (right arrow) or Exclude (left arrow) button.

   • Double-click
     Double-click an Date Format to move it from one list to the other.

   To select more than one Date Format at once, hold down Ctrl while you click on the Date Formats.

5. If you need to move a Date Format in the Hierarchy to a different position, select the Date Format in the right-hand list and:
   • Click Promote to move the Date Format higher up in the Hierarchy.

   • Click Demote to move the Date Format lower down in the Hierarchy.

6. If you want to rename the Date Format as it appears in the Date Hierarchy, click on the Date Format in the right-hand list and specify the new name in the Name field.

   The name specified here is the label the user sees in Discoverer Plus.

7. Click Next.

   This opens Hierarchy Wizard: Step 3 (see Figure 14-7).

Figure 14-7 Hierarchy Wizard: Step 3

8. Move the Date Items that you want this Date Hierarchy applied to, from the Available Items list to the Selected Items list.

   There are three ways of moving Items from one list to the other:

   • Drag & Drop
     Drag one or more Items from one list to the other.

   • Include / Exclude buttons
     Select one or more Items in a list and then click either the Include (right arrow) or Exclude (left arrow) button.

   • Double-click
     Double-click an Item to move it from one list to the other.

   To select more than one Date Format at once, hold down Ctrl while you click on the Date Formats.

   If you only want to create a Date Hierarchy Template (without applying it to a Date Item), don't select any Date Items on this page.

9. Click Next.

   This opens Hierarchy Wizard: Step 4 (see Figure 14-8).

Figure 14-8 Hierarchy Wizard: Step 4

10. Specify a name for the new Date Hierarchy.

11. Specify a description for the new Date Hierarchy.

12. If you want this Date Hierarchy Template to be the default, tick Set as default date hierarchy, otherwise clear it.

    The default Date Hierarchy Template, appears as the default selection in the Date hierarchies, using: drop-down list under Automatically generate: on Load Wizard: Step 4. For more information, see Section 7.2.2.6, "Load Wizard: Step 4, Automatic Attributes."

13. Click Finish.

This creates the Date Hierarchy Template and applies it to the Date Items that you selected on Hierarchy Wizard: Step 4. These Date Hierarchies and the Date Hierarchy Template itself are listed on the Hierarchies page.

14.2.3 Date Format and Date Format Masks

14.2.3.1 Date Format

A Date Format differs from a Date Format Mask in that a Date Format truncates a date into a standard format (whereas a Date Format mask only affects the display characteristics of a date). A truncated date is one that represents a period (E.g. the whole period of the year 2000 will be represented in Discoverer as the date 01-JAN-00).

A user can then apply a condition to the truncated date to return all the records within a period. For example, truncating a date item as eul_date_trunc(shipdate, "YY") = 01-JAN-00 will return only the records for the year 2000.

14.2.3.2 Date Format Mask

A date format mask (displayed in the item properties sheet) influences the way that a date is displayed to the user - it has no effect on the way the date is stored. Therefore, if you have a format mask defined as "Q", you will be able to display the quarter, but to apply a condition you would need to also apply a corresponding date format to return the results you expect, for example eul_date_trunc(shipdate, "Q").

A more subtle aspect of this is that the standard Oracle date format includes time (DD-MON-YY:HH24:MI:SS). When Discoverer loads dates in, it gives them a date Format MASK of DD-MON-YY (i.e. without the time). If a user then applies a condition on dates where time is actually stored in the database then the time component will be included in the condition, even though the Format MASK says it is not. If this is the case, the solution is to change the item definition and truncate the date item itself to just DD-MON-YY.

14.2.3.3 The EUL_DATE_TRUNC function

The function EUL_DATE_TRUNC (used by the Date Format) enables you to truncate a date value to the format specified in the format mask, and keep it as a DATE data type. This has several advantages:

• It enables you to have formats such as YYYY-QU.

• It ensures that when you specify a quarter format the item knows which Year the quarter is in.
  For Example:
  If you apply Eul_date_trunc(Date,'YYYY') to the dates 25-Aug-1934, 11-nov-1934 & 03-feb-1933 we would produce 01-jan-1934,01-jan-1934 & 01-jan-1933 respectively. Hence we have produced the same value for the 1st 2 dates because they were the same in the elements of the date asked for in the format mask.

14.3 Editing Hierarchies

This section describes how to edit an existing Hierarchy.

1. Display the Edit Hierarchy dialog box (see Figure 14-9).

   There are two ways to do this:

   • Popup Menu
     Right-click the Hierarchy on the Hierarchies page and choose Edit Hierarchy... on the popup menu.

   • Menu
     Click the Hierarchy on the Hierarchies page and choose Edit | Edit...

Figure 14-9 The Edit Hierarchy Dialog Box with the Name Page Selected

2. Edit the Hierarchy as required.

   The Edit Hierarchy dialog box is divided in to two pages.

   • Items
     Use this tab to add or remove the Items that use this Hierarchy.

   • Name
     Use this page to edit the Hierarchy's name and description.

   For more information on the fields on this dialog box, click Help.

3. Click OK.

14.4 Editing Date Hierarchy Templates

This section describes how to edit an existing Date Hierarchy Template. When you edit an existing Date Hierarchy Template, all Date Hierarchies that use the Date Hierarchy Template are modified to reflect the changes.

1. Display the Edit Hierarchy dialog box (see Figure 14-9).

   There are two ways to do this:

   • Popup Menu
     Right-click the Date Hierarchy Template on the Hierarchies page and choose Edit Hierarchy... on the popup menu.

   • Menu
     Click the Date Hierarchy Template on the Hierarchies page and choose Edit | Edit...

Figure 14-10 The Edit Hierarchy Dialog Box with the Name Page Selected

2. Edit the Hierarchy as required.

   The Edit Hierarchy dialog box is divided in to two pages.

   • Date formats
     Use this tab to change the Date Formats and their position in this Date Hierarchy Template.

   • Name
     Use this page to edit the Date Hierarchy Template's name and description.

   For more information on the fields on this dialog box, click Help.

3. Click OK.

14.5 Applying Date Hierarchy Templates to Date Items

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 Administration Edition 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 (and are prefixed with the name of the original Date Item). If you change the Date Hierarchy Template that is applied to a Date Item, all the Date Items that had been created by Discoverer Administration Edition to complete the Date Hierarchy are removed and new ones are created to reflect the new Date Hierarchy Template.

To apply a Date Hierarchy Template to a Date Item:

1. Open the Date Item's Properties dialog box.

   There are four ways to do this:

   • Double-click
     Double-click the Date Item on the Data page.

   • Popup Menu
     Right-click the Date Item on the Data page and choose Properties on the popup menu.

   • Toolbar Icon
     Click the Date Item on the Data page and click the Properties toolbar icon ()

   • Menu
     Click the Date Item on the Data page and choose Edit | Properties.

2. Select the Date Hierarchy Template (from the Date hierarchy drop-down list) that you want this Date Item to use.

   If you want the Date Item to use the default Date Hierarchy Template, select None.

3. Click OK.

14.6 Setting the Default Date Hierarchy Template

This section describes how to set the Default Date Hierarchy Template.

The default Date Hierarchy Template, appears as the default selection in the Date hierarchies, using: drop-down list under Automatically generate: on Load Wizard: Step 4. For more information, see Section 7.2.2.6, "Load Wizard: Step 4, Automatic Attributes." This is the only effect of setting

To set the Default Date Hierarchy Template:

1. Display the Edit Hierarchy dialog box (see Figure 14-9).

   There are two ways to do this:

   • Popup Menu
     Right-click the Date Hierarchy Template on the Hierarchies page and choose Edit Hierarchy... on the popup menu.

   • Menu
     Click the Date Hierarchy Template on the Hierarchies page and choose Edit | Edit...

2. Tick Set as default date hierarchy on the Name page.

3. Click OK.

14.7 Deleting Hierarchies

This section describes how to delete Hierarchies or Date Hierarchy Templates.

1. On the Hierarchies page, select the Hierarchy (or Hierarchies) that you want to delete.

   To select more than one Hierarchy at once, hold down Ctrl while you click on the Hierarchies.

2. Delete the Hierarchy (or Hierarchies):

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected Hierarchies and choose Delete Hierarchy... on the popup menu.

   • Menu
     Choose Edit | Delete.

   • Keyboard
     Press Delete.

   This opens the Confirm Delete dialog box.

3. Click Impact.
   This displays the Impact dialog box that shows any other objects that may be affected by the deletion (Figure 14-11). The Impact dialog box helps you to make the right choice.

Figure 14-11 The Impact Dialog Box

4. When you have finished reviewing the impact this action will have, click OK.

5. If you still want to delete the selected Hierarchies, click Yes.

14.8 Date Hierarchies and Performance

Default date hierarchies can be automatically applied to all date columns when selected in the Load Wizard during bulk load (see Chapter 7.2.2.6, "Load Wizard: Step 4, Automatic Attributes").

How date hierarchies are applied to date items can affect subsequent performance in Discoverer Plus. For example if you are importing a large fact table (i.e. one containing many rows) that also has a date column in it (e.g. transaction_date), the default date hierarchy can then be applied to the date item during bulk load.

If you have applied a date hierarchy to a fact table containing a date, bulk load creates a folder containing date items using the EUL_DATE_TRUNC function (such as `Year', Quarter' and `Month'). If these items are subsequently selected in a query using Discoverer Plus, any indexes applied to the fact table that improve performance will not be used.

It is therefore recommended that you do not apply date hierarchies to date items in folders based on fact tables as fact tables are likely to have indexes. To overcome this problem 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') specifying time periods. Apply date hierarchies to this dimension table on bulk load. You are then able to create a complex folder containing items from both the dimension and fact tables, using the items created by the date hierarchy such as `Year'. When a Discoverer Plus user queries items using the EUL_DATE_FUNCTION they will not restrict the use of indexes on the fact table. This can considerably improve performance.

The general rule is do not apply default date hierarchies to date items on tables that rely on indexes for performance because the indexes will not be used.