Stitched Query

[HalfBloodPrince]

What is stitched Query  ? Can somebody give some example of it and explain ?

[MFGF]

A stitch query is generated when a report spans multiple "fact tables" in your model. Essentially two queries are generated - one to each fact and it's associated dimensions, then the results are "stitched" together via coalesce functions using full outer join behaviour. This behaviour is designed in to ReportNet, Cognos 8 and Cognos 10, and is triggered automatically simply by selecting data from multiple fact query subjects.

The benefit is that it will return correct results in a multi-fact multi-grain scenario - for example, a report that pulls in monthly sales figures from a sales fact table and quarterly sales targets from a targets fact table.

You can find a full description of stitch queries in the "Guidelines for Modeling Metadata" documentation - look at the part covering multi-fact multi-grain queries.

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.1.1/ug_best.pdf

Regards,

MF.

[the6campbells]

Fan-trap

An employee has 0:N skills and 0:N billings - you want to query data from both where neither skills and billings are directly related. The engine sees the 1-N and 1-N relationships and forms two query blocks (employees-skills) and full outer joins it with a query block (employee-billings)

Multi-fact query

Same as prior example except folks will talk about merging data from two or more fact tables in the context or one or more related dimension tables.

[MFGF]

Fan-trap

An employee has 0:N skills and 0:N billings - you want to query data from both where neither skills and billings are directly related. The engine sees the 1-N and 1-N relationships and forms two query blocks (employees-skills) and full outer joins it with a query block (employee-billings)

Multi-fact query

Same as prior example except folks will talk about merging data from two or more fact tables in the context or one or more related dimension tables.

Hmmmmm. I used to teach that exact example on PowerHouse courses (link to and to) :) I can take a very good guess who you are... Welcome to Cognoise!

[blom0344]

I got a deja-vue of old Business Objects days , Fan-trap&Contexts in universe designer   ;)

[rmdarezzo]

I hope this is the right forum for this... We use COGNOS Powerhouse on openVMS.  I have a question regarding the Powerhouse data dictionary.
   Is there a way to import just one definition?  Up to now, If we make any data dictionary changes we need to import the ENTIRE DICTIONARY to our live dictionary in order for the changes to be live in our system?
  Any help on this would be much appreciated.

  Thanks
     Bob D'Arezzo
     UNFI
      401-256-6902
      bdarezzo@unfi.com

[cogswell]

I'm sorry for Frankensteining this old topic, but: does this mean that Cognos automatically resolves fan traps by creating stitched queries, and the modeler doesn't need to worry about them?

[Rahul Ganguli]

Hi,

No it is not the case. Traps in Framework Manger are not errors but they are areas of investigation.
if you have a comformed dimension between two facts like time dimension will be connected to Sales and Sales Target you will encounter this trap but that is fine. but if you have a non-conformed dimension in between two facts that is the Trap you need to resolve.

Just keep in mind we model in Framework Monager for Report developer so they get expected outcome. Final descision lies with the modeler. We just have set of best practices which makes our jobs easy. There will be scenario which will be best suited if you don't go by best practices but that not the case most of the time.

Regards,
Rahul