Hi All,

I am explaining to understand basics of Cartesian product.

Once we include tables in the data foundation, we need to link tables using different joins. based on join condition the data retired from the tables.

Tables are normally joined with a primary key and forging key relationship. If tables are not joined in the data foundation then a query run on this tables, this results as every row of one table to every row of another table like a cross joins.

Example:

Customer, City tables are included in the data foundation and not provided any join between these tables.

Selected the check box allows Cartesian products (Navigation: Select the Data Foundation -> Click in the SQL Options tab (SQL settings can be defined for relational universes only)).

/wp-content/uploads/2014/06/4_482551.png

Once business layer is created to generate the query to display customer and related cities, both the customer and cities have six records in the database.

Query using the Cartesian join between these two tables will give total as 36 records in the query results.
/wp-content/uploads/2014/06/3_481736.png


How SQL statement generated

/wp-content/uploads/2014/06/5_481739.png

If you have not provided a join between the tables and not selected the check box allows Cartesian products./wp-content/uploads/2014/06/1_481716.png


Generate the query to display customer and related city’s getting the error message:  “You cannot run this query because it will produce a Cartesian product.  (IES 00012)” and no SQl code will generate.

/wp-content/uploads/2014/06/2_481717.png


Cartesian product occurs when you select object from different tables and there is no link defined between the tables, always give incorrect results. Best practices should not be any free standing tables in the data foundation.

Hope this helpful.

Thanks

Sreeni

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply