Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  stitch query concept

    Posted Wed April 06, 2022 09:36 AM
    Hello all,
    can anyone please explain what is stich query.



    Thanks and Regards,
    Priyanka Joshi

    ------------------------------
    Priyanka Joshi
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: stitch query concept

    Posted Wed April 06, 2022 09:51 AM
    Hi Priyanka,

    When queries that request facts from multiple tables are performed in IBM Cognos Analytics with Watson, 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.

    You will find the explanations in the 
    Metadata Modeling Guidelines
    in the pages 9 to 10.

    Best regards,


    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------

    Original Message


  • 3.  RE: stitch query concept

    Posted Wed April 06, 2022 09:56 AM

    Hi,

    A stitch query is a query designed to retrieve accurate, consistent results in a report that spans more than one fact table in a data warehouse. Imagine a situation where you have sales figures held at the date level in the Sales Fact table, and sales target figures held at the quarter level in the Sales Target Fact table. If you were just to join these normally (via conformed dimensions, of course), you'd end up with the Sales Target numbers being repeated multiple times - corresponding to each date in the quarter. If there were 91 days in the quarter that had sales, and you summed the Sales Target figures in your report, you'd end up with Sales Target totals which were 91 times the real value. This is often called "double counting", and is obviously not the right thing for your report to do.
    Stitch queries address this issue by (effectively) issuing separate queries to each fact table independently, then taking the results of these and full outer joining them together, based on the non-null (coalesced) values of the common dimensional items used. Rules in the model (often an FM package, but also a Data Module these days) allow the query engine to know how to correctly aggregate each of the queries either side of the full outer join.
    There is intelligence in the query engine that identifies when a query is spanning multiple fact tables vs a single fact table. It uses the cardinalities of the relationships in the model to make this determination. If an object is at the "many" end of *all* relationships to it used in the query, it is identified as a fact table. If an object is at the "one" end of *any* of the relationships to it used in the query, it is identified as a dimension table.
    You can look at the SQL generated in a report and easily identify when it is a stitch query. Look for "full outer join" and also the coalesce functions existing.

    Hope that helps!

    MF.



    ------------------------------
    Mark Fry
    Technical Consultant
    ------------------------------

    Original Message


Related Content