Nobody has asked if you actually want to create a cross join. A cross join creates a row in the resulting projection, for each row in one table, matched against every row in the other table. While they are rare, there are certainly valid use cases where a cross join is the right solution. See, e.g.,
Learn How to Combine Data with a CROSS JOIN - Essential SQL, for some examples. Cross joins can be used where both tables are very small (or at least one of them is), but bear in mind that even two relatively small tables of 1,000 rows each would yield a cross join result of 1,000,000 rows (called a Cartesian product). In 20+ years of working with SQL, I can count on one hand the number of times I've intentionally created a cross join.
By default, cross joins are generally prohibited by Cognos (and by data modelers using Framework Manager) because they're most often created by mistake, and might result in massive queries which can slow a system, or in rarer cases, take down a server.
The mistake that leads to unintended cross joins is identifying two tables to join, without defining the relationship between them (i.e., type of join, and columns to use for matching). Avoiding this kind of oversight (easier than you'd think to actually make) is one reason I always prefer to define my joins in the FROM clause (specifying [INNER, OUTER, CROSS] JOIN myTable ON [join condition]) rather than defining the table relationships in the WHERE clause, where they're harder to match to the table list in the FROM clause.
Enough SQL theory. You seem to want to just make the error message go away; fair enough. But if a cross product is
not what you want, just turning off the limiter will let the report run, but the result will be a mess, and not what you're after. So Cognos is helping you by alerting you to a problem which most likely should be resolved by fixing it, not ignoring and going around it.
You say you're joining two queries. Have you defined any relationship between them? Cognos allows you to see the SQL version of the join you create between queries in Report Studio; does yours reflect a proper join condition? There is a possibility that the cross join is not between the two queries, but elsewhere in your data module. You may need to inspect it to see if it reflects any table joins without relationships.
There's one last possibility, and that's that the error message is itself mistaken. Apparently, there was a coding error (bug) in Cognos Analytics 11.1.2 which would throw this particular error when it shouldn't, which bug was fixed in v. 11.1.3. See,
PH12175: XQE-PLN-0091 CROSS JOIN ERROR ON REPORTS THAT USE A DATA MODULE AFTER UPGRADE TO 11.1.2 (ibm.com); IBM Cognos Analytics 11.1.3 Fix List - CognosPaul. Note that the bug affected data module based reports, as you've said yours is.
The issue has also cropped up in v. 11.1.3, but probably not due to the same bug. See,
PH17891: CA 11.1.3 REPORT GETS ERROR XQE-PLN-0091 CROSS JOIN USING A DATAMODULE BASED ON UPLOADED EXCEL FILE AND DQM PACKAGE (ibm.com).That last tech note suggests the user should set "the Cross Product Join governor in the Framework Manager model for the data module's package to "Allow" and republish the package." And if you're intending to perform a cross join for a valid reason, you'll need to override the governor either at the report level, or at the data module level.
Good luck.
------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------
Original Message:
Sent: Thu February 25, 2021 09:58 AM
From: Francesco Lastrino
Subject: Cross Join Error in Data Module
Hi all,
I'm facing this error code when I try to do a Join between 2 queries (XQE-PLN-0091 Cross Join Error)
I am using a data module and i haven't found any "Cross Product Allowed" inside my queries options. How can I perform this operation? Thanks
------------------------------
Francesco Lastrino
------------------------------
#CognosAnalyticswithWatson