Sequence of table joins

You can use inner table joins and outer table joins. If you use inner table joins, the sequence of the table joins is only important in terms of performance. If you use inner table joins and outer table joins, however, the result set will be different depending on the sequence the table joins are performed.

Inner joins reduce result sets. Outer joins increase result sets. Therefore inner joins should be performed before outer joins. If you first perform an outer join and subsequently an inner join, the desired increasing of the result set during the outer join is lost.

If you are working with cube models, you typically join the tables within a dimension and within a fact. Afterwards, you create the joins between the fact tables and the dimension tables. You can view the sequence of the joins within a dimension or a fact on the Joins tab in the Properties view of the dimension or the fact. Table joins between a fact and a dimension are not ordered. You can view the list of joins between a fact and a dimension in the Joins tab of the Properties view of the input model.

Example

You might have the tables SALES, PRODUCTS, and PDGROUPS. These tables contain data about sales, products, and product groups. The tables might look like this:

Table 1. The table SALES
PD_ID	TIME_ID	SALES_AMOUNT
1	11/01/2008	10
99	12/02/2008	20

Table 2. The table PRODUCTS
PD_ID	PD_NAME	PDGROUP_ID
1	Bonita Mirror 2	1
2	Mera T-Shirt 50	2

Table 3. The table PDGROUPS
PDGROUP_ID	PDGROUP_NAME
1	Furniture
2	Sportswear

From the tables above, to retrieve the products that are sold and - if available - their product groups, the following table joins are required:

To retrieve all products that are sold that are included in the table SALES and also the products that might accidentally not be included in the table PRODUCTS, you must use a left outer join between the tables SALES and PRODUCTS.
To retrieve the product group - if available - of each product that is sold, you must use an inner join between the tables PRODUCTS and PDGROUPS.

Depending on the sequence the table joins above are performed, the result set will be different.

Starting with the left outer table join

If you first perform the left outer table join and subsequently the inner table join, the SQL query looks like this:

SELECT SALES.*, PRODUCTS.PD_NAME, PDGROUPS.*
FROM (SALES LEFT OUTER JOIN PRODUCTS ON SALES.PD_ID=PRODUCTS.PD_ID)
INNER JOIN PDGROUPS ON PRODUCTS.PDGRP_ID=PDGROUPS.PDGRP_ID;

The result set of the SQL query above looks like this:

Table 4. Result set of the sequence left outer join => inner join
PD_ID	TIME_ID	SALES_AMOUNT	PD_NAME	PDGRP_ID	PDGRP_NAME
1	Nov 1. 2008	10	Bonita Mirror 2	1	Furniture

Starting with the inner table join

If you first perform the inner table join and subsequently the left outer table join, the SQL query looks like this:

SELECT SALES.*, PRODUCTS.PD_NAME, PDGROUPS.*
FROM SALES LEFT OUTER JOIN (PRODUCTS INNER JOIN PDGROUPS ON PRODUCTS.PDGRP_ID=PDGROUPS.PDGRP_ID) ON
SALES.PD_ID=PRODUCTS.PD_ID;

The result set of the SQL query above looks like this:

Table 5. Result set of the sequence inner join => left outer join
PD_ID	TIME_ID	SALES_AMOUNT	PD_NAME	PDGRP_ID	PDGRP_NAME
1	Nov 1. 2008	10	Bonita Mirror 2	1	Furniture
99	Dec 2, 2008	20

Conclusion

Defining the wrong sequence for table joins leads to undesired results. The different result sets in the table Table 4 and Table 5 show that the inner table join must be performed before the left outer table join. If you first perform the left outer join, the desired result of including also the products that are sold but accidentally not included in the PRODUCTS table is lost.

Changing the sequence of table joins

Input model from scratch

If you create an input model from scratch, you can view the sequence of the table joins in the Joins Tab of the Properties View of the input model.

You can change the sequence of the table joins by selecting a join from the list and clicking Move the Selected Join Up or Move the Selected Join Down.

Imported Cubing Services cube model

If you import a Cubing Services cube model, you can view the sequence of the dimension-to-dimension joins within a particular dimension in the Joins Tab of the Properties View of the dimension. The sequence of dimension-to-dimension joins cannot be changed.

You can view the sequence of the fact-to-fact joins in the Joins Tab of the Properties View of the facts. The sequence of fact-to-fact joins cannot be changed.

You can view the list of fact-to-dimension joins in the Joins Tab of the Properties View of the input model. Fact-to-dimension joins are not ordered.

Feedback