Loop and COntext in Universe

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.

1. Click on Tool
2. Automated Detection
3. 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

WHERE

( 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.

Country.country_id=Region.country_id

Country.country_id=Resort.country_id

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

Country_region =Region.country_id

Country.country_id=Resort.country_id

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

1. Select the table for which you want to create a alias.
2. Right click on table and from menu click on alias OR
3. from Insert menu select Alias
4. Give the new name of an alias
5. Remove the join from original table
6. 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.

What is a context?

A context is a group of path that defines a specific path for a query. Any objects created on a table column which belong to specific contexts is naturally compatible with all other objects from same contexts. When objects from two or more contexts are used, separate SQL is generated and results are then merged in a micro cube. This makes sure that no incorrect result is generated due to loop or any other join path issue.

Testing Contexts

When context exist in universe. Designer can generate three types of queries.

• Ambiguous query
• Inferred query
• Incompatible Objects query.

Whenever you create a context you should check context against these three queries two test correctness of context.

Ambiguous query

If you have multiple contexts in universe, try creating a report which includes objects common to both the context. This situation does not give enough information on which context to use while generating the query and inturn it prompts all available contexts to user to choose from. Once user selects the context, query is generated accordingly.

Make sure Allow selection of multiple context option is selected from SQL tab of universe parameters as user might select multiple context in case of ambiguous query.

Incompatible Objects query.

If you have used object in a report which belong to multiple context and objects which are unique to each contexts. The tool creates multiple SELECT statements for each context and then result is merged in to present in single table form this is called as Incompatible Objects query.

Inferred query

Inferred query is query which gives enough information to tool to choose the right context without prompting to user.

When you have context in universe make sure you test the contexts by creating all three types of query and observe the behavior of tool in each query type.

Resolving Loops using Shortcut Join.

Apart from alias and context, shortcut join can also be used to resolve the loops in universe.

When do I use Contexts in a Universe?

It’s really quite simple. If your universe has multiple fact (transaction) tables, you will need one context for each fact. And just to be clear, for this purpose, I am defining a fact table as the lowest level of transactional data. There may be multiple tables that belong to one fact, such as Invoice Header and Invoice Detail. In this case, the Invoice Detail table would be considered the lowest level of transactional data.

So, if your universe has only one fact table, you don’t need any contexts. But if you have multiple facts, you will need contexts.

OK, So what is a Context?

A context is a list of joins that define a logical path through a universe. Contexts are used to resolve loops that are caused by multiple fact tables accessing common dimension tables. The joins that belong to a context are the joins from the fact table, leading to any and all dimension tables that might be needed for that fact. In the picture below, the SALE_MODEL table is the fact. The joins highlighted in blue, are all members of the Sales Context.

As you can see, the highlighted joins include not only the joins that touch the SALE_MODEL table, but also joins that extend to distant dimension tables, such as REGION, that contain data related to the fact table.

IMPORTANT: Once you add contexts to a universe, all joins must be a member of at least one context (Shortcut joins are an exception to this rule). Joins that are not members of a context are called isolated joins. Isolated joins will not be recognized by the reporting tools. (Note: This rule will go away in BI 4.0.)

As you can imagine, joins that do not connect directly to a fact table, can be in multiple contexts. For example, in the image above, the join between the CLIENT and REGION tables would be used for both Sales and Rentals, so it would be a member of both contexts.

When properly implemented, contexts will prevent Fan Traps and Chasm Traps from returning incorrect results. If a user runs a query which includes objects from two different contexts, Web Intelligence will generate two separate SQL statement, and join the results on the common dimensions after results have been retrieved.

If you are using universes with Crystal Reports, you will need to change the value of the JOIN_BY_SQL parameter to Yes. This is because Crystal Reports doesn’t support the generation of separate SQL statements like Web Intelligence. So the JOIN_BY_SQL parameter will cause Crystal Reports to generate multiple select statements in a single SQL sentence with a FULL OUTER JOIN between them.

How do I create a Context?

There are several methods for creating contexts. They can be created manually, or automatically. The most accurate method is to create them manually. If you choose to create them automatically, you will still need to manually check to make sure that the context is correct.

To create the context manually, Ctrl-Click on the joins connected to the fact table, as well as all joins to dimension tables that contain data related to that fact. Once you have them all selected, click on the Insert Context button.

This opens the New Context box.

Enter a user friendly name for the context, and a user friendly description. More on this later. Click OK, and you have a new context created.

If you wish to create your contexts automatically, make sure that all joins have the correct cardinalities set. The detection tool uses the cardinalities to determine which joins to include in a context. There are several ways to create a context automatically, but they all work basically the same way, so we’ll just talk about one way.

Once you have verified that your cardinalities are set correctly on all joins, click the Detect Context button.

First, this button will look for fact tables. It recognizes a fact table as a table that is at the many end of all of its joins. Next, it checks to see if there is an existing context by the same name as that fact table. If not, it formulates the context, based on the cardinality of the joins in the universe, and recommends the context. You can then choose to accept, or reject, the proposed context. If you accept it, you can rename it with a more user friendly name. Once you have added the context, you can edit the context by double clicking on it in List View.

Editing an Existing Context

There are several things that may need to be edited in  a context. These include the name, description, and which joins are included. To edit a context, open List View, and double click on the context in the right pane. In the Edit Context box, you can change the name, add, or edit, the description, or add or remove joins. All joins will be shown in this box, but only the ones highlighted in blue are members of the context. To add a join, find it in the list, and click on it. To remove a join, find the highlighted join in the list, and click on it. (NOTE: You don’t have to hold down the Ctrl key when clicking on the joins in this box.)

What about the User Experience?

There has been an eternal debate regarding the user experience with contexts. When you add contexts to a universe, users may occasionally be prompted to select a context when running a query. There are those who believe that this should never happen. I disagree. I believe that it should be very rare that a user is prompted to select a context. But I wouldn’t say it should never happen. A context prompt, in my mind, is no different than any other prompt. It’s a way to allow the user to select query options at run time.

Having said that, I find that many universes prompt for contexts unnecessarily. We want to avoid this. Most of the time, a user is prompted for a context for one of two reasons:

1. The query includes no objects from a fact table. In this case, the prompt may be legitimate.

2. The universe isn’t built properly.

If users are being legitimately prompted for a context, that’s fine. Make sure that the contexts have user friendly names (Proper case and no underscores), and a user friendly description, that helps the user make the right choice. Note that, in Web Intelligence, if the user is prompted for a context, the prompt will appear each time the query is refreshed. This is the default behavior, but can be changed in the query properties.

If the users are being prompted for a context, but shouldn’t be, then you may need to take steps to minimize this. For example, if an object should always use one context, you can force it through that context by adding the fact table from that context into the Tables button of that object. This will prevent that object from prompting for a context. This is an excellent method for keeping those prompts to a minimum.

In Conclusion

When properly implemented, Contexts make universes much simpler for the users, as well as the developers. If you take the time to understand the correct use of Contexts, you will find them to be quite simple to implement and maintain. And if you don’t get it right the first time, fear not. Contexts are easy to edit.