Joins in Siebel are well-known.

In fact, conversations and most interviews will begin like this:

Interviewer: Could you please share examples of your Siebel work that added significant value to the customer’s business?

Interviewee: Created picklists, joins, links and a ton of applets and views.

Joins are one of the most basic configuration items that you can do in Siebel. The purpose of join is simple and relates directly to how a join is used in a relational database management system.

What is a Siebel Join? #

Data is organised into distinct business layer entities called “business components” (BCs) in Siebel. The BCs relate back to the base table(s)  in the DB. When there is a need to relate data across tables, you create a ‘join’ in the BC and get access to the column from related tables.

The UI component (“applet”) sits on top of the BC, and shows all the data that is correlated through BC. Data from the base table and related table are together shown in the context of the record.

The related data from different tables is collated and presented to the user using Joins and Multivalue Links (MVLs) at the business layer. A Siebel Join is used to create a 1:1 or M:1 relationship between two entities. In Siebel speak the involved components are a BC and a related table.

How to configure a Siebel Join? #

You create a join in two steps-

1. Define the join configuration

Create the join under the specific business component against the “Join” object in Siebel Tools.

• Specify the ‘Join Specification’, which relates the source column with the column in the destination table. There can be one or more than one specifications for a single join.
• Specify the ‘Join Constraint’: Include additional criteria to filter data in the relationship based on one or more columns in the target table. Join constraint is a simple text value in most cases. For example, join constraint can be ‘STATUS_CD’ set to ‘Active’. (where STATUS_CD is a target table column).
  However, specific calculation formulae are also allowed. For example, POSTN_ID = “PositionId()”.

Join is referred by its ‘Alias’.

2. Create a field using the join

Create a field in the business component, specify the “Alias Name” of the join, and specify the column in the joined table that maps against the BC field being created.

When the user navigates to a view or queries for a record, the Siebel object manager (or Siebel.exe in the remote client) dynamically generates the SQL & executes a query against the database. Steps (1) and (2) will introduce additional filter criteria in the ‘where condition’ of that SQL statement.

SELECT
 ...
 FROM
  SIEBEL.S_OPTY T1,
  SIEBEL.S_ORG_EXT T2
 WHERE
  T1.PR_DEPT_OU_ID = T2.PAR_ROW_ID AND
  T2.CUST_STAT_CD = "Active"

The above SQL statement gets generated when the user accesses opportunity applet -

• The applet has ‘Opportunity’ as the underlying base table. Query fetches the related ‘Account’ of the given ‘Opportunity’ (T1.PR_DEPT_OU_ID = T2.PAR_ROW_ID)
• A ‘Join Constraint’ introduces additional filter criterion to make sure the customer status is “Active”. (T2.CUST_STAT_CD = “Active”)

Types of Joins #

There are two types of joins.

1. Implicit and explicit joins

Explicit joins are available as ‘Join’ objects in Siebel Tools. You create the join, define Alias and use the join in a field. The example of opportunity/account described earlier uses an explicit join.

Implicit joins are not available as distinct join objects in Siebel Tools. You find these joins -

• in business components based on S_PARTY table. e.g. Contact BC has a join between S_PARTY base table and S_CONTACT table.
• to specify relationships between the base table and an extension table. For e.g., Opportunity BC is based on S_OPTY and uses S_OPTY_X extension table.

In implicit joins, you will find fields that use a table other than a base table in the ‘Join’ property. However, you will not find definitions of that join in the join specification.

2. Inner and Outer Joins

With creating a joint definition you can check a flag indicating whether the join is an ‘Outer Join’. The concept of outer and inner joins are similar to those you see in a database.

Use an inner join if the joined table is always expected to have a corresponding record. The parent record is closely tied to the child record. Either they show up together, or not at all. Parent table records will not be shown if there are no corresponding records in the joined table.

The example of account/opportunity above is an inner join as represented by -

WHERE
  T1.PR_DEPT_OU_ID = T2.PAR_ROW_ID AND

Opportunities MUST have an account and are not considered valid without one.

An outer join would look like this -

...
T1.PR_DEPT_OU_ID = T2.PAR_ROW_ID (+)

The results will show opportunities regardless of whether they are tagged against accounts.

Interesting Points to Note #

1. Joins may have one or more join specification and join constraint. You will rarely see more than one being put to use though 🙂
2. A constraint is typically frowned upon and assumed to cause potential performance problems. There is no scientific basis for the assumption. You should be able to use join specifications in a reasonable way, and there are situations where constraints create a positive effect on the overall execution time.
   A join constraint is used less often in typical Siebel implementations.
3. You could use a join for a ‘1: M’ relationship, though purists may argue that one has to be crazy to do that.
   If you spool the query resulting from such a configuration and run it directly on the database you will see more than one record returned. However, Siebel returns only one record regardless of the underlying database result set. Siebel intelligently applies filters at the business layer to show a single record - precious time is lost in fetching unnecessary data, filtering records at the object manager level and therefore, detrimental to performance.
4. The default recommendation is to create database indexes on the columns used as ‘source columns’ in the join. This will be super useful if you’re using a join field in search or sort specifications.
5. A custom foreign key created in the source table for creating a join should have the “Foreign Key Table” property populated at the Table > Column level. This will help you to resolve records in EIM, but is of no consequence to other aspects of configuration.
6. Although you could technically use any unused OOB column in the table as a source column for a join, this can impact the sanity of the team members at some point in time. It leads to some interesting data migration issues in the short/long term.

Know of anything else that is interesting about joins? Comment and let me know!