Effectively Rewriting Siebel Predefined Queries for Performance

The following is a cross-post from my co-worker Jeroen Burgers, who shares his experiences as an Oracle Implementation Advisor on his blog with the same name. In one of his recent articles, Jeroen picked up the topic of Siebel Predefined Queries (PDQ) and the impact they can have on performance. I am pleased that he agreed to publish his findings on Siebel Essentials.

***

Ever had to deal with PDQs which required to fetch based on date functions such as Year-to-date or Month-to-date (or any related�)?

I came across an implementation where a customer became very creative trying to resolve this. But the end-result was a terrible performance. Why? Because the PDQ could not be completely be executed as SQL.

A generic implementation flaw in queries written by Siebel configurators or business analysts: misusing calculated fields to be used in e.g. search Expressions and PDQs. It can (or will) hammer performance. A lot.

For example:

Provide me all the Opportunities YTD. This was the original PDQ:

"[Due Date] <= Today() AND JulianYear(Today())=JulianYear([Due Date])"

It does the job. But the SQL WHERE clause would only include the [Due Date] <= Today() clause. Assume you have some 15 years of Opportunities. It would fetch all records. Only in-memory the object manager would be able to further filter based on the condition JulianYear(Today())=JulianYear([Due Date]). You can imagine how resource-extensive this would be. Not to imagine the end-user performance perceived.

Similar constructions for Month-to-date and Quarter-to-date queries.

How to circumvent this?

Goal would be to have Profile Attributes available throughout the application which would carry values such as:

• 1st day of the year - "01/01/2014"
• last day of the year - "31/12/2014"
• 1st day of the month: - "01/08/2014"
• last day of the month: - "31/08/2014"
• Well, you get the point.

To realize this you can easily configure a number of fields on the �Personalization Profile� business components. The nice feature of this business component is that all fields are loaded for every session immediately after login. And those fields - well - become Profile Attributes. Typically the �Personalization Profile� business component consist out fields which can be joined toward the Party record for the user logging in (can be an Employee, but can be also a Portal user). But you can also create Calculated Fields. And that will be of great help. Consider the Calculated Fields below (you can grab the complete.xls here).

Click to enlarge.

The �green� ones are the interesting profile attributes. The white ones are just supporting field to make the calculated fields somewhat readable.

Now let�s rewrite the PDQ from the example.

"[Due Date] <= Today() AND JulianYear(Today())=JulianYear([Due Date])"

The optimized version would become:

[Due Date] > GetProfileAttr("Year Start") AND [Due Date]) < Today()

The optimized PDQ would translate completely into a more enjoyable SQL WHERE clause. It will no longer have to fetch unnecessary data. Let the database take care of this. And of course ensure an appropriate index exist for an efficient execution plan :-)

This article was originally published on the Oracle Implementation Advisor blog by Jeroen Burgers.

***

have a nice day

@lex

Application Deployment

“In any collection of data, the figure most obviously correct, beyond all need of checking, is the error...”

Check, Double-Check, Recheck is the mantra while depolying on production in order to avoid any goofups. Despite any technique being used for deployment including ADM or EIM a thorough sanity should be done after deploying the artifacts on the production envirnoment. This becomes quintessential in multi-project environments where roll out is in phases. Every body has its own strategy based on the environment to perform a sanity check. However the one i have been using is to perform a count of records across the environment along with ADM to deploy artifacts. While ADM ensures that artifacts have been deployed successfully the count helps in confirming that nothing is missed from the source environment.

Here is sample query which can be customized based on the specific artifacts which gives us the count of records from multiple tables such as Personalization rules, runtime events, Views, Views/Responsibility, LOV's :

SELECT

(SELECT COUNT(*) FROM SIEBEL.S_RESP) S_RESP_COUNT, -- Count of Responsibilties

(SELECT COUNT(*) FROM SIEBEL.S_APP_VIEW) S_APP_VIEW_COUNT, -- Count of Views

(SELECT COUNT(*) FROM SIEBEL.S_APP_VIEW_RESP) S_APP_VIEW_RESP_COUNT, -- Count of Resp-View association

(SELECT COUNT(*) FROM SIEBEL.S_LST_OF_VAL) S_LST_OF_VAL_COUNT, -- Count of LOVs

(SELECT COUNT(*) FROM SIEBEL.S_CT_ACTION_SET) S_CT_ACTION_SET_COUNT, -- Count of Runtime Action sets

(SELECT COUNT(*) FROM SIEBEL.S_CT_RULE_SET) S_CT_RULE_SET_COUNT, -- Count of Personalization Action sets

(SELECT COUNT(*) FROM SIEBEL.S_SYS_PREF) S_SYS_PREF_COUNT, -- Count of System Preferences

(SELECT COUNT(*) FROM SIEBEL.S_VALDN_RL_SET) S_VALDN_RL_SET_COUNT -- Count of Validation Rules

FROM DUAL

This query should be run against source and destination databases and results should be compared. The output of this query looks like:

Happy crunching!!

Mapped List Columns/Controls

As the chineese saying go "Ink is better than the best memory", documentation is key to success for any project.

UIS,LLD, HLD are generally part of Document deliverables. We will not discuss these specs here rather this blog will focus more on getting extract from siebel tools for future reference.

This tip may be useful for LLD spec. For most of the objects siebel gives direct export from tools. But consider a scenario when you want export of columns/controls which are mapped to applets. Direct export from list Columns or controls will give you all columns available in the applet and not the mapped one. An export from applet Web Template item will only give columns which are mapped but not the other desired properties like pickapplet or mvg applet.

Following SQL will help to extract list columns which are mapped to list applet in edit list mode. Anybody can change the name of Applet in the query for which columns need to be extracted.

select

D.NAME,

D.AVAILABLE_FLG,

D.DISPLAYFORMAT,

D.FIELD_NAME,

D.HTML_TYPE,

D.MVG_APPLET_NAME,

D.PICK_APPLET_NAME,

D.CHECKBITMAP,

D.COMMENTS,

D.READONLY,

D.RUNTIME_FLG,

D.VISIBLE

From

siebel.S_APPL_WTMPL_IT A,

siebel.S_APPL_WEB_TMPL B,

siebel.S_APPLET C,

siebel.S_LIST_COLUMN D,

siebel.S_LIST E

where

A.APPL_WEB_TMPL_ID = B.ROW_ID AND

B.APPLET_ID = C.ROW_ID AND

D.NAME = A.CTRL_NAME AND

E.ROW_ID = D.LIST_ID AND

E.APPLET_ID = B.APPLET_ID AND

C.NAME = 'Employee List Applet' AND -- Applet name

B.NAME= 'Edit List'; -- Base or Edit List Mode

OUTPUT TO employee_List_Column.csv

Following SQL will help to extract Controls which are mapped to Form Applet in edit mode. Applet name can be changed in order get desired output.

select

D.NAME,

D.FIELD_NAME,

D.HTML_TYPE,

D.MVG_APPLET_NAME,

D.PICK_APPLET_NAME,

D.COMMENTS,

D.READONLY,

D.RUNTIME_FLG

From

siebel.S_APPL_WTMPL_IT A,

siebel.S_APPL_WEB_TMPL B,

siebel.S_APPLET C,

siebel.S_CONTROL D,

where

A.APPL_WEB_TMPL_ID = B.ROW_ID AND

B.APPLET_ID = C.ROW_ID AND

D.NAME = A.CTRL_NAME AND

D.APPLET_ID = B.APPLET_ID AND

C.NAME = 'Case Form Applet' AND -- Applet name for which extract is required

B.NAME= 'Edit'; -- Edit Mode

OUTPUT TO Case_control.csv

This sql should be run on local databse using dbisql utility and could be fine tuned for columns which are desired in UIS.

Ink is truly, better than the best memory.