Cognos Report studio warning: XQE-WRN-0042 Join optimization: used the alphabetically first fact table


I have a crosstab report that has 2 queries:

1.  A prompt page query for an academic term.  It only pulls data items from the query subject called Terms.
2.  A detail crosstab query that is using the same subject area as the prompt page data items  but has several other dimension data items and fact data items.  However, they are all coming from the same subject area of the FM package.

The report runs fine and returns valid data but when I validate the report I get the following warning:

Cognos Report studio warning:  

XQE-WRN-0042 Join optimization: used the alphabetically first fact table

My questions:
1. Why if I have a query that only references data items from a single query subject would I get this warning?
2  Why if  the other query has a valid combination of dimension and fact data items in the query and on the report page (all from the same subject area of the FM model as the query from question 1) would I get this warning?

I did a test where I even tried to add a reference to the same fact table from query 2 in query 1 and I still get the same warning message.  I am just trying to understand why / how this warning gets generated under the conditions listed above.  Thanks.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A typical FM model will usually consist of two or three "levels" of query subjects, of which only one layer (the "presentation" layer) is actually visible from within the Cognos studios.  This level normally consists of "shortcuts" to the query subjects in the next layer (either a business layer consisting of model query subjects, or the database/import layer consisting of datasource query subjects).  

So while it may look like you're pulling your query items all from the same query subject in Report Studio, if you right click on the individual query items in the Source Explorer window and select "Lineage", you may (if you have enough security) see that they're actually coming from different lower-level query subjects.

Ultimately, then, what this message is telling you is that there are ambiguous joins in the model.  This means that there are multiple "paths" that the system could use to connect the various database tables that are being referenced in the report.  The documentation describes several scenarios and how to resolve them:  Cognos FM Documentation

If you're unable to change the model, and you just want the warning to go away, the easiest way is to include a dataitem from one of the joining tables.  So for example, if your report pulls data from database tables A and D, and those tables can be joined either through table B or C, and you want it to pull data from table C, then include a dataitem from table C in your report (something simple like a filter with C.dataitem=C.dataitem, for example)  

However, resolving the ambiguity in the model is the best solution, as any report-hacks may impact future versions of the report or cause unexpected behavior in more complicated models.

One other note:  a "Fact" table, from Cognos's perspective, is ANY table that is at the "many" side of a one-to-many relationship, regardless of what is actually in that table.  So just because a table is labeled in your ERD as a dimension doesn't mean Cognos will treat it as a dimension when processing.  This isn't a function of data but rather a function of preserving the data based on the modeling.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.