Troubleshooting
Problem
For Cognos Business Intelligence please use the following technote: http://www-01.ibm.com/support/docview.wss?uid=swg21341798 In certain reporting situations, a user will inadvertently create a cross join. A cross join is a join between two query subjects that do not have any links at the model level (or in some cases at the database level). This can happen under a wide variety of conditions. For example, in a model, there may be a table which is standalone because it is a lookup table that is used to create filters when filtering other query subjects. In this case, when a user attempts to include query items from the lookup table in a filter on a query subject in the model, a cross join would be created. The reason Cross Joins are not desirable is because they cause the Cognos server to do query processing rather than having the database do the query processing (so basically, "Local Processing" is occurring rather than "Database Processing."
Symptom
RQP-DEF-0103 Cross joins (between query subjects X and Y) are not permitted for the user who has the identity (XXXXYYYYZZZZ)
Cause
No Relationship between tables at the model level
Environment
Cognos Analytics
Resolving The Problem
There are a few different ways to circumvent this error:
A. Find a different way to achieve the desired results that does not end up in a cross join.
B. Enable cross joins for the query causing the error.
C. Enable cross joins in the model.
Note that enabling cross joins is not always the best solution - especially when it would involve doing a cross join between very large tables. Cross joins between large tables can take a long time to complete. In this case, it might be best to find an alternative way to achieve the end reporting goal without using cross joins.
Steps:
B. Enable cross joins for the query causing the error
1. Edit the report in Cognos Analytics Reporting.
2. Navigate to the Queries, and select the query that is experiencing the cross join error.
3. Click on "show properties" and set "Cross Product Allowed" to "Allow".
5. If the report is run now, the cross join error should disappear.
C. Enable cross joins in the model
1. Open the model in Framework Manager.
2. Click the "Project" menu, and then "Edit Governors..."
3. Set "Cross-Product joins" to "Allow."
4. Publish the package.
5. The report should be able to be run without the cross join error now.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21998214