Configuring Siebel efficiently

Improving Siebel Configuration Efficiency

Author: Ioannis Xanthopoulos
Siebel Contractor
ix@ixanos.com



This article presents a practical approach of configuring Siebel using Ixanos ( http://www.ixanos.com/ ).

Configuring Siebel is a very broad exercise. Then again, from a developer’s perspective, it can be broadly categorized to include some or all of the areas listed bellow:

1. Use Siebel Tools / Siebel Client to prepare User Interfaces / Business Logic / Data Layer
2. Prepare programs that are partly or completely hosted outside of Siebel (RDBMS,DLL,JMS, etc)
3. Integrate and test all parts of your solution

During the above steps you will find yourself many times in the position of repeating tasks that you can recall having done before in other projects. Even during your present project you will many times repeat the same thing over and over.

Example: You are about to develop a workflow process and can recall that you have build similar logic in the past. The only thing you remember is that that particular workflow had something to do with the table S_ASSET. But you don’t remember the name of the workflow neither where this particular table was used in (in a workflow-step or in a workflow step argument or in a workflow condition criterion).

You can use Siebel Tools to attempt to retrieve all those workflows, but this would probably be a complicated exercise and it would certainly require more than one queries.

Then again, you can take some time and spool out some SQLs and assemble your own specialized query to meet your needs. This will not only help you understand Siebel better, but it will also set you up for the same task in the future. This is namely what I do. And then I can run the above search in seconds.

The results for S_ASSET could look as the ones shown below. You can see for example that S_ASSET is referred to in 28 workflows and is among others used in the workflow step [RB - Account Membership Approval Workflow] All queries you will write will have to be parameterized as you would want to reuse the query for any possible base table or even for a combination of base tables. Once you have your query the task of building it into Ixanos is simple. Just cut and paste it into the menu creation wizard, determine the dynamic parts by replacing them with tags in the form of [&n] , where n = Integer and next time you will only need to supply the name or names of the tables in order to retrieve the workflows that have something to do with them.

A snapshot of the menu creation wizard for this particular case is: A snapshot of my workflow menu in Ixanos is:

As you see I have queries to fully reveal all workflow processes based on many criteria. Will there be more? Yes. Sure. Depends on what the next project will need.

You could of course prepare menus that speed up your own particular work. The beauty of this is that you can connect to any environment for which you have a suitable JDBC driver and you can define your SQL statements to be database dependent. I have the same multi-windows environment when I run statements against my local Sybase database!

Further on, you can build your own Siebel knowledge database, tailor made to suit your own particular needs. After some time your tailor made Siebel menus would probably look like mine:

For example, an SQL statement that you have prepared for an Oracle 8i database for displaying all inactive database columns that are mapped to active business component fields may not look the same as the one that is prepared for a DB2 v8 database.

During the creation of an SQL menu in Ixanos, you will need to determine the database type. Afterwards and anytime you execute this menu, Ixanos will check if the currently active connection is of the same database type and will complain if its not. If for example you execute an SQL statement defined for a DB2 v8 database against an HSQL database, you will get a message like this:

Except, finding objects using Ixanos it makes also good sense to prepare validation scripts and run them against any new project environment to assess the strong and week points in the repository. A typical example is the query I mentioned above. Namely, to identify inactive database columns that are mapped to active business Component fields. Similarly, you may wish to identify any LOV / EAI datamaps active/inactive differences when switching from one environment to another. You can prepare many queries for this purpose. You could perhaps combine them into a complete repository review.

Further on, I find it very useful to configure Ixanos to export all scripts of the repository into a text file and to index it using Ixanos. Then I can run search engine queries against it. I could then identify a business component field in a business component property and also in a business service simultaneously (in the same query). This gives me a much faster and better understanding of what is there and what needs to be done.

Once you have setup Ixanos to contain most of the queries you need, you only need a USB port to run Ixanos from and you will always have the same front end in all your Siebel projects. You will not need to install it on your project computer, you can simply run it from your flash drive.


Regards
Ioannis Xanthopoulos
ix@ixanos.com
www.ixanos.com

Debugging Siebel EIM

Debugging Siebel EIM results (Part I)

Author: Ioannis Xanthopoulos
Siebel Contractor
ix@ixanos.com


This article presents a practical approach of debugging EIM processes using Ixanos
( http://www.ixanos.com/ ).
To download it in pdf format use:
( http://www.ixanos.com/docs/Debugging%20Siebel%20EIM%20results.pdf ).

The rough procedure of migrating data into the Siebel database using EIM is:

1) populate the staging tables
2) pre-processing activities
3) tune the database to prepare for the data load, create auxiliary db objects
4) identify and populate the appropriate interface tables
5) populate the interface table(s)
6) determine the EIM logging level
7) start the EIM task
8) check the results
9) check the EIM logs
10) clean up and post-processing activities

Errors may appear in any of the high-level stages identified above, but in this article we will only focus on stage 8 errors.

Typically, errors in this step emanate from incorrectly defined foreign keys in the interface tables. In other words, the values entered in the interface tables could not be resolved to match the foreign keys in the target database. Other error sources are duplicate or partly populated rows, faulty LOV values, numeric vs alphanumeric values, not expected attributes (suppressed in the .ifb file) etc.

In order to swiftly identify the source of errors in this stage, it is very helpful to select some rows that have been successfully processed and some that haven’t and to compare them in order to identify all value- differences. Most of the times a list of the different values in a given interface table column is self-evident and the problem can be solved swiftly.

To select both successful as well as failed rows from the interface table, we can use a query similar to the one that follows. In case the interface involves more than one interface tables we could involve the remaining tables as well. Further on, the staging tables could also be a good comparison source.

select *
from siebel.eim_asset
where if_row_batch_num = 1000
and if_row_stat = 'IMPORTED'
fetch first 5 rows only
union
select *
from siebel.eim_asset
where if_row_batch_num = 1000
and if_row_stat <> 'IMPORTED'
fetch first 5 rows only

In the displayed results we can then simply utilize Ixanos and right-click and select the option

“compare selected rows” and analyze the differences.

The differences are then displayed in a separate list. This list will only contain the columns that have different values in the rows that we have selected. These columns will be presented in bold letters and will have all its different values listed directly underneath them. A snapshot of such a list could look as follows. The right panel shows the connection details, ignoring passwords and allows you to have several result comparisons in parallel, as you would know which database produced them.
Further on, you may store these results on disc and port them to any other computer for a comparison or review with your team or to debug them at a later point in time. In case you have results from previous runs of the very same interface you can also load those into Ixanos and compare them to the current results. Both save and load functionality is provided in Ixanos and allows you to store any SQL-query results that you want.
Practical experience shows that sometimes EIM tasks run flawless in an environment and fail when ported on another environment. Different environments typically correspond to different databases. In this case we would need to setup a database link to run the query above and collect successful and failed rows from both environments simultaneously. Then again using Ixanos, we can simply connect to both databases, run the select statement against each of them and then drag and drop the results into a new table or one of the existing tables. And finally just use the Ixanos option “compare selected rows” and analyze the differences.

Another approach could of course be to store the results of the query of both environments on disc and load them into Ixanos and compare them. This would make sense in case one of the environments is not available and you want to run the comparison later.

Another possible scenario is that of the EIM-run being a completely new one, in which case you have no results to compare to. In this case it is advisable to concentrate on the control-T_ columns in order to see which have been resolved and which not. This can be easily done using Ixanos, by just filtering them out of the interface table using the integrated menu option EIMREGEX as can be seen below:

Once the control-T_ columns ( ___EXS, ___UNQ, ___RID, ___STA) have been selected you may also wish to display the user key of the underlying base table. This can be done either by simply enabling the selected columns manually or by using the REGEX option.

These filtering options are complementing each other and will not erase the T_ columns selected previously.

An example can be seen here:
Finally, to also include all columns that fulfil certain criteria, for example all columns starting with PAR_ and ending either with _BU or with _LOC you can use the REGEX button and define the exact columns you wish to include by defining their 3 parts (prefix, core and suffix). All three parts can contain multiple values that will be OR-combined to determine which columns will be included in the result-set.

In the example below we select all columns that start with PRE_ and end with _BU or with _LOC. This would include columns like PAR_BU and PAR_DIVN_LOC.

Pay attention to the pipe-delimiter in the definition of the suffix in the input mask (_bu_loc). Also note that Ixanos uses lower-case for all columns. This is done to have more meaningful sorting of the column names.
In case we still haven’t understood the reason why the EIM task fails, we would have to look at all non empty columns of the interface table or perhaps just look at all the empty ones to verify that we have indeed populated all columns as indicated in the .ifb file.

Of course some Siebel interface tables have more than 200 columns and observing their values may be a very frustrating exercise. In this case we can simply utilize Ixanos and ask him to present to us all columns that have at least one row in the result set that has a value or we can have Ixanos present to us all columns that have no values in all selected rows.
Finally, we may wish to concentrate on a single row of the result set. Ixanos present the result set in two views. The first one shows all columns of the row as a list applet. The second one displays all columns of the row as a form applet. On this form applet you have again the option to display only non-empty columns (ie columns that have a value) or to display all columns that are empty.
A typical list- and form-applet view of an Ixanos SQL results’ set can be seen here. As you can see for the classic table S_ORG_EXT browsing the column values in the list applet may be difficult. Then again the form applet gives you a much easier way of viewing the data.

Note, that the filled columns are highlighted in blue as opposed to the empty ones (like DCKING_NUM) which are not highlighted.

The [Results]-view of Ixanos is a desktop which can contain as many result screens as you need for your analysis. In the image below we can see two screens. One is maximized and visible and the other is minimized as a button in the lower left corner.
Many times during EIM-troubleshooting you will find yourself having ten to twenty such open results’ windows that you wish to store so as to use them again next day. This can easily be done using Ixanos by utilizing the save desktop /load desktop functionality:
As I have personally worked in more than ten EIM projects in west Europe so far and was involved in companies across sectors (telco, banking, pharma, public sector, utilities, automotive), I have seen most of the issues that occur during EIM troubleshooting. All these issues have been dealt by Ixanos easily and made my troubleshooting experience a very smooth one. I hope this article sheds some light in this sometimes frustrating exercise and I hope to have also shown you how to make your task more pleasant using Ixanos.


Regards
Ioannis Xanthopoulos
ix@ixanos.com
http://www.ixanos.com/