Scroll

When to Use Cartesian Processing

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:

Person

Person.ID Person
1 Bob
2 Linda

Order

Person.ID Order Order.ID
1 $26 1
1 $30 2
2 $40 3

Store

Store.ID Store Person.ID
1 Kmart 1
2 Home Depot 2
3 Starbucks 2
4 Panera 2

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:

Person Order Store
Bob 26 Kmart
Bob 30 Kmart
Linda 40 Home Depot
Linda 40 Starbucks
Linda 40 Panera

Output with Cartesian processing

If the Cartesian processing feature is enabled, the repeated values are removed from the report:

Person Order Store
Bob 26 Kmart
Bob 30  
Linda 40 Home Depot
Linda   Starbucks
Linda   Panera

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

"MUST" clause

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.

Capture_7.png

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"):

Person Order Store
Bob 26 Kmart
Linda 40 Home Depot
Linda   Starbucks
Linda   Panera

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"):

Person Order Store
Bob 26 Kmart
Bob 30  
Linda 40 Home Depot

Selecting both options removes all rows that do not have both an Order and a Store:

Person Order Store
Bob 26 Kmart
Linda 40 Home Depot

Hidden Article Information

Article Author
Natalie
created 2015-11-10 19:02:53 UTC
updated 2017-07-12 20:41:55 UTC

Labels
one-to-many, cartesian, advanced joins, advanced,
Have more questions? Submit a request