Chapter 7: Universe Joins

---

Joins define how two tables relate to one another. In Designer, your strategies (under File | Parameters) can automatically propose joins based on common names between two tables. Alternatively, you can manually define the joins. Most of the complexities   around joins occur when your universe contains multiple star schemas or when you are using BusinessObjects against a normalized transaction system.

Join Graphics

In Designer, you can change the appearance of the join lines and the symbols used to display relationship cardinalities. You also can have Designer provide you with additional information that will help you create or modify the joins more accurately. These settings will apply to all universes (unlike parameter settings reviewed in Chapter 6 that are universe specific). Figure 7-1 shows the database structure for a sample Oracle Sales History (SH) database. A number of the graphic settings have been changed to make the structure easier to read.

Figure 7-1: This sample Oracle sales history universe shows table sizes, key names, and cardinalities. This information is useful in defining joins.

To display this information in all universes, select Tools | Options | Graphics from the Designer menu.

The Join Shape settings determine how you would like the join lines drawn. This is purely personal preference and does not affect your universe design. The Arrow join shape is the least helpful, as it does not provide any information about the cardinality of the join. Use either Arity or 1,n. Arity will give you a crow's foot for a many relationship, and 1,n will display numbers. The Best side check box merely has the line drawn from the side of the table that will make your entity relationship diagram appear less busy and easier to read. Table 7-1 summarizes other graphics options.

Table 7-1: Designer Graphics Options Used in the Structure Pane

Option	Purpose
Default number of columns	For tables with many columns, Designer will display the first n columns in the structure pane and use an ellipsis to indicate that more columns exist in the table.
3-D Effect	Displays a shadow box around the table name.
Aliased name	Aliases are described later in this chapter, in the section 'Aliases.' This checkbox will allow you to see both the alias name used in the universe as well as the physical table name in the underlying RDBMS.
Show Row Count	This option only works when you have separately extracted the row counts for each table. The row count will display the actual or expected number of rows in each table, useful for determining join order and outer join issues.
Show Format	Show Format will display the format of the column: C for character D for date N for number T for long text L for lob-all large binary objects (blob, clob, bfile, nblob in Oracle) Join fields are either character, date, or number. Most databases do not allow you to join between two columns that are a different format, even if the values are the same. For example, if the ARTICLE_CODE in SHOP_FACTS is character and ARTICLE_CODE in ARTICLE_LOOK_UP is numeric, the database will return an error during query execution and/or when you select Parse in editing the join statement. During the universe integrity check, quick parsing will not reveal this error, but thorough parsing will. Therefore, use thorough parsing.
Underline Keys	Joins between two tables are usually between key columns. For Designer to underline which columns are keys, check this box, and in addition, extract the key information via your strategies (see Chapter 6). Select File | Parameters | Strategies tab. In the Join box, select All Matching Column Names. Note that the first join strategy does not include key info. If Designer does not underline the key names in the structure pane, refresh your database structure by selecting View | Refresh Structure.
Left Justified or Centered	Left Justified and Centered affect the appearance of whether the column names are left aligned or centered beneath the table name in the structure pane.

Show Row Count

Extracting row count information requires a full table scan against each table. It can be slow to generate this information for fact tables that contain millions of rows of data. Therefore, when you turn on the option to display the row counts, Designer does not automatically fetch these numbers. To fetch the numbers, ensure that your mouse is not on any one table, then select View | Number of Rows in Table. Designer will ask you if you want to Set or detect row counts for all tables. Click OK. Select Refresh row count for all tables, as shown here:

The Modify manually tables row count option allows you to enter manually the number of rows for an individual table. This can be useful if you are working with test data and you want to enter expected rows or if you know the approximate row counts for large fact tables and you do not want to wait for Designer to extract this information.

The row count information is used in two ways: First, it helps you as the designer understand when you are joining large tables together. Second, BusinessObjects lists the largest table first in the FROM section of a SQL statement to make queries run faster. Databases that do not have an optimizer will benefit from careful ordering of tables in the SQL statements. Databases such as Oracle and DB2 have an optimizer, so there is no need to spend time modifying row counts (unless you are using the Rule-based optimizer).

The row count information works in conjunction with the REVERSE_TABLE_WEIGHT parameter in the database.prm file. By default, this parameter is set to Y to list tables largest to smallest. Set this parameter to N for smallest to largest.

---