4
Tutorial

The Custom Folder dialog should look similar to Figure 4-22.

Figure 4-22 The Custom Folder Dialog

5. Click Validate SQL.

   A prompt displays advising whether the SQL string is valid.

   • If it is not valid, click OK to close the prompt. Then correct the string and click Validate SQL again.

   • If it is valid, click OK to close the prompt.

   • Click OK again to close the Edit Custom Folder dialog and create your custom folder.

The custom folder is now listed in the business area. Note the SQL label on the folder icon. This identifies the custom folder as distinct from simple or complex folders.

Figure 4-23 Business Area with Custom Folder

4.6.2 Editing The Custom Folder's SQL

1. Right-click the Alternate Sort Days custom folder on the Data page to open the Custom Folder Properties dialog.

2. Choose Properties on the popup menu.

   The Custom SQL field contains the SQL statement that defines the folder. You can resize the Custom Folder Properties dialog to view the entire statement.

Figure 4-24 Custom Folder Properties Dialog

3. Click in the Custom SQL field.

   This opens the Edit Custom Folder dialog which contains the SQL statement (see Figure 4-25).

   Do not edit the statement; the purpose of this step is to show how to access the Edit Custom Folder dialog box.

Figure 4-25 Edit Custom Folder Dialog

4. Click Cancel to close the Edit Custom Folder dialog.

5. Click Cancel to close the Custom Folder Properties dialog.

The next lesson shows you how to create joins between folders, this is described in Section 4.8.

4.7 Lesson 7: Creating Joins

Data analysis often requires information that resides in more than one folder. In order for the analysis to occur, the folders must be linked by a join. Joins are part of both the database and the business area design. As the Discoverer administrator you have the option to create joins that provide the information that the end user needs for business analysis.

In this lesson you will learn how to create the joins that are required in the business area.

This lesson consists of the following exercise:

• 4.7.1 Creating Joins for Folders in the Business Area

4.7.1 Creating Joins for Folders in the Business Area

To achieve this you will establish joins to relate the data between the folders that you loaded in Lesson 2 Section 4.2.3, "Selecting Tables and Views to Load into the Business Area".

Now you will create joins for each folder, using a Key from the Master table and joining to the corresponding Key of the Detail table.

1. Click the (+) symbol next to the Product Information folder on the Data page

2. Open the New Join dialog.

   There are three ways of doing this:

   • Popup Menu
     Right-click the Product Information.Product Key item on the Data page and choose New Join... on the popup menu.

   • Toolbar Icon
     Click the Product Information.Product Key item on the Data page and click the New Join toolbar icon ().

   • Menu
     Click the Product Information.Product Key item on the Data page and choose Insert | Join.

   This opens the New Join dialog box with Product Information.Product Key as the master item.

Figure 4-26 Creating a Join Between Two Folders

3. Click the Detail Folder drop-down list.

   This opens the second New Join dialog box which lists the folders in the New Video Stores business area.

4. Select Sales Details.Product Key.

   The New Join dialog box should look similar to Figure 4-27.

Figure 4-27 Selecting the Detail Item

5. Click OK.

   The New Join dialog (Figure 4-26, "Creating a Join Between Two Folders") should now show Sales Details.Product Key in the Detail Item field.

Figure 4-28 Join is now ready to create

6. Select the equal sign from the Operator drop-down list.

   This list shows the types of joins that you can create. The equal sign represents an equi-join, which combines rows that have equivalent values for the specified items.

7. Enter a name for this join.
   If you don't enter a name Discoverer Administration Edition will automatically create a default name for the join when you click OK.

8. Enter a description.

9. Click OK.
   The join appears as an item in the Product Information folder. Note the direction of the icon next to it, indicating that the Product Information.Product Key is the master item and the Sales Details.Product Key is the detail item. In this one-to-many relationship, for each product record there are many sales details records.

10. Now repeat ALL the steps of this exercise to create joins for the following Folders:
    - Store Information
    - Time Information
    
    NOTE: When creating joins for each of the Folders above, you need to use the appropriate Master and Detail Folder/Key values as shown in Table 4-2 below (refer also to Figure 4-28 for field names):
    

Folder Name	Master Folder/Key required	Detail Folder/Key required
Store Information:	Store Information.Store Key	Sales Details.Store Key
Time Information:	Time Information.Time Key	Sales Details.Time Key

Now that you have created joins between the Product Information, Store Information, Time Information and the Sales Details folders, they can be used for analysis in Discoverer Plus.

For more information about creating and editing joins, read Chapter 11, "Joins"

The next lesson describes how you can customize items in a business area, described in Section 4.8.

4.8 Lesson 8: Customizing Items

The Discoverer Administrator's concern is not only what the user views in Discoverer Plus, but that data is presented in a way that makes the information more readable and, therefore, easier to analyze. In this lesson you will learn how to make certain elements invisible, design the axis structure, define lists of values and alternative sorts for them, and how to create derived items and aggregated calculations that are useful for queries and reports.

This lesson consists of the following exercises:

• 4.8.1 Hiding Items in the Business Area

• 4.8.2 Setting the Display Axis for Items and Display Sequence

• 4.8.3 Creating Lists of Values

• 4.8.4 Creating an Alternative Sort

• 4.8.5 Creating a New Calculated Item

4.8.1 Hiding Items in the Business Area

End users may not need to see all the items in a business area. For example, primary and foreign keys, sensitive information such as pay scale and time in service, and some items used in calculations might be inappropriate. The items listed in Table 4-3 are essential to the business area as they are used in join conditions, but they are of no interest to the business user.

Hiding items does not delete them. Deleted items are removed from the business area, whereas hidden items remain in the business area, but are not visible to the end user.

In general it is a good idea to hide all items that users do not need to query. This makes the list of items presented to the user shorter, making it easier to find what they are looking for.

In the New Video Stores business area, typical items that would be hidden are shown in Table 4-3:

Table 4-3 Hidden Items

To hide the key items from the end users:

1. Select the following items:
   • Product Information.Product Key

   • Time Information.Time Key

   • Store Information.Store Key

   • Sales Details.Time Key

   • Sales Details.Product Key

   • Sales Details.Store Key

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

2. Right-click one of the selected items and choose Properties from the popup menu.

   This opens the Item Properties dialog box showing the properties that each of the selected items have in common.

3. Set Visible to user to No.

4. Click OK.

Note that the items that are hidden from the user are now shown in grayed text.

4.8.2 Setting the Display Axis for Items and Display Sequence

A crosstab worksheet in Discoverer Plus can display data along three axes named top, side, and page (see Figure 4-29, "Rearranging the Display Axes in Discoverer Plus"). In Discoverer Administration Edition, you can specify the default axis for each item. Specifying a default axis does not prevent the user from switching an item from one axis to another during data analysis.

Set the default position of Store Information.Region to the side axis:

1. Open the Item Properties dialog box for the Store Information.Region item.

   There are three ways of doing this:

   • Double-click
     Double-click the Store Information.Region item on the Data page.

   • Popup Menu
     Right-click the Store Information.Region item on the Data page and choose Properties on the popup menu.

   • Menu
     Click the Store Information.Region item on the Data page and choose Edit | Properties.

2. Set Default position to Side.

3. Click OK.

In Discoverer Plus, the user can rearrange the axes to override the settings you create in Discoverer Administration Edition. Figure 4-29 shows the Workbook Wizard, Step 3 (in Discoverer Plus), where the user can rearrange the axes.

Figure 4-29 Rearranging the Display Axes in Discoverer Plus

Figure 4-30 A Sample Workbook showing the Axes in Discoverer Plus

Key to Figure 4-30 showing how the axes look in the Discoverer Plus worksheet:

1. Page axis

2. Top axis

3. Side axis

4.8.3 Creating Lists of Values

Lists of values are created using an `Item Class'. Most of the item classes for this tutorial were generated automatically when you loaded the EUL in "Lesson 2: Using the Load Wizard". However, you can create new item classes that contain lists of the unique data values in an item.

In your New Video Stores business area, it would be useful to create a list of values to show each department name, for example.

1. Click the Item Classes tab in the work area.

2. Open the Item Class Wizard.

   There are three ways of doing this:

   • Popup Menu
     Right-click the New Video Stores business area (on the Item Classes page) and choose New Item Class... on the popup menu.

   • Toolbar Icon
     Click the New Video Stores business area (on the Item Classes page) and click the New Item Class toolbar icon ().

   • Menu
     Click the New Video Stores business area (on the Item Classes page) and choose Insert | Item Class....

3. Under Select item class attributes:
   • Tick List of values.

   • Clear Alternative sort.

   • Clear Drill to detail.

   The Item Class Wizard should look similar to Figure 4-31.

Figure 4-31 Item Class Wizard

4. Click Next.

   This opens Item Class Wizard: Step 2.

Figure 4-32 Selecting the Item that Generates the List of Values

5. Select the Product Information.Department item.
   This will be the source used to create a list of values for the new item class

6. Click Next.

   This opens Item Class Wizard: Step 3, as shown in Figure 4-33.

Figure 4-33 Selecting the Items that Use the Item Class

7. Click Next.

   This opens Item Class Wizard: Step 4.

8. Specify the Item class name as Departments.

9. Specify the Item class description as Video Store Sales and Rentals.

   The Item Class Wizard: Step 4 should look similar to Figure 4-34.

Figure 4-34 Naming and Describing the New Item Class

10. Click Finish.

    This creates a new item class named Departments.

The Item classes page of the work area enables you to view the following information about each of the item classes:

• A list of values that form the item class

• A list of items that use the item class

To view this information for the Departments item class:

1. Expand the New Video Stores business area on the Item classes page of the work area (click the plus (+) sign).

2. Expand the new Departments item class.

3. Expand List of Values.

   To build a list of values, Discoverer Administration Edition needs to read every row of the relevant table from the database. If the table has a large number of rows, it can take a long time to build the list of values. Discoverer Administration Edition warns you of this and asks you if you want to continue.

   Click Yes.

4. Expand Items using this Item class.

   The Item Classes page should look similar to Figure 4-35.

Figure 4-35 Item Classes Tab with New Item Class and List of Values
4

What the User Sees--Selecting from a List of Values in Discoverer Plus

In Discoverer Plus, the user sees the list of values created in Discoverer Administration Edition, and can use this list of values to apply a condition.

Figure 4-36 Selecting Item Values from a List of Values in Discoverer Plus

When creating a worksheet, a user creates a condition by selecting the specific item values from the list of values in the `Selected' column above.

4.8.4 Creating an Alternative Sort

An alternative sort enables users to sort values in a non-standard fashion. For example, standard sorts include alphabetical, numerical, or chronological sorts, whereas a non-standard sort might be by region, such as North=1, South=2, East=3, and West=4 or the days of the week. An alternative sort sorts an item by using a sequence provided by another item (does not apply to ODBC databases).

In this exercise we will create an Alternative Sort for the Days of the Week item in the Time Information folder. To do this we will create a new Item Class that defines an alternative sort and apply it to the Days of the Week item. The alternative sort that we will use is Alternate Sort Days, a custom folder we created previously (see Section 4.6, "Lesson 6: Designing a Custom Folder").

1. From the menu select Insert | Item Class
   This displays the item class wizard:

Figure 4-37 Item Class Wizard - Select Alternative Sort

2. Select the Alternative Sort check box

3. Click Next
   This displays Step 2 of the item class wizard:

Figure 4-38 Item Class Wizard - Select Item for List of Values

4. Select Day of the Week from the Alternate Sort Days Custom Folder
   This is the item that will generate the list of values.

5. Click Next
   This displays Step 3 of the item class wizard:

Figure 4-39 Item Class Wizard - Select Item containing Alternative Sort sequence

6. Select the Alternate Sort Item
   This item is the one that contains the alternative sort sequence.

7. Click Next
   This displays Step 4 of the item class wizard:

Figure 4-40 Item Class Wizard - Select the Items that use this Item Class

In the Item Class Wizard Step 4 you select the item(s) that will use the alternative sort sequence. In this lesson you will choose an item from the Time Information folder.

8. Open the Time Information folder from the New Video Stores business area.

9. Select the Day of the Week item from the Available Items list.

10. Click the right arrow to move the Day of the Week item into the Selected Items list

11. Click Next
    This displays Step 5 of the item class wizard:

Figure 4-41 Item Class Wizard - Name the Item Class

12. Enter Days of the Week - Alternative Sort

13. Click Finish
    This creates a new Item Class called Days of the Week - Alternative Sort used by the Days of the Week Item in the Time Information folder

To see the new alternative sort in the Days of the Week item carry out the following steps:

1. Return to the Data tab and click on the Time Information folder

2. Expand the Day of the Week item to retrieve the values
   The list of values using the new Alternative Sort is displayed.

Figure 4-42 Alternative Sort - View result in Day of Week folder

The days of the week can now be seen displayed in the sequence defined in the complex folder Alternate Sort Days.

4.8.5 Creating a New Calculated Item

Calculated items can be a critical part of a report. Typical business calculations include values such as:

• profit margins,

• average revenues per month,

• expected sales

• percent of profit by product type.

You can improve the usability of the business area, by defining the calculations that an end user is likely to need. Discoverer Administration Edition stores these calculations in the EUL for use in Discoverer Plus.

For further information on calculations see Chapter 12.1.1, "What is a Calculated Item?".

Building a Calculation

This exercise shows you how to produce a calculation of profit as a percentage of sales.

1. Create a new item in the Sales Details folder.

   There are three ways of doing this:

   • Popup Menu
     Right-click the Sales Details folder (on the Data page) and choose New Item... on the popup menu.

   • Toolbar Icon
     Click the Sales Details folder (on the Data page) and click the New Item toolbar icon ().

   • Menu
     Click the Sales Details folder (on the Data page) and choose Insert | Item....

   This opens the New Item dialog box. The left-hand side lists the Sales Details folder and the items it contains.

2. Specify Percent Profit as the Name.

3. Specify SUM(Sales Details.Profit)/SUM(Sales Details.Sales) in as the Calculation.

   You can type the calculation directly or you can use some of these ways to minimize the amount of typing you need to do:

   • To add items, select the items from the list on the left and click the Paste >> button to paste them directly into the calculation.

   • To add an operator, click the operator buttons below the Calculation area.

   • To display a list of database functions, click the Functions button.

   NOTE: Calculations follow the Oracle calculation standard syntax. For a full description of the Oracle calculation syntax, see the Oracle SQL Language Reference Manual.

   The New Item dialog should look similar to Figure 4-43.

Figure 4-43 New Item Dialog

Key to Figure 4-43:

1. Calculation Panel

2. Operator buttons

4. Click OK.
   This saves the new calculation in the business area.

Examples of Other Calculations

1. To derive an address record.

   For example: Sidney Sloan, 21 Great Jones Street Apt. 2B, New York City, New York 10012

   • Name: Address

   • Calculation: Name || "," || Street Line 1 || Street Line 2 || "," || City || "," || State || Zip Code

2. To derive a personnel record.

   For example: Business Analyst in Accounting Department, $50,000

   • Name: Function

   • Calculation: Job Title || "in" || Department || "," || Salary

3. To derive annual compensation based on monthly salary and commission. (NVL permits a zero value.)
   • Name: Compensation

   • Calculation: Salary*12+NVL(Commission, 0)

Examples of Aggregated Items

1. Name: Total Compensation

   Calculation: SUM(Salary + NVL(Commission, 0))

2. Name: % Commission

   Calculation: SUM(Commission)/SUM(Salary)

3. Name: Avg. Units per Customer

   Calculation: SUM(unit_sales)/SUM(customer_count)

For more detailed information about creating calculations, see Chapter 12, "Calculations".

What the User Sees--Calculations in Discoverer Plus

Figures 4-44 and 4-45 are samples of Discoverer Plus windows showing how the end user will see and use the calculated items you create in Discoverer Administration Edition.

Figure 4-44 Discoverer Plus Sample: Percent Profit Item in the Worksheet Wizard

Percent Profit, a calculated item created in Administration Edition, is shown in Figure 4-44 with all of the other items in the Sales Details folder.

The user could create a worksheet in Discoverer Plus using the Workbook Wizard. A Table style report could be chosen using the New Video Stores Business Area. The user could select items such as Profit SUM, Sales SUM, Percent Profit from the Sales Details folder and City from the Store Information folder. The Percent Profit item (a calculated item, not an actual column) is displayed with all the other items in the Sales Details folder.

When the user runs the query, Percent Profit is displayed in the report.

Figure 4-45 The Percent Profit Report with Percent Profit Calculated

Including the Percent Profit calculated item in the report above causes Discoverer Plus to perform the calculation on each row and display the results in a column.

To control the display format of numbers for the Percent Profit item the number format was set to the first option of the `Percent' category list in Discoverer Plus through the menu option Sheet | Format | Format Data | Number.

The next lesson takes you through creating a complex folder that is made up of items from other folders in a business area; this is described in Section 4.9.

4.9 Lesson 9: Designing a Complex Folder

A complex folder is a special type of folder which contains items from other folders. When you place an item from a simple folder into a complex folder, you are really making a reference to the original item.

You can simplify the end user's data analysis tasks by gathering items from separate folders into one complex folder. This avoids the need for a user to select items from multiple folders and completely hides joins from users.

Discoverer Administration Edition will not allow you to add an item to a complex folder if the item does not have a join associating it with the other items in the complex folder.

For more information on this topic, see Chapter 11, "Joins"

This lesson consists of the following exercises:

• 4.9.1 Creating a Complex Folder

• 4.9.2 Creating a Condition

4.9.1 Creating a Complex Folder

The following exercise shows you how to create a complex folder that the end user can use to analyze profit, product type, product category, and other pertinent information associated with our hypothetical video stores.

1. Create a new folder in the New Video Stores business area.

   There are three ways of doing this:

   • Popup Menu
     Right-click the New Video Stores business area (on the Data page) and choose New Folder on the popup menu.

   • Toolbar Icon
     Click the New Video Stores business area (on the Data page) and click the New Folder toolbar icon ().

   • Menu
     Click the New Video Stores business area (on the Data page) and choose Insert | Folder | New.

   This creates a new folder titled NewFolder in the New Video Store business area. Note the difference between the icons representing this complex folder and the simple folders.

2. Rename NewFolder1 as Store and Sales Details.

3. In the same way, create another new folder and name it Video Analysis.

To make it easier to copy items from one folder to another, you can open another work area window. This provides you with another view on the same work area. The next few steps show you how to do this.

4. Choose Window | New Window.
   A new work area window opens, providing you with a second view of the same work area (see Figure 4-46).

Figure 4-46 Using Two Work Area Windows

Now you're ready to add some items to your complex folders.

1. In the left work area window, expand the Store Information folder and select the following items:
   • Store Name

   • City

   • Region

   • Floor Plan Type

   • Store Size

   • Reports

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

2. Drag the selected items to the work area window on the right and drop them into the Store and Sales Details complex folder.

   This copies the selected items into the complex folder.

   As the next few steps illustrate, you repeat the process of copying items from other folders to populate the complex folder with all the items your user needs.

3. In the same way, copy the Sales Details.Profit item to the Store and Sales Details complex folder.

4. Copy the following items from the Product Information folder into the Store and Sales Details folder:
   • Description

   • Full Description

   • Product Category

   • Department

5. Copy the following items from the Time Information folder into the Store and Sales Details folder.
   • Transaction Date

   • Year

You are now ready to populate the Video Analysis folder.

1. Copy the following items from the Sales Details folder into the Video Analysis folder.
   • Sales

   • Unit Sales

   • Cost

   • Profit

2. Copy the following items from the Store Information folder into the Video Analysis folder.
   • Store Name

   • City

   • Region

   • Reports

3. Copy the following items from the Product Information folder into the Video Analysis folder.
   • Description

   • Full Description

   • Product Category

   • Department

4. Copy the following items from the Time Information folder into the Video Analysis folder.
   • Transaction Date

   • Year

   • Month

5. Close the second work area window and maximize the first window.

The item classes providing lists of values are now shared between the two complex folders. The items you have copied into the complex folders inherit the properties of the original items. If you delete one of the original items from a folder all references to that item are also deleted.

4.9.2 Creating a Condition

A condition filters retrieved information. For example, you can create a condition for analyzing the chain of video stores that selects only the Video Sale or Video Rentals departments. The user can use this condition in Discoverer Plus to review recent sales activity for each video store in those two departments to determine which stores are most profitable.

This exercise shows you how to create a condition.

1. Create a new condition for the Video Analysis.Department item.

   There are three ways of doing this:

   • Popup Menu
     Right-click the Video Analysis.Department item (on the Data page) and choose New Condition on the popup menu.

   • Toolbar Icon
     Click the Video Analysis.Department item (on the Data page) and click the New Condition toolbar icon ().

   • Menu
     Click the Video Analysis.Department item (on the Data page) and choose Insert | Condition.

   This displays the New Condition dialog box. Note that Item is set to Video Analysis.Department.

2. Set Condition to the IN operator because you want to filter the data to only find video sales and rentals.

3. Select Video Rental from the Values drop-down list.

4. Select Video Sale from the Values drop-down list.

5. Clear Generate a name automatically.
   This enables you to specify your own name for the condition.

6. Change the name of the condition to Department is Video Rental or Video Sale.

   The New Condition dialog box should look similar to Figure 4-47.

Figure 4-47 The New Condition Dialog

7. Click OK.

In Discoverer Plus the user sees the condition represented by a filter icon. Figure 4-48 shows how the condition appears in Discoverer Plus.

Figure 4-48 Viewing the Condition in Discoverer Plus

The next lesson takes you through creating hierarchies that help Discoverer Plus users navigate through data; this is described in Section 4.10.

4.10 Lesson 10: Working with Hierarchies

This lesson shows you how to set up items in a hierarchy so that the user can navigate up and down a hierarchy path to view related information.

Drilling through a hierarchy involves displaying a lesser or greater detail of aggregated information. For example, drilling through sales figures from the entire company to region to district and finally to an individual video store. Each level of the hierarchy reveals greater detail of aggregated information. Users can perform drill-downs in a filtered or unfiltered fashion. For example, users can select a specific data value in an axis item (for example, the year 2000), or they can select all data values for the axis item (for example, all years) and drill down.

You can provide Discoverer Plus users with several methods of drilling into related information. In this section, you'll enable the drill up/down capability.

This lesson consists of the following exercises:

• 4.10.1 Defining a Simple Item Hierarchy

• 4.10.2 Defining a More Complex Item Hierarchy

• 4.10.3 Creating a Date Hierarchy Template

• 4.10.4 Modifying an Item's Content Type

• 4.10.5 Defining Drill to Detail Data

4.10.1 Defining a Simple Item Hierarchy

Some of the data in a business organization is hierarchical in nature. Store totals naturally fold into district sales, district sales fold into regional sales, and so on until the top of the hierarchy is reached.

Defining hierarchies provides users with a very easy way of drilling into summary information without having to know exactly what items make up the hierarchy.

This exercise shows you how to create a simple item hierarchy.

1. Click the Hierarchies tab in the work area.
   This enables you to work with the hierarchies in the New Video Stores business area.

Figure 4-49 The Work Area Window for Hierarchies

2. Create a new hierarchy in the New Video Stores business area.

   There are three ways of doing this:

   • Popup Menu
     Right-click the New Video Stores business area (on the Hierarchies page) and choose New Hierarchy... on the popup menu.

   • Toolbar Icon
     Click the New Video Stores business area (on the Hierarchies page) and click the New Hierarchy toolbar icon ().

   • Menu
     Click the New Video Stores business area (on the Hierarchies page) and choose Insert | Hierarchy....

   This opens the Hierarchy Wizard (see Figure 4-50).

Figure 4-50 The Hierarchy Wizard

3. Select Item Hierarchy

   Item hierarchies are for drilling up and down through character items and numeric items. Date hierarchies are for drilling up and down through date items (Year, Quarter, Month, Week, Day, etc.).

4. Click Next.

The next steps show you how to choose the items that you want end users to see in their drill hierarchies. The position of each item dictates how the drill is displayed in Discoverer Plus.

1. Move the Video Analysis.Region item 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.

2. Move the Video Analysis.City item to the list on the right.

3. Move the Video Analysis.Store Name item to the list on the right.

   The Hierarchy Wizard: Step 2 should look similar to Figure 4-51.

Figure 4-51 Selecting Items for the Hierarchy

4. Click Next.

5. Enter the name Regional Hierarchy.

6. Enter Region-City-Store as the description.

   The Hierarchy Wizard: Step 3 should look similar to Figure 4-52.

Figure 4-52 Naming and Describing the Hierarchy

7. Click Finish.

The Hierarchies page of the work area lists the Regional Hierarchy in the New Video Stores business area. You can expand Regional Hierarchy to view the hierarchy (see Figure 4-53). Users can now navigate through all the levels of the hierarchy, skipping levels if necessary.

Figure 4-53 Regional Hierarchy

What the Discoverer Plus user sees - Navigating the Hierarchy

Figure 4-54 shows the three levels of the hierarchy (Region, City, and Store Name) in a Discoverer Plus worksheet. The Discoverer Plus user does not have to explicitly set up a hierarchy. When an item is selected for a report that is part of a hierarchy, the user can navigate to any other element in the hierarchy via a pop-up menu.

Figure 4-54 Navigating Through Items in a Hierarchy

4.10.2 Defining a More Complex Item Hierarchy

This section shows how to create another, more complex item hierarchy. This example drills into the product details.

1. Create a new hierarchy in the New Video Stores business area.

   There are three ways of doing this:

   • Popup Menu
     Right-click the New Video Stores business area (on the Hierarchies page) and choose New Hierarchy... on the popup menu.

   • Toolbar Icon
     Click the New Video Stores business area (on the Hierarchies page) and click the New Hierachy toolbar icon ().

   • Menu
     Click the New Video Stores business area (on the Hierarchies page) and choose Insert | Hierarchy....

   This opens the Hierarchy Wizard.

2. Select Item Hierarchy

3. Click Next.

4. Move the following items (in the order they are listed) to the list on the right:
   • Product Information.Product Type

   • Product Information.Department

   • Product Information.Product Category

   • Product Information.Description

   • Product Information.Full Description

5. Select the following two items from the hierarchy on the right.
   Do this by selecting the first item, then hold down the Ctrl key and highlight the second item. Both of the following items should now be highlighted:
   • Product Information.Description

   • Product Information.Full Description

6. Click Group

   This groups the selected items so that they appear at the same level in the hierarchy. Grouping items in this manner causes them to be pulled into a user query (from Discoverer Plus) simultaneously. Drilling down from Product Category will display Description and Full Description level at the same time.

   The Hierarchy Wizard: Step 2 should look similar to Figure 4-55.

Figure 4-55 Grouping Items in a Complex Hierarchy.

7. Click Next.

   This opens Hierarchy Wizard: Step 3.

8. Name the hierarchy Product Hierarchy.

9. Enter a description.

10. Click Finish.

The Hierarchies page lists the Product Hierarchy in the New Video Stores business area. If you expand Product Hierarchy, it should look similar to Figure 4-56.

Figure 4-56 Product Hierarchy

4.10.3 Creating a Date Hierarchy Template

Date hierarchy templates enable automatic date hierarchies to be created. For example, a template can be created that enables drilling down from year to month to week to day. Associating a date hierarchy template with a date item automatically creates new items for each date (e.g., year, month, week, day) and defines drill relationships between them.

Discoverer Administration Edition already includes a standard date template which includes the hierarchy of year to quarter to month to day. If that template does not meet the user requirements, you can create a new date hierarchy. For example you might want to drill from year to week rather than month, or even drill to hours minutes and seconds for time based data.

This exercise shows you how to create an alternative date hierarchy template.

1. Create a new hierarchy in the New Video Stores business area.

   There are three ways of doing this:

   • Popup Menu
     Right-click the New Video Stores business area (on the Hierarchies page) and choose New Hierachy... on the popup menu.

   • Toolbar Icon
     Click the New Video Stores business area (on the Hierarchies page) and click the New Hierachy toolbar icon ().

   • Menu
     Click the New Video Stores business area (on the Hierarchies page) and choose Insert | Hierarchy....

   This opens the Hierarchy Wizard.

2. Select Date Hierarchy

3. Click Next.

   This opens Hierarchy Wizard: Step 2.

Figure 4-57 Selecting Date Items

4. Move the following date formats (in the order they are listed) to the list on the right:
   • YYYY (in the Year folder).

   • "Q"Q-YYYY' (in the Quarter folder).

   • Mon-YYYY (in the Month folder).

   • "W"W Mon-YYYY (in the Week folder).

   • Dy "W"W Mon-YYYY (in the Day folder).

   • HH24 Dy "W"W Mon YYYY (in the Hour folder).

5. Click Next.

   This opens Hierarchy Wizard: Step 3. This is where you assign the date hierarchy you've just created to items in the business area.

Figure 4-58 Selecting Items for Date Hierarchies

6. Move Time Information.Transaction Date to the list on the right.

7. Click Next.

8. Enter the name Y-Q-M-W-H Hierarchy and add a description (see Figure 4-59).

Figure 4-59 Naming and Describing the Date Hierarchy

9. Click Finish.

Earlier, in Section 4.9, we copied Transaction Date and Year to the Store and Sales Details complex folder. We also copied Transaction Date, Year and Month to the Video Analysis complex folder.

The Year and Month items were generated automatically from the Transaction Date item by the Load Wizard using the default date hierarchy template.

However, when you apply a new date hierarchy to a date item, the existing date hierarchy is removed. All the Year and Month items (including those copied to the complex folders) were therefore deleted when you applied your new date hierarchy to the Transaction Date item.

The next steps recopy the year and month data into the complex folders:

1. Select the following item on the Data page of the work area:
   • Time Information.Transaction Date YYYY

2. Choose Edit | Copy.

3. Select the Stores and Sales Details complex folder.

4. Choose Edit | Paste.

5. Copy and Paste the following items into the Video Analysis complex folder in the same way:
   • Time Information.Transaction Date YYYY

   • Time Information.Transaction Date Mon YYYY

What the User Sees

In Discoverer Plus, the user can navigate through all levels of the hierarchy, skipping levels if necessary. Figure 4-60 is a Discoverer Plus worksheet that shows the three levels of the date hierarchy: Year, Quarter, and Month. The user does not have to explicitly set up a date hierarchy. When a date is selected for a report that is part of a hierarchy, the user can navigate to any other element in the date hierarchy via a pop-up menu.

For more information, read Chapter 14, "Hierarchies".

Figure 4-60 Navigating Through Items in a Date Hierarchy

4.10.4 Modifying an Item's Content Type

Database columns usually contain content which is actual data. This data is displayed in Discoverer Plus as a result of a SQL query. However, you can also put information in a column which is either metadata (data about data) or a reference to data that needs to be run in another application to be displayed correctly. For example, if a file in the local or network file system is a video (.avi) file, a video application has to run to play the file.

Where an item contains a reference to a file that requires another application to run, Discoverer finds and loads the application enabling the user to view the data. The item's content type needs to be changed so Discoverer can recognize that an external application needs to be launched according to the file's extension. A typical file path is C:\ORANT\DISCVR4\DEMO\MEMO.DOC.

You specify that an item contains a file name (rather than just text) with the Content Type property. The Content Type item property has two values: FILE or NONE. If FILE is selected, Discoverer launches an application according to the file extension and information defined on the users computer. If NONE is selected the file name will be displayed as text.

Table 4-4 shows some example applications that are typically launched according to their file extensions.

Table 4-4 File Content Type Examples

In the New Video Stores business area, the Store Information.Reports item actually refers to a MSWord document, and the information in the database column is the directory path and filename. The Product Information.Full Description item is a Web page containing HTML code, and the information in the column is also the directory path and filename. The Reports document should be opened by a word processor, and the Full Description document should be opened by a Web browser.

Here's how to modify the content type so that Discoverer Plus will pass the contents of the items (the directory path and file name) to the operating system, which will launch the appropriate application to view the document:

1. Select the following items on the Data page of the work area:
   • Store Information.Reports

   • Video Analysis.Reports

2. Open the Item Properties dialog box for the selected items.

   There are three ways of doing this:

   • Popup Menu
     Right-click one of the selected items and choose Properties... on the popup menu.

   • Toolbar Icon
     Click the Properties toolbar icon ().

   • Menu
     Choose Edit | Properties....

3. Set Content type to FILE.

4. Click Apply. Do not click OK.

5. Select the Product Information.Full Description item on the Data page of the work area.

6. Return to the Item Properties dialog box.

7. Set Content type to FILE.

8. Click OK.

What the User Sees--External Application Icons in the Worksheet

When you open these items in Discoverer Plus, the information is displayed in the appropriate word processor and Web browser.

The user can tell which items need external applications to run based on the icon displayed in the worksheet. Figure 4-61 shows an example of a worksheet that can invoke Microsoft Word and an HTML page.

Figure 4-61 Discoverer Plus Sample with External Application Icons

4.10.5 Defining Drill to Detail Data

In Discoverer Plus, users use the hierarchies you create to drill up or down to view different levels of summarized data. There are occasions when the user needs to drill to detail; that is, jump to related information details in the summarized data. For example, your user may be reviewing daily sales results from a number of video stores. He or she may want to see the detail transaction records that comprise the daily total.

To enable the user to drill to the detail data, you need to create a drill-to-detail item class. A drill-to-detail item class lets users select an aggregated detail item in a report and drill directly to the source data that was accumulated to produce the result.

You can drill to any item that is an item class that contains items from other folders.

The two conditions cited above mean that a link exists between the items, and the user can automatically drill to detail to any folder which has an item in the same item class.

The following steps show how the Item Class Wizard makes it easy to edit an item class for drilling into related detail information from summary information, and for viewing a list of unique values from the Region column. (The item class was created by the Load Wizard at the start of this chapter.)

1. Click the Item classes tab.

2. Expand the New Video Stores business area to display all item classes.

3. Right-click Region and choose Edit Item Class... from the popup menu.

4. Click the List of Values tab.

5. Ensure that the Region item class in the Store Information folder is selected (see Figure 4-62).

Figure 4-62 Edit Item Class to Create Complex Folder for Drill-to-Detail

The next steps create a list of unique values in the Region database column.

6. Click the Select Items tab.

7. Ensure that the Selected items list on the right lists the following:
   • Store and Sales Details.Region

   • Video Analysis.Region

   • Store Information.Region

8. Tick Use these items to drill to detail.

   The Edit Item Class dialog box should look similar to Figure 4-63.

Figure 4-63 Selecting Items with the Item Class Wizard

9. Click OK to record the changes and close the Edit Item Class dialog box.

Now that you have associated a list of values with the Region item, a plus (+) symbol appears next to it on the Data page. Now the user can view the list of unique values in the column.

For more information, read Chapter 10, "Items and Item Classes".

The next lesson takes you through creating summaries. Summaries help reduce the time taken to retrieve data for Discoverer Plus users; this is described in Section 4.11.

4.11 Lesson 11: Creating Summaries to Optimize Performance

The New Video Stores business area is now ready for end users. However, allowing users to analyze data from folders and columns associated directly with the detail tables in the production database may be inefficient, and can sometimes take a long time to return results if the tables contain very large numbers of rows.

To improve query efficiency, Discoverer Administration Edition enables you to create summary folders. A summary folder contains pre-aggregated data that you know the end user will want to analyze and display in a final report. Discoverer can redirect queries to the pre-aggregated table, avoiding aggregating large volumes of detail data on the fly, and therefore optimizing query efficiency.

In this lesson, you will create the new summary table and a schedule for updating it.

This lesson consists of the following exercises:

4.11.1 Creating a Summary Folder

4.11.2 Setting up Internal Summary Combinations

4.11.3 Setting up the refresh schedule and naming the Summary Folder

4.11.1 Creating a Summary Folder

There are two ways to create Summary folders in Discoverer:

1. You can create Summary folders manually.

2. You can use Discoverer's Automated Summary Management (ASM) facility to create Summary folders for you. For details about ASM see Section 16.2, "Running ASM using the Summary Wizard".

In order to learn more about how Discoverer creates Summary folders, in this lesson we will create Summary folders manually by specifying Items that exist in the End User Layer.

1. Start the Summary Wizard.

   There are three ways of doing this:

   • Toolbar Icon
     Click the New Video Stores business area (on the Data page) and click the New Summary toolbar icon ().

   • Menu
     Click the New Video Stores business area (on the Data page) and choose Insert | Summary....

   • Popup Menu
     Right-click an existing Summary folder on the Summaries page and choose New Summary... on the popup menu.

Figure 4-64 Summary Wizard - Specify summaries yourself

2. Click I want to specify the summaries myself

3. Click Next

4. Select From items in the End User Layer.

   The Summary Wizard should look similar to Figure 4-65.

Figure 4-65 Create a new Summary Folder with the Summary Wizard

5. Click Next.

6. Under Available Items, choose New Video Stores from the drop-down list.

7. Move the following items from the Available items list to the Selected Items list:
   • Video Analysis.Region

   • Video Analysis.Department

   • Video Analysis.Transaction Date YYYY

   • Video Analysis.Transaction Date Mon YYYY

8. Move the SUM and COUNT functions, for each of the following data point (numeric) items, from the Available items list to the Selected items list:
   • Sales

   • Cost

   • Profit

   The Summary Wizard: Step 3 should look similar to Figure 4-66.

Figure 4-66 Choose the Axis and Summary Table Items

9. Click Next.

4.11.2 Setting up Internal Summary Combinations

A combination is a single set of axis and measure items in a summary table. If a user runs a query with the same items as those specified in a combination, the query runs against the summary table instead of the detail data in the database. Consequently, the query process speeds up because it uses the pre-summarized results instead of running calculations on all detail rows.

Your next procedure is to choose the summary combinations that you want to be pre-built and managed by Discoverer.

In Summary Wizard: Step 4, each numbered column represents combinations in a table.

10. Click Add Combination.

    This creates a new combination column.

11. Tick the following items in the new column (marked 1):
    • Region

    • Department

    • Transaction Date YYYY

12. Clear the Transaction Date Mon YYYY item in the new column.

    The Summary Wizard: Step 4 should look similar to Figure 4-67.

Figure 4-67 Choosing Internal Summary Combinations

13. Click Next.

    This opens Summary Wizard: Step 5.

4.11.3 Setting up the refresh schedule and naming the Summary Folder

Your next exercise is to schedule the initial summary refresh and periodic refresh intervals then name the Summary folder and schedule its build. The refresh schedule establishes the timeframe in which Discoverer automatically updates the data in the Summary folder. It is important for you, as the Administrator, to set up a periodic refresh that ensures that the summary tables will have current, relevant data at the periods required by users.

Figure 4-68 Refresh a summary folder

1. Clear the Automatically refresh this Summary Folder, starting on: check box

2. Click Next.

   This displays last page of the wizard, Summary Wizard: Step 6 (see Figure 4-69).

Figure 4-69 Name, describe and build the summary folder

3. Specify a descriptive name such as Video Analysis by Region and Department

4. Specify a description for the Summary folder.

5. Specify when you want to build this Summary folder.
   • Click the Build immediately radio button

6. Click Finish.
   A progress bar is displayed while the summary is being created.

7. Your new summary is now listed on the Summaries page of the work area.

8. Right-click on the summary you've just created (from the list on the Summaries page) and choose Properties from the popup menu.

9. Ensure Available for queries is set to Yes.

4.12 Conclusion

Congratulations! You have just completed the Discoverer Administration Edition tutorial. By now, you should have a solid understanding of the major features of Discoverer Administration Edition and how it affects every aspect of the user's view of database information via business areas in Discoverer Plus.

You've just seen that you can use Discoverer Administration Edition to make your end users' use of database information much more effective by creating business areas that reflect their view of data, and simplify their tasks by creating all of the conditions and joins they will need to use in reports.

If others in your organization plan to use this tutorial, you may want to delete the New Video Stores business area. To do this follow these steps:

1. Click the Summaries tab
   If you have completed Lesson 11 you will have created summaries. These must be deleted before you can delete the New Video Stores business area.

2. Delete summaries.

   There are two ways to do this:

   • Popup Menu
     Right-click the summaries (on the Summaries page of the work area) and choose Delete from the popup menu.

   • Menu
     Click the summaries (on the Summaries page of the work area) and choose Edit | Delete.

3. Click Yes in the Confirm Delete dialog
   This deletes any summaries created as part of this tutorial.

4. You can now delete the New Video Stores business area.

   There are two ways to do this:

   • Popup Menu
     Right-click the New Video Stores business area (on the Data page of the work area) and choose Delete Business Area from the popup menu.

   • Menu
     Click the New Video Stores business area (on the Data page of the work area) and choose and choose Edit | Delete.

   Discoverer Administration Edition asks you to confirm this action.

5. Select Delete this business area and its folders.

6. Click OK.

   This removes all of the work you have done as part of this tutorial from the EUL.

For more information regarding any feature, use online help or refer to the Table of Contents in this book to find the appropriate chapter.