So what exactly does it mean when Cognos performs a stitch query? Let’s start with a business scenario and question:
A Manager asks a Report Developer to provide a report showing a comparison between Actual Sales and Forecast Sales for any years we have data.
But these measures come from two completely separate facts: the Forecast Fact and Sales Fact. How would Cognos understand their relation to each other? To achieve this Cognos uses a conformed dimension(s) (dimensions that occur in both facts) as a common data point. Since the Manager has requested the attribute Year as the granularity, we will use the dimension which Year is a part of: Time. The example below shows how the Time dimension for each fact is used to bridge the two subject areas:
Now that we have a way to relate the two facts, let’s create an actual report. The Report Developer is going to create a list report by dragging on three items:
Year | Actual Sales | Forecast Sales
Behind the scenes, Cognos will need to first perform a query for each fact separately to retrieve each year/measure combination:
Cognos will then stitch the two queries together on the common Year data points with a third stitch query, displaying this to the user:
From a technical standpoint Cognos is performing a full outer join between the queries. This can also be done using measures from multiple facts. While seemingly simple to the user, behind the scenes Cognos does the heavy lifting and gets surgical with stitch queries.
