Outer Joins

 < Day Day Up > 



Outer joins are a special join type and one that requires careful consideration before using. An outer join is a relationship between two tables in which records from one table do not have matching records in the other. BusinessObjects does not provide a sample universe that contains outer joins, but you can create one using ARTICLE_LOOKUP and SHOP_FACTS.

In the ARTICLE_LOOKUP table, you add a new record, ARTICLE_CODE 189480, a new sweatshirt for Leeds United Football Club. You know that there are no sales against this sweatshirt in the SHOP_FACTS table, because you just added it. If a user were to run a report for the sales category "Sweats," he/she would not see this sweatshirt listed if the universe contained a default equi-join. Therefore, you need an outer join on the ARTICLE_LOOKUP table.

click to expand

To change the existing equi-join to an outer join:

  1. Double-click the join line between SHOP_FACTS and ARTICLE_LOOKUP or right-click and select Join Properties from the pop-up menu.

  2. Under Table1, ARTICLE_LOOKUP, select the Outer join box.

  3. Select Parse to ensure the join is valid.

  4. Click OK to save the change to the join.

  5. Click Save on the Designer toolbar to save the changes to the universe.

    click to expand

    Note 

    Designer version 5.1.3 has a bug that switches which table is Table1 in the Edit Join dialog box. Be sure that you are putting the outer join on the correct table.

Now if you run a BusinessObjects report, you will see the Leeds United sweatshirt even though you have had no sales yet. The outer join allows this new ARTICLE_CODE to appear in the report, even though there are no corresponding rows in SHOP_FACTS.

click to expand

The actual SQL syntax for outer joins will vary depending on which database and driver you are using. In the Edit Join dialog box, ODBC does not update the expression to show the outer join. Other RDBMs will adjust the join expression in the dialog box. The Microsoft Access SQL uses the following syntax:

oj Article_lookup LEFT OUTER JOIN Shop_facts ON Article_lookup.Article_code=Shop_facts.Article_code

Oracle uses the + to indicate the outer join. The + always goes on the table that has fewer records (yes, it feels illogical to me, too!):

SHOP_FACTS.ARTICLE_CODE=ARTICLE_LOOKUP.ARTICLE_CODE(+) 

You may now be thinking that it makes sense to put outer joins on all lookup tables, since you often have inventory before items have sold. However, it's also possible to have items in a fact table that do not have a corresponding record in the dimension table. As an example, imagine a frustrated sales clerk who keeps trying to scan a trendy new scarf for an impatient customer. The scanner does not ring up the product at the register, so the sales clerk manually enters the article code from the scarf's tag (let's avoid the worst scenario, when the clerk rings it up under a different article with the same unit price ... a common occurrence at my local department store). Why didn't the scarf scan? Who knows! Of course, the scarf should have been in inventory! And it should not have been on display without existing in the article master! But it happened, and unfortunately, it happens more than business people realize and more than data modelers wish.

In an ideal world, the sales transaction would automatically have added an entry in the article master. In an almost ideal world, the data warehouse will plug a number in the ARTICLE_KEY such as 999 or XXX to say the article description is not found.

In reality (such as a transaction system or poorly modeled data mart), you will need to use an outer join. Outer joins may not be a problem for small lookup tables, but they are best avoided for large lookup tables because the RDBMS cannot use the index to process the query because of lousy response times. Also, earlier versions of certain databases did not support outer joins.

Even when you use an outer join on a small lookup table, be sure to test the response time or analyze and explain the plan in your RDBMS. If the response time is slow, train the users to understand that if they want full product listings, full customer listings, or a list of customers who have not bought this year, analyze that data separately. Use of subqueries (discussed in Chapter 22) may help them answer the same questions more efficiently.



 < 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