COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: dssd on 09 Apr 2012 03:12:56 PM

Title: Avoiding stitched queries
Post by: dssd on 09 Apr 2012 03:12:56 PM
I guess its a common question. Can we avoid stitched queries, as in have an inner join instead of an outer join from two facts and conformed dimension. Secondly, should we be doing so?
Title: Re: Avoiding stitched queries
Post by: cognostechie on 09 Apr 2012 03:56:01 PM
According to best practices, you should not join Fact to Fact but if you need to, you can do that but make sure to set a unique determinant to the fact which has a higher granularity.
Title: Re: Avoiding stitched queries
Post by: blom0344 on 10 Apr 2012 02:50:12 AM
Stitch queries are there with a distinct purpose, they are not some sort of anomaly.  If you think this through you will realise that applying an inner join with multiple facts will almost certainly lead to loss of data.

I am most interested why you want to avoid them in the first place..
Title: Re: Avoiding stitched queries
Post by: dssd on 10 Apr 2012 05:22:37 AM
Well because in some buisness scenarios you are sure that all the data would be retrieved with an inner join becasue of the way the data is.
Title: Re: Avoiding stitched queries
Post by: blom0344 on 10 Apr 2012 05:49:25 AM
Then why not define a reporting view against those tables and base your model on the explicitly defined SQL?
Title: Re: Avoiding stitched queries
Post by: wyconian on 20 Jul 2012 04:19:53 AM
Hi

I agree with CT you shouldn't really be join fact tables together directly.  You should try to use some kind of conformed dimension or maybe a bridge.

It is possible to avoid stitch queries by changing the cardinality of the join into the facts.  If you make it 1:1 FM won't recognise the facts as facts and won't try to stitch the queries.  This can be useful if you have factless facts BUT you will loose the ability to correctly roll up values if that is something you need to do.

Blom is also right stitch queries can be very useful if you have a multi fact query but I would add some kind of dimension/bridge between the facts and make sure you add the determinants correctly.

Good luck
Title: Re: Avoiding stitched queries
Post by: navissar on 29 Jul 2012 02:53:50 AM
There are ways to avoid stitch queries, such as toying with cardinality (Making it 1..1<-->1..1).
HOWEVER, stitch queries are necessary to prevent data loss in the absolute vast majority of the cases. When two facts exist in the data source which are on the same granularity, relate to the same objects etc. they should be mushed together. If it's a data warehouse, check the ETL process and perhaps fix it so they're on fact. If it's a transactional DB, perhaps create a view for these tables.