7
Lesson 6: Creating joins

Lesson 6: Creating joins

It will probably take you about 10 minutes to complete this lesson.

In this lesson you will improve the association between folders by learning how to create the necessary joins between folders in the business area.

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

Note: End users cannot create joins, therefore if you want end users to create reports that combine information from multiple folders, you must make sure that the folders are joined.

This lesson consists of the following exercise:

• "Exercise 1: Creating joins between folders in the business area"

Exercise 1: Creating joins between folders in the business area

In this exercise you will create joins between folders in the business area by associating the data between the folders that you loaded in "Lesson 2: Connecting to Discoverer Administrator, using the Load Wizard and the Workarea".

You will create joins for each folder by joining a key from the master table to the corresponding key of the detail table.

To create joins between the folders:

1. Display the Workarea: Data tab and click the + symbol next to the New Video Stores business area to display its folders.

2. Click the + symbol next to the Product Information folder to display its items.

Figure 7-1 Workarea: Data tab

Text description of the illustration newjba.gif

3. Click the Product Information.Product Key item and choose Insert | Join to display the New Join dialog.

Figure 7-2 Join Wizard: Step1 dialog

Text description of the illustration newj.gif

The New Join Wizard: Step 1 dialog already displays the Product Information.Product Key as the master item.

4. Choose the = sign from the Operator drop down list.

   This list displays the types of joins that you can create. The = sign represents an equi-join, that combines rows with equivalent values for the specified items.

5. Click the Detail Folder drop down list to display the New Join dialog.

Figure 7-3 New Join dialog

Text description of the illustration newjs.gif

The New Join dialog lists the folders in the New Video Stores business area and enables you to select the folder and item that you want to use for the detail part of the join.

6. Select Sales Details.Product Key.

7. Click OK to display the Join Wizard: Step 1 dialog with Sales Details.Product Key in the Detail Item field.

Figure 7-4 Join Wizard: Step 1 dialog

Text description of the illustration newj1.gif

8. Click Next to display the Join Wizard: Step 2 dialog.

Figure 7-5 Join Wizard: Step 2 dialog

Text description of the illustration newj2.gif

9. Click Finish to display the join as an item in the Product Information folder.

   Note: You do not need to enter a name for the join, Discoverer Administrator automatically creates a default name for the join when you click OK.

Figure 7-6 Workarea: Data tab

Text description of the illustration newjba1.gif

Note: The icon displayed next to the Product Information folder indicates 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. Repeat the previous steps of this exercise to create joins for the following folders:
    • Store Information

    • Time Information

    Note: When creating joins for the above folders, use the appropriate master and detail folder/key values as shown in the table below:

    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

    Once 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 and Discoverer Viewer.

    Note: You must create joins in Discoverer Administrator to make folders available to end users of Discoverer Plus and Discoverer Viewer. When an end user selects an item or folder to create a worksheet, only the folders that are joined to the selected folder are available. Therefore, if you have not created a join between two folders, neither the unselected folder nor its items will be available to the worksheet.

    For more information about creating and editing joins, click Help.

    In this exercise you created joins between folders in the business area by associating the data between the folders that you loaded in "Lesson 2: Connecting to Discoverer Administrator, using the Load Wizard and the Workarea".

    Lesson summary

    In this lesson you improved the association between folders by learning how to create the necessary joins between folders in the business area.

    In this lesson you learned how to create the necessary joins between folders in the business area, and completed the following exercise:

    • created joins between folders in the business area

    In the next lesson you will learn how to customize items in a business area.