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

Posted on 2014-07-15
Last Modified: 2014-07-21

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.

Question by:ktQueBIT
    1 Comment
    LVL 12

    Accepted Solution

    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.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Hello All, In previous article we used Hortonworks sandbox to work with Hadoop. Now, lets think to create own single node Hadoop on Linux. Here we Install and Configure Apache Hadoop on UI based Oracle Linux. I assume, you have VMware installe…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now