Skip to main content

Data & Intelligence

Cognos Concepts: Stitch Query in Cognos Reporting Explained

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:

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Ryan Johnson

Ryan Johnson is a lead consultant specializing in Business Intelligence and Data Warehousing as part of the IBM Business Analytics group. He has extensive education in the area with a master of science in business intelligence and specialized training to accompany his many successful client engagements.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram