EIM Table IF_ROW_BATCH_NUM - SQL Server

When loading the EIM tables in Siebel use the logic below to populate the IF_ROW_BATCH_NUM attribute

SQL Server

CASE
WHEN ROW_NUMBER() OVER (ORDER BY ROW_ID)%5000 >= 0 AND
             ROW_NUMBER() OVER (ORDER BY ROW_ID)%5000 <= 5000
THEN
            CEILING(ROW_NUMBER() OVER (ORDER BY ROW_ID)/5000+10000)
ELSE   
            ROW_NUMBER() OVER (ORDER BY ROW_ID)

Oracle
Replace the CASE statement above to DECODE

Siebel Local DB

Siebel Local DB ::
If you forget the username and password to access the Local DB here is the solution

Siebel Version 6.x
User Name : SQL
Password : DBA

Siebel Version > 7.x
User Name : SQL
Password : Enterprise Server Name in Uppercase

SQL Server: Update Records

DECLARE @B_RID NVARCHAR(15);
DECLARE @B_LOC NVARCHAR(50);
DECLARE @B_NAME NVARCHAR(100);
DECLARE @AM_LOC NVARCHAR(50);
DECLARE @AM_NAME NVARCHAR(100);
DECLARE C1 CURSOR FOR
SELECT BASE.ROW_ID, BASE.LOC, BASE.NAME, AUTO.ACCOUNT_ID, AUTO.ACCOUNT_NAME FROM S_ORG_EXT BASE, UCM_ACCT_POST_AUTO_MATCH AUTO
WHERE BASE.LOC = AUTO.ACCOUNT_ID AND BASE.LOC IN (
SELECT DISTINCT ORG_ACCNT_LOC FROM EIM_FN_CIF_SYST WHERE IF_ROW_STAT = 'PARTIALLY_IMPORTED')
ORDER BY BASE.LOC;
OPEN C1;
FETCH NEXT FROM C1 INTO @B_RID, @B_LOC, @B_NAME, @AM_LOC, @AM_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE S_ORG_EXT SET NAME = @AM_NAME WHERE ROW_ID = @B_RID
    FETCH NEXT FROM C1 INTO @B_RID, @B_LOC, @B_NAME, @AM_LOC, @AM_NAME
END
CLOSE C1
DEALLOCATE C1

Generate Address Name for EIM

Many times we have to replicate the ADDR_NAME configuration/calculation for Initial load.

Below is the query to generate the exact string as it gets generated from UI. This is written in SQL Server and the inbuilt functions can be changed to replicate the same in Oracle

ADDR NAME
------------------------
SELECT
LEFT
(ADDR,
CASE
WHEN (100-CASE
WHEN STATE IS NULL
THEN 0
ELSE LEN(STATE)
END
-
CASE
WHEN CITY IS NULL
THEN 0
ELSE LEN(CITY)
END
- 4) < (LEN(ADDR))
THEN 100-CASE
WHEN STATE IS NULL
THEN 0
ELSE LEN(STATE)
END
-
CASE
WHEN CITY IS NULL
THEN 0
ELSE LEN(CITY)
END - 4
ELSE LEN(ADDR)
END)
+
CASE WHEN ADDR_LINE_2 IS NOT NULL THEN ', ' ELSE '' END
+
ISNULL(ADDR_LINE_2,'')
+
CASE
WHEN ADDR IS NOT NULL AND CITY IS NOT NULL OR STATE IS NOT NULL
THEN ', '
ELSE ''
END
+
CITY
+
CASE WHEN (STATE IS NOT NULL AND ADDR IS NOT NULL OR CITY IS NOT NULL)
THEN (CASE WHEN CITY IS NOT NULL THEN ', ' ELSE '' END)
ELSE ''
END
+
STATE
FROM dbo.S_ADDR_PER

For Oracle Replace
+ with (Double Pipe)
ISNULL with NVL
LEN with LENGTH
LEFT with SUBSTR
CASE WHEN THEN ELSE END with DECODE

Siebel Repository Tables Query

To get the column information based on View and Applet from the Repository

SELECT A.NAME, C.APPLET_NAME, E.FIELD_NAME, G.COL_NAME, H.NAME
FROM
S_VIEW A, S_VIEW_WEB_TMPL B, S_VIEW_WTMPL_IT C,
S_APPLET D, S_CONTROL E,
S_BUSCOMP F, S_FIELD G,
S_TABLE H, S_COLUMN I
WHERE
A.ROW_ID = B.VIEW_ID
AND B.ROW_ID = C.VIEW_WEB_TMPL_ID
AND A.ROW_ID = ''
AND A.REPOSITORY_ID = ''
AND D.NAME = C.APPLET_NAME
AND D.REPOSITORY_ID = ''
AND D.NAME = ''
AND D.ROW_ID = E.APPLET_ID
AND E.FIELD_NAME IS NOT NULL
AND F.NAME = D.BUSCOMP_NAME
AND F.REPOSITORY_ID = ''
AND G.BUSCOMP_ID =F.ROW_ID
AND G.JOIN_NAME IS NULL
AND E.FIELD_NAME = G.NAME
AND F.TABLE_NAME =H.NAME
AND H.REPOSITORY_ID = ''
AND I.TBL_ID =H.ROW_ID
AND I.NAME =G.COL_NAME

Automated Script/Batch File: To Stop Server, Compile SRF, Generate Scripts, Start Server

The batch file will perform the following Siebel Administrative Tasks:

Backup the SRF from servers
Perform Full Compile
Stop Siebel Services
Copy Compiled SRF
Start Siebel Services
Run the Genbscript Utility
Copy Genbfolder
Restart Web Server

Batch File:

@echo off
REM ——————————————————————————————————————————————————————————————————
REM Batch compiles Siebel SRF and places on the server
REM ——————————————————————————————————————————————————————————————————

REM —- Step1 : Create Backup —-
SET MONTH=%DATE:~4,2%
SET DAY=%DATE:~7,2%
SET YEAR=%DATE:~10,4%
SET HOUR=%TIME:~0,2%
SET MINUTE=%TIME:~3,2%
SET SECOND=%TIME:~6,4%

copy “\\siebel\siebsrvr\OBJECTS\ENU\siebel_sia.srf” “\\siebel\siebsrvr\OBJECTS\ENU\back\siebel_sia_%YEAR%_%MONTH%_%DAY%_%HOUR%_%MINUTE%.srf” /y
E: <>
cd \\siebel\siebsrvr\OBJECTS\ENU\

REM —- Step2 : Stop Siebel Service —-
net stop <> -- Sample : “siebsrvr_DEVENT_DEVSRVR1″

REM —- Step3 : Stop Siebel Service on second server —-
SC \\<> STOP <> -- Sample : “siebsrvr_DEVENT_DEVSRVR2″

REM —- Step4 : Complie it in local directory —-
\\siebel\Tools\BIN\siebdev.exe /C “\\siebel\Tools\BIN\ENU\tools.cfg” /d ServerDataSrc /u sadmin /p sadmin /bc “Siebel Repository” siebel_sia_new.srf

ping -n 3600 localhost > nul

REM —- Step5 : Copy to siebsrvr folder on both server —-

copy \\siebel\Tools\OBJECTS\ENU\siebel_sia_new.srf \\siebel\siebsrvr\OBJECTS\ENU\siebel_sia.srf /y

ping -n 120 localhost > nul

REM —- Step6 : Run Genb script———–
E: <>
cd \\siebel\siebsrvr\bin
genbscript “\\siebel\siebsrvr\bin\enu\Test.cfg” \\enu

REM —- Step7 : Start Siebel Service —-
net start “siebsrvr_DEVENT_DEVSRVR1″

REM —- Step8 : Start Siebel Service on Second Server —-
SC \\<> START “siebsrvr_DEVENT_DEVSRVR2″

REM —- Step9 : Stop Web Server —-
net stop “W3SVC”

ping -n 180 localhost > nul

REM —- Step10 : start Web Server —-
net start “W3SVC”

—————End of Batch Script—————————————–

Popup Applets

Every implementation we aim to rollout a ‘vanilla’ solution. Everyone also understands the ongoing benefits of not over-customizing. We also understand that there are still situations that justify slight tweaks to the Siebel application…

In Siebel 7x it’s possible to launch a pop-up applet from a normal applet by using the ShowPopup method. You can also find the information in Bookshelf -> Configuring Siebel eBusiness Applications -> Configuring Special Purpose Applets -> Configuring Pop-Up Applets Launched from Applets.

In a jist

Add a control to your applet
Set the control Method Invoked to ShowPopup
Set the control User Property Popup to the name of your popup applet
The popup applet specified in the user property must use a class derived from CSSSWEFramePopup.
To see all possible classes select ‘Class’ in Tools Object Explorer and query for ‘Super Class’ = CSSSWEFramePopup: the standard class for a popup list applet is CSSSWEFrameListPopup. If you’re creating a new applet for your popup and you don’t expect edits in the popup, it’s simplest to configure your layout in ‘Base’ mode.

The popup applet can be based on any business component in currently active business object, and will appear in context. So you could launch a popup from the Contacts applet, for instance, and list all Items. Alternatively, you can base your popup on a VBC and display any random list of choices you desire. Because it’s all in context, capturing the user action and invoking a change on the launching business component is trivial.

Useful SQL's -- Siebel

Query to identify all Custom Column Extensions

SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE
OWNER='SIEBEL' AND
TABLE_NAME LIKE 'S\_%' ESCAPE '\' AND
COLUMN_NAME LIKE 'X\_%' ESCAPE '\';

Query to identify all Custom Indices

SELECT INDEX_NAME, COLUMN_NAME, TABLE_NAME
FROM ALL_IND_COLUMNS
WHERE
TABLE_NAME LIKE 'S\_%' ESCAPE '\' AND
COLUMN_NAME LIKE 'X\_%' ESCAPE '\' AND
TABLE_OWNER = 'SIEBEL';

Query to identify all Custom Tables

SELECT TABLE_NAME
FROM ALL_TABLES
WHERE
OWNER='SIEBEL' AND
TABLE_NAME LIKE 'CX\_%' ESCAPE '\';

EIM Performance Tuning for Dummies

A quick look at the various strategies available for tuning EIM on Oracle.

EIM is the mechanism used to load large data volumes into a Siebel database.

EIM can also be used for ongoing data loads where data is processed (typically from an external system) to create, update, merge or delete records.

Data quality

EIM can load data fast. However, if the data is missing key fields from the source system, the entire data load will have to be repeated. Exhaustive, thorough checking, testing and validation of all data will have significant benefits. EIM performance degrades in direct proportion to the number of rejected records.

Consider using a staging table with multiple check constraints to validate the data and enforce integrity as much as possible.

Testing

Test, test, check and double check the load procedure. Check that data is actually visible in the Siebel application. Failure to populate a single primary column may mean data will not be visible to the end user.

Profiling

Analyse the SQL statements issued by EIM Process and the performance profile. The IFB parameter SQLPROFILE may be specified to generate a detailed breakdown of each EIM step together with the most resource intensive SQL statements.

Gathering statspack snapshots at the start/end of EIM processing may be used to report on general Oracle performance metrics.

Disable Transaction Logging

Siebel supports mobile web clients (remote users who work in a discinnected mode and synchronise with the Siebel server). For large scale data loads,the Siebel System Preference 'Docking: Transaction Logging' should be set to 'FALSE'. When the data load is complete, mobile web clients should be extracted.

Tune the IFB

ONLY BASE COLUMNS
The parameters 'ONLY BASE TABLES and, in particular, 'ONLY BASE COLUMNS' limit the amount of processing done by EIM. In addition, the resulting IFB documents the data mappings.

DEFAULT/FIXED COLUMNS
The EIM parameter 'DEFAULT COLUMN' and 'FIXED COLUMN' are relatively inefficient. EIM generates an UPFDATE statement for each such column for each batch processed. It is preferable to populate constant values prior to the EIM load either using SQL*Loader or updating the data.

Index hints
Two IFB parameters USE ESSENTIAL INDEX HINTS (default=TRUE) and USE INDEX HINTS (default=FALSE) should usually both be set to TRUE on Oracle. The parameters issue additional index hints to choose the appropriate indexes.

Direct path inserts
EIM also supports direct path inserts. The IFB parameter 'ORACLE INSERT APPEND MODE = TRUE' should be specified. This will add the /*+ APPEND */ hint to all insert statements.

Direct path load locks the target table. However, this isn't an issue here as the insert operation is the last part of EIM processing and is followed by a commit.

Tune the batch size
EIM processes data in batches. The size of each batch is determined by the IF_ROW_BATCH_NUM column in the EIM interface table. It is worth experimenting with batch size between 2,500 and 25,000 to determine the optimal value. There isn't a massive difference and 5,000 or 10,000 is normally a decent choice (and helps makes the sums easier).

Index monitoring
Maintaining all those indexes is expensive. Oracle provides a feature to monitor index usage. This technique can be used to enable index monitoring, execute an EIM data load. Any non-unique indexes that were not used during EIM processing may be dropped.

The necessary DDL to re-create the indexes should be captured. This allows the indexes to be reinstated when data loading is complete. Parallel index creation may be used to create the indexes quickly. The Siebel DDLSYNC utility will also recreate any missing indexes but is sequential (and relatively slow).

Tune Oracle
Siebel is an OLTP application with users connecting using a web browser. EIM is a batch application. Consequently, there is scope to reconfigure the SGA. The size of the shared pool may be reduced and the memory allocated to the buffer cache.

Pre-allocate tablespace
For large data loads, it is recommended to pre-allocate the necessary tablespace. Dynamic extent allocation is expensive and should be avoided.

Redo logs(Undo Tablespaces)
EIM generates large volumes of redo information. In addition to the actual insert's of new records into the target tables, EIM performs updates on the EIM interface tables to set and modify status values. In addition, some primary columns will also result in update statements.

Parallelism
The final step of EIM tuning should be to run multiple EIM processes in parallel. EIM is database intensive and consumes relatively few resources on the Siebel application server. With modern disk storage technologies, the limiting factor for EIM throughput should be the CPU resources on the database server.

Siebel High Interactivity Framework

Have you ever wondered where are all the records???

When clicking on Pick/MVG/Shuttle Applet in Siebel Web Client, the applet which pops up does not seem to show any records.

The IE instance which runs the Siebel Application crashes when you invoke the Pick/MVG/Shuttle Applet

The pop up window is hidden and small

If any of the above happens then there is a damaged High Interactivity Object. Try deleting all the Siebel High Interactivity object and launch the application again. The object will be recreated and the pop ups should work properly. Alternatively check if the Pop Up Blocker is disabled.

TO DELETE THE HIGH INTERACTIVITY OBJECTS:

Internet Explorer –> Tools Menu –> Internet Options
Go To General Tab
In the section "Temporary Internet Files" click on the Setting Button
There will another PopUp
Click on the "View Object" Button
Delete all the Siebel High Interactivity Object.

Or

Navigate to "\\WINDOWS\Downloaded Program Files"
Delete all the Siebel High Interactivity Object.

Siebel LOVs Performance

Whenever one needs to access lists of values data in the configuration, note the of the business components defined on the S_LST_OF_VAL table, Picklist Generic is cached while List Of Values is not. The impact of this is that repeated queries to Picklist Generic will be served from the memory of the Object Manager, whereas queries to List Of Values will always hit the database. So for improved performance be sure to use Picklist Generic in your configuration, not List Of Values.

Caching behaviour is controlled by the business component property Cache Data: when this property is set to true then the database is only read once for a given query. You can see in Object Explorer that Picklist Generic has Cache Data set to true, while it’s false for List Of Values. This configuration allows the List Of Values BC to be used for managing LOVs, where it’s critical to always have the current database values, while Picklist Generic can be used where performance is more important.

Oracle Forums

http://forums.oracle.com/forums/category.jspa?categoryID=151

Siebel and Internet Explorer 7

Siebel started supporting its application using IE7 for the High Interactivity Web client for all versions higher than 7.5.

The summary of the Siebel versions (and QuickFixes) required to support IE7 is:

8.0.0.1 with QF0103
7.8.2.5 with QF0547
7.7.2.8 with QF0814
7.5.3.17

Oracle Wiki

Official Oracle Public WIKI is out

The Wiki is based on WetPaint and the article editor is pretty intuitive and easy to use.

http://wiki.oracle.com/

Siebel Documentation

Oracle has published the Siebel documentation which is now freely available.

The technical staff no longer need an account on Siebel SupportWeb to access the Siebel documentation. Siebel Bookshelf, for all versions (6.x, 7.x and 8.0), may be viewed online and can also be downloaded locally (HTML format) for reference.

The Siebel documentation is also available in PDF format which allows searching.

http://www.oracle.com/technology/documentation/siebel.html

Siebel -- DBCHCK

DBCHCK is Siebel supplied utility to compare the Logical Schema in the Siebel Repository against the Objects in the Physical Oracle Database.

DBCHCK should be run regularly to check the status of the Siebel Enterprise, particularly following a release.

DBCHCK can be useful to to identify custom indexes added by the development team but not added to the Repository using Siebel Tools.

Syntax:
dbchck /S ODBC_DATASOURCE /U USERNAME /P PASSWORD /T TABLE OWNER /R REPOSITORY /L LOGFILE /D CHECK_AGAINST_DICTIONARY /A ALL_TABLES

Siebel using Oracle Cost Based Optimizer

Have you ever encountered a problem where, when you navigate to an applet in Siebel it takes time to populate the data and when you spool the query and execute it against the database it will work in less than a second

Try Analyzing the table and if it doesnt help try the work around below

Check the TechNote 582. The technote has all the background to the Cost Based Optimizer, and the key is the explanation of Siebel’s Oracle session variables. In summary, to make your SQL client behave like a Siebel session, you need to set the following session variables:

ALTER SESSION SET optimizer_mode = FIRST_ROWS_10
/
ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE
/
ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE
/
ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE
/

The really significant variable here is that first one:
optimizer_mode = FIRST_ROWS_10.
This tells Oracle to optimize for the first page, rather than the whole query, which can dramatically change the way indexes are evaluated.

Sunday Night Live - 2007 Oracle Open World

Siebel Sample Questions

What are the new features available in Siebel 7.7 (Choose Two)

Uses IE back button to navigate previous pages
Uses Siebel back button to navigate previous pages
Uses Siebel history to show the history
Uses IE history button to show the history

Where can you find the remaining views in the view tab?

Using the screen links
From the right arrow button shown in the extreme right corner of view tab
Using view links
From the drop down menu shown in the extreme right corner of view tab

Which EVT syntax creates the HTML output the computer screen (Click exhibit button)

evt.exe –r d:\siebel..\evt.ini –o html –t web d:\siebel\evt\1233.htm
evt.exe –r d:\siebel..\evt.ini –o html
evt.exe –r d:\siebel..\evt.ini –o htmlfile
evt.exe –r d:\siebel..\evt.ini –t web

When upgrading a multilanguage deployment of Siebel applications to version 7.7.
For languages other than the base language for this deployment, what do you need to import?

Symbolic Strings
Locale-specific objects
String tabl
Multiple .srf files

Describes the Network Image Creator?
A utility that helps to avoid media swapping when installing Siebel software on multiple machines


Which controls are applicable only in the shuttle applet, and should appear only there?

Delete and Query
Find and Starting with
Available and Selected
Add, Remove, and Remove All

What new or enhanced feature of Siebel Tools shows applets in both design and preview modes?

Entity Relationship Designer
Customizable Object Explorer
Screen View Sequence Editor
Grid Layout Editor


What object definition will you need to change to implement multi lingual?

Control object definition
Symbolic String object definition
Control Locale object definition
Symbolic String Locale object definition

Which status icon shows Enterprise server running in the Server Management-- > Task view

No status icon
Green
Red
Yellow

How so you submit the job at repeated intervals (Only one)

Create job template
Create job template, set the repeating intervals
Create a new job and select job template and set the repeating intervals
You can’t set

What will be the new password when your DBA reset the Password? You enterprise server name is Siebel

Siebel
Siebel12
Siebel123
Password

You are connected to the enterprise server FIRSTUNIVERSAL Siebel server. Your DBA reset your mobile client password. What is your new mobile client password?

FIRSTUNI
FIRST
FIRSTUNIVERSAL
FIRST123

How does the remote admin set the mobile client Authentication?

Create user id and password
Set the Authentication credential other than none
Use local credentials
Use remember credentials

Which report shows the user sync info at the given period of time?

Synchronization Session Report
Synchronization Frequency Report
Synchronization session
Mobile User Status Report

Where can you specify the condition for a Decision Step (choose one)

Double-click the connector of the Decision step.

What happens when a rule group is not specified for an assignment rule?

Rules are always assigned to a rule group since this is a required field.
A rule cannot be released unless a rule group is specified.
Assignment rules are automatically assigned to a rule group based on the server key mapping.
Rules that are not specifically assigned to a rule group are assigned to the Default rule group.

How does rule sequencing improve performance?

Rule sequencing evaluates only as many rules as needed to ensure that the object is assigned.

All workflows can be paused or suspended by users at run time.

True
False

All running workflows are displayed in the Workflow Instance Admin view

True
False

Authentication for synchronization is performed via the Security Adapter

True
False

Which routing model allows the user to designate a record for synchronization?

Mobile Client – Standard
Mobile Client - Extract Only
Selective Retrieval
Executive Management

What values must be set to enable Time-Based Filtering? (Choose two)

Dock Object
Cutoff Time

TrickleSync frequency can be set to: (Choose two)

Weekly
System boot up
Monthly
Siebel Server restart

What is the usage of MVG? in which situation you will use MVG?

MVG applet is used in a M:M relationship

What is the usage of EIM?
EIM is a server-based program that supports asynchronous integration with other corporate databases.
EIM manages the data exchange b/w external systems and siebel. EIM can be used to perform a bulk import, delete, export, merge.

SIEBEL CRM Links

http://blog.stuandgravy.com/
http://www.dnb.com/demos/siebel/index.asp
http://siebel.ittoolbox.com/
http://download-uk.oracle.com/docs/cd/B31104_01/books/Fundamentals/FundamentalsTOC.html
http://download-uk.oracle.com/docs/cd/B31104_01/portalres/pages/docalpha_all.htm
www.siebel.com/common/includes/pdf_frame.shtm?pdfUrl=/downloads/case_studies/microsoft.pdf
http://www.microsoft-oracle.com/technical_content/siebel/whitepapers/default.aspx
www.siebelonmicrosoft.com
http://www.greenbeacon.com/siebel/default.asp?T=1&M=21&A=678
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebDocs/?Search&Query=[HTMLDocumentName=WM*]+AND+(siebel%20)&Start=1&Count=50&SearchOrder=1&SearchMax=10000
http://eimguide.com/
http://siebel.ittoolbox.com/
http://www.geocities.com/gc_cnctr/eimguide/index.html
http://www.tomsiebel.com/res_Siebel+Workflow_0.php
http://download-east.oracle.com/docs/cd/B40099_01//80Siebel_HTML/books/OTR/booktitle.html