DWD relations

You manage DWD relations using the Relations Manager which you can open from the Design menu. The Relations Manager screen presents DWD relations in text and graphics formats. Using the Relations Manager, you can create, view, and edit DWD relations.

Inner Join

An inner join combines records (rows) from two tables based on the values of data in specific columns called keys. When the key in one table has the same value as the key in the other table, that record (row) is included in the temporary inner join table. When the data values are not identical, the record is ignored by the inner join. An example of an inner join expressed in SQL is:

SELECT *
FROM   division 
       INNER JOIN company 
          ON division.companyID = company.companyID

In plain language: Create an inner join between table company and table division where company is identical in each table.

For example, in the inner join displayed here in the DWD Relations Manager, a one to many relation is displayed between company and division. The relation was defined as an inner join as shown in the name of the relation, Division [INNER] Company, in the left-side tree. This inner join includes only the records for which division.companyID = company.companyID.

Note: Inner joins are used in the catalog composers and exclusively in the data mart(s) between the fact and dimension tables

Left Outer Join

An outer join includes all the records from one table plus the inner join records from the other table.

For example, in the outer join illustrated below, a one to many relation is shown between tables SalesOrganization2 and Business Area Code. The relation was defined as a left outer join as shown in the name of the relation, SalesOrganization2 [LEFT OUTER] Business Area CodeTable in the left-side tree. This LEFT Outer join includes all the records from SalesOrganization2 and, where the key values (Company and Business Area Company) are identical, the related area code. An example of an outer join expressed in SQL is:

SELECT *
FROM   company 
       LEFT OUTER JOIN division 
          ON company.companyID = division.companyID

This query selects all company records and lists the company division for each (if it is recorded).

Note: The DWD Relations Manager always places the one side of a one-to-many outer join relation on the left in the table graphic and in the relation name (SalesOrganization2 [LEFT OUTER] Business Area CodeTable).

Right Outer Join

This join is similar to the left outer join except that all the records from the right table are listed while a particular value (typically more than once for a many table on the right). Records in the left table contribute data to the join rows when the key values are identical.

Cross Join

A cross join is the product of the related tables. If table A contains 200 rows and table B contains 400 rows, the join contains 80,000 rows. Each row in one table is joined to all rows in the other table.

Full Outer Join

This join combines the result of an inner and outer join. In effect, this join merges the tables in that all records from both tables are included. If table A contains 200 rows and table B contains 400 rows, the join contains 600 rows.