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?
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.
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..
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.
Then why not define a reporting view against those tables and base your model on the explicitly defined SQL?
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
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.