Exago BI uses special Cartesian processing to avoid Cartesian Products when data models contain multiple one-to-many relationships.
This article demonstrates how this processing works. Take the following data as an example:
The relationship between Person and Order, and the relationship between Person and Store are both one-to-many.
Output without Cartesian processing
If the Cartesian processing feature is disabled, a report containing fields from all three Categories would form a Cartesian Product and display repeated values:
Output with Cartesian processing
If the Cartesian processing feature is enabled, the repeated values are removed from the report:
Enabling Cartesian processing
Cartesian processing will only apply to reports if one-to-many joins are defined as One-To-Many in the configuration. If a join is one-to-many but is defined as One-To-One in the configuration, then Exago will not know to use Cartesian Processing to avoid Cartesian Products.
By default, Cartesian processing is on for all reports. To turn it off by default, set the configuration setting to False:
( Database Settings ) Enable Special Cartesian Processing
To enable or disable Cartesian processing for a specific report, click Report Options > Advanced > Joins to open the Joins window, then select True or False from the Enable Special Cartesian Processing list. (If the Joins window is not available, it may be inaccessible to the current Role, or turned off globally).
When Cartesian Processing is enabled and the report contains data with multiple one-to-many relationships, the Joins window has an option to apply a "MUST" clause. This option allows you to specify Inner/Outer relationships between two objects that are not related.
For example, the "Order" and "Store" categories are not directly related to each other, and must be joined through the "Person" category. Selecting Order causes any Store that does not have an associated Order to be removed from the report (in this example, Bob’s order of "30"):
Selecting Store causes any Order that does not have an associated Store to be removed from the report (in this example, the rows with stores "Starbucks" and "Panera"):
Selecting both options removes all rows that do not have both an Order and a Store: