What is Loop
Loop is a join path problem which causes query to return less records than expected. Loop arises when join between tables forms a closed path.
e.g. in below tables, join arrangement between table forms a loop when these tables are joined in universe.
How to detect loop.
There two ways to detect loop, automatically and manually. You can just view the structure in BO designer and detect the loop visually by seeing a closed or circular join path or other way is detect it using loop detection tool.
- Click on Tool
- Automated Detection
- Detect Loop or click on detect loop icon on toolbar.
How loop causes a query to return incorrect data
Now if you look carefully at country table. Country table has two meaning, country where resort exists and country of a tourist.
Now if you create a report to see number of tourist and their country by each resort country, you will get undesired result. Query will only show data for only that country where tourist country and resort country is same, means it will not show tourist which came from other country.
If you build report on above object, designer will generate query like
( City.city_id=Customer.city_id )
AND ( City.region_id=Region.region_id )
AND ( Country.country_id=Region.country_id )
AND ( Resort_Country.country_id=Resort.country_id )
AND ( Customer.cust_id=Sales.cust_id )
AND ( Sales.inv_id=Invoice_Line.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Resort.resort_id=Service_Line.resort_id )
AND ( Service.sl_id=Service_Line.sl_id )
AND ( Service_Line.service_line = ‘Accommodation’ )
Now if carefully notice the WHERE caluse you will see why result are coming wrong.
Above two joins are putting the restriction causing query to return only those tourists which reside in same country of resort
You can fix the loop issue using Aliases or Context in designer.
Resolve Loops using Aliases
Alias is an alternative name given to table or any other object. Same technique can be used to break the loop in designer.
In our loop problem COUNTRY table is serving two purposes, resort country and customer country. We can break this loop by creating an alias table for country table to separate resort country and customer country as.
Country table will join to resort to become resort country
Country_Regioin alias will be joined to region to become customer country
Now if you create same report you will see
There is now one join applying a restriction on the Country table and another join applying a restriction on the Resort_Country table. Now you can see the loop has been broken.
How to create an alias
- Select the table for which you want to create a alias.
- Right click on table and from menu click on alias OR
- from Insert menu select Alias
- Give the new name of an alias
- Remove the join from original table
- Join the alias table appropriately and set the cardinality.
Once you have created an alias, you would also need to redefine the object to use alias table. In above case origin country should use coutry_region.country column instead of country.country column.
Resolving Loops using Contexts
Context is another way to resolve loops in universe. Contexts resolves loop by defining a set of join that defines specific path through tables in a loop. It makes sure that join are not included from different path in the same query.
Generally contexts are used solve a loop caused by multipurpose lookup table.