Siebel MVL vs. Link
Multivalue links and links are one of the basic configuration items in Siebel alongside joins . Although they form one of the foundational blocks for Siebel data model, it surprises me how misunderstood they are.
What is a Siebel link?
A link establishes a 1:M or M:M relationship between two Siebel business components.
Siebel, as most CRM systems, enables storage of master and transactional data. Business components in Siebel represent the entities stored, and can retrieve data from one or more database tables.
In real world, situations demand data to be viewed in the context of other data. This may be -
- accounts and opportunities tagged against those accounts
- accounts and business contacts within the customer organisation
- contacts and proposals submitted to those contacts
Siebel sits on top of a relational database (a RDBMS), and tends to represent the underlying entities as business objects. Data is only denormalised to extend, and as dictated by relational database makes sense to store specific data in defined tables.
For example: account data is stored in S_ORG_EXT, Contact data is stored in S_CONTACT and so on.
To represent the relationships and to show data in context of some other entity, Siebel uses links.
While the top applet in a view shows account data (which is regarded as a parent in this view), the bottom applet displays opportunities for that account.
To render this view, Siebel throws two distinct queries at the database. One is for fetching the account data, followed by another query to retrieve opportunity data. Siebel users the relationship parameters defined in the link as filter criteria in the opportunity query.
How to configure a Siebel link?
You can create a link between any two business components in the Link object in Siebel tools.
When the name of the link is defaulted to < parent BC>/
You create a M:M relationship by specifying source, destination, and inter-table fields. The inter-table acts as a “junction” table to relate one or more records in the parent table to one or more records in the child table.
At the database query level all this is interpreted in form of joins and nothing more. For example the account/opportunity link will result in the following query:
.. S_ORG_EXT.ROW_ID = S_OPTY.PR_DEPT_OU_ID ..
Inner joins are used here since Siebel is trying to retrieve the opportunity records for the given account, and nothing more.
If I can represent the same for the Opportunity/Contact M:M link -
.. S_OPTY.ROW_ID = S_OPTY_CON.OPTY_ID AND S_OPTY_CON.PER_ID = S_CONTACT.ROW_ID ..
Following attributes play key role in the configuration of link:
- Cascade delete: specify as Delete, clear, or none to instruct Siebel to either delete child records, dissociate child from the parent, or just do nothing when the parent record is deleted
- No Associate, No Delete, No Insert, No Inter Delete, No Update: Pretty self-explanatory. These flags allow/disallow specific operations on the child records when being viewed in the context of the parent record.
- Inter Child Delete: This is confusing after we have dealt with the “no” properties earlier. When set as “Y” for M:M links, this property enables the child records and the association to be deleted when parent records are deleted. A value of “N” specifies that only the Association records have to be deleted.
- Primary ID Field: This is based on a column in the parent table which will store the row ID of a child record that is denoted as “primary”. Primary IDs are not mandatory for links.
- Search specification: Specify additional filter criteria for child records. For example you may choose to show only active opportunities for the account.
- Association list sort specification: In a M:M link this sort spec is applied on the associate applet.
- Visibility rule applied: You can either specify “Never” to not apply visibility rules while displaying the child records. Or, specify “Always” to include the visibility filters for the child records.
For example: I may want my users to view all the opportunities regardless of whether they are part of the team when viewing account - opportunity details. The users will not be able to drill down an opportunity that does not belong to them.
- Visibility type, Visibility auto all: These function very similar to the same properties that can be specified at the business component level. You can specify distinct visibility rules for the parent-child relationships on the link.
A few interesting points about links
- Though the Primary ID Field of the link does not contribute towards increasing performance of queries against child records, a lot of designers just follow it as a best practice. Primary ID field can denote information that can be potentially useful for the user. For example who is my primary contact against an account?
- Link search specification ( if specified) is AND’d with business component search specification. Both filter criteria are considered when Siebel generates a query for a child business component.
- Visibility Type Applied is a nifty property that can avoid a lot of additional configuration that would have been required at the view web template item level. Else you would have two specify the visibility against each view and each MVL that you configure.
- Cascade Delete is set to “None” for many OOB components. This leads to orphaned child records when parent records are deleted. Designers would’ve to account for changing the cascade delete property as applicable to their implementations.
- You can have more than one link defined between two business components. The link that is considered for a particular view will be specified against the business object component in the parent business object.
- You can create a link for 1:1 and M:1 Relationships as well, but typically does not have any practical use.
The “Multivalue link”
What is a MVL?
MVL also enables read and other operations of child data in the context of parent.
It is specifically used in the UI to display the child data on the parent applet itself. This is done through a “multivalue field”, which shows a MVG icon. You can view more details of the child record by configuring a multivalue group (MVG) applet.
How to configure a MVL?
You create a multivalue link under the business component in question (the “parent” business component).
Specify a name, destination link and a few more required properties, and you are set.
The MVL depends on the underlying link to establish relationship between the parent and the child. It does not care whether the underlying link is 1:M or M:M. But, it does have a “source field” property that supplies the value for the source field in the link. This means that you can provide any field in the source BC to act as the source field in the link.
Following attributes play key role in the configuration of multivalue link:
- Auto primary: Can have the values default, selected, or none. When a new record is created/associated against the parent through the MVL, the primary flag is either automatically set for that child record, set the primary to the same value as in other multivalue fields based on the same child BC (if it is not already set), or Siebel will simply wait for the user to set it,
- No Associate, No Delete, No Insert, No Update: Pretty self-explanatory again. These flags allow/disallow specific operations on the child records when being viewed in the context of the parent record.
- Popup Update Only: Allows the child record to be updated only when the MVG applet is popped up.
- Primary ID field: Topic of many a performance lesson, Primary ID Field denotes the primary child record for the parent record - for this particular MVL only.
- Use primary join: When this flag is set to true, “Primary ID Field” is used to join the child table records In the parent record query and show the information, without the need of additional queries on child table. When a user queries on a multivalue field of a MVL using primary join, the criteria is used in a single query to fetch parent records with the specified value in the primary child record.
For example: if user queries for “KA” in the “state” field of account, Siebel will fetch all accounts were the primary address has the state as “KA”.
If this flag is not set, additional query is used by Siebel to fetch child record information. User queries in a multivalue field that does not use primary join, Siebel uses an “exists” clause to fetch parent records with at least one Child record having the specified value.
- Type field, Type value: You can specify a field on the child BC that will be pre-defaulted to the specified value When records are being created through this MVL. Type field and time value is used as additional filter criteria when records are being queried to the MVL.
This is helpful in the same child BC/table is being used to store distinct child entity data. For example there may be three types of Account Notes exposed as three distinct fields in the account applet. These are stored in the same underlying table, but segregated when displayed to the user.
- Check no match: Forces automatic update of the primary ID field if it finds the value of “no match"in that field. It uses the value of “auto primary” to determine the “valid” value.
Interesting points about MVL
- You can have more than one MVL referring the same underlying link.
This is helpful when more than one relationship exists between the same parent and child combination. For example account and address components are related in multiple ways - there may be distinct billing and shipping addresses.
- Check no match property is useful if there are quite a bit of dependencies that can mess up the relationships, or there is a constant data load in the system that will not set primary. But the use of check no match is discouraged unless the relationship is absolutely critical for the implementation. Check no match generates additional queries to determine the value that can be updated to the primary ID field.
- You can set “use primary join” to true but not have a “primary ID field”. This makes no sense but it still works. It is not recommended as part of Siebel configurations best practices.
- If you don’t set “use primary join” but use this primary field on the list applet that shows 10 records, Siebel will generate 10 additional queries on the child table.
- I have seen multiple implementations which went ahead with “use primary join” set to “false”. Though this has a performance implication, users found that it was really easy to work with the multivalue fields without using “exists” query.
Have anything else that you find interesting about Links or MVLs? Comment and share your knowledge 🙂