Basic Joins

 < Day Day Up > 



Once you have set your graphics options, you can begin modifying or creating basic joins. Displaying the joins in list mode can be helpful in determining the order of the tables in the join statement as well as seeing long join statements. Click the View List Mode button on the toolbar or select View | List Mode from the pull-down menus.

Referring back to the Test Fashion universe we created in Chapter 6, the universe has two basic joins from SHOP_FACTS to CALENDAR_YEAR_LOOKUP and SHOP_FACTS to ARTICLE_LOOKUP. The arity or three-lined "crow's foot" indicates that the relationship between SHOP_FACTS and ARTICLE_LOOKUP is one to many. For every one article in ARTICLE_LOOKUP, there may be one or many sales transactions in SHOP_FACTS (for example, blazers can sell one or more times). View List Mode gives an overview of all join statements, as you can see here:

click to expand

There are several ways to modify the join statement:

  • Double-click the join line in the Structure pane.

  • Double-click the join line in the Join list.

  • Manually enter changes in the formula bar.

Modify the join between SHOP_FACTS and ARTICLE_LOOKUP. You are presented with the Edit Join dialog box. Keys are often used in joins. If your keys are not underlined in the structure pane, check File | Parameters | Strategies and refresh the structure.

click to expand

Table1, ARTICLE_LOOKUP, appears on the left-hand side and Table2, SHOP_FACTS, on the right. In some RDBMSs, which table is right or left does not matter, but in others, it can affect how quickly a query is processed. When Designer proposes joins via the Quick Design Wizard, it puts the smaller table on the left side of the join and the larger table on the right. This order may result in faster queries for certain databases, particularly for joins other than equi-joins (BETWEEN, >=, >!, =).

Note 

When Oracle uses the cost-based optimizer, the order of the joins and the order of the tables in the FROM section of the SQL does not matter; however, if you are using the rule-based optimizer, it does.

The first drop-down box lets you define the join operator:

Join Operator

Explanation

=

Equi-join. This is the most common and default join type. Two tables are related when every data value in the left table has an equivalent value in the right table

!=

Not equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

Cardinality

Above the join type, the cardinalities are indicated with 1,n = 1,1. Cardinality defines how many instances there are of each unique record in the related table: zero, one, or many. In a standard, single-star schema, all of your relationships will be one to many between the dimensions and the fact table.

If you remove the check from the cardinalities box, the 1,n and 1,1 are no longer displayed in the join panel beside the join operator. To change the appearance of the cardinality in the structure pane, select Tools | Database | Graphics.

Cardinality applies to each table in a join statement:

  • Table 1(1,n) Every item in the dimension table has one or more records in the fact table.

  • Table 2(1,1) For every record in the fact table, there is one and only one record in the lookup table.

Cardinality detection and display did not exist in earlier versions of BusinessObjects. According to the vendor-provided documentation, the sole purpose of cardinality detection is to warn you of possible loops. However, cardinality detection does not work reliably and has no effect on loop detection, though it does affect detection. As of this writing, Cardinality detection does not correctly detect many-to-many relationships, nor does it detect zero relationships (which would be quite useful in defining outer joins). According to BusinessObjects, the detection algorithm counts rows to determine the cardinality and does not catch anomalies that may affect cardinality. There are no plans to change this in version 6.

If you skip cardinality detection completely, Designer will still correctly detect loops but will not propose contexts. Therefore, you may want to skip this process entirely if

  • Your universe will have only one star or snowflake schema.

  • The cardinality between tables is not typical of star schemas. A star schema typically has one dimension to many facts. If your tables have many-to-many relationships and Designer correctly detects this (it normally doesn't), then Designer will falsely tell you 'No New Contexts Needed.' For more accurate context detection, manually set the cardinalities.

If your data is clean, however, and if your universe will have multiple schemas, cardinality detection can be helpful in later detecting contexts.



 < Day Day Up > 



Business Objects(c) The Complete Reference
Cisco Field Manual: Catalyst Switch Configuration
ISBN: 72262656
EAN: 2147483647
Year: 2005
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net