11
Joins

This chapter consists of the following sections:

• 11.1 Introduction

• 11.2 Creating Joins

• 11.3 Editing Join Properties

• 11.4 Editing Joins

• 11.5 Deleting Joins

• 11.6 Fan Traps

11.1 Introduction

In Discoverer, a Join relates two folders using one or more common items. This is similar to a Join in the database which relates two tables using common columns.

The Joins you create in Discoverer Administration Edition affect the combinations of items that can be selected during the following operations:

• Creating a worksheet in Discoverer Plus.

• Creating a Complex Folder in Discoverer Administration Edition.

If you select an Item, during either of these operations, you can then only select Items from Folders joined to the Folder containing the selected Item. If one or more Items from these Folders are selected then further joined Folders become available.

Joins are defined with a master and detail end. The master end is the folder that has one row, for which there are several detail rows. For example, the relationship of a master row in the Department folder to the many detail rows in the Employee folder.

It is important to make sure you define the Join with the correct folders at the master and detail ends. If you set up this relationship incorrectly, it adversely affects what combinations of folders a user can combine in a single query, which in cases of queries with three or more folders can, in some circumstances, lead to misleading or incorrect results. It can also affect whether summary tables can be used to speed up queries.

Usually joins are one-to-many, where one row in the master folder is joined to multiple rows in the detail folder.

Occasionally there are one-to-one and many-to-many joins. Many-to-many joins are not supported directly in Discoverer, or in any relational system, although they can always be worked to be transformed to multiple many-to-one joins.

Users of Discoverer Plus cannot set up their own join conditions. However, they can decide which join path to use if more than one join exists.

11.2 Creating Joins

This section describes how to create Joins. If you have already created a Join in the tutorial, you are familiar with the Join dialog boxes and have seen how they create Complex Folders from which an end user can access useful combinations of data.

Creating a Join starts with either selecting the Item that will be the Master Item, or the Folder it belongs to. If you choose Insert | Join before selecting an Item or Folder, a selection dialog box opens for selecting the Item that will be the Master Item.

1. On the Data page of the work area, select the Item that you want to be the Master Item.

2. Choose Insert | Join.

   If you did not select the Master Item in step 1, the first New Join dialog box opens (see Figure 11-1). Select the Folder that contains the Item that you want to be the Master Item and click OK.

Figure 11-1 Selecting the Item for the Join

This opens the main New Join dialog box (see Figure 11-2). The Master Item is displayed in the Master Folder column.

Figure 11-2 New Join Dialog

3. Specify the Join type using the Operator field.

   Operator-- use the drop-down list to select an operator for the type of join you want to create. Section 11.1, "Introduction." describes the different types of Joins. Operators include:

   =	equijoin, combining rows that have equivalent values for specified items
   < >	not equal
   <	less than
   <=	less than or equal
   >=	greater than or equal
   >	greater than

   Detail Items--select the folder that contains the detail item from the drop-down list. The detail item can be in a folder in either the same business area as the master item, or in a folder in a different business area. The syntax for the value in Detail Items is folder name.item name.

   Name--name the join you are creating.

   Description--text field for describing the join you are creating.

   Multi-item--replaces the New Join dialog with a New Multi-item Join dialog with room for multiple rows of join criteria, which can be added and deleted using the Add and Delete buttons (Figure 11-4).

   Options--displays a dialog box for defining outer join conditions (Figure 11-3).

Figure 11-3 Join Options Dialog

This dialog presents the following options:

Outer join on detail--creates an outer join. Returns all master rows that have no corresponding detail items, plus all matching master and detail rows.

Outer join on master--creates an outer join. Returns all detail rows that have no corresponding master, plus all matching detail and master rows.

NOTE: This construct is fairly rare in real schemas. It requires the Detail foreign key can have null values option, described next.

Detail foreign key can have null values--this setting should only be used on rare occasions, and only affects a particular aspect of when summary tables are used to satisfy a query. Foreign keys usually have values and, in most cases, they are mandatory columns in the database. Such joins are sometimes described as lossless joins.

This setting is required if you are setting detail rows that have no master, because it implies that the foreign key column will contain some null values. On its own, it has no effect on SQL generation, but is required to determine when Discoverer can use summary tables in some special circumstances. For example,

• If you have created a summary folder that contains items from both a Master and a Detail folder,

• and the folders are joined with this option enabled

• and a user issues a query which uses items only from the detail folder,

• then the summary table will not be used to satisfy the query, because Discoverer cannot guarantee that the row set is the same in both cases.

If Detail foreign key can have null values is not set, this query uses the summary table. This is this setting's only effect.

One to one join relationship between master and detail--creates a one-to-one relationship instead of a one-to-many relationship between the master and detail tables. There is no real master and detail in this case, because there is only one row in each. These joins are unusual, but do occur in schemas occasionally.

This setting has no effect on the SQL that Discoverer generates, because SQL does not know about the cardinality of joins. It only affects the fan trap detection described in Section 11.6, "Fan Traps."

Because true one-to-one joins do not result in Cartesian products, it is possible to query a master with multiple detail folders, provided that all but one of the detail folders are joined with one-to-one joins. If these were all one-to-many joins, making it possible for a row in one detail table to join to many rows in another detail table, and vice versa, the result is a Cartesian product. Cartesian products are explicitly disallowed in Discoverer, because they are almost never the desired or expected result.

11.2.1 Using the New Join Dialog

The New Join dialog opens when you do one of the following:

• Select a folder or item and either click the join toolbar icon, or choose
  Insert | Join.

• Click its toolbar icon or choose Insert | Join and then select an folder from the Join dialog box.

Use the New Join dialog as follows:

1. The Master Item field displays the folder or folder and item you have selected as the master item. The folder name displays with a period followed by the item name.

   Use the pull-down list if you want to specify a different item in the same folder.

   Click the item to be used as the master item.

   NOTE: If you want to change the folder in the Master Item or Detail Item fields, click more items in the drop-down list. This reopens the Select Item dialog, which you can use to select a different folder and item.

2. Use the Operator drop-down list to define equi- or non-equi-joins.

3. If the Detail Item field contains no value, or if you want to change the value, click the pull-down arrow. A new dialog opens for selecting the item.

   In this new dialog, select the folder and the detail item for the join. You can choose a detail item from another open business area.

4. Click OK. The item dialog closes and you return to the New Join dialog. The folder, followed by a period, and the detail item display in the Detail Item box.

   If you want to create multi-item joins, click the Multi-item button and go to Section 11.2.2, "Creating Multi-item Joins."

5. Click OK.

The New Join dialog closes and the join is added to the folder, with an icon next to it indicating the join relationship.

• Master to Detail
  This icon represents the one-to-many relationship between the two items in different folders in the master to detail join relationship. The master item is on left and the detail item is on the right.

  
  
  

• Detail to Master
  This icon represents the many-to-one relationship between the two items in different folders in the detail to master join relationship. The detail item is on the left and the master item is on the right.

For information about editing joins, read Section 11.4, "Editing Joins."

Hints and Tips

1. You can only join between items, you can't directly include functions or literals, such as text strings, numbers or dates. The way to do this is to create calculated items that have the functions or literals you want to use, and then specify these in the join.

2. Items in joins can be hidden later, insulating users from the structural details, but allowing them the use of joined folders.

11.2.2 Creating Multi-item Joins

The join feature includes the option of adding multi-item joins. Click the Multi-item button to open the New Multi-Item Join dialog. Use this dialog to add items to the join between folders.

In a multi-item join, all master items must belong to one folder and all detail items must belong to one folder. If you add a master or detail item from a different folder, all items from the previous folder will be deleted from the join.

Figure 11-4 Multi-Item Join Dialog

1. Click Add. A new row appears. Notice that the folders named in this row are the same folders as in the previous row. The Add button is dimmed.

2. Use the drop-down lists for Master Items and Detail Items to select a new pair of items. Keep in mind that a single master item can join to many detail item values.

   When you have completed selecting items in this row, the Add button reactivates, ready to add another row.

3. Continue to use the Add and Delete buttons to add items to the join, tailoring it to the users' requirements. When you are satisfied with the multi-item join, click OK.

11.3 Editing Join Properties

Join properties are accessible through Join Properties dialog boxes. This section shows you how to edit Join properties. Figure 11-5 shows an example Join Properties dialog box.

Figure 11-5 Join Properties Dialog Box with the General Tab Selected

11.3.1 Editing the Properties of a Single Join

This section describes how to edit a Join's properties.

1. Open the Join's Properties dialog box.

   There are four ways to do this:

   • Double-click
     Double-click the Join on the Data page.

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

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

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

2. Make your changes as required.

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

3. Click OK.

11.3.2 Editing the Properties of Multiple Joins

The following steps show you how to set common properties for more than one Join at a time:

1. Select all of the Joins whose properties you want to edit.
   (Ctrl-clicking enables you to select more than one Join.)

2. Display the Join Properties dialog box.

   There are three ways to do this:

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

   • Toolbar Icon
     Click the Properties toolbar icon ()

   • Menu
     Choose Edit | Properties.

   All properties that are common to each of the selected Joins are displayed. If the data for a field is not common to each of the selected Joins, the field is blank.

3. Make your changes as required.
   Any changes you make here will be applied to all of the selected Joins.

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

4. Click OK.

11.4 Editing Joins

This section describes how to edit an existing Join.

1. Display the Edit Join dialog box (see Figure 11-6).

   There are three ways to do this:

   • Popup Menu
     Right-click the Join on the Data page and choose Edit Join... on the popup menu.

   • Menu
     Click the Join on the Data page and choose Edit | Edit...

   • Join Properties Dialog Box
     Click in the Formula field on the Join Properties dialog box.

Figure 11-6 Editing an Existing Join

The Edit Join dialog box works in the same way as the New Join dialog box (see Section 11.2.1, "Using the New Join Dialog" for more information).

2. Edit the Join as required.

3. Click OK.

11.5 Deleting Joins

This section describes how to delete Joins.

1. Select the Join(s) that you want to delete.

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

2. Delete the Join(s):

   There are three ways to do this:

   • Popup Menu
     Right-click one of the selected Join(s) and choose Delete Join 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 11-7). The Impact dialog box helps you to make the right choice.

Figure 11-7 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 Join(s), click Yes.

11.6 Fan Traps

Occasionally you set up joins that include a master with two separately joined detail folders, such as pictured in Figure 11-8. This situation is referred to as a fan trap.

Figure 11-8 An Example of a Fan Trap Schema

As an example, the fan trap schema in Figure 11-8 includes a master folder (ACCOUNT) and two detail folders (SALES and BUDGET). An account can have several sales and several budget figures for each period. In most cases Discoverer can resolve queries involving fan trap schemas to give the correct results. For example, consider the following rows:

ACCOUNT
ID	Name
1	Account 1
2	Account 2
3	Account 3
4	Account 4

BUDGET
Accid	Budget	Period
1	200	1
1	200	2
2	100	3
3	150	2
3	250	3
3	350	4
4	100	1
4	100	2

SALES
Accid	Sales	Period
1	100	1
1	100	2
1	200	3
2	50	1
2	80	2
3	200	3
4	150	2
4	50	3
4	100	4

If the user selects ACCOUNT name, sum of SALES and sum of BUDGET in a query they will see the following results:

Account	Sales	Budget
Account 1	400	400
Account 2	130	100
Account 3	200	750
Account 4	300	200

However there are situations where Discoverer prevents queries involving fan trap schemas from returning the expected results. In these cases, Discoverer disallows the query and displays an error message. Such situations arise in any of the following circumstances:

• The detail folders are joined to the master folder using different keys from the master for the join.

• There is a direct join relationship between any of the detail folders (thereby creating an ambiguous circular relationship).

• Non-aggregated values are chosen from more than one of the detail folders.

• More than one detail folder has a separate join relationship to a different master folder.

11.6.1 Fan Traps inside Complex Folders

If you are setting up a fan trap join configuration inside a Complex Folder, to guarantee that correct results are returned, be sure to specify the aggregates to use for the details by editing the Item properties and setting the formula (for example SUM(Sales Fact.Sales)).

Figure 11-9 An example of an aggregate formula specified for an item in a complex folder