SIEBEL CLINICAL TRIAL MANAGEMENT SYSTEM and associated Glossary

Oracle's SIEBEL CLINICAL TRIAL MANAGEMENT SYSTEM is a CRM software solution for managing Clinical trials. Siebel Clinical allows pharmaceutical companies, biotechnology companies, and contract research organizations (CROs) to better manage the clinical trial process, maintain quality of clinical trials,and manage investigator relationships. It provides a comprehensive set of tools for clinical research associates (CRAs), clinical investigators, and site coordinators, including a personalized Internet portal to conduct study activities more efficiently.

Generic terms related to Clinical Trials:

Clinical Trial: A systematic study of a test article (treatment, drug or device) in one or more human subjects. An investigation in human subjects intended to discover or verify the clinical, pharmacological and/or other pharmacodynamic effects of an investigational product(s), and/or to identify any adverse reactions to an investigational product(s), and/or to study absorption, distribution, metabolism, and excretion of an investigational product(s) with the object of ascertaining its safety and/or efficacy.

Subject/Trial subject: An individual who participates in a clinical trial, either as recipient of the investigational product(s) or as a control.

Application. Application made to a health authority to market or license a new product.

Visit:. A clinical encounter for a subject in a trial. Visits are frequently referred to as occurring on Day X or during Week Y;

Sites: Sites are locations where clinical trials are conducted. They are typically a clinic or hospitals where investigators see subjects and perform study procedures, such as medical checks.

Product(Compound): A pharmaceutical form of an active ingredient or placebo being tested or used as a reference in a clinical trial. Products can also be devices such as Pacemakers

Program: Groups of Clinical Studies or Clinical Trials for the same compound.

Projects: Groups of Studies within a Program (Oracle Clinical Only)

Investigator: A person responsible for the conduct of the clinical trial at a trial site. If a trial is conducted by a team of individuals at a trial site, the investigator is the responsible leader of the team and may be called the principal investigator.

Sub-investigator:  Any individual member of the clinical trial team designated and supervised by the investigator at a trial site to perform critical trial-related procedures and/or to make important trial-related decisions (e.g., associates, residents, research fellows).

Case Report Form:A printed, optical, or electronic document designed to record all of the protocol-required information to be reported to the sponsor on each trial subject. The CRF is the way the Clinical Data for Patients is collected.

Clinical Research Associate (CRA): Person employed by a sponsor, or by a contract
research organization acting on a sponsor’s behalf, who monitors the progress of investigator sites participating in a clinical study. At some sites (primarily in academic settings), clinical research coordinators are called CRAs.

Clinical Research Coordinator (CRC): Person who handles most of the administrative responsibilities of a clinical trial, acts as liaison between investigative site and sponsor, and reviews all data and records before a monitor’s visit. Synonyms: trial coordinator, study coordinator, research
coordinator, clinical coordinator, research nurse, protocol nurse.

Discrepancy:Problems found with data reported in the CRF pages by Investigators for specific Patients

Protocol/Study Protocol: The study protocol is the blueprint that all researchers will follow.

A study protocol is a document that describes, in detail, the plan for conducting the clinical study. The study protocol explains the purpose and function of the study as well as how to carry it out. Some specific things included in the protocol are the reason for the study, the number of participants, eligibility and exclusion criteria, details of the intervention or therapy the participants will receive (such as frequency and dosages), what data will be gathered, what demographic information about the participants will be gathered, steps for clinical caregivers to carry out, and the study endpoints. A single standard protocol must be used without deviation to ensure that the resulting data will be significant and reliable.

===========================================

SIEBEL CTMS:

Each clinical trial starts with a protocol for a specific compound (product). Each protocol is conducted at sites and managed by site personnel. A protocol can have many versions and multiple protocols can roll up to a single program. Protocols can also roll up to regions. Subjects are screened and enrolled at protocol sites for specific protocol versions. Protocol sites are paid, based on the activities they complete. Visits and activities are generated for subjects based on templates defined for the protocol. The Clinical Research Associates perform site initiation activities for protocol sites and submit periodic trip reports. A protocol can also be associated with one or more projects.

The entities and tables in Siebel CTMS are as shown below:

Entity                                             Tables
Account                                     S_ORG_EXT, S_PARTY
Activity                                      S_EVT_ACT
Address                                     S_ADDR_PER
Affiliation                                   S_PTCL_ST_CON_LS
Application                                 S_CL_PGM_APP_LS
Clinical Payment                         S_SRC_PAYMENT
Contact                                     S_CONTACT, S_PARTY
Design                                      S_CL_DSGN_LS
Position                                     S_POSTN
Product                                      S_PROD_INT
Program                                     S_CL_PGM_LS
Project                                        S_PROJ
Project Subcontractor                    S_PROG_ORG
Project Subcontractor Contact         S_PROJ_ORG_CON
Protocol                                      S_CL_PTCL_LS
Protocol Site                                 S_PTCL_SITE_LS
Subject                                        S_CL_SUBJ_LS
Subject Status                              S_CL_SUBJ_ST_LS
Subject Template                          S_SUBJ_TMPL_LS
Template Version                          S_SBJTMP_VER_LS
Template Visit                              S_TMPL_PLANITEM

Trip Report                                   S_EVT_ACT
Visit                                            S_EVT_ACT

A typical Clinical Trial Scenario:

The clinical director and the study manager, working for a clinical research organization, or pharmaceutical, biotech, or medical device company, have administrator responsibilities in Siebel Clinical to:
■ Set up a new treatment study program.
■ Create one or more protocols designed to assess the safety and efficacy of certain compounds in
the treatment of the disease.
■ Set up the geographic regions where the protocols are to be carried out.
■ Compile a list of documents that are critical to the study and implement tracking at the protocol,
region, and site levels, and for accounts and contacts.
■ Create a subject visit template to facilitate consistent application of the protocol across sites and
subjects. This template is used to set up subject visit schedules and activities according to the
guidelines laid out in the protocol.

When the program, protocol, and subject visit templates have been set up, the CRAs who are the
end users of the Siebel Clinical product do the following:

■ Enter data about the:
  ❏ Sites where the protocols are carried out.
  ❏ Members to be assigned to the teams at the site, region, and protocol levels.

  ❏ Accounts, institutions such as hospitals and clinics where the studies are conducted.
  ❏ Contacts, site personnel such as investigators, site coordinators, and nurse practitioners who carry out      the protocols.
  ❏ Subjects recruited for the clinical trial.
■ Screen and enroll subjects and, if necessary, rescreen the subjects.
■ Use the subject visit template to set up detailed schedules for the subjects’ visits to the sites.
■ Track required documents at the protocol, region, or site level, or for accounts or contacts.

Some major steps to be performed in Siebel CTMS to achieve the above mentioned scenario are:

1. Create a new Clinical Program. The clinical program is the highest-level initiative in Siebel Clinical. Protocols, regions, sites, and subjects must be associated with a program.

2. Setup a Protocol

3. Set Up Regions

4. Define a Subject Visit template. Subject visit templates allow you to set up a template schedule based on the protocol. The template is then used to generate screening, rescreening, and enrollment schedules for each subject, according to the subject’s screening, rescreening, and enrollment dates.

5. Create an Account and Contacts. An account is the institution from which clinical trials are managed. Typically, it is the facility where the investigators conduct the trials. More than one site can be associated with an account and one account can carry out multiple protocols. IRBs (institutional review boards), central labs, CROs(clinical research organizations), and other subcontractors may also be tracked as accounts. Contacts is the term used for personnel working at clinical sites. This includes the investigators, typically medical professionals who are also researchers and site coordinators, who may be the practicing nurses administering the treatment plan according to the clinical protocol.

6. Create a Site. The site is the group at an account, headed by a principal investigator, who carries out a particular protocol. In Siebel Clinical, a separate site record must exist for each unique combination protocol,
account, and principal investigator.

======================================

Hierarchical relationship of programs, protocols, regions, and sites:

DAC Parameters for Informatica Workflows

The main location for the parameters for DAC are in the 2 files located in $DAC_HOME/Informatica/parameters/input

• - parameterfileOLTP.txt is used for source systems/SDE mappings
• - parameterfileDW.txt is used for DW mappings/SIL and PLP mappings

Another place to define the parameters  is in the task’s Parameters tab in DAC console itself. The parameter values defined in here will overwrite those set in the text files.

When DAC starts to execute the task, it creates the parameter file needed for each workflow in Informatica on the fly based on these definitions. DAC changes the format of the file suitable for Informatica including changing the command name into actual Informatica session name [FolderName.SessionName]. It also adds common DAC parameters into each workflow parameter file.

Oracle Business Analytics Warehouse

The Oracle Business Analytics Warehouse is a unified data repository for all

customer-centric data. The purpose of the Oracle Business Analytics Warehouse is to

support the analytical requirements of Oracle E-Business Suite, Oracle's Siebel CRM,

and PeopleSoft Applications. It is composed of following components:

1. A complete relational enterprise data warehouse data model with numerous

pre-built star schemas encompassing many conformed dimensions and several

hundred fact tables.

2. Prebuilt data extractors to incorporate data from external applications into the

Oracle Business Analytics Warehouse.

3. A set of ETL (extract-transform-load) processes (Informatica) that takes data from Oracle

E-Business Suite, Siebel CRM, PeopleSoft Enterprise and other transactional

systems (OLTP), and creates the Oracle Business Analytics Warehouse tables.

4. The Oracle Business Intelligence Data Warehouse Administration Console (DAC),

a centralized console for the set up, configuration, administration, loading, and

monitoring of the Oracle Business Analytics Warehouse.

Date Manipulations in OBIEE

Dates are manipulated in OBIEE using TIMESTAMPADD function.

The syntax is as follows:

TIMESTAMPADD(SQL_TSI_interval, init_expr, timestamp_expr)

e.g if you want to add a month to a given date; it can be done as follows:

TIMESTAMPADD(SQL_TSI_MONTH, 1, given_date)

Other SQL_TSI intervals are:

• SQL_TSI_SECOND
• SQL_TSI_MINUTE
• SQL_TSI_HOUR
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

How to find out the referenced table and column name if the foreign key constraint name is given

The following query will give the desired result:

select
ac.table_name child_tab,
acc1.column_name child_col,
ac.constraint_name foreign_key,
acc.constraint_name primary_key,
acc.table_name parent_tab,
acc.column_name parent_col
from
all_constraints ac,
all_cons_columns acc,
all_cons_columns acc1
where
ac.owner=acc.owner
and acc.constraint_name=ac.r_constraint_name
and ac.constraint_name=acc1.constraint_name
and ac.constraint_name= :foreign_key_constraint_name

Constraints

In Oracle, constraints are a facility to enforce rules to make sure that only allowable data values are stored in the database(i.e to make sure the data integrity in database). All constraints have a name. The developer who defines the table or adds a constraint can name it at that time. However, if the name is not supplied, Oracle will assign a system generated name that will uniquely identify the constraint. System generated names are pre-fixed with SYS_C (for "system constraint") followed by a 7 digit integer.

Types of Constraints:

1. NOT NULL: If as per business logic, a column or a set of columns in a table can not allow NULL values, then NOT NULL constraint can be used to enforce this rule.

e.g

alter table SALES modify (cust_id NOT NULL);

2. UNIQUE: If as per business logic, a colum or a set of columns in a table need to store unique values, then, UNIQUE constraint can be used to enforce this rule.

e.g

create table test (col1 number UNIQUE);

UNIQUE constraints allow NULL values to be stored.

3. PRIMARY KEY : Primary Key constraint is a combination of NOT NULL and UNIQUE constraints. The column or the set of columns on which Primary Key is defined, will allow only unique and not null values. There can only be 1 (and only 1) primary key in an Oracle table.

e.g

create table test (col1 number PRIMARY KEY);

4. FOREIGN KEY:  It is frequenly required that data in one table should be validated by comparing it to data in other table.(e.g if you add a new order in your ORDERS table, you must cross check that a valid product corresponding to this order is present in your PRODUCTS table). To achieve this kind of data integrity, foeign key constrained is used. This type of validation is also known as referential integrity. A foreign key constraint always makes refrence to a Primary key or a unique constraint of other table. The table that has foreign key defined is called referencing table. The table that has Primary key or Unique constraint defined is called referenced table.

e.g

create table orders
(
  order_no number primary key,
  customer_name varchar2(10),
  constraint cons_prod_fk foreign key(prod_no) references product(prod_no)
);

> If you define the foreign key constraint with 'ON DELETE CASCADE' option, then if any rows are deleted from the referenced table, then the corresponding rows will also be deleted from the referencing table.

> NULL values are allowed in Foreign Key columns.

5. CHECK:  Check constraints are used to enforce one or more conditions to be checked for the data row.

e.g

alter table customers add constraint customer_credit_limit CHECK (credit_limit <= 1000)

Some additional information regarding Constraints:

• Constraint names can be found in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.

• Constraints can, at any time, be either enabled or disabled. When you create, disable or enable a constraint, you may speify some other information regarding how the constraint behaves. An ebabled constraint can have two options VALIDATE and NOVALIDATE. VALIDATE will validate the existing data in the table while NOVALIDATE will not validate the existing data afyter the constraint is enabled.

• When you create or enable a Primary key or Unique constraint, Oracle will create a unique index on the columns of that constraint. Foreign key constraints do not enforce an automatic creation of index.However it is worthwhile to build an index on the columns of each foreign key constraint. Without an index on the corresponding columns in the child table, Oracle is forced to take out a table lock on the child while it performs the DELETE on the parent. If an index is existing, Orcale uses it to identify and lock just the necessary rows in the child while the parent row is deleted.

OBIEE Training for Beginners

Here is a PPT Presentation to introduce Beginners to OBIEE

OBIEE Session