Loops

---

Loops occur when there are two different paths to accomplish one join. The following structure now includes PRODUCT_PROMOTION_FACTS in the Test Fashion universe. If users want to analyze articles versus time, there are now two join paths. BusinessObjects does not know which path to take, the one via SHOP_FACTS or the one via PRODUCT_PROMOTION_FACTS.

The circular appearance of these four joins is a loop, which can give undesired SQL results.

If users tried to create a query based on articles and time, prior to the administrator resolving this loop, they would receive the error message shown in Figure 7-2.

Figure 7-2: Users receive this error message if you do not resolve loops or allow contexts in File | Parameters, SQL tab in Designer.

Contexts help break this loop into two sets of join statements, so the desired join path is always clear.

Inserting a New Context

Designer can help you detect and resolve loops by using contexts. To enable this, we will first add a table to create a loop and then resolve the loop with a context.

1. Add the PRODUCT_PROMOTION_FACT table to your universe. From the pull-down menu, select Insert | Table or right-click in the Structure pane and select Insert Table from the pop-up menu.

2. From the table browser, select the PRODUCT_PROMOTION_FACT table and click Insert.

3. If your Database Options are set to extract joins with tables (the Options dialog box reached by choosing Tools | Options, Database tab) and if your universe parameters are set to propose joins based on matching column names (in File | Parameters,  Strategies tab,  see Chapter 6), Designer will automatically add the join between PRODUCT_PROMOTION_FACT and ARTICLE_LOOKUP. Recall also from Chapter 6 that the join names of the columns for WEEK_KEY do not have the same initial caps between the two tables. Therefore this join is not automatically detected and must be manually defined. Draw a join line between CALENDAR_ YEAR_LOOKUP.WEEK_KEY and PRODUCT_PROMOTION_FACT.WEEK_KEY. This last join is what creates the loop and closes the circle just shown.

4. Click the Detect Loops button on the toolbar or select Tools | Detect Loops from the pull-down menus. Designer will highlight all the joins that create the loop or circle.

5. If you had already defined a context, at this point Designer would propose a Candidate Context. This universe does not have existing contexts, so you need to detect them. Designer will only detect contexts when the cardinalities have been set. Set them manually or click Detect Cardinalities. Designer will check the cardinality box in the join statement and show the relationships in the Structure pane.

   Caution 

   If your data is not clean and the cardinalities do not follow the typical one-to-many for one-dimension-to-many facts, Detect Contexts will not work.

6. Select Detect Contexts from the toolbar or Candidate Context from the Loop Detection dialog. Designer will present you with a list of names as proposed contexts. These contexts include all the joins in each part of the loop or one set of joins. The proposed context name comes from whichever table name is at the center of the join path, in this case SHOP_FACTS and PRODUCTION_PROMOTION_FACTS.

7. Highlight each Candidate Context and select Add to include the two new contexts in the universe.

8. Click OK to close the Candidate Context box and Close to close the Loop Detection dialog. The universe Structure pane and List Mode should now appear as follows. Note that when a particular context is highlighted in the Contexts list, the join statements that belong to that context are also highlighted in the Joins list as well as the Structure pane.

   

9. Click Save on the toolbar to save your universe changes.

Congratulations! You have resolved your first loop. Unfortunately, your real world universe may not be so easy. If your universe contains aggregates or is a snowflake design, Designer may propose more contexts than necessary. Likewise, as you add new tables to your universe, your contexts may become incomplete. If you leave the context name at its default and if your cardinalities are typical, Detect Contexts works very well and will help you identify isolated joins that do not yet belong to a context.

Modifying a Context

As a general rule, leave the context name as the default until you have added all tables, loops, and contexts. This makes the universe designer's life easier, as Designer will automatically add the table to an existing context, when you use Detect Contexts. With this semiautomatic insertion, you may never want to rename contexts. However, it is not the friendliest situation for users. When users launch a query involving a potential loop, users will have to select a context. If the context name uses the default table name, it may not be user friendly. It's always better to make life harder for one designer than for hundreds of users!

In the following example, I have renamed the context 'Product Promotion Fact' to 'Promotion.' You can rename a context either while initially defining the context or after you have added the context to the universe. To rename the context after it has been defined:

1. Select the Context from the list.

2. Use the pull-down menu Edit | Context Properties or right-click the context name and select Context Properties.

   

3. In the Context Name box, enter a business-oriented name; replace Product Promotion Facts with Promotion.

4. In the Description box, enter help text that will appear when users are prompted to select a context. For example, BusinessObjects is not sure how to answer your question. If you want to know which articles were promoted in a certain time period, select the Promotion context. If you want to know which articles sold in a certain time period, select the Shop Facts context.

   

5. Click Check to verify that this particular context contains no loops; it does not check for other loops within the universe.

6. Click OK to close the Edit Context box.

Isolated Joins

Once you use contexts in a universe, you must keep using them and ensure they are complete. As you add new tables to your universe, the joins between the tables must be added to an existing context or included in a new context. In the following example, you will add the PROMOTION_LOOKUP table to the universe and include it in an existing context:

1. Add the table PROMOTION_LOOKUP to your universe. From the pull-down menu, select Insert | Table or right-click in the Structure pane and select Insert Table.

2. From the table browser, select the PROMOTION_LOOKUP table and click Insert.

3. If the join was not automatically created, draw a join line between PROMOTION_LOOKUP.PROMOTION_KEY and PRODUCT_PROMOTION_FACT.PROMOTION_KEY.

4. From the toolbar, click Check Integrity.

5. Universe integrity is discussed in more depth in Chapter 15. In this example, I want to point out the isolated join you just created. Check the boxes for cardinalities, loops, and contexts, as shown here:

   

6. Click OK to run the Integrity Check. Designer will now go through and verify that all loops are resolved and any new joins belong to at least one context. The following screen shows that the newly added table PROMOTION_LOOKUP and its related join does not belong to a context:

   

7. Click OK to close the Integrity Check Results box.

8. From the pull-down menu, select Tools | Detect Context or click Detect Context.

9. Designer will prompt you with the context product_promotion_facts, which includes the three joins to the fact table. If you did not rename this context Promotion in the last section, Designer will ask you if you want to overwrite your existing context.

10. Click OK and then Save to include this new context or additional join in the existing context.

Once you have created a context, it is also possible to add additional joins without using the Detect Context command. You will need to do this if your cardinalities are atypical (or if detection is slow and you know to which context new tables need to be added). To modify a context, either double-click the context from the list pane or use the pull-down menus to select Edit | Context Properties.

Caution 

Once you start using Contexts, you must ensure all joins are included in at least one context. If you fail to do this, user queries are split into multiple SQL statements that may lead to inaccurate results or messages such as 'Incompatible combination of objects.' What happens depends on the universe Parameters | SQL | Multiple Paths settings.

How Contexts Are Used

Now that you have two contexts, all user queries will be affected in one of three ways, sometimes with user prompting and sometimes without prompting:

1. When a user selects objects from tables purely within one context (CALENDAR_ YEAR_LOOKUP, SHOP_FACTS, ARTICLE_LOOKUP), BusinessObjects is smart enough to know which context or join path to use to generate the SQL. The user is not prompted to choose a context.

2. When a user selects objects from both contexts, for example, sales and promotion costs by article and month, both contexts need to be used and BusinessObjects will intelligently generate two separate SQL statements and seamlessly stitch the results together in one report. Some vendors refer to this functionality as Multipass SQL. As the administrator, you must make sure you have the correct Parameters | SQL settings to allow this or users will see the error message shown earlier in Figure 7-2 (see Chapter 6 for correct settings).

   Note 

   The capability to generate multiple SQL statements and automatically stitch the results together is one of BusinessObjects most powerful features and one that sets it apart from other BI tools. Prior to this technology, users would have to follow such a process manually.

   The left side of Figure 7-3 shows the first SQL statement BusinessObjects generates to retrieve sales amounts. Select1 uses the Shop_Facts context. The right side shows the second SQL statement BusinessObjects generates to retrieve the promotion costs for the same year and article IDs. This is very important. The dimensions form the GROUP BY section of the SELECT statement. If these are not exactly the same, the query still executes but with additional rows of data, as BusinessObjects is not clear how to synchronize the results. For example, if I added a row Radio promotion to my query, it would only appear in Select2. The beauty of this synchronization feature is that users never see it; their business question is answered automatically and correctly.

   
   Figure 7-3: BusinessObjects automatically generates a Select statement for each context.

3. When a user selects objects from the lookup tables without including objects from a fact table, BusinessObjects cannot determine automatically which context to use. Therefore, it prompts the user. In the next example, I created a query that includes Article Id and Year. BusinessObjects cannot determine if I want to know which articles sold (Shop_Facts context) within a certain year or which articles had promotion costs (Product_Promotion_Fact context) within a certain year. Note in the following screen that users see the Description added in the earlier section 'Modify a Context':

   

Are Loops a Bad Thing?

You have spent a lot of time identifying loops and getting rid of them with contexts. In many cases, a loop is unintended, caused by poorly written SQL against complex schemas. However, loops can be the result of a valid business question if users really want to know which articles and weeks are common between both the promotion fact table and the sales fact table.

Look at the following two BusinessObjects reports. The articles Polo Collared T-Shirt (166544), Whisky Dancer T-Shirt (166550), and Pomodore Lace T-Shirt (167695, week 33) had promotions but no shop sales, so they appear in the report labeled 'Product Promotion Context' but not in the report labeled 'Loop Report.'

The first report, Product Promotion Context, contains ten rows of data for articles that had promotions during particular weeks. The second report, Loop Report, contains only seven rows of data and lists articles and weeks that are common between both the promotion fact table and the shop fact table. There are less rows in this second report because not all products had both promotions and sales in the exact same week. BusinessObjects does not allow loops in universes; users cannot run queries that contain an unresolved loop. The Loop Report was created using the following Free-hand SQL (discussed in Part III of this book):

SELECT   Article_lookup.Article_code,  Article_lookup.article_label,  Calendar_year_lookup.Week_In_Year,  Calendar_year_lookup.year_week FROM   Calendar_year_lookup,   Article_lookup,   product_promotion_facts, shop_facts WHERE   ( Calendar_year_lookup.Week_Key=product_promotion_facts.Week_key  )   AND  ( Article_lookup.Article_code=product_promotion_facts.Article_code  ) And   ( Calendar_year_lookup.Week_Key=shop_facts.Week_key  )   AND  ( Article_lookup.Article_code=shop_facts.Article_code  )

The bottom line? Designer does not allow loops and will force you to resolve them. One may consider this a limitation of BusinessObjects. However, most SQL that contains a loop is often unintentional; therefore, BusinessObjects forces designers to construct universes in a way that consistently provides correct answers. Similar business questions can be answered more efficiently in other ways, as described in Chapter 22.

Composite Keys and Complex Joins

Occasionally in resolving loops, Designer will suggest the loop can be resolved by an alias. This may happen when you are trying to join two tables together with two different fields, for example, SALES_FACT to CUSTOMER.SHIP_TO and SALES_FACT to CUSTOMER.SOLD_TO. Be careful not to confuse this with composite keys in which more than one column uniquely identifies a record.

Most fact tables have several columns that uniquely identify one row of data. In the BEACH or Island Resorts Marketing universe, the two columns CUST_ID and INV_ID uniquely identify the records in the SALES table. The two columns together are called a composite key. Lookup or dimension tables also may have a composite key. None of the BusinessObjects sample universes contain lookup tables with composite keys, and a recent, well-designed data warehouse also should not require them. However, first-generation data warehouses often used composite keys, and transaction systems still do.

For example, to track changes in customer reference data, the month and year often may be part of the composite key. Figure 7-4 shows multiple records for the customer IDs 106, 207, and 306.

Figure 7-4: This table shows that Customer_id and MM_YY together uniquely identify each record as customer names and addresses have changed over the years.

• Customer 106, Baker moved from Chicago, IL, to Sparta, NJ, in April 1997. The row from customer ID 106, MM_YY 04-1996 contains old data for the same customer.

• Customer 207, Dupont divorced and reverted to her maiden name of Hayem in May 1997. The row from customer ID 207, MM_YY 05-1997 contains old data for the same customer.

• Customer 306, Jones married and changed her name to Whitwell in January 2001. The row from customer ID 306, MM_YY 04-1996 contains old data for the same customer.

When analyzing reservations by city or by last name, the join between CUSTOMER and RESERVATIONS must now include MM_YY to RES_DATE, in addition to the CUST_ID. In earlier versions of BusinessObjects, one could include these joins as separate line items (see Figure 7-5) since BusinessObjects automatically connects multiple join statements with an AND. In fact, when you use join detection or if your database options are set to extract joins with tables, Designer will incorrectly propose multiple join statements. However, with the addition of loop detection, these compound joins must be done in one join statement or Designer will falsely detect a loop.

Figure 7-5: In older versions of BusinessObjects, one could create complex joins with multiple join statements. This kind of join will give a false loop detection in version 5.

To correctly create a complex join, select the join on CUST_ID and double-click it to bring up the Edit Join box. In the Expression box, type AND at the end of the join statement. As soon as you enter AND, Designer changes the join type to Complex. You must continue to manually enter your join statement so that it reads

Customer.cust_id=Reservations.cust_id AND Customer.MM_YY=Reservations.res_date

Unfortunately, it is not possible to select the additional join columns from the drop-down lists. As complex joins are manually entered, be sure to Parse these statements in particular.

Aliases

When Designer sees two join statements between the same two tables, it often will propose an alias to resolve the loop. If your company has multiple SQL tools accessing a data warehouse, your DBA may have resolved many of these issues by creating synonyms or views in the RDBMS. For example, one physical CUSTOMER dimension table could be joined to a fact table two times as synonyms SHIP_TO_CUSTOMER or SOLD_TO_CUSTOMER. Synonyms in the source RDBMS appear to Designer as physical tables, even though they behave much like aliases. If such synonyms or views do not exist in your RDBMS, create an alias to use one physical table in different ways.

Note 

Laura Reeves, co-author of The Data Warehouse Lifecycle Toolkit (John Wiley & Sons, 1998) and TDWI instructor, recommends using physical tables rather than views. Too many views can confuse database optimizers and result in slower query performance.

When you insert a table that already exists in the universe, Designer will force you to create an alias and prompt you to give a new name to the table. Alternatively, you can ask Designer to detect potential aliases, or you can consciously choose tables that you know you want to use in multiple ways.

Detecting Aliases

The sample Island Resorts Marketing (BEACH.UNV) universe contains information about resorts and customers who visit those resorts. Resorts can be located in different countries, and customers can be located in different countries. If you were building this universe from scratch, Designer would have detected a join between the following tables:

COUNTRY.COUNTRY_ID=REGION.COUNTRY_ID (which eventually joins to CUSTOMER) COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID. RESORT.COUNTRY_ID=REGION.COUNTRY_ID

Note in the following screen that these joins cause a loop:

At this point, loop detection would suggest using aliases to resolve these loops. As you are a good data modeler who understands the business rules that 'country' has multiple meanings, you agree and can move directly to alias detection.

1. Create a new universe that includes the three preceding tables.

2. From the pull-down menus, select Tools | Detect Aliases or click the Alias Detection button.

3. Designer suggests that an alias should be created for COUNTRY called COUNTRY_RESORT. Click Create.

4. Click OK to confirm the Alias creation. Note in the structure pane that Designer has inserted what appears to be a new table with the physical table name indicated below the alias (see Figure 7-6), if you have set your Join Graphics as described earlier in this chapter.

   
   Figure 7-6: With aliases, the data model no longer contains a loop. The alias name replaces the physical table name in parentheses.

Inserting Aliases Manually

You may want to insert an alias manually if Designer does not propose an alias that makes business sense or if you know your business meanings in advance. For example, in the Evaluation Kit universe, the region alias is not correctly proposed. Designer proposes breaking the loop by creating an alias for SERVICE_LINE rather than COUNTRY.

1. Select the COUNTRY table from the structure pane.

2. Click the Insert Alias button or select Tools | Insert Alias.

3. When prompted, enter the name Country_Resort as the new alias name.

   

4. Add a join between RESORT.COUNTRY_ID and the new alias, COUNTRY_RESORT.COUNTRY_ID.

   Tip 

   Best practice is to name the alias using the first part of the underlying physical table to more easily keep track of related physical tables and aliases.

   Caution 

   Although many DBAs and data modelers would prefer that the initial physical table also had an alias such as COUNTRY_CUSTOMER for consistency and clarity, Designer requires the physical table name in the universe. If you delete a physical table name that is used in aliases, all related aliases are also deleted.

Aliases in SQL

In generating the SQL, BusinessObjects will use the alias name in the column selection, join statements, and WHERE clause. In the FROM section, BusinessObjects rephrases the physical table name with the new alias name. This is standard SQL syntax and is not unique to BusinessObjects.

SELECT   Country_Resort.country,   Resort.resort,   Customer.last_name,   Country.country FROM   Country  Country_Resort,   Resort,   Customer,   Country,   Reservations,   Reservation_Line,   Service,   Service_Line,   Region,   City WHERE   ( Service.sl_id=Service_Line.sl_id  )   AND  ( Reservations.res_id=Reservation_Line.res_id  )   AND  ( Reservations.cust_id=Customer.cust_id  )   AND  ( Reservation_Line.service_id=Service.service_id  )   AND  ( Customer.city_id=City.city_id  )   AND  ( City.region_id=Region.region_id  )   AND  ( Service_Line.resort_id=Resort.resort_id  )   AND  ( Region.country_id=Country.country_id  )   AND  ( Resort.country_id=Country_Resort.country_id  )

---