Work with Physical Joins

This topic provides information about how to create and modify physical joins.

Topics:

• About Physical Joins

• About Joining Fragmented Data

• Add and Define Physical Joins

About Physical Joins

Physical joins indicate relationships between physical tables and tell the Oracle Analytics query engine how to retrieve data from the tables.

Creating a physical join automatically creates a join key for the identifier column and adds it to the primary table's properties. To view a table's join keys, open the physical table and then go to the General tab. You can view join keys, but not edit them. To find more information about a join, open the Physical Diagram and click a join to find out the tables it's joined to, join cardinality, join type, join condition, and so on.

You must explicitly define joins in each data source or between data sources to express relationships between tables in the physical layer. You can create a join can based on a join condition, or on an expression that you provide. For most data sources, join conditions are preferred for performance reasons. Joins based on expressions usually don't perform as well because they don't use key column relationships to form the join.

Joins that are defined to enforce referential integrity constraints can result in specifying incorrect joins in queries. For example, joins between a multipurpose lookup table and several other tables can result in unnecessary or invalid circular joins in the SQL queries issued by the Oracle Analytics query engine.

You can define a join from one metadata database object to another metadata database object. This is called a multi-database join.

While the Oracle Analytics query engine has several strategies for optimizing the performance of multi-database joins, these joins are significantly slower than joins between tables within the same database. As a result of the negative performance impact, you should avoid using multi-database joins whenever possible.

About Joining Fragmented Data

Fragmented data is data for a single entity that is split between multiple tables.

For example, a data source might store sales data for customers with last names beginning with the letter A through M in one table and last names from N through Z in another table. With fragmented tables, you need to define all of the join conditions between each fragment and all the related tables. The figure shows the physical joins with a fragmented sales table and a fragmented customer table where the data are fragmented the same way (A through M and N through Z).

Description of the illustration frag_table_ex_v.png

You could have a fragmented fact table and a fragmented dimension table with fragments across different values. You create the joins to the fragmented table and define a one-to-many join, as shown in the Customer A to F and from Customer G to Z to Sales A to M example.

Description of the illustration joins_frag_table.png

Note:

Avoid adding join conditions where they aren't necessary, for example, between Sales A to M and Customer N to Z. Extra join conditions can cause performance degradations.

Add and Define Physical Joins

You can define joins between physical tables in the same data source, or you can define joins between physical tables in different data sources. You use the Physical Diagram to add and define joins.

Semantic Modeler determines what type of join to create based on the selected object types and the join expression.

See About Physical Joins and Use Hints in SQL Statements.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. Click a table, right click, and select Show Physical Diagram and select Selected Tables Only.
5. Drag and drop additional tables to the Physical Diagram.
6. Working in the Physical Diagram, hover over the first table in the join (the table representing many in the one-to-many join.), grab its handle, and drag to the table that you want to join to (the table representing one in the one-to-many join).
   A box is displayed around the table that you are joining to.
7. In the Add Physical Join dialog box, specify one or more join conditions, or click Use Join Expression to specify the join properties and enter the join expression.
   If using Unknown cardinality, then you only need to select Unknown for one side of the join. For example, choosing unknown-to-1 is equivalent to unknown-to-unknown and appears as such the next time you open the dialog box for this join.
8. Click Add.