There are three main types of SQL design traps: fan, chasm, and loops. I describe each of these traps in detail. There is no automated way to identify most types of SQL design traps, but I show how to spot them and how to use advanced data foundation options and techniques, such as shortcut joins, contexts, and aliases, to fix these traps, saving you time and effort.

Testing a universe before publishing it and making it available for use are important parts of the universe development life cycle. I discuss the critical tasks of finding and solving SQL design traps that every universe designer should consider when testing universes. Design traps are logical joins between tables that initially seem to be natural, but actually generate wrong figures if they aren’t identified and fixed before publishing the universe.

SQL Design Traps

Testing universes is a critical part of the universe development life cycle because if the universe designer ignores this phase, the final universe may not satisfy the business requirements for which it was built. In the testing phase, the goal is to examine the universe to make sure that it is aligned with the initial business requirements. To achieve this, the Information Design Tool (IDT) offers many features that can help with testing universes, such as:

• The check integrity wizard: This wizard checks the metadata of universe tables against the actual physical table structure. It tests joins, data types, and length of columns and table names. It also parses all objects (dimensions, measures, attributes, and pre-defined filters) in the business layer.
• The query panel: The query panel from the business layer file can be used to construct queries based on different dimensions, measures, attributes, and pre-defined filters to test the figures’ outputs. This ensures that relationships between tables and joins are correct.
• BusinessObjects reports: The final step of universe testing is to validate the figures of BusinessObjects documents, such as Web Intelligence and Crystal reports. This validation is done by comparing the figures in final reports against business figures generated from the source system. You can check the generated SQL script behind the report if the figures fail validation. This helps you when checking the SQL query, debugging it, and then finding the required fix. This fix normally takes place in the universe; once fixed, you may need to do another test to make sure it’s working correctly.

Note that it is not necessary to end up with a required fix in the universe; sometimes it may be discovered that the problem was introduced in the original underlying data-mart tables, aggregations, or even in the data-warehouse loading mechanism. Some examples of this include when:

• The query returns wrong or unexpected results.
• The table joins initially seem to be logical and all joins seem to be parsed successfully, but they are not.
• The business-layer objects’ (e.g., dimensions, measures, attributes, or pre-defined filters) syntax seems to be correct, and they appear to be parsed successfully as well, but they are not.

These issues can happen if a universe is trapped by one of SQL design traps that I describe.

Design traps, by definition, look like normal or logical joins between tables and are only found through examining joins cardinalities. In these cases, table joins may seem to be logical at first glance, but after a deeper look, you find that it is not logical to join these tables together in that way.

Design traps are very common in relational models, but are very rare in star schema models. This is because the relationships (joins) in the star schema model are always defined between two tables (fact and dimension), whereas in the relational model, there are many tables to join. Therefore, traps are more likely to occur when using the latter design model (i.e., relational) than when using star schemas. This is because you need at least three tables and two joins to create a trap situation.

I explain the differences between the three kinds of traps and how to fix them using different techniques, but before that, here’s an overview of some tables that you need to understand when reading this article.

CUSTOMERS table: This table contains the CUSTOMER_ID as well as all the customer’s information. Figure 1 shows the CUSTOMERS table structure.

Figure 1

The CUSTOMERS’ table structure

ACCOUNTS table: This table is shown in Figure 2. It contains the accounts information for the customer; like his ID, credit-card account number, and the number of credit cards he has.

Figure 2

The ACCOUNTS’ table structure

CREDIT_CARDS table: This table is shown in Figure 3; it contains the following information:

• Credit card number (primary key)
• Customer ID
• Account ID
• Credit card type
• Credit card limit

Figure 3

The CREDIT_CARDS’ table structure

Next, I discuss the different kinds of traps and how to find and fix them.

Fan Traps

A fan trap can occur when you join three or more tables using one-to-many joins. The name fan come from the nature of this trap because the one-to-many join is propagating from one table to another (e.g., it fans out). If you have three tables (A, B, C), a fan trap can happen when you join them as follows:

• The join cardinality between table A and B is one to many
• The join cardinality between table A and B is one to many

You can write this as (A ? B ? C).

To illustrate, I use an example from the banking industry. In this case, a customer may have multiple bank accounts. Each bank account can be used to issue many credit cards. This relationship can be described as one in which a customer may have many accounts and one account can have many credit cards. You can see the relationship between the tables (CUSTOMERS, ACCOUNTS, and CREDIT_CARDS) in Figure 4.

Figure 4

The relationship between the customer’s ID, bank accounts, and credit card accounts—the joins

At first glance, these look like logical and normal relationships between tables CUSTOMERS, ACCOUNT, and CREDIT_CARDS. After you look deeper, however, you can identify what is causing the fan trap. In fact, just because some conditions are missing, it does not necessarily mean that a fan trap will occur. For a fan trap to happen, all the following conditions must be in place:

• The query should contain a measure from the main table. This is the table on the left side (the one side) of the one-to-many relationship. The other linked table is the one on the right, the one on the many side of the relationship. For example, the Customers table (A) is the main table if you are considering the one-to-many relationship between CUSTOMERS (A) and ACCOUNTS (B), whereas the ACCOUNT table (B) is the linked one. Or, the ACCOUNTS table (B) is the main table if you are considering the one-to-many relationships between the ACCOUNTS (B) and CREDIT_CARDS (C) tables, with the CREDIT_CARDS table as the one that is linked.
• The query should contain a measure or dimension from the linked table (e.g., the table on the right [many] side).

The measure used can be a standalone measure that represents facts related to the main table, such as account balance if you are focusing on the ACCOUNTS table. In other cases, the measure used may be an aggregation for a linked table, such as NO_OF_CREDIT_CARDS (which is an aggregation for the linked CREDIT_CARDS table) stored in the ACCOUNTS table.

Consider the following query:

SELECT A.CUSTOMER_NAME, C.CREDIT_CARD_TYPE, SUM (B.NO_OF_CREDIT_CARDS)

FROM CUSTOMERS A, ACCOUNTS B, CREDIT_CARDS C

WHERE A.CUSTOMER_ID =B.CUSTOMER_ID

AND B.ACCOUNT_NUMBER =C.ACCOUNT_NUMBER

GROUP BY A.CUSTOMER_ID, C.CREDIT_CARD_TYPE

Next, translate it into numbers. Assume the following data (shown in Figure 5) is stored in your tables.

Figure 5

Customers’ data stored in tables

In this case, the expected query output would look like the data in Table 1.

In Table 1 you can see that Julia has two Visa cards and two MasterCards. This data is wrong. If you examine the CREDIT_CARD table, you see that Julia has only one Visa card (45807594625414) and one MasterCard (54897564123567). Let’s take a closer look at how the SQL engine generates the results for this query.

The SQL engine first tries to join the CUSTOMERS table with the ACCOUNTS table, and it generates the dataset shown in Table 2.

The dataset generated by joining the CUSTOMERS and ACCOUNTS tables is correct. Julia has one account (1000000120) and two cards. As mentioned earlier, you need to join at least three tables to use the one-to-many tables relationship.

Next, the SQL engine tries to join the generated dataset with the CREDIT_CARDS table and to generate the resulting dataset, shown in Table 3.

Finally, the SQL engine filters only the selected columns, CUSTOMER_NAME, CARD_TYPE and NO_OF_CARDS, to display data (shown in Table 1).

Now I analyze the problem and explain why the wrong results were returned by the query:

• You joined three tables using the one-to-many relationship.
• You used one field (dimension or measures) from the right side table (CREDIT_CARDS, table). In this case, the card type.
• Finally, you used one measure (any measure) from the middle table (ACCOUNTS, table B). In this case, the NO_OF_CARDS measure.

Next, I show you how to use NorthWind Universe to learn how to fix fan traps using IDT functions.

Start by opening the Information Design Tool (IDT) by following menu path Start > SAP Business Intelligence > SAP BusinessObjects BI platform 4 Client Tools. In the screen that opens (Figure 6), select the Information Design Tool option.

Figure 6

Open the IDT

In the next screen (Figure 7), open the NorthWind Data Foundation layer and check the relationship between the following tables:

• Customer
• Orders
• Order details

Figure 7

Open the NorthWind Data Foundation

The logical relationship between these three tables is to join the Customers table with the Orders table using the one-to-many cardinality join. This is because one customer may have many orders, and one order may be placed by only one customer. The orders table should also be joined with the order details table using a one-to-many join. This is because one order may have multiple (many) detail records in the orders detail table, whereas an order detail record can only describe one order.  This fulfills the first fan-trap condition, which is to join three or more tables together using one-to-many cardinality. You can see the relationship between the customers, orders, and order details tables in Figure 8.

Figure 8

The conditions are in place to create a fan trap

As mentioned before, you can still create a query and get correct results if you didn’t fulfill the second condition of having a query display measure from the middle Orders  table with a dimension from the last table, “Order Details.”

Now I examine a fan trap using a numerical example (Figure 9):

1. Open the NorthWind business layer (#1, on the left).
2. Select Queries from the middle-right panel (#2).
3. Click the insert query icon (#3).

Figure 9

Create a new query

This action opens the Query Panel in which you need to create the query using the details in Table 4. Drag and drop the corresponding dimensions and measures as shown in Figure 10, and then click the OK button to run the query.

Figure 10

Drag and drop dimensions and measures to construct the query

Once run, the query results should look like the screen shown in Figure 11.

Figure 11

The query results for query #1

As you can see from the query results (Figure 11), the customer (Thomas Axen) placed two orders. The first order (36) has only one detail record in the order detail table, which shows that he purchased 200 orders of Northwind Traders Clam Chowder. The customer made another order (63) for 50 Northwind Traders Syrup and three Northwind Traders Curry Sauce (two details records). The Shipping Fee for each order is $7.00.

Now remove Orders Details: ID and Product Name from the query and see what happens. The new query results are shown in Figure 12.

Figure 12

The new query results

In Figure 12, once the new query is run, you can see that the shipping fee for the first order (36) is $7.00, as expected, but that is it $14.00 for the second order, which is double the expected amount. This is because the first order has only one order detail record, but the second one has two order detail records for two different products.

Now, look at the SQL query script generated from the query. After you click the View script button (Figure 12), the query code appears in the next screen (Figure 13). As you can see, it is one query. The joins all mention three tables (Customers, Orders, and ORDER_DETAILS), as well as the aggregated measure from the middle table (Shipping Fee, referring to the FROM clause in Figure 10), and finally a measure or dimension from the last table (Quantity).

Figure 13

The query script showing that a fan trap occurred

Click the OK button to save the query. Once the fix has been applied, you need to run it again. In the following sections I show you how to locate and fix fan traps.

Finding and Fixing Fan Traps

As mentioned previously, fan traps are not as harmful as other design traps. You can query tables trapped by fan traps and get the correct results if you don’t use measures from the middle table and fields from the third table. As a matter of fact, you can solve many potential fan traps in your universe by fixing them in the business layer. However, you need to create a work-around and special handling to avoid creating conditions that might case a fan trap. On the other hand, to fix fan traps, all you have to do is enable a simple feature in your universe preferences. This feature is the Multiple SQL statement for each measure option in the properties of the business layer. You can access it by following these steps (Figure 14):

1. Open the NorthWind business layer (#1).
2. Select the Business Layer tab (#2).
3. Then select the NorthWind BusinessLayer option (#3).
4. Go to the Query Options tab (#4).
5. Select the Multiple SQL statements for each measure check box (#5). Save the business layer by pressing Ctrl + S.

Figure 14

Enable the Multiple SQL statement for each measure feature

This option checks to see if the measures used in your query are retrieved from one or multiple tables. In turn, the query generator generates a separate query for each measure set.

Now, go back and open the query that you saved earlier and click the execute query icon. This generates a query output like the one shown in Figure 15.

Figure 15

The query results after enabling the multiple SQL statement for each measure feature

As you can see in Figure 15, the issue has been fixed and now your query returns the correct values. To find out why, click the edit query icon (not shown) and then the View Script button (not shown). This opens a screen like the one in Figure 16.

Figure 16

The new script generated from the same query after applying the fix

The universe engine generates separate queries for each set of measures in your query. The set of measures are measures that come from the same table. This is the main purpose of selecting the Multiple SQL statements for each measure check box that you checked earlier (in the universe properties). Using separate queries for each set of measures breaks the conditions required for fan trap and so you will not be trapped.

Chasm Traps

A chasm trap can take place when two (many-to-one) joins converse with one table. This means that the middle table exists on one side for both one-to-many joins. The join sequence should be many-to-one between the first two tables and then one-to-many between the other two tables. To illustrate, assume that you have three tables (tables A, B, and C). A chasm trap occurs when you join them as follows: A ? B ? C.

For example, the customer may have multiple current bank accounts. The customer also may have many credit cards. This relationship can be formulated as one customer maps to many accounts, and one customer maps to many credit cards, as shown in Figure 17.

Figure 17

Chasm trap

As you can see, the joins seem to be valid, and nothing appears to be wrong with this logic. So, consider the following query:

SELECT A.CUSTOMER_ID, COUNT (C.CREDIT_CARD_NO), COUNT (B.ACCOUNT_NUMBER)

FROM CUSTOMERS A, CURRENT_ACCOUNTS B, CREDIT_CARDS C

WHERE A.CUSTOMER_ID =B.CUSTOMER_ID

AND A.CUSTOMER_ID = C.CUSTOMER_ID

GROUP BY A.CUSTOMER_ID

Now, translate this into numbers. Assume the data shown in Figure 18 is stored in your tables.

Figure 18

ACCOUNTS’, CUSTOMERS’ and CREDIT_CARDS’ tables

In that case, the output result of your query would like Table 5.

The main problem with chasm traps is that there is no clear relationship between left and right tables (in this case, the ACCOUNT [A] and CREDIT_CARDS [C] tables). Here, the relationship between the CUSTOMERS and ACCOUNTS tables is straightforward, and you can easily tell which account is related to which customer. The relationship between CREDIT_CARDS and CUSTOMERS is also clear as you can tell which credit card is related to which customer. However, finding a clear and direct relationship between the ACCOUNTS and CREDIT_CARD tables becomes a challenge. Here you can’t see which account is related to which credit card and vice versa.

Now try to simulate how the SQL engine translates the query to have a better understanding about why the query returned the wrong results.

The SQL engine first tries to translate the join between CUSTOMERS table and ACCOUNTS table. As you can see Margaret has two accounts, 1000000191 and 1000000192. The dataset generated from this join is shown in Table 6.

Next, the SQL engine translates the join between the generated dataset (Table 6) and CREDIT_CARDS table using CUSTOMER_ID. The dataset shown in Table 7 is generated as a result of this join.

As you can see, the query results in four records because Margaret has two credit cards (one Visa and one MasterCard) and you already have two records in the initial dataset because Margaret has two accounts. The SQL engine identifies that there is no clear relation between accounts and cards based on the defined joins (using CUSTOMER_ID) and therefore it does all the calculations. Each account is related to all the credit cards, which generates four records (2 x 2 = 4).

Finally, the SQL engine tries to filter and calculate the final results based on the last dataset (which contains four records). The final output should display the number of accounts and number of credit cards, in this case, four for each.

Again, using the NorthWind universe as my example, let’s try to identify a chasm trap. You have the following tables:

• Order Details
• Products
• Purchase order details

The tables are joined using two one-to-many joins conversing to the Products table in the middle, as shown in Figure 19.

Figure 19

Chasm trap in the NorthWind universe

The Order Details table contains the customer’s orders’ details. As you can see, customers can place many orders for the same product (e.g., one product can be ordered many times). The Purchase Order Details table contains purchase order inventory information. Using this table, if you have low inventory of one product, you can issue a purchase order to increase this product’s inventory. The purchase order can contain many products at the same time; therefore the relationship between the Products table and the Purchase Order Details table is one to many.

The question now is, can you identify (with the currently defined relationship) the specific purchase order that was used to get the current product sold by a specific customer order? In this case, the answer is no, because there is no direct relationship between the Order Details table and the Purchase Order Details table. This is also not logical and results in erroneous query results when the following query is run.

Check the following query generated from the Orders Details and Products tables (Table 8). This query displays order details for the Chai (tea) product (product ID = 1).

The result of this query shows how much of the Chai product (the quantity) is sold in each order. The query results should look like the screen in Figure 20.

Figure 20

The quantity of Chai sold by order

As you can see, Chai was sold in two orders. The quantity of the first one (order 32) is 15, and the quantity of the second one (order 63) is 25. This brings the total number of Chai items sold to 40.

Now look at the same Chai product, but from the inventory point of view. The query in Table 9 displays purchase orders containing the Chai product and how many items are in each purchase order.

This query shows how many items of Chai are purchased, by purchase order. The query result is displayed in Figure 21.

Figure 21

Query results for Chai purchased by purchase orders

The query results show that the Chai product was purchased using three purchase orders (purchase orders 238, 281, and 290, respectively). The total received Chai quantity is 81.

Now query the quantity of Chai purchased from both tables (Table 10).

Once run, the query result looks like the screen in Figure 22.

Figure 22

Query results (chasm trap)

As you can see, the query results, 120 sold and 162 purchased, are not as expected. The query generated should return the number of Chai items sold (40 items, per the first query) plus the number of Chai items purchased (81 items according to the second query). This error is the result of having two records generated from the first query (Order Details) and three records generated from the second query (Purchase Order Details). In this case, the sold quantity is multiplied by three (40 x 3 = 120) and the purchased quantity is multiplied by two (81 x 2 = 162).

Next, I explain how to fix chasm trap using:

• Context
• Alias tables

Solving Chasm Traps Using Context

Context is a defined join path that tells the SQL engine to generate a separate SQL query for each context. It is a logical grouping. In this example you have two clear contexts: one for Orders and the other one for Inventory. To define a context, you need to select joins related to the context purpose.

There are two ways to create a context:

• Automatically, by using the detect-context function.
• Manually, by inserting a new context and selecting joins to be included in that context.

First, I show you how to test the automated method by using the detect-join feature.

To detect a context, follow these steps (Figure 23):

1. Open the data foundation layer (#1).
2. Navigate to Aliases and Contexts in the middle-right panel (#2).
3. Click the detect-context icon (#3).

Figure 23

Steps to detect contexts

This opens a screen like the one in Figure 24 in which you can select your desired contexts. The joins that are included in the contexts are marked by green plus-sign icons, and excluded joins are marked with red minus-sign icons.

Figure 24

Select the contexts you want to insert

The detect-context feature detected that there are two possible contexts to create: one based on the order details table and the other one based on the purchased order details table. Select both of them and then click the OK button to save the data foundation.

Now return back to your query and run it again (Figure 25). Congratulations. The chasm trap was fixed using the contexts you created.

Figure 25

The new contexts correct the chasm trap when the query is re-run

You can also create a context manually and add and exclude joins, but you first need to make sure that you define the right join paths at the outset, or you may face problems later on. In cases like this, it is only after spending hours investigating that you discover that these issues were caused by the context being defined incorrectly.

To define a context manually, follow these steps (Figure 26):

1. Open the NorthWind Data Foundation (#1).
2. Navigate to the Aliases and Contexts tab (#2).
3. Click the insert-context icon (#3).

Figure 26

Create a context manually

Then follow these steps to create the orders context (Figure 27):

1. Enter Orders in the context name (#1).
2. Click the Edit Graphically button (#2).
3. Click the join between the Products and “Order Details” tables (#3). (A green plus-sign icon appears when the joins are successfully included in this context.)
4. Click the join between the Products and “Purchase Order Details” tables (#4). Again, you see the green plus-sign icon. Click it again to convert it to red minus-sign icon. This means that this join is now excluded from this context.

Figure 27

Order contexts

Using the same steps as you took previously, create the inventory context. But this time, include the join between the “Purchase Order Details” and Products tables, and then exclude the join between the Products and order details tables. The inventory context should look like the screen in Figure 28.

Figure 28

Inventory context

Solving Chasm Traps Using Alias Tables

The context method is the preferred way to fix chasm traps, because you don’t need to create any extra objects or modify any table joins. Your design remains the same but using a context tells the SQL engine how to generate the right query. Another method for doing this is to create an alias table. When using the create alias table method, you create an alias table from the middle table to break the relationship between the left and right tables.

To illustrate, go back to the table example used earlier. There are three tables (A, B, and C) and a chasm trap occurs because the relationship between those three tables is as follows: A ? B ? C. The solution is to create an alias table from table B; in this case, called table B*. The relationship should now be like this: A ? B and B ? C. As you can see, now there is no connection between A and C and you can adjust your report query. This can be implemented by using a combined (union) query or the merge dimension feature. From a design perspective, this solution fixes chasm traps and you don’t have to worry about getting the wrong results or an incorrect number of records again.

To create an alias table from the Products table, follow these steps:

1. Select the Products table and right-click. Then select Insert > Alias Table… from the context-menu options (Figure 29).

Figure 29

Insert the alias table for the Products table

2. Enter Inventory Products at the new name for the alias table in the screen that opens (Figure 30). Then click the OK button.

Figure 30

Select and rename the alias table

3. In the screen that opens (Figure 31), join the “Purchase Order Details” table with the “Inventory Products” (Products) table instead of the Products table.

Figure 31

The alias “Inventory Products” (Products) table is created

Finding and Fixing Loops

Loops can take place when three or more tables are joined together. This results in fewer records being produced than expected when those tables are queried at the same time. You need to join at least three tables together to create a loop. For example, if you have three tables (A, B, and C) the relationship between those three tables should look like this (A ? B, B ? C, and C ? A). Since there is no start or end for this relationship, loops look like a circle, hence their name. As a result, you are not able to see how these tables are linked using just one scenario. Loops are the easiest design issue to be discovered, but they’re not the easiest to solve. Again, using the example of the NorthWind universe, I show you how.

In this example I removed the context that was created earlier to solve the chasm trap. I also created an extra join between the Order Details and Purchase Order Details tables using the Purchase Order ID reference key. Now, check the relationship between the following tables again (Figure 32):

• “Purchase Order Details”
• Products
• “Order Details”

Figure 32

The loop

To visualize loops you need to follow these steps (Figure 33):

1. Open the Data Foundation (#1).
2. Navigate to Aliases and Contexts (#2).
3. Go to the Loops area (in the middle top of the figure) and click the visualize loops icon (#3).

Figure 33

Visualize the existing loops

This opens a screen (Figure 34) in which you can see a list of the existing loops. In this case, you should see only one loop (in the pane on the left of Figure 34) because you have created only one. Select the loop on the left of the screen, double-click, and the details for that loop open as shown on the right of the figure.

Figure 34

View the details of the selected loop

Before you start fixing this loop, look at how it affects the last query results by following these steps:

• Go to the Business Layer.
• Go to Queries.
• Open and execute the last saved query. This results in an error message like the one in Figure 35.

Figure 35

The query execution error message caused by the loop trap

Next I show how to fix this look and run and check the query again. There are two ways to fix loops: using a shortcut join or using the context method.

Fixing Loops Using Shortcut Joins

A shortcut join is an option that you can choose when creating a new join. If this option is checked, then the SQL engine ignores this join when a loop takes place. This happens when you create a query that selects from table A, B, or C, assuming that there is a loop between those tables. This join is only considered by the universe SQL engine if you try to select from the two tables involved in this join.

Consider the following example. Tables A, B and C are joined (as shown in Figure 36). This causes a loop as table A is joined with table C, table B is joined with table C, and table C is joined with table A—a loop. The dotted line between tables A and B represents a shortcut join, while the solid line between B and C, and C and A are normal joins. If you tried to write a query that selects from tables A, B, and C—the shortcut join—the dotted one will be considered as if it does not exist. As a result, only two joins will be included in your query (B and C and C and A—leaving out A and C). The shortcut join will be considered a normal join in one case only: when you write a query that selects from tables A and B only. In that case, instead of taking the long way (e.g., table B to C and then table C to A) to create a relationship between A and B, a shortcut join is used. This is why it’s called a shortcut—because it takes the short way instead of the long one.

Figure 36

A shortcut join

To implement this, follow these steps (Figure 36):

• Open the Data Foundation layer.
• Double-click the join between the “Order Details” (Table 1) and “Purchase Order Details” (Table 2) tables to edit it.
• Check the Shortcut join check box and then click the OK button.

Figure 37

Enable the shortcut join option

The shortcut join now appears as a dotted line, as shown in Figure 38.

Figure 38

Re-check the loop

Navigate back to the Loops area (Figure 34) and click the detect loops icon again. This opens the screen in Figure 39 in which, as you can see, the loop is solved and is no longer listed.

Figure 39

The loop is fixed

Now go back to the Query Panel in business layer and execute the last query that you used to examine the loops. Instead of getting an error message you see that that query can now be executed successfully (Figure 40).

Figure 40

With the loop fixed, the query can be executed

Note that although the query can be executed, it returns the wrong results because you removed the context that you created to solve and fix the chasm trap (Figure 26). The shortcut join only resolves loop traps—you still need to check and see if there are any other kinds of traps before moving on.

In the next section I show you how to use the context method to solve loops. This time you are able to fix both traps—chasms and loops—at the same time using this one method.

Fixing Loops Using the Context Method

You already learned what context is and how to use it to fix chasm traps; now I show you how to use this method to fix loops.

Before I go into details about how to create a context, let me explain how this method solves loops. As discussed before, a context is a logical grouping for related joins that makes sure a separate query is issued for each context. The idea here that you want to implement a context to break the loop. This can be done by categorizing loop joins into two contexts.

To solve loops using the context method, you need to exclude the same join that you previously selected as a shortcut join from both the orders’ and purchase order contexts.

To do this, navigate to the Aliases and Contexts screen (Figure 20) and click the detect-context icon. This opens a pop-up window with an error message like the one in Figure 41.

Figure 41

The detect-context option results in an error message

This message states that using alias tables to resolve loops is the preferred method over the context method. I discuss this in more detail later, but for now, insert two contexts manually, as shown in Figures 42 and 43.

Figure 42

Insert order contexts

Figure 43

Insert inventory contexts

Now navigate back to the Loops area again (Figure 38) and click the detect-loop icon. This opens the screen in Figure 44 in which you can see that the loop was detected and fixed using the context method.

Figure 44

Loop is fixed using the context method

Go back to the Query Panel from the business layer, re-run the query, and check the results. This time you get the correct expected results because you solved the chasm trap and loop at the same time using the same context method.

Fix Loops Using Alias Tables

The alias table method is the only other method for fixing loops. In this method you make an alias table from the PRODUCTS table. Then use the original PRODUCT table with Inventory side, and the PRODUCTS alias table with the order side. This method breaks the loop as there are now two independent copies of the products table.

Open the NorthWind Data Foundation (Figure 27), navigate to the Aliases and Contexts screen, and click the detect-aliases icon (Figure 30). This opens a screen like the one in Figure 45.

Figure 45

Fix the loop by creating an alias table (using the detect method)

This creates an alias table named “Products_Order_Details” that is used as the product table for Orders side (Figure 46).

Figure 46

The loop is fixed by creating an alias table for the Products table and using it with the “Order Details” table (Order Side)

Taha M. Mahmoud

Taha M. Mahmoud is a PMP, TOGAF, ITIL, and CSM, and a senior BI consultant, BI project manager, and solution architect. He has more than seven years of experience consulting and deploying successful BusinessObjects projects in the banking and telecom industries. Taha is the author of the book, Creating Universes with SAP BusinessObjects.

You may contact the author at tabouelfootooh@ejada.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.