Stitch queries

When queries that request facts from multiple tables are performed in IBM Cognos Analytics, the query service performs what IBM Cognos calls a stitch query. Stitch queries consist of subqueries, one for each fact table, that are then merged together on their common attributes from a shared dimension table.

In the following example of a model, Products and Time are clearly shared dimensions, based on the cardinality defined between Returned items fact and Sales fact.

In the following results of the query that is based on this example, you see in the last row that in 2013 there were no returns for Hibernator Pad.

The following pseudo SQL shows that as the query was run, Cognos Analytics created two subqueries, one for Sales and one for Returns. Data from the subqueries is combined using a full outer join operation on the Year and Product columns. In 2013, there were sales but no returns for the Hibernator Pad, hence a null value is returned for the Return quantity.

Select 
	  coalesce(D2.Year1,D3.Year1) as Year1,
  	coalesce(D2.Product_Name,D3.Product_name) as Product_name, 
	  D2.Quantity as Quantity, 
	  D3.Return_quantity as Return_quantity 
from
	(Sub query 1) D2
full outer join
 	(Sub query 2) D3
on
	((D2.Year = D3.Year) and (D2.Product_name = D3.Product_name)) 

Examine the results of each subquery. First, look at the subquery results that retrieve data for the Quantity fact. The query returned the following four records.

Now, examine the subquery results for the Return quantity fact. Notice that there are only three records. There were no returns for 2013.

However, in a stitch query, when there are more records in one subquery than in the other subquery, nulls are returned for the rows where there is no match, as seen in the following query result (which was presented earlier in this section):

In the pseudo SQL example earlier, the coalesce function is used to return the first non-null record set from the subqueries. If both are null, no record is returned. If one is null and the other is not, a record is returned, but the subquery that had no match displays a null value.

For more information about dealing with nulls in calculations in reporting, see this article (www.ibm.com/support/pages/node/6252027).

If dimensions and facts are incorrectly identified, stitch queries can be created unnecessarily, which can be costly to performance. Or the queries can be incorrectly formed, which can give incorrect results.

In some instances, fact detection and stitch queries are not desired. In these cases, you must know the data well and be sure that the relationships are 1 to 1, and the summary row aggregations would be incorrect. This typically occurs with combination analysis scenarios.

For more information on combination analysis, see this article (www.ibm.com/support/pages/node/6252021).