What is Loops in BO universe Designer and how to resolve it?

Loops is nothing but a closed path between tables. In other word, loop is a set of joins that defines a closed path through a set of tables in your universe designer schema. Loops occur when joins form multiple paths between universe structure tables.

Why loops in a universe schema and not in the database?
In a database, multiple paths between tables may be valid and implemented to meet specific user requirements. When each path is included individually in a query it returns a distinct set of results. However, the schema that you design in universe Designer often needs to allow queries that include more than one path, which a relational database may not be designed to handle, so the information returned can be incorrect.

The rows that are returned are an intersection of the results for each path, so fewer rows are returned than expected. It is also often difficult to determine the problem when you examine the results.

How Does a Loop affect report result?
In the above model, universe structures are designed with loops and it will restrict your report result. Without resolving loops, it will reduce the number of records in your report.

I want to see the Product Manufacturer country wise Product sales and I would expect the below result.

But you will get the below result because the sales happened only in USA & China and loops create additional joins between the tables hence the result shows below.

Below are the join created the when you add only 3 objects to the report query.

( COUNTRY.COUNTRY_ID=PRODUCT.COUNTRY_ID )
AND ( COUNTRY.COUNTRY_ID=REGION.COUNTRY_ID )
AND ( REGION.REGION_ID=CUSTOMER.REGION_ID )
AND ( PRODUCT.PRD_ID=SALES_FACT.PRD_ID )
AND ( CUSTOMER.CUST_ID=SALES_FACT.CUST_ID )

Country table act as a reference table for PRODUCT & REGION and PRODUCT table holds the lookup for the product manufacturers country information and REGION table holds the purchaser / Customer country information.

AND ( COUNTRY.COUNTRY_ID=REGION.COUNTRY_ID )
AND ( REGION.REGION_ID=CUSTOMER.REGION_ID )
AND ( CUSTOMER.CUST_ID=SALES_FACT.CUST_ID )

So the above joins are creating additional restriction hence the results shows less number of rows than expected.
There are 2 ways to resolve the loops.
1. Alias
2. Context

How to resolve the Loops using Alias:
Alias is a reference table (duplicate table) from the original universe structure and this can be used for 2 difference purposes. In this example we have country table to serve multiple purpose (1. Act as reference table for PRODUCT Manufacturers country and 2. Reference table for customers country). So, lets create an alias table for COUNTRY and name it like COUNTRY_PRODUCT & COUNTRY_REGION.

Create additional objects for country (country-Product Name, Country-Region name) and use Country-Product name object in place of Country Name in the above report and you will get the exact result.

How to resolve the loops using context:
Context is nothing but collection of joins in which designer can choose which path to use. Context are create based on the business needs and in our example, we two different type of subjects. One is for Country wise manufacturer sales and another one is customer wise country sales. So we will create 2 context for each subjects.

Context 1 – Country_Mfg_Sales
Context 2 – Country_cust_sales

Created 2 contexts and added the required join paths into the respective context. Now your report will get the joins only from respective subject and return expected result.

Note: You can use Designer to automatically detect loops and propose candidate aliases and contexts that you can insert in your schema to resolve the loops. But it will help when you have less number of tables in your universe structure. The suggestion would be always use loops option to validate the loop but the solution would be define by the designer based the report requirement.

These are 2 methods you can resolve your loops in the universe design.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.