Creating Loops on the Data Foundation

A loop is a join path issue that arises from the way tables relate to one another in a relational database and form a closed path. Loops can produce instances where a query returns too few rows of data.

For example, here the universe designer has added joins between the tables Showroom and Country to create two linked sets of information:

• One set links the sale details, the client, the client's region, and the client's country of residence.

• The other set links the sale details, the showroom, and the country where the showroom is located.

• Together, these joins form a loop.

Suppose that users of the Motors business layer want to produce reports showing the revenue generated by sales, including the showroom location where the products are sold and the address of the clients. The designer adds the tables that provide this information, and creates the joins as shown in the previous example. The designer has also created objects for the Showroom Country, Client Country, and Sales Revenue.

Since joins restrict the data that is returned by the query, in a loop the joins apply more restrictions than the designer intended, and incorrect data is returned.

If the loop were allowed to remain, and a query was run using the Showroom Country, Client Country, and Sales Revenue objects, the report results would be incorrect. The report would suggest that clients bought only products in the same country where they live. However, the report would not show any clients who bought products in a country different from the country where they live.

It can be difficult to determine the problem when you examine the results. The data foundation has a tool that detects loops and suggests how to resolve them for you.

Before using Visualize Loops, verify that all the tables in the schema are joined correctly, and that all cardinalities are set correctly.