Resolving Loops Using Contexts

Another way to solve a loop in the data foundation structure is to create contexts. A context resolves a loop by defining a set of joins that identify one specific path through tables in a loop. It ensures that joins are not included from different paths within the same SQL query.

You use contexts in a schema that contains multiple fact tables that share lookup tables. An example of this situation is the Sale table in the Motors Data Foundation. The Sale table contains rows of data for cars both sold and rented. The Sale_Type column is used as a flag to indicate the type of transaction (S = car sale, R = car rental). Without the restriction, the result set of the query would produce rows where the Sale_Type column is equal to either ‘S’ or ‘R’.

Previously, you defined this restriction to ‘S’, so that any object based on the table, or joins passing through that table, would produce query results covering only car sales. One way to retrieve data concerning rental sales is to create an alias of the SALE table called RENTAL, set the restriction to ‘R,’ and create an alias table of the SALE_MODEL table called RENTAL_MODEL.

Creating the aliases tables, however, creates a loop. The query does not know which table to go through to move from the CLIENT table to the MODEL table, the SALE or the alias RENTAL table.

Any objects derived from tables included in a context are compatible with each other. When a query is made with objects related to separate contexts, more than one SELECT statement is inferred and run. The results of the queries are then merged in the end user's tool. This method avoids incorrect results that might arise due to a loop or other situations with alternative routes.

The data foundation has a utility that detects contexts by identifying tables that have only the many ends of joins attached.

No joins flowing back from one-to-many are included. To view the flow of contexts within a structure, you can arrange the table joins so that they flow as many-to-one from left to right across the structure.

Included in the context are all the tables that can be reached by following the flow from many-to-one (N -1). Tables that can only be reached by flowing back from one-to-many (1-N) are not included in the context.

In the Sales and Rental example, you can follow two different paths from the Client table to the Model table.

• By way of Rental and Rental_Model:

  

• By way of Sale and Sale_Model:

  

Each context represents what may be inferred in a single SELECT statement. Any query that infers SQL code from multiple contexts infers multiple SELECT statements.

You then create different sets of objects from the tables in the different contexts. As a result, users can run queries that return data under specific contexts.

The following two methods are possible to detect contexts:

1. Using the main menu of the Information Design Tool → Actions → Detect → Contexts.
   
2. Using the navigation tabs of your data foundation. Select Aliases and Contexts on the top of your menu.
   

The Detect Contexts window appears. Select the contexts and click OK. The accepted contexts are displayed in the Contexts table of your data foundation.

A single click on the context name displays the tables that are involved in the context. The green plus icon in the visualization indicates the tables involved in the Select context, and a red minus icon indicates those tables excluded in the context.

You can also create a context manually by selecting the Insert Context icon on your data foundation menu. Double-click the new context name and click on each join that you want to add to your context. Rename the context and exclude the joins that are not part of the context.

By double-clicking on the context name, the Edit Contexts window appears, and you are able to edit the context definition for each context. Enter a meaningful name and description for the context because this information could be prompted to the end-user.

If you double-click one of the join expressions, you can change the status of the join. It can be one of the following:

• Included -The context specifically includes the join.

• Excluded - The context specifically excludes the join.

Once contexts are in place, the end-user query tool generates the SELECT statement(s) in one of three ways. To test contexts, make at least three queries, one to test each form of SQL generation when applying contexts:

• Inferred query

• Incompatible objects query

• Ambiguous query

Inferred query The query contains enough information for the correct context to be inferred. For example, create a query using the Showroom, Model, and Sales Revenue objects. The Sales Revenue object is a sum on the SALE and SALE_MODEL tables, which are part of the Sales context. The query, therefore infers that the Sales context is the one to use for the query.

Incompatible objects query Objects that reference tables with joins from two different contexts are included in a single query. The query tool creates one SELECT statement for each context and executes the queries. The query tool then unites the results together so that the report data can be presented in a single table. For example, you can run a query containing the Showroom and Model objects, with both Sales Revenue and Rental Revenue objects. However, no single context can contain all the joins necessary to include the SHOWROOM, Sales, SALE_MODEL, RENTAL_MODEL,RENTAL, and MODEL tables, to which the four objects refer. It therefore generates two SELECT statements in the query and merges the results.

Ambiguous query This situation occurs when a query includes objects that, when used together, do not give enough information to determine which context to use. When a query is ambiguous, the user is prompted by a dialog box in the Query Panel to select the appropriate context. When the user selects a context, it inserts the corresponding tables and joins into the SQL query. For example, you can run a query containing only the Showroom and Model objects. In this instance more than one context contains all the joins necessary to include the SHOWROOM and MODEL tables, to which the two objects refer. The user is then prompted to identify the correct context to use by displaying the Context Selection dialog box. When the user selects one of the contexts and clicks OK, a SELECT statement is inferred, using the join path for the context chosen.