Ask the Expert 01

Question :

Hi,

I have been following your site for quite sometime and its great.
Wanted to have a clear idea on the Siebel EMail Response module.

I did go through the bookshelf which gives a very good idea but still wanted to know if there are some practical implementation done.

Requirement is that a service request or an activity needs to be created from an email.

Response :

From what i understand of your requirement, please find the following reply to your query. Once the email is triggered using the Outbound Communication Manager BS, an activity gets generated as per the Siebel vanilla functionality. The activity of Type Email-Inbound or Email-Outbound will get created provided the Email templates, Email server, Outbound Communication Manager component is configured properly.

If one wants to generate customized activity / SR post the email is triggered, then e-scripts or VB scripts have to be used. For e.g The email has to be sent to the customer on updation of customer’s demographic info. The e-script written on appropriate event(s) can be called. This code will further invoke an email Workflow (WF). After the WF has been invoked, another chunk of code/Workflow has to be invoked for generating activity / SR by creating a new record in the custom BC.

To know more about Ask the Expert

Introduction of External Business Component (EBC)

In almost all the implementation of Siebel Application we come across the requirement as – “Bring the data from the external source and display” well for that in Siebel the solution is External Business Component (EBC) and Virtual Business Component (VBC). In this Blog the content is specific to EBC, so let me give the defination of EBC first then the process of creating it –

Defination:

EBC is a method used when there is a need to display data which is external to siebel. The external schema is imported into siebel tables using a wizard. Once the exteranal schema is imported, to display this data in an applet, the configuartion is going to be the ame as creating a BC, an Applet,a View etc.

Steps of Creating:

1. get the DDL file for your external table.
here is how a sample ddl file will look like:

CREATE TABLE TPMS.EBC_VEC
(
demo1 VARCHAR2(20),
demo2 VARCHAR2(20),
demo3 NUMBER(10)
)

2. Use siebel object creation wizard to create this table.
File –> New Object –> External Table Schema Import

3. The wizard will ask for following inputs:

i. Select Project this table will be part of from the list -
ii. Select the database where external table resides – Enter the database, for this example it is Oracle Server Enterprise Edition
iii. Specify full path of the file where table definition resides -
iv. Specify a 3 digit batch code for this import – eg 001
v. Click on Next and then click on Finish

4. This will create your External table. with a name like EX_001_0000001. The names of External tables begin with “EX_” the next 3 characters are batch codes and the rest is just a serial number.

* The Type field will be “External” for this table.
* You will also have to map one of the table columns to the Siebel’s Id field. to do this: go to the desired table column and in the “System Field Mapping” column select “Id”

5. Changes to be made in cfg file now follow the below steps

• create an entry for a new datasource under [DataSources] section

TPMS = TPMS

• add a new section [TPMS] to describe the datasource params:

[TPMS]
Docked = TRUE
ConnectString = VECDEV
TableOwner = TPMS_INT
DLL = abc.dll
SqlStyle = OracleCBO
DSUserName = vecdev
DSPassword = vecdev

• Now that you have defined the Datasource in cfg file, go back to siebel tools and add the datasource to your external table. Go to your external table, and go to the Data Source and add a new record:
  Name = TPMS
• External table is now ready for use in a EBC.

Use siebel object wizard to create a BC based on this table. Once the BC is created, change the Data Source property of the BC to “TPMS” .You are now ready to use this BC in a applet/view.

In the above process the description is about an external data source called TPMS and we are fetching the  data from TPMS to Siebel.

But what if we come across a bit more complex requirement … suppose the data is in siebel but it should not be modified from the front end or from the back end (unless and untill one has right to do so) just like an external data source schema. Or let me put it as can we make EBC on the same database we are working i.e. Siebel Database???

The answer and solution is the same Yes we can create an EBC based on the same database but for that we need to create a different DSN and then follow the steps given above.

Please feel free to put comments/questions/ideas

Read More About  External Business Component

Introduction of External Business Component (EBC)

In almost all the implementation of Siebel Application we come across the requirement as – “Bring the data from the external source and display” well for that in Siebel the solution is External Business Component (EBC) and Virtual Business Component (VBC). In this Blog the content is specific to EBC, so let me give the defination of EBC first then the process of creating it –

Defination:

EBC is a method used when there is a need to display data which is external to siebel. The external schema is imported into siebel tables using a wizard. Once the exteranal schema is imported, to display this data in an applet, the configuartion is going to be the ame as creating a BC, an Applet,a View etc.

Steps of Creating:

1. get the DDL file for your external table.
here is how a sample ddl file will look like:

CREATE TABLE TPMS.EBC_VEC
(
demo1 VARCHAR2(20),
demo2 VARCHAR2(20),
demo3 NUMBER(10)
)

2. Use siebel object creation wizard to create this table.
File –> New Object –> External Table Schema Import

3. The wizard will ask for following inputs:

i. Select Project this table will be part of from the list -
ii. Select the database where external table resides – Enter the database, for this example it is Oracle Server Enterprise Edition
iii. Specify full path of the file where table definition resides -
iv. Specify a 3 digit batch code for this import – eg 001
v. Click on Next and then click on Finish

4. This will create your External table. with a name like EX_001_0000001. The names of External tables begin with “EX_” the next 3 characters are batch codes and the rest is just a serial number.

* The Type field will be “External” for this table.
* You will also have to map one of the table columns to the Siebel’s Id field. to do this: go to the desired table column and in the “System Field Mapping” column select “Id”

5. Changes to be made in cfg file now follow the below steps

• create an entry for a new datasource under [DataSources] section

TPMS = TPMS

• add a new section [TPMS] to describe the datasource params:

[TPMS]
Docked = TRUE
ConnectString = VECDEV
TableOwner = TPMS_INT
DLL = abc.dll
SqlStyle = OracleCBO
DSUserName = vecdev
DSPassword = vecdev

• Now that you have defined the Datasource in cfg file, go back to siebel tools and add the datasource to your external table. Go to your external table, and go to the Data Source and add a new record:
  Name = TPMS
• External table is now ready for use in a EBC.

Use siebel object wizard to create a BC based on this table. Once the BC is created, change the Data Source property of the BC to “TPMS” .You are now ready to use this BC in a applet/view.

In the above process the description is about an external data source called TPMS and we are fetching the  data from TPMS to Siebel.

But what if we come across a bit more complex requirement … suppose the data is in siebel but it should not be modified from the front end or from the back end (unless and untill one has right to do so) just like an external data source schema. Or let me put it as can we make EBC on the same database we are working i.e. Siebel Database???

The answer and solution is the same Yes we can create an EBC based on the same database but for that we need to create a different DSN and then follow the steps given above.

Please feel free to put comments/questions/ideas

To know more about External Business Component

Dynamic MTD / YTD Measures in Siebel Analytics

Definition:

MTD: Acronym for Month to Date. MTD value of a certain measure would be the aggregated total of the measure from the start of the month till the defined date. For example: For the date 15th August 2008, MTD Dollars would be the total dollars from starting of August 2008 i.e. 1st August 2008 to 15th August 2008.

YTD: Acronym for Year to Date. YTD value of a certain measure would be the aggregated total of the measure from the start of the year till the defined date. For example: For the date 15th August 2008, YTD Dollars would be the total dollars from starting of 2008 i.e. 1st January 2008 to 15th August 2008.

Situation:

The client required MTD YTD metrics for a few reports. The twist in the requirement was the calculation based on user specified dates supplied from the dashboard prompt instead of the usual current date MTD YTD metrics. This means capturing the date value entered by the user and applying its relation to the concerned request.

Solution:

After a lot of brainstorming and trying out N number of unsuccessful RPD and Answers permutations and combinations, the solution was arrived at with the help of “Filter based on other request” option in the Answers section and a dummy request to capture the prompted date.

Implementation:

Step1:  Creating the dummy report

This dummy request is used to capture the Transaction date. As seen, the Transaction Date column is prompted and the dashboard prompt date value is fed to this request. We also take Month and Year columns for MTD YTD calculation purposes.

Step2: Creating the MTD Request

The request shows Amount by Pay Type. We want to calculate MTD amount for a date specified by the user. The Date filter columns of the dummy and the main request should be same.

Step3: Creating MTD specific filters

Using the “Filter based on Another Request”, we specify the filters for MTD calculation.

Step4: Creating the dashboard prompt

It’s always a good practice to specify a default value to the dashboard prompt so that the dummy request is initialized to some value. Needless to say, the dashboard prompt, the dummy request and the main request should come from the same subject area.

Step5: Displaying and verifying the MTD metrics

The MTD request has been placed on the dashboard along with the normal reporting date request. This way one can verify the MTD results quite efficiently.

Conclusion:

• The YTD metrics can be calculated in a similar manner. The month filter won’t be applicable for YTD. Rest remains unchanged.
• Combine request feature of Answers should be used if the reporting date, YTD and MTD metrics need to be shown together in one single request.
• This dummy request feature is an excellent way of capturing the user entered prompt values of any data type and manipulating the report metrics on the fly based on the captured prompt value in the dummy request.

To summarize, via this method, we get the power of the presentation variables of OBIEE, strictly to just some extent, in Siebel Analytics.

To know more about Siebel Analytics

To Build or Buy? – The Answer is ROI

For Business Intelligence project managers, sponsors and decision makers, things are getting lot more interesting (and complicated) with the advent of packaged BI Applications. Packaged BI is not new but this domain has been getting a big push in recent years from all the major enterprise application vendors.

The logic behind Packaged BI looks sound and bullet-proof. It goes like this – The enterprise applications vendors understand the business aspects very well and have handled complexity of a high order. The collective experience over many years have been distilled into creating specific BI solutions (Financials, Supply Chain, Operations, Sales etc.) and these come packaged with data models, pre-built ETL jobs, standardized reports and high-end predictive analytics. For an example, take a look at this blog describing the packaged BI Applications from Oracle.

So what’s the problem – Why can’t everybody buy packaged BI applications and live happily ever after?

It appears that the choice is not so simple. Packaged BI has certain drawbacks some of which are outlined below:

Packaged BI imposes a certain way of capturing business entities and metrics (euphemistically termed best practices), which might go against an organization’s way of doing things.
The pre-packaged data integration jobs (ETL) stays relevant only for a plain-vanilla implementation of enterprise apps.
Customization done to transaction systems would involve customization to pre-packaged ETL jobs and reports that involves considerable effort and is error-prone.
Packaged BI apps come with embedded ETL and Reporting tools that might be different from the already chosen enterprise standard tools.
From my own experience, I have seen that the packaged BI comes with so many entities and attributes for each domain that it appears “bloated” for companies taking a first step into performing analytics for that particular domain.

Ultimately, the current situation is such that, BI decision makers are grappling with the question of “Build or Buy” – Should I build the BI application from scratch or buy one of those packaged applications? One way to overcome this problem is to build a strong ROI (Return on Investment) framework for BI initiatives in your organization. ROI is computed by dividing the Net Present Value of cash flows over a time horizon by the initial investment. The details of ROI computation and Hexaware’s proprietary tool for financial assessments in BI will be the discussed in subsequent blogs. For now, let’s assume that you have computed the ROI for a Build solution and also for a Packaged BI solution. Once this is done, the choice becomes a little clear – If the ROI for Packaged BI solution is better than expected and the organization can manage the typical pains of implementing a packaged solution, then consider the “Buy” option, else look for a “Build” option.

Now here comes the little twist In my experience, I have seen customers looking at a shorter time-horizon where the ROI of a build solution is typically higher and then move onto a buy solution with a longer time-frame in mind. The extra advantage of this approach is that the organization understands its analytical needs much better before implementing a Packaged BI solution. So it is strictly not a “Build vs Buy” question but can also be a “Build and Buy” scenario.

Thanks for reading. Please do share your thoughts.

Toggling with the Search Specification in Applet

Working with Siebel, I have come across many challenging requirements. Keeping in mind the Siebel Best Practices, the challenge gets tougher. In this post, I will share such a requirement. 

The requirement was to put search specification in a Toggle Applet. Sounds very practical and easy but trust me it was not. About the toggle applet, Siebel Tools Help says:If two applets are defined with the same business component and both have a search specification, then the search specification for the applet in lowest sector is used.

Do not specify different search specifications where several applets in one view are based on the same business component. All of these applets should have the same or an empty search specification.

Going by the above mentioned documentation, I thought of two immediate approaches:

1. Copy the BC and Map one applet which is having the search spec.

I had already made a copy of the BC and creating a third copy, to map to the two given applets, was not an intelligent approach.

2. As per the Siebel Help content we can give the search specification in a form applet.

The Applets we were dealing with were List applets and not Form applets. So the second approach was not applicable either.

Hence the best alternative was to write a script which is as follows:

Set profile attrib in two applets when the user is going to toggle or executed query or SortAscending or SortDescending.

APPLET 1:

if (MethodName == “ExecuteQuery” ||  MethodName == “SortAscending” ||  MethodName == “SortDescending”)

{

TheApplication ().SetProfileAttr (“UserDoingQuery”, “Y”);

}

if (MethodName == “ToggleTo”)

{

TheApplication().SetProfileAttr(“Uni Tgt Team ToggledBackToAdmin”, “2″);

}

APPLET 2:

Use the same code as above and,

Set Profile Attribute as “Uni Tgt Team ToggledBackToAdmin” = 1.

In Event method of Business Component (BusComp_PreQuery   event)

Get the profile Attrib and give the search spec as per your requirement and clear the profile Attribute.

if ((TheApplication().GetProfileAttr(“Uni Tgt Team ToggledBackToAdmin”) == “2″) || (TheApplication().SetProfileAttr(“UserDoingQuery”) == “Y”))

{

this.SetSearchSpec (“Name”, “XYZ”);

TheApplication().SetProfileAttr (“Uni Tgt Team ToggledBackToAdmin”,”");

}

if ((TheApplication().GetProfileAttr(“Uni Tgt Team ToggledBackToAdmin”) == “2″) || (TheApplication().SetProfileAttr(“UserDoingQuery”) == “Y”))

{

this.SetSearchSpec (“Name”, “ABC”);

TheApplication ().SetProfileAttr (“Uni Tgt Team ToggledBackToAdmin”,”");

}

This script helped in enabling the search specification in a toggle applet. Hence, another challenging requirement was successfully met.

To know more about Toggle Applet

End Point in the Business Intelligence Value Chain

An interesting aspect of Business Intelligence is the fact that there
are many end-points possible in a BI Value Chain. Let me explain
a bit here and build a case for creating “Reference Architectures”
in the BI domain.

In my view, there are typically 5 different configurations for the
BI Value Chain that leads to 5 possible end-points. They are:

End Point 1: Reporting and Ad-hoc Analysis

This is the most common type of enterprise BI Landscape.
The objective here is to provide business users with standardized
reports and ad-hoc analysis capabilities to analyze the business.
With that objective in mind, data warehouses and/or data
marts are created as data repositories and semantic layers
for analysis flexibility.

End Point 2: Data Hub or Master Data Repository

This is a scenario where the objective is to consolidate data
and create master data repositories. The consumption of this
master data is typically left to individual consumers to figure
it out for themselves. The complexity in this type of configuration
is more in terms of data quality and governance mechanisms
around the data hub, as the business value increases only if more
systems utilize the data hub.

End Point 3: Source Systems

This configuration indicates a fairly mature landscape where the
feedback loop from the analytical systems to the operational
ones is in place. The concept of Operational BI is built on this
foundation where the data from transaction systems go
through the analytical layers, gets enriched and reaches
its place of origination with the intent of helping business
make better informed transactional decisions.

End Point 4: Data Mining models

This is a configuration that helps organizations
compete on analytics. Integrated, subject oriented, cleansed data
that is taken out of data warehouses / marts are fed into
data mining models in a seamless fashion. The results obtained
from the data mining exercise are used to optimize business
decisions.

End Point 5: Simulations

Here is a configuration that I haven’t seen in practice but have
a strong feeling would be the future of BI. I have some
experience in working with Simulation tools (Powersim,
Promodel to name a few) where the idea is to create a model
of the business with appropriate leads, lags, dependencies
etc. The starting criteria (set of initial parameters) would
typically be fed by a business analyst and the output of
the model would indicate the state of business (or specific
business area being modeled) after a period of time. Given
this context, I think it would be more powerful to have
the simulation models being fed with data from analytical
systems in an automated fashion. Presuming that the simulation
models are built correctly by experts in that particular area,
the output tends to be a better illustration of the future
state of the business than compared to “gut feel” extrapolation.

Outlined above are the 5 different configurations of BI systems.
The logical next step from the technology standpoint is
to publish reference architectures for each of these configurations.
This would help organizations get an idea of the components
involved once they decide on a particular configuration
for their business.

Reference Architectures and Simulations in BI environments
are areas that will be explored more in the subsequent posts.

Thanks for reading. Have a great day!

Read More About Business Intelligence Value Chain

Informatica and Stored Procedures

A. Described below is a scenario where the requirement is to have a stored procedure that returns a cursor as a source.By and large PowerCenter does not support a stored procedure that returns a cursor as a source. The workaround for this is1. The procedure that will load the data to a new table:

CREATE OR REPLACE procedure load (p_initial_date in date, p_final_Date in date) as

str_load varchar2 (500);
str_clean varchar2 (500);
begin
str_clean:= ‘DELETE FROM EMP’;
str_load:= ‘INSERT INTO EMP select * from EMPLOYEE where DOJ between trunc

(p_initial_date) and trunc (p_final_Date) ‘;
execute immediate str_clean;
execute immediate str_load;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
end load;

2. Create the table that will receive the data from the procedure:

SQL> create table EMP as SELECT * from EMPLOYEE where 1 > 2;

3. Add a Store Procedure transformation to the PowerCenter mapping. This transformation will execute this new procedure called as LOAD on this example.

4. Set the run method to be Source Pre Load, to be executed before read the source table.

5. Import the EMP table as a Source Definition. This table will be populated by the new Store Procedure.

If the original store procedure is used by the customer application and you can’t change the source code, you can create a new store procedure that call the original one (without inserting into a table), and execute the insert on the new table executing a loop on the returned cursor.

B. Given below is a situation where you wanted to pass a mapping variable to a stored procedure transformation (it can either be connected or unconnected).

Connected Stored Procedure

The parameters that are passed to a connected Stored Procedure have to be linked from another transformation.
Given below are the steps to pass mapping variable to a connected Stored Procedure transformation:

1. Create an Expression transformation.
2. Create an output port in the Expression transformation with the following expression:

$$mapping_variable

This sets the value of this output port to the mapping variable.

1. Link this output port to the Stored Procedure transformation.

Unconnected Stored Procedure

For unconnected Stored Procedure transformations you can use the mapping variable in the expression calling the stored procedure.
Follow the steps below to pass mapping variable to a unconnected Stored Procedure transformation:

1. Create an Expression transformation.
2. Create an output port in the Expression transformation with the following expression:

: SP.GET_NAME_FROM_ID ($$mapping_variable, PROC_RESULT)

In case if you are attempting to use a mapping variable to store the output value of the stored procedure, the session will fail with the below error.

“TE_7002 Transformation Parse Fatal Error; transformation stopped: invalid function reference. Failed to Initialize Server Transformation.”

To resolve the issue replace the mapping variable with the PROC_RESULT system variable.

Example:

Incorrect, using a mapping variable:

:SP.PROCEDURE(FIELD1, $$mapping_variable)

Correct, using the PROC_RESULT system variable:

:SP.PROCEDURE(FIELD1,PROC_RESULT)

Or

:SP.PROCEDURE($$mapping_variable,PROC_RESULT)

The PROC_RESULT system variable assigns the stored procedure output to the port with this expression.

Read More about Informatica

Business Intelligence Challenge – Understanding Requirements, User Object Analysis

Let us start with the Law of (BI) Requirements“Requirements can not be created nor destroyed; it can only be transformed from one form to another”. The thought is that in all customer environments the requirements for a BI system are always available in some form or the other. We need to find the ‘base object form’ of the requirement and build upon it for further improvement.

In general data in every transaction system gets analyzed and reported in one way or the other. The BI system is built only to improve that process of analysis to a much easier and sophisticated way. Typical requirements ‘understanding’ has been through the means of Questionnaires, Interviews and Joint Discussions, these kinds of requirements gathering could miss out understanding certain things that the user needs because we might not ask the right questions or the user is not in a good mood during the discussion or the user might just provide details on what he can remember at that point of time. When we are talking about users in thousands and located across globe it becomes much bigger challenge.

The solution to cover all aspects of requirements understanding from a user perspective is by analysis of the objects that a user ‘creates or uses’ in his day today activities, we can call this ‘User Object Analysis’.

A ‘User Object’ is any artifact that a user is creating as part of his data preparation, analysis and reporting, this object could be an Excel, PowerPoint slide, Access database, a Word Document, a notepad or an e-mail.

Following are the steps in ‘User Object Analysis’

• Collect all the ‘Objects’ from all users, the objects collected can go across years, but the key is to collect all of them which the user feels as relevant and applicable
• Convert all of the content in each of the ‘User object’ into a relational structure, the conversion process would involve mapping the data in the Objects to its metadata like the business names/elements, tables-columns, username, depart etc
• Analysis of this collected metadata gives a wider view, enables questioning, makes us understand the needs of the users and enables us to define improvements or provide another perspective to the existing ones
• Prepare and submit the ‘User Object Analysis’ report highlighting the needs of each user (or user clusters) to get user confirmation

Benefits of User Object Analysis

• An effective means to understand the needs of a user based on what he does as a daily routine
• An easy way for the user as he has to just read thru final report for approval and need not work in providing inputs through questionnaire or discussions
• Easily managed for users in large numbers or multiple locations
• A good base for us to define improvements for the existing process of analysis
• Platform to consolidate the needs across multiple users and carve out the user clusters who perform same kind of analysis
• Enables us to think through the business process and improves business understanding

Next time let us discuss about another perspective to Requirements Understanding called ‘System Object Analysis’.

Read More About  Business Intelligence Challenge