Siebel 8.

0 Essentials

Siebel Data Model 1 of 21

Module 16: Siebel Data Model
 Module Objectives

• To identify prominent tables in the Siebel database

• Get to know the various keys and Indexes for the tables.
– Ex: Primary Key, Foreign Keys, User Key
• To understand how to configure the data layer as necessary for the
business implementation

Siebel Data Model 3 of 21

 Siebel Data Model
• Standard third-party relational database or Oracle database is
used to store the data used by the Siebel applications
• Is designed to support the data requirements across Siebel
applications

Controls/ Defines the UI

Screen View Applet interface
List Columns

Business Business Defines the

Field Business Layer
Object Component

Defines the
Table Column data storage
structure

Siebel Data Model 4 of 21

 Data in Siebel
• Is organized and stored in normalized tables in a relational
database
– Each table has multiple columns storing atomic data. Single value
cannot be logically further sub-divided
• Primary Key is a column that uniquely identifies each row in a
table
– ROW_ID serves as the primary key for Siebel tables

Table
S_ORG_EXT

Primary Key

EMAIL_ADDR
ROW_ID

BU_ID
NAME

Columns

Siebel Data Model 5 of 21

 ROW_ID
• Is a column in every table that is unique across all tables
and mobile-users
• Is a Siebel application-generated unique identifier
– Is generated even before the data is written to the database
• It must not be modified by the users. It’s managed by the
Siebel applications

ROW_ID for a
particular record

Siebel Data Model 6 of 21

 Tables
• Data,Interface and Repository are three major types of tables.
– Type of the table is specified by its TYPE property
• Nearly 3000 tables are present in the database
– Database schema is created during Siebel database server
installation

Data Table Interface Table Repository Table

S_ORG_EXT EIM_ACCOUNT S_TABLE

MAIN_PH_NUM
EMAIL_ADDR
ROW_ID

ROW_ID

ROW_ID
BU_ID
NAME

ALIAS
NAME

NAME

TYPE
LOC

Siebel Data Model 7 of 21

 Data Tables
• Have names prefixed with S_
• Store the user data
– Administrative Data
– Seed Data
– Business Data
• Are populated and updated by:
– Users through the siebel application
– Server processes such as Assignment Manager, Enterprise
Integration Manager (EIM)
• Are documented in Siebel Data Reference

Siebel Data Model 8 of 21

 Prominent Data Tables
 There are seven prominent data tables that store data for
the major business entities

Internal Product

Siebel Data Model 9 of 21

 Columns
• Columns determine the system and user data that can be stored
in the table
– Determine the size (Length) and type (Physical Type) of the data that
can be stored in column
– Limit proposed modifications to a standard application
– Existing properties should not be edited

Determines Type
of data

Determines
maximum size of
data

Siebel Data Model 10 of 21

 System Columns
• Store the System data. They exist for all tables and are
maintained by Siebel applications and tasks

System
Columns

Siebel Data Model 11 of 21

 User Keys
• Determine the uniqueness of records when importing, integrating or
entering data
– Are predefined and cannot be modified
– All columns in User key may not be Required
• Creation of New User Keys will be done by Expert Service Group

Column combination
provides a unique value

Siebel Data Model 12 of 21

 Indexes
• Used to retrieve and sort data rapidly
• Separate data structure to store data value for a column
and a pointer to a corresponding row
• Modifying existing indexes or adding new ones to be done
by experienced DBA
_P index based
on Primary Key

_U index based
on User Key

Position of the column in the index.

Sequence affects the sort order

Siebel Data Model 13 of 21

 Table Relationships
• To support the as-delivered application, Siebel tables have
many predefined relationships
– 1:M  One-to-Many
– M:M  Many-to-Many

Employees Address

S_USER S_ORG_EXT S_ADDR_ORG

PW_LAST_UPD

EMAIL_ADDR

ADDR_NAME
PASSWORD

ROW_ID

ROW_ID
ROW_ID

LOGIN

BU_ID
NAME

ADDR
CITY
M:M Relationship 1:M Relationship
S_EMP_ACCNT is
Intersection Table

Siebel Data Model 14 of 21

 1:M Relationships
• On the Many side, Foreign Key columns in the table are
used to capture the relationship
– Are columns in a table that refer to the Primary Key column of
the related or parent table.
– Many are named with suffix _ID
• FK column on Many side of relationship references the PK
column on the One side

Foreign Key
S_ORG_EXT S_ADDR_ORG
EMAIL_ADDR

ADDR_NAME
ROW_ID

ROW_ID

OU_ID
BU_ID
NAME

ADDR
CITY

Siebel Data Model 15 of 21

 Foreign Key Columns

• Are maintained by Siebel applications and tasks to ensure

referential integrity and should never be updated directly via SQL.
• Use the Foreign Key Table property in the column definition to
determine the column that serves as the foreign key

S_ORG_EXT S_ADDR_ORG
EMAIL_ADDR

ADDR_NAME
ROW_ID

ROW_ID

OU_ID
BU_ID
NAME

ADDR
CITY

Siebel Data Model 16 of 21

 M:M Relationships

• M:M relationships are captured using the foreign keys

columns in a third table called Intersection table

S_USER S_ORG_EXT
PW_LAST_UPD

EMAIL_ADDR
PASSWORD

ROW_ID
ROW_ID

LOGIN

BU_ID
NAME
S_EMP_ACCNT

Intersection Table
ROW_ID

EMP_ID

OU_ID

Siebel Data Model 17 of 21

 1:1 Extension Table
• Is a special table that has 1:1 relationship with the base table
– Foreign key for this is located in the Extension table and is known as PAR_ROW_ID
– A row in an extension table is created only if there is data to store in one of its columns.
– The ROW_ID for a row in an extension table is same as the ROW_ID of the related row
in the base table. This is an exception to the convention that ROW_IDs are unique
across all tables

• A base and an extension table can be considered as a single logical table.

– Provides additional columns for the business components referencing the base table
– Have the name of the base table with suffix _X
– Examples of Vanilla 1:1 extension tables: S_CONTACT_X for S_CONTACT,
S_ORG_EXT_X for S_ORG_EXT etc

S_SRV_REQ S_SRV_REQ_X

PAR_ROW_ID
ATTRIB_47

ATTRIB_46
ASGN_DT
OPTY_ID
ROW_ID

ROW_ID

Siebel Data Model 18 of 21

 1:M Extension Table
• Is a special table used to store child data related to an existing
parent table
• Have the name of the main table suffixed with _XM
– Examples of Vanilla 1:M extension tables: S_CONTACT_XM,
S_ORG_EXT_XM

S_SRV_REQ S_SRV_REQ_XM
PAR_ROW_ID

ATTRIB_01
ASGN_DT
OPTY_ID
ROW_ID

ROW_ID

NAME
TYPE

NAME stores the name of the

child entity

PAR_ROW_ID stores FK to
ROW_ID in the main table

TYPE defines data classification

Siebel Data Model 19 of 21

 Module Highlights
• Siebel data model defines how the data is stored in the third
party relational database
• Primary key (ROW_ID) uniquely identifies each row in a table
• User keys are used to uniquely determine the existence of a
row while entering, importing or Integrating data
• Foreign key columns are maintained by Siebel applications
and tasks to ensure the referential integrity. They are used to
capture 1:M relationships
• M:M relationships are captured using foreign key columns in
Intersection tables
• 1:1, 1:M extension tables are prebuilt for many major tables in
Siebel database

Siebel Data Model 20 of 21

 Lab
• In the lab you will:
– Examine tables, columns, indexes, and user keys that make up
the Siebel Data Model
– Determine the form of relationships between tables in the Siebel
Data Model

Siebel Data Model 21 of 21