Informatica PowerCenter 8x Key Concepts – 2

The PowerCenter Repository is one of best metadata storage among all ETL products. The repository is sufficiently normalized to store metadata at a very detail level; which in turn means the Updates to therepository are very quick and the overall Team-based Development is smooth. The repository data structure is also useful for the users to do analysis and reporting.

Accessibility to the repository through MX views and SDK kit extends the repositories capability from a simple storage of technical data to a database for analysis of the ETL metadata.

PowerCenter Repository is a collection of 355 tables which can be created on any major relational database. The kinds of information that are stored in the repository are,

1. Repository configuration details
2. Mappings
3. Workflows
4. User Security
5. Process Data of session runs

For a quick understanding,

When a user creates a folder, corresponding entries are made into table OPB_SUBJECT; attributes like folder name, owner id, type of the folder like shared or not are all stored.
When we create\import sources and define field names, datatypes etc in source analyzer entries are made into opb_src and OPB_SRC_FLD.
When target and related fields are created/imported from any database entries are made into tables like OPB_TARG and OPB_TARG_FLD.
Table OPB_MAPPING stores mapping attributes like Mapping Name, Folder Id, Valid status and mapping comments.
Table OPB_WIDGET stores attributes like widget type, widget name, comments etc. Widgets are nothing but the Transformations which Informatica internally calls them as Widgets.
Table OPB_SESSION stores configurations related to a session task and table OPB_CNX_ATTR stores information related to connection objects.
Table OPB_WFLOW_RUN stores process details like workflow name, workflow started time, workflow completed time, server node it ran etc.
REP_ALL_SOURCES, REP_ALL_TARGETS and REP_ALL_MAPPINGS are few of the many views created over these tables.

PowerCenter applications access the PowerCenter repository through the Repository Service. The Repository Service protects metadata in the repository by managing repository connections and using object-locking to ensure object consistency.

We can create a repository as global or local. We can go for‘global’ to store common objects that multiple developers can use through shortcuts and go for local repository to perform of development mappings and workflows. From a local repository, we can create shortcuts to objects in shared folders in the global repository. PowerCenter supports versioning. A versioned repository can store multiple versions of an object.

Read More about Informatica PowerCenter

Auditing Best Practices on a button Click – EScript doc

Prevention is better than precaution” An audit (ES Review) was conducted for the previous project and the report stated that most of the Siebel best practices were not implemented which led to poor performance of the application. Sometimes we used to perform the hectic job of copying each and every script manually to a word document for script documentation.

After the project was over, a thought came into my mind, why not to prepare a tool that would scan the script, check for the Siebel best practices implementations and would generate a word document regarding the same, in addition to this if this tool would also copy the script into word document then it would be like a “red cherry on the cake.” So I prepared a tool named escript doc aud

• eScript Auditor is an audit tool developed in VB .Net 2005 which checks whether the script has followed Siebel Best practices and documents the entire code in a word document

Features:

The major feature of the escript doc aud is that it documents and audits every script written at the following level:

• Application

• Business Service

• Business Component

• Applet

Working:

Search for the required object script for which you need to mention the object name in search textbox shown above with selecting appropriate script type and click “Go” button
Respectively a script will be displayed under script area also with all the non followed best practices (highlighted in red shown in above picture)
If required hit “Create Script Area Doc” button to generate a word document for that particular script along with its non followed best practices.
Advantages:

• Can copy thousands of lines of code (LOC) in couple of minutes.

• Reduces the manual effort required.

• In fraction of second audits the entire script.

Script Doc Aud gives the customer friendly GUI to see each script and perform best practice violation check up.
To know more about EScript doc

Hexalyzer- An analyzer tool to view Siebel repository

The ever changing needs and requirement of Business Process makes it imperative for the clients to upgrade from their previous Siebel versions to the latest one.Besides providing better usability and customer satisfaction, upgradation also provides access to new functionality and software applications.

Upgradation facilitates compliance at a lower cost of maintenance and ongoing operations. By providing “out-of-the-box” solution to requirements upgradation helps to increase the efficiency of the applications and businessWhile we were upgrading from Siebel 7.8 to Siebel 8.0, we faced various challenges during the upgrade such as:-

Analyze Repository and generate detail report of the customized Siebel objects

In order to find the information about customized objects every time users are required to fire a query which slows the response time and increases the load on database.

Unfortunately due to time constraint and unavailability of Hexalyzer tool forced us to perform various lengthy but important tasks manually. This was the time when we realized to come up with a tool which would help the upgrade process to take place faster thereby saving lots of time.

The thought process results in Hexalyzer, to generate the report of all the customized objects based on Inactive objects

Count-list of new and modified objects

Hexalyzer has the major advantage of reducing the overload of bulk operations performed on the database.

Hexalyzer automates many such steps and helps the customer to get a broader view of the entire repository. For example by clicking the “New Applet” button the tool will generate an excel sheet with the information of all the new applets created during Upgrade process. Similarly, if one clicks “Modified Views” the information exported to the excel sheet contains the entire modified Views name during the Upgrade process.
This tool is now the part of Post Upgrade task for me and my team. Hexalyzer identifies and reports all the customized objects from the start baseline date for the repository.

Solutions / questions / comments are always welcome.

Thanks for reading this blog.To know more about Hexalyzer

Siebel Business Solution Tool – Adding value for our customers

As a Siebel Consulting firm, our sales team hunts for prospective clients. Once the client is acquired, cost estimation and resource allocation are vital processes before kick starting the project. After lots of hard work and hurdles (difficulties / issues) the development team comes up with the product.

Do the developers ponder on the modules they developed and its viability for client ? Do they go back and see how end users are using it? What value the Client is deriving, out of the application/product’s usage?

One day the above unanswered questions resurfaced on my head. By gathering some techno-functional expertise, I attempted to answer the above questions. I shared these views with my seniors, and eventually got the nod to work on Development of a Business Solution Tool. Some of the salient features of this Business solution tool are as follows:

• Automated Siebel CRM Tool that captures the usage details of the application.
• It keeps track of every user’s login details to the application and the number of views that are traversed by the user.

The above requirement was a challenge and I was looking forward to develop something which would give add-on value for our Clients.

Few of these challenges were:

• Calculating cost effectiveness – capturing the time spent by the end user on a particular view of the application.
• Keeping Track of time spent by each user in the application.
• Statistics w.r.t the heavy and sparing use of the views in the application.

After overcoming the obstacles faced, the development of the Business Tool was done in 2 weeks time. After testing, re-tesing and ensuring the product was bug-free, the Tool was served on the platter of our client. Couple of features of the Tool was cited as below followed by the screenshot:

• Keeps track of Login details of the users. “Login name” and “Application In-Out time.
• Keeps track of Time spent by each user in a particular view. “View In-Out time”.

This Tool will be helpful for the Client to find out the usage of application by their customers. Sometimes navigation becomes difficult to understand for the end users in Siebel. For e.g. The user is unable to some views due to the lack of knowledge of navigation. This tool fulfills that requirement too.

After the development of an Application, the most important benefit our Client derive out of this tool is as follows:

• Calculate Cost Effectiveness of Siebel System with respect to Time and Efficiency.

Solutions / questions / comments are welcome.

Thanks for reading this blog. To know more about Siebel Business Solution Tool

AUTO SAD-Siebel Administration Automation Tool

Auto Sad is a production support tool that automates and provides a faster mechanism to perform Siebel Administrative Tasks on clicks of some buttons on multiple servers.

Siebel Architecture involves the following Servers

• Siebel Server

• Siebel Gateway Name Server

• Siebel Web Server

These servers may or may not be on the same machine.

As a system administrator, one often has to do repetitive tasks such as

• Bouncing of Services(Starting or stopping services)

• Migrating SRF from one location to another

• Compilation of Siebel Repository File(SRF)

• Generate Browser Scripts.

To perform these tasks the Siebel Administrator needs to login into each server remotely and then execute them. If there are only a handful of servers, this task may not be very intimidating, but there are many times when there are many servers installed on separate machines. This is where Auto Sad comes into the picture which automates the whole process. Using Auto Sad, a Siebel Administrator can perform all the basic tasks by entering required values in their profile and then by click on buttons those tasks can be performed.

AUTO SAD tool involves various steps such as:

1. Login through sadmin credentials
2. Create/Edit Environment Profile
3. Generate batch Files for individual tasks
4. Execute Batch Files for the required tasks
5. Launch Siebel Thin Client

From the above screenshot, it is clear that the various administrative tasks can be performed easily by simply click of buttons thereby facilitating the Siebel administrator to work efficiently.Any organization implementing Siebel can make use of this tool to achieve faster and convenient way to perform Siebel Administrative Tasks thereby saving 30% of the time for deployment process.

Read More about AUTO SAD

Computer and Telephone going hand in hand

Computer Telephony Integration (CTI) as the name suggests, is a technology that allows the interactions between a computer and a telephone to be integrated or synchronized.

CTI systems can take several types of inputs including a voice input from Interactive Voice Response systems (IVR) or email, fax and web etc.

Where is CTI used?
Depending on the business type and real time interactivity, we need to decide whether we can go with this approach.
For e.g. In an Online, Help/Solution providing firm, Tele-marketing centers or a Call Center the telephone usage would be high. This means if the business is telephone intensive then we might use this approach so that it would be easy to trace the calls and the caller information.

How to implement CTI in Siebel?
Depending on the business requirements either of the following approaches can be used to implement CTI setup for Siebel Call Center Application
It’s a faster approach as compared to the second one. It uses the vendor specific library which has the configuration parameters required for the setup.
This includes the development of certain customized web services and usage of the ASI s.

Example: Scenario where IVR triggers CTI
In our scenario, various methods of customized Business service for IVR trigger the middleware and the middleware further invokes the CTI module.
These methods need to be exposed as web services to publish them on the web at the host address. This host address is specified by the path which is same as the one where we need to get the CTI toolbar and CTI services enabled.
In all, the Business service methods act as the traversing points through which the CSR can navigate to the specified view, taking the output arguments from the function as the input CED (Caller Entered Digit) data for the CTI system.
CTI Applications:

What CTI can do and why is it preferred?

• Displays the complete call information

• Authenticates the caller

• Agent state information can also be displayed

• Can receive fax messages and route it to respective recipients

Greater efficiency
Dialing out automatically with a single mouse click from a computer saves time and is more accurate.
Staff can handle more calls in a given period of time.

Logging calls, allows monitoring the effectiveness of :

• Operating procedures

• Individual staff members.

• Improved Customer Service

• Receiving customer details on screen at the same moment you receive the incoming call.

• Automatically seeing key details about that customer.

• Automatic call distribution (ACD) automatically answers calls with a voice menu, and uses caller responses to route incoming calls.

Challenges in setting up a CTI Systems:

If IVR is used for triggering purposes, then the Web service response time can be a hurdle in case of screen pops configured in the CTI system.

There could be some cases, where the vanilla views can’t be used for displaying the complete info, so lot more customizations are to be done.

Navigating between various screens could be time consuming.Screen transfer between agents could be difficult to implement.As the implementation (Middleware used) is Vendor dependent, the functionalities and services provided also differ. Hence, there is a difficulty in choosing among the various vendors.

As the CTI services are server dependent, there is always a difficulty in maintaining those IC servers
Network congestion could also hamper the CTI response time.

Read More about Computer Telephony Integration

Sales Cycle & Siebel Sales Application

Introduction to Sales Cycle
Sales Cycle plays a critical role in Customer Relationship Management. It is critical in a way because it is the initial point of contact for any customer. For any CRM application it is important to understand the Sales Cycle. The Sales Cycle starts from Prospecting and ends when a deal is closed and the revenue is realized.
There are various steps involved in an end-to-end Sales Cycle.

1. Prospecting:It involves creating the target database.
2. Lead Generation: A lead is an expressed interest shown by the prospects. It is used by a sales agent to determine whether there is a potential for some sales opportunity.
3. Opportunity Creation: nce a lead is qualified, an opportunity is created.
4. Quote Generation: Sales agent prepare quotes to communicate prices, discounts and special offers that are associated with a product/service which is part of a sale.
5. Order Creation:Once the quote is accepted it is converted into an order.
6. Closure:The deal is closed once the order is created.

The Sales Cycle may differ to some extent depending upon the type of business. But a typical sales cycle would follow the above mentioned steps.
Mapping the Sales Cycle with SIEBEL Sales Application
Following are the important business entities involved in Siebel Sales Application:

1. Accounts
2. Contacts
3. Opportunities
4. Quotes

The lead that is generated through various sources is captured in Accounts screen. It captures the details like – Account Name, Address, Status of the Account (i.e. whether the Account is active/inactive, Qualified, contract pending etc), Account Type (i.e. whether the Account holder is an existing customer, Business, Competitor or Vendor etc.) within the Account Screen.
The Accounts Screen is then linked to Opportunities, Contacts and Quotes.
The Opportunities Screen will have the details such as opportunity name, account name to which it is associated, revenue that could be generated from the opportunity, sales stage (prospecting, qualification, closing, lost), name of sales team, lead quality(excellent, very high, high, fair, poor) etc.
The Contacts Screen will have details such as name of the person, phone number, job title, name of the account, address etc. The Quotes Screen will have details related to the quotes that have been generated for a particular order.
The quotes may undergo some iteration if there is any negotiation between the two parties. A Quote Screen will typically have the Quote name & number, Revision, date of creation, name of the account to which it is associated, name of sales rep, Status (whether the quote is approved, accepted, in-progress, active etc.)

Advantages of Siebel Sales Application
Following are the advantages of Siebel Sales Application:

1. Siebel Sales simplifies and optimizes the task of planning and managing the sales process by providing complete visibility into the sales cycle, helping companies to plan and manage effective selling activities.
2. By bringing business activity information closer to the user, displayed in one place, sales people can find what they need to work on and then quickly navigate to that object within the application.
3. Each organization may use slightly or sometimes greatly different terminology. Often applications will use industry standard terminology that is not necessarily applicable to a particular organization. Customization allows each customer to use their own chosen terminology. This way it cuts down on time that is required to train users on what each field is for and thus allowing for a more seamless transition to a new application.

Read More about Siebel Sales Application

BI Publisher Highlighted

I want a single application to print my reports, invoices, checks, labels etc. and also want the same application to send these to different destinations like printer, fax and e-mail. These reports should contain:

• Graphs
• Tables in highly formatted fashion like table headers should be repeated on each page
• Section totals
• Page totals
• Should have headers and footers
• Should have conditional formatting
• Cross Tab Support
• Easy to create templates

These are my requirements for reporting. All of these can be fulfilled very easily in Analytics dashboards, but, when it comes to printing, dashboard is not a very good choice. For these reports BI Publisher is a perfect solution. Oracle BI Publisher is a Standalone and Platform Independent reporting tool which creates highly formatted printable reports. Oracle BI Publisher uses Template for formatting purpose .Templates can be created in MSWORD, ACROBAT, EXCEL and many other Formatting Tools.
It takes the data from the source, formats it suitably using Template and prints it in different formats such as PDF, EXCEL and HTML.

The Formatted Reports can be published over Printer, Fax or Internet.Oracle BI Publisher overcomes the shortcomings of Siebel Analytics when it comes to Printing of a Report. It provides a unified solution to the above mentioned requirements, hence reduces cost, complexity and manpower.

It can use data from any source which provides JDBC connection like HTTP XML feeds, web services, file data sources. It is pre integrated with OBIEE Suit. You can also use requests developed in OBIEE as the sources of data for BI Publisher.

Data sources for BI Publisher.
BI Publisher can have many sources like OBIEE, Oracle Database, SQL Server, XML and various other data sources are supported by BI Publisher.

• SQL Query: A connection to a database has to be specified for SQL Query. Then a query to retrieve the required data is fired on the database. Bi Publisher also provides a feature called Query Builder.
• BI Answers: It uses Dashboard requests as the data source.
• Web service: It uses a web service which returns data as its data source.

Creating report templates in BI Publisher
Report templates can be created in RTF Format as well as PDF format. To create templates in RTF format you need to have any text editor with RTF support like MS Word, for PDF template, you need to have any tool which gives facility of PDF creation like Adobe Acrobat Professional.On installation, the BI Publisher Template Builder integrates itself with MS Word.

To create a simple RTF template, load the XML containing your sample data by clicking Data->Load XML Data on Template builder toolbar. Once the data is loaded, you can put the data fields in the template. You can provide groupings, conditional formatting and conditional regions. We can also use the wizard to create tables, crosstabs, and charts. Preview of the report is possible with sample data available in PDF, RTF, PPT, HTML and Excel formats. After creating these templates, these templates have to be uploaded to the server to be used by the report. The output format of reports can also be restricted; we can tell the BI Publisher to output report as only PDF and no other format.

Other features of BI Publisher

• Provides a facility of LOV (List of Values) so that data in a report can be filtered according the value selected in LOV.
• Different layouts for same report are supported.
• Bursting, split a report based on a key in the report data and deliver a report based on the second key in the report data.

– Inputs from Anand M

Read More about BI Publisher

Oracle Fusion Middleware

Applications can be integrated using either of the following integration solutions:

• P2P (Point To Point) – It involves creating a direct connection between each pair of application to be integrated.
• Hub-and-Spoke – It involves connecting each application to a central integration server running middleware.

In comparison to P2P integration strategy, Hub-and-Spoke methodology is preferred. Adding an additional application requires only one new data transport to and from the integration server. The numbers of data transports are reduced from n² to n.

Oracle provides a strong Integration Server architecture known as Oracle Fusion Middleware Service Oriented Architecture (SOA) suite. This architecture comprises of Oracle Application server, Enterprise Service Bus (ESB), Business Process Execution Language Process Manager (BPEL PM) and other components for security, BI, administration, monitoring etc.

1. Oracle Application Server provides a J2EE container for other applications and third party applications.
2. ESB is an engine which routes and transforms the data. It can have multiple data entry points.
   Example – The source and target systems are having different schemas. Data transformation can be done using Siebel data mapper and can be transported using the Business Service. The same requirement can also be solved using ESB service. Apart from this, ESB service can be initiated from Siebel CRM after importing the “wsdl” of the Outbound Web Service.
3. BPEL PM automates the business processes across multiple applications. It uses a visual coupled with programmatic constructs.
   Example – Combined information from Siebel CRM, Credit Card Company and a shipping company to generate final form of order. BPEL PM can be used to orchestrate and automate these multiple applications using a single entry point.

SOA suite resides on the Integration Server (IS). Siebel CRM lies on one side of the IS and third party applications reside on other side of the IS. Siebel can thus behave as a Sender or Receiver.

Read More about Oracle Fusion Middleware

Handling Oracle Exceptions

There may be requirements wherein certain oracle exceptions need to be treated as Warnings and certain exceptions need to be treated as Fatal.

Normally, a fatal Oracle error may not be registered as a warning or row error and the session may not fail, conversely a non-fatal error may cause a PowerCenter session to fail.This can be changed with few tweaking in

A. Oracle Stored Procedure

B. The Oracle ErrorActionFile and

C. Server Settings

Let us see this with an example.

An Oracle Stored Procedure under certain conditions returns the exception NO_DATA_FOUND. When this exception occurs, the session calling the Stored Procedure does not fail.

Adding an entry for this error in the ora8err.act file and enabling the OracleErrorActionFile option does not change this behavior (Both ora8err.act and OracleErrorActionFile are discussed in later part of this blog).

When this exception (NO_DATA_FOUND) is raised in PL/SQL it is sent to the Oracle client as an informational message not an error message and the Oracle client sends this message to PowerCenter. Since the Oracle client does not return an error to PowerCenter the session continues as normal and will not fail.

A. Modify the Stored Procedure to return a different exception or a custom exception. A custom exception number (only between -20000 and -20999) can be sent using the raise_application_error PL/SQL command as follows:

raise_application_error (-20991,’<stored procedure name> has raised an error’, true);

Additionally add the following entry to the ora8err.act file:

20991, F

B. Editing the Oracle Error Action file can be done as follows:

1. Go to the server/bin directory under the Informatica Services installation directory (8.x) or the Informatica Server installation directory (7.1.x).

E.g.,

For Infa 7.x

C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act

For Infa 8.x

C:\Informatica\PowerCenter8.1.1\server\bin

2. Open the ora8err.act file.

3. Change the value associated with the error.
“F” is fatal and stops the session.
“R” is a row error and writes the row to the reject file and continues to the next row.

Examples:

To fail a session when the ORA-03114 error is encountered change the 03114 line in the file to the following:

03114, F

To return a row error when the ORA-02292 error is encountered change the 02292 line to the following:

02292, R

Note that the Oracle action file only applies to native Oracle connections in the session. If the target is using the SQL*Loader external loader option, the message status will not be modified by the settings in this file.

C. Once the file is modified, following changes need to be done in the server level.

Infa 8.x

Set the OracleErrorActionFile Integration Service Custom Property to the name of the file (ora8err.act by default) as follows:

1. Connect to the Administration Console.

2. Stop the Integration Service.

3. Select the Integration Service.

4. Under the Properties tab, click Edit in the Custom Properties section.

5. Under Name enter OracleErrorActionFile.

6. Enter ora8err.act for the parameter under Value.

7. Click OK.

8. Start the Integration Service.

PowerCenter 7.1.x

In PowerCenter 7.1.x do the following:

UNIX

For the server running on UNIX:

1. Using a text editor open the PowerCenter server configuration file (pmserver.cfg).

2. Add the following entry to the end of the file:

OracleErrorActionFile=ora8err.act

3. Re-start the PowerCenter server (pmserver).

Windows

For the server running on Windows:

1. Click Start, click Run, type regedit, and click OK.
2. Go to the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\
PowerMart\Parameters\Configuration

Select Edit; New; String Value. Enter the “OracleErrorActionFile” for the string value.
Select Edit; Modify.

Enter the directory and the file name of the Oracle error action file:

\ora8err.act

Example:

The default entry for PowerCenter 7.1.3 would be:
C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act

And for PowerCenter8.1.1 it would be
C:\Informatica\PowerCenter8.1.1\server\bin

Click OK

Read More about Oracle

Business Intelligence Landscape Documentation – The Gold Copy

Software systems, in general, need good comprehensive documentation. This need becomes a “must-have” in case of BI systems, as these systems evolve over a period of time. My recommendation (a best practice, if you will) is to create a “Gold Copy” documentation of the enterprise BI landscape. The “Gold Copy” is distinct from the individual project documentation (this would continue to exist) and is updated with appropriate version controls over a period of time.

The “Gold Copy” would comprise of the following documents related to the Business Intelligence environment:

1) Architecture and Environment

This document has two broad sections. The first section explores the physical architecture and then attempts to explore each of the architectural components in more detail. The second section explores the environmental and infrastructure requirements for development, production and operations.

2) Data Sources

This document explores the various source systems from which data is acquired by the datawarehouse. The document attempts to provide a layman’s view of what data is being extracted and maps it to the source system data structures where they data originate from.

3) Data Models

This document builds on the two previous documents. The document attempts to map the source data onto the datawarehouse and the data mart models and provides a high-level picture of the subject orientation.

4) Reporting and OLAP

The document takes a closer look at information delivery processes and technology to the end user from the datawarehouse. The initial section explores the architectural components by way of middleware server software and hardware as well as the end user desktop tools and utilities used for this purpose. The second section looks at some of the more important reports and describes the purpose of each of them.

5) Process

This document takes a process view of data movement within the enterprise datawarehouse (EDW) starting from extraction, staging, loading the EDW and subsequently the data mart. It explores the various related aspects like mode-of-extraction, extract strategy, control-structures, re-start and recovery. The document also looks at naming conventions followed for all objects within the datawarehouse environment.

The above set of documents cover the BI landscape with its focus on 3 critical themes – Architecture Track, Data Track and Process Track. Each of these tracks have a suggested reading sequence of above mentioned documents

Architecture Track – This theme focuses entirely on components, mechanisms and modes from an architectural angle. The suggested reading sequence for this track is – Architecture and Environment, Data Models, Reporting and OLAP

Data Track – This theme focuses on data – the methods of its sourcing, its movement across the datawarehouse, the methods of its storage and logistics of its delivery to the business users. The suggested reading sequence for this track is -Sources, Data Models, Reporting and OLAP.

Process Track – This theme focuses on datawarehouse from a process perspective and explores the different aspects related to it. The suggested reading sequence for this track is – Architecture and Environment, Process, Reporting and OLAP.

I have found it extremely useful to create such documentation for enterprise wide BI systems to ensure a level of control as functional complexity increases over a period of time.

Thanks for reading. Please do share your thoughts. Read More About  Business Intelligence

Data Integration Challenge – Error Handling

Determining the error and handling the errors encountered in the process of data transformation is one of the key design aspects in building a robust data integration platform. When an error occurs how do we capture the errors and use them for effective analysis. Following are the best practices related to error handling

1. Differentiate the error handling process into the Generic (Null, Datatype, Data format) and the Specific like the rules related to the business process. This differentiation enables to build reusable error handling code
2.  
3. Do not stop validations when the record fails for one of the validations; continue with the other validations on the incoming data. If we have 5 validations to be done on a record, we need to design that the incoming record is taken through all the validations, this ensures that we capture all the errors in a record in one go
4.  
5. Have a table Error_Info; this has the repository of all the error messages. The fields would be ErrorCode, ErrorType and the ErrorMessage. The ErrorType would carry the values ‘warning’ or ‘error’, the ErrorMessage would have a detail description of the error and the ErrorCode a numeric value which is used in place of the description.
6.  
7. In general each validation should have an error message, we could also see the table Error_Info as a repository of all error validations performed in the system. In case of business rules that involve multiple fields, the field ErrorMessage in the table Error_Info can have the details of the business rule applied along with the field name, we can also create an additional field Error_Category to group the error messages
8.  
9. Have a table Error_Details; this stores the errors captured. The fields of this table would be KeyValue, FieldName, FieldValue and ErrorCode. The KeyValue would hold the value of the primary key of the record which has an error, the FieldName would store name of the field which has an error, the FieldValue has the value of the field which has failed or is an error, the ErrorCode details the error through a link to the table Error_Info.
10.  
11. Write each error captured as a separate record in the table Error_Deatils i.e., if a record fails for two conditions like a NULL check on field ‘ CustomerId’ and the data format check on the field ‘Date’ then ensure we write two records one for the NULL failure and one for the data format failure
12.  
13. To retain the whole incoming record have a table structure Source_Datasame as the incoming data. Have a field FLAG in the Source_Data, a value of ‘1’ would say that the record has passed all the validations and ‘0’ would say that it has failed one or more validations
14.  

In summary the whole process would be to read the incoming record, validate the data, for any validation failure assign the error_code and pipe the errors captured to the Error_Details table, once all validations completed assign the FLAG value (1- Valid record, 0-Invalid record) and insert that record into the Source_data table. Having the data structure as suggested above would enable efficient analysis of the errors captured by the business and IT team.

Read More About  Data Integration Challenge

Competencies for Business Intelligence Professionals

The world of BI seems to be largely driven by proficiency in tools that I was stumped during a recent workshop when we were asked to identify BI competencies. The objective of the workshop, conducted by the training wing of my company, was to identify the competencies required for different roles within our practice and also to define 5 levels (Beginner to Expert) for each of the identified competencies.

We were a team of 4 people and started listing down the areas where expertise is required to be a successful BI practice. For the first version we came up with 20 odd competencies ranging from architecture definition to tool expertise to data mining to domain expertise. This was definitely not an elegant proposition considering the fact that for each of the competencies we had to define 5 levels and also create assessment mechanisms for evaluating them. The initial list was far too big for any meaningful competency building and so we decided that we have to fit all this into a maximum of 5 buckets.

After some intense discussions and soul searching, we came up with the final list of BI competencies as given below:

1) BI Platform

2) BI Solutions

3) Data Related

4) Project / Process Management

5) Domain Expertise

BI Platform covers all tool related expertise ranging from working on the tool with guidance to being an industry authority on specific tools (covering ETL, Databases and OLAP)

BI Solutions straddles the spectrum of solutions available out-of-the-box. These solutions can be packages available with system integrators to help jump-start BI implementations at one end (For ex: Hexaware has a strong proprietary solution around HR Analytics) to the other extreme of Packaged analytics provided by major product companies (Examples are: Oracle Peoplesoft EPM, Oracle BI Applications (OBIA), Business Objects Rapid Marts etc.)

Data Related competency has ‘data’ at its epicenter. The levels here range from understanding and writing SQL Queries to Predictive Analytics / Data Mining at the other extreme. We decided to keep this as a separate bucket as this is a very critical one from BI standpoint for nobody else has so much “data” focus than the tribe of BI professionals.

Project Management covers all aspects of managing projects with specific attention to the risks and issues that can crop up during execution of Business Intelligence projects. This area also includes the assimilation and application of software quality process such as CMMI for project execution and Six Sigma for process optimization.

The fifth area was “Domain Expertise”. We decided to keep this as a separate category considering the fact that for BI to be really effective it has to be implemented in the context of that particular industry. The levels here range from being a business analyst with the ability to understand business processes across domains to being a specialist in a particular industry domain.

This list can serve as a litmus paper for all BI Professionals to rate themselves on these competencies and find ways of scaling up across these dimensions.

I found this exercise really interesting and hope the final list is useful for some of you. If you feel that there are other areas that have been missed out, please do share your thoughts.

The team involved in this exercise: Sundar, Pandian, Mohammed Rafi and I. All of us are part of the Business Intelligence and Analytics Practice at Hexaware.

Read More About  Business Intelligence Professionals

Informatica Exceptions – 3

Here are few more Exceptions:

1. There are occasions where sessions fail with the following error in the Workflow Monitor:

“First error code [36401], message [ERROR: Session task instance [session XXXX]: Execution terminated unexpectedly.] “

where XXXX is the session name.

The server log/workflow log shows the following:

“LM_36401 Execution terminated unexpectedly.”

To determine the error do the following:

a. If the session fails before initialization and no session log is created look for errors in Workflow log and pmrepagent log files.

b. If the session log is created and if the log shows errors like

“Caught a fatal signal/exception” or

“Unexpected condition detected at file [xxx] line yy”

then a core dump has been created on the server machine. In this case Informatica Technical Support should be contacted with specific details. This error may also occur when the PowerCenter server log becomes too large and the server is no longer able to write to it. In this case a workflow and session log may not be completed. Deleting or renaming the PowerCenter Server log (pmserver.log) file will resolve the issue.

2. Given below is not an exception but a scenario which most of us would have come across.

Rounding problem occurs with columns in the source defined as Numeric with Precision and Scale or Lookups fail to match on the same columns. Floating point arithmetic is always prone to rounding errors (e.g. the number 1562.99 may be represented internally as 1562.988888889, very close but not exactly the same). This can also affect functions that work with scale such as the Round() function. To resolve this do the following:

a. Select the Enable high precision option for the session.

b. Define all numeric ports as Decimal datatype with the exact precision and scale desired. When high precision processing is enabled the PowerCenter Server support numeric values up to 28 digits. However, the tradeoff is a performance hit (actual performance really depends on how many decimal ports there are).

Read More about Informatica

Ask the Expert

Do you have a question on Siebel Upgrades, Implementation or just about the best Siebel business practices in general? No hassles! Submit your question in the comments section below.

Experts from our Siebel practice will answers your questions twice a week and the answers will be published in the Ask the Expert Section

Read More about Ask the Expert