Chapter 10. Siebel Analytics 325
Answers. The HTTP request is passed on to the Siebel Web Server which in turn
sends the request to the Siebel Analytics Server. The Siebel Analytics Server
converts the logical request into a physical SQL query based on the Siebel
Analytics Repository configuration and sends the SQL to the SRMW database.
The SQL is processed by the database and the results returned back through the
Siebel Analytics Server and Siebel Analytics Web to the browser.
However, despite the architectural similarities, Siebel Analytics is a little different
than the Siebel CRM. One major difference in the Siebel Analytics architecture is
the ETL (Extract Transform & Load) component. This is a major component in the
architecture and is the one that most affects the OLTP database. To load the
SRMW database, Siebel Analytics uses prebuilt Informatica workflows/mappings
that are stored in the Informatica repository, typically on the SRMW database
and are executed by the Informatica server. Siebel Analytics also provides a
Datawarehouse Application Console (DAC) that complements Informatica to
manage and administer the ETL process, including calls to the Informatica
Server for executing the workflows/mappings as well as tasks such as dropping
and creating indexes, executing the DB2 utility RUNSTATS, and so on. The full
features and capabilities of DAC are documented in the Siebel Datawarehouse
Installation and Administration Guide. In this chapter, we discuss features that
are relevant to the scope of this book in general and this chapter in particular.
10.1.1 DAC and Change-Capture process on OLTP database
To understand the impact of the ETL process on the OLTP database, we take a
brief look at the data Change-Capture process implemented by Siebel Analytics
on the OLTP database. For the purpose of extracting data from the OLTP
database and loading it into the SRMW, a set of tables, commonly called the
“Image” tables are created and maintained on the OLTP database. These include
the S_ETL_R_IMG_* tables (R Image tables), the S_ETL_I_IMG_* tables (I Image
tables) and the S_ETL_D_IMG_* tables (D Image tables). The S_ETL_R_IMG_*
tables help identify rows in the OLTP tables that have been modified or newly
created (inserted) during a period of time. For this purpose, the last update
column of the source tables’ records is compared to the S_ETL_R_IMG_* tables to
effectively extract the records that fall within the ETL extract window specified in
the DAC ETL preferences. The S_ETL_I_IMG_* tables are temporary holders that
just store the row identifiers that have either changed or been newly created and
are used for incremental ETL. To capture deletes in the OLTP database,
however, the change capture process uses delete triggers in conjunction with the
S_ETL_D_IMG_* tables. These triggers are created on the OLTP tables through
DAC. When rows are deleted from the OLTP table, the triggers capture the row
identifier into the corresponding S_ETL_D_IMG table. During the Change-Capture
process run by DAC, it moves the deleted row information from the D Image
tables to the I Image tables with the OPERATION column set to a value of “D”.

Get Siebel 7.8 with IBM DB2 UDB V8.2 Handbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.