| Bookshelf Home | Contents | Index | Search | PDF | |
Siebel Analytics Server Administration Guide > Working in a Repository's Physical Layer >
About Physical Joins
All valid physical joins need to be configured in the Physical layer of the Administration Tool. When you import a physical schema and import keys, the primary key-foreign key joins are automatically defined. Any other joins within each database or between databases have to be explicitly defined.
NOTE: Imported key and foreign key joins do not have to be used in metadata. Joins that are defined to enforce referential integrity constraints can result in incorrect joins being specified 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 issued by the Siebel Analytics Server.
Multi-Database Joins
A multi-database join is defined by a table in one database that logically joins to a table in another database. You need to specify multi-database joins in order to combine the data from different databases. Use the Physical Diagram editor to specify multi-database joins. The joins can be between tables in any databases, regardless of the database type, and are performed within the Siebel Analytics Server. For information about using the Physical Diagram editor, see Defining Physical Joins in the Physical Table Diagram.
Fragmented Data
Fragmented data is data from a single domain that is split between multiple tables. For example, a database 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 tables it relates to. Figure 11 shows the physical joins with a fragmented sales table and a fragmented customer table where they are fragmented the same way (A through M and N through Z).
In some cases, you might have a fragmented fact table and a fragmented dimension table, but the fragments might be across different values. In this case, you need to define all of the valid joins, as shown in Figure 12.
TIP: Avoid adding join conditions where they are not necessary (for example, between Sales A to M and Customer N to Z in Figure 11). Extra join conditions can cause performance degradations.
Primary Key and Foreign Key Relationships
A primary key and foreign key relationship defines a one-to-many relationship between two tables. A foreign key is a column or a set of columns in one table that references the primary key columns in another table. The primary key is defined as a column or set of columns where each value is unique and identifies a single row of the table. You can specify primary key and foreign keys in the Physical Table Diagram or by using the Keys tab and Foreign Keys tab of the Physical Table dialog box. See also Defining Physical Joins in the Physical Table Diagram and Using the Columns, Keys, and Foreign Keys Tabs.
Complex Joins
Complex joins are joins over nonforeign key and primary key columns; that is, the relationship between the tables is arbitrary. When you create a complex join in the Physical layer, you can specify expressions and the specific columns on which to create the join. When you create a complex join in the Business Model and Mapping layer, you cannot specify expressions or columns on which to create the join. A complex join in the Business Model and Mapping layer acts as a placeholder when the business model has no foreign keys but requires a physical join to be created with expression and column information. A complex join in the Physical layer does not require a matching join in the Business Model and Mapping layer.
You can create physical and logical complex joins using the Joins Manager or using the Physical or Logical Table Diagram. For more information, see Defining Physical Joins with the Joins Manager and Defining Physical Joins in the Physical Table Diagram.
| Bookshelf Home | Contents | Index | Search | PDF | |
Siebel Analytics Server Administration Guide Published: 23 June 2003 |
