Unwanted Stitch query

[MichaelB]

I've encountered a situation where I am seeing a stitch query being performed when a certain data item is pulled into my analysts report. I've read up as much as I can on stitch queries and how they occur, but most of that information seems geared toward wanting and being ok with them. In this case, it is incorrect.

To start, I am fairly new in this role so I am still on a big learning curve with few places to turn for help. Cue Cognoise! So my namespace has two query subjects in there - BioInfo, and Addresses. But the underlying foundation of these queries is straight SQL, with no data items set to Fact.

Question #1 - Does Cognos consider this underlying SQL to be a Fact table even if there are no "facts" in it (nothing to be aggregated in either table)?

I did notice that the data item that causes the stitch query to happen exists in one of the tables but not the other. The 9 digit zip shows in the BioInfo but not Addresses. For more background, BioInfo contains a Primary Address where Addresses contain all (don't tell me this is bad - I am in the midst of a rework of the entire thing anyways, but need to patch what I have now). That 9 digit zip is pulled from somewhere else, but again the underlying SQL of that query is all that is feeding BioInfo.

Question #2 - Is the fact that the 9 digit zip not existing in Addresses but existing in BioInfo what is causing the stitch query to be performed when the data item is pulled into my analysts query? When it is not pulled in, it is east to see in the generated SQL that the stitch is not being performed.

Question #3 - What else could cause a stitch query to happen like this, that is unintended?

Thank you all so much in advance for any insight you can provide!

[MFGF]

I've encountered a situation where I am seeing a stitch query being performed when a certain data item is pulled into my analysts report. I've read up as much as I can on stitch queries and how they occur, but most of that information seems geared toward wanting and being ok with them. In this case, it is incorrect.

To start, I am fairly new in this role so I am still on a big learning curve with few places to turn for help. Cue Cognoise! So my namespace has two query subjects in there - BioInfo, and Addresses. But the underlying foundation of these queries is straight SQL, with no data items set to Fact.

Question #1 - Does Cognos consider this underlying SQL to be a Fact table even if there are no "facts" in it (nothing to be aggregated in either table)?

I did notice that the data item that causes the stitch query to happen exists in one of the tables but not the other. The 9 digit zip shows in the BioInfo but not Addresses. For more background, BioInfo contains a Primary Address where Addresses contain all (don't tell me this is bad - I am in the midst of a rework of the entire thing anyways, but need to patch what I have now). That 9 digit zip is pulled from somewhere else, but again the underlying SQL of that query is all that is feeding BioInfo.

Question #2 - Is the fact that the 9 digit zip not existing in Addresses but existing in BioInfo what is causing the stitch query to be performed when the data item is pulled into my analysts query? When it is not pulled in, it is east to see in the generated SQL that the stitch is not being performed.

Question #3 - What else could cause a stitch query to happen like this, that is unintended?

Thank you all so much in advance for any insight you can provide!

Hi,

I've posted on this a number of times over the years, and the crux of the issue is found in the assumptions the query engine makes when generating queries. There are some very important things you need to take into consideration when creating your model:

1. Cognos is designed to generate queries over tables in a star-schema or snowflake-schema modelled data warehouse presentation layer. These tables will either be Fact tables or Dimension tables. Fact tables normally contain just keys and measures, and Dimension tables normally contain just keys and descriptive attributes.
2. The query engine will make assumptions about the query subjects used in your report - each query subject will either be assumed to be a Fact table or a Dimension table.
3. If your report spans multiple assumed Fact table query subjects, the query engine will behave as though it is reporting over different star schemas in a data warehouse, and will generate a stitch query (or query split - whatever you prefer to call it).
4. The rules the query engine uses to make the Fact/Dimension determination are based on the cardinalities of the relationships connecting those query subjects in your model.
4a: If a query subject is at the 'n' end of ALL relationships linking to it in the report, it is assumed to be a Fact table.
4b: If a query subject is at the '1' end of ANY relationship linking to it in the report, it is assumed to be a Dimension table.
4c. A common mistake is to assume the Fact/Dimension determination is based on whether or not there are query items in a query subject with a usage of "Fact". The usage of query items has no influence on the assumptions the query engine makes about whether a query subject is a Fact table or a Dimension table, though. The determination is made based entirely on relationship cardinalities.

If your data is, in fact, stored in star schema or snowflake schema structures, all is well, and things work beautifully. If not, you have a much bigger modelling task to create a model that delivers accurate, predictable results. The approach to take is to define query subjects and relationships in your model that look like star / snowflake schemas - ie you add new model query subjects that take the items from your underlying data source query subjects and arrange them into "Fact tables" and "Dimension tables" in your model. Although easy to advise, the task can be quite challenging at times.

I always recommend new FM Modellers to attend the official training course. It's quite a big commitment in time and money (a full week of training) but it teaches all the critical need-to-know modelling practices to allow you to create models that are robust and deliver accurate, consistent results.

Good luck!!

MF.

[MichaelB]

They say that an expert is someone who can take what they know and in turn, teach it to someone else. You sir (or ma'am) are an expert!

Thanks for the info! I believe you answered every one of my questions and even more. I've already gone through (mostly) the online FM course from IBM, via Udemy. That is how I even knew to dig into this issue in the first place.

I can easily see now that both of these queries are on the 'n' end but are treated as a dimension in the way they are presented in the Presentation View.

Thanks again for your time!

[MFGF]

They say that an expert is someone who can take what they know and in turn, teach it to someone else. You sir (or ma'am) are an expert!

Thanks for the info! I believe you answered every one of my questions and even more. I've already gone through (mostly) the online FM course from IBM, via Udemy. That is how I even knew to dig into this issue in the first place.

I can easily see now that both of these queries are on the 'n' end but are treated as a dimension in the way they are presented in the Presentation View.

Thanks again for your time!

Very kind words - thank you Michael! I spent ten years of my career as an instructor working for Cognos, and I tried to find the best way to explain this and honed it over many (many) FM courses. That was a good while ago now, though, but I'm glad I can still help :)

I'm definitely not a ma'am. Except at weekends, but we don't talk about that :)

Cheers!

MF.

[the6campbells]

A query subject which sits at the many end of 1 - many relationship is considered to be a 'fact'. Irrespective of if  query items in the query subject are marked as fact or not.

When the query engine plans a query, it will build a graph of the query subjects it needs to navigate to resolve the query items your BI-query/Query-subjects depend on.

The query engine may determine that a query subject requires data from two or more 'facts' which triggers the topic of 'stitch' queries.

This pattern can occur in a normalized database design (some may call it a fan or chasm trap) as well as in a star-schema design.

The term 'stitch' was one dubbed by Ralf Kimball in his early teachings on performing queries using one or more dimensions that joined to two or more fact tables.

Cardinality is also used by the query engine to help control how expressions are created to avoid 'double counting'. 

By joining a single to row to several rows, it creates what you can think of as a denormalized virtual table. Hence, column values in that virtual table are duplicated as a side effect of what a relational join does.  Hence, if you were to perform aggregations over those columns you do not want to compute inflated ('double counted') data.

Avoid the temptation to change cardinality from 1-N to 1-1 to make 'stitch' queries etc 'go away'. If your data really reflects a 1-N relationship express it as such.

You should also look at your physical table design that you are mapping from within FM. Potentially, you are defining join paths between several query subjects which in effect to the query engine looks like you are trying to join areas of the data which to it implies 'stitching' of intermediary virtual tables in the query is required.

In some cases, you may find that you have relationships you can remove so they do not appear in the graph during planning and so on.