[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-07-15
1
Medium Priority
?
1,574 Views
Last Modified: 2014-07-21
All,

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 10.2.1.1 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.

Ken
0
Comment
Question by:ktQueBIT
1 Comment
 
LVL 12

Accepted Solution

by:
RWrigley earned 2000 total points
ID: 40199008
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

872 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