Scroll

Special Cartesian Processing

Part of the Performance and Scaling series.

Exago BI can detect when a report query will return a Cartesian product (aka cross join) and process the query in such a way to avoid returning a nonsensical table. This can considerably improve the end-user experience, but it can negatively impact execution performance.

Special Cartesian Processing, enabled by default for all reports, looks at the join configuration to see whether a report contains multiple one-to-many join relationships.

Assuming that the "many" side of these relationships have no direct join to each other, a Cartesian product will be formed when executing this query. This results in a table where every field in the first object is joined to every field in the second object, causing many duplicate entries.

With Cartesian processing turned on, each joined pair is executed as a separate query, then the resulting tables are joined in the application according to the key fields for the "one" side of the joins.

Example

Take the following object relationships as an example. Let's say we have a report with the following three data objects:

  • Cities
  • Avenues (by city)
  • Boulevards (by city)

We potentially have the following joins:

  1. (one) Cities >> Avenues (many)
  2. (one) Cities >> Boulevards (many)

There is no given relationship between Avenues and Boulevards. When we execute this query normally, we expect a table like the following:

City Avenue Boulevard
Los Angeles Melrose Ave Hollywood Blvd
Los Angeles Melrose Ave Sunset Blvd
Los Angeles Melrose Ave Colorado Blvd
Los Angeles La Brea Ave Hollywood Blvd
Los Angeles La Brea Ave Sunset Blvd
Los Angeles La Brea Ave Colorado Blvd

With Cartesian processing, we will get the following table instead:

City Avenue Boulevard
Los Angeles Melrose Ave Hollywood Blvd
Los Angeles La Brea Ave Sunset Blvd
Los Angeles   Colorado Blvd

As long as we remember that there is no implied relationship between Avenue and Boulevard, we will get a much more readable table. The downside is that this will take longer to execute than the Cartesian product, especially for large data sets. 

Enabling Special Cartesian Processing

By default, Cartesian processing is on for all reports that have multiple one-to-many joins. 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.

Note: If the Joins window is not available, it may be disabled for the current user.


Hidden Article Information

Article Author
Exago Development
created 2015-11-10 19:02:53 UTC
updated 2018-01-27 23:18:32 UTC

Labels
advanced, scaling, performance, join, cross,
Have more questions? Submit a request