Business Intelligence Challenge – Product Upgrades & Migrations, Moving the Code – 4

Last time we discussed about Impact Assessment , the next logical step after this is to perform the actual upgrade or migration of the code.

Moving the Code: Performing Upgrade or Migration of the Objects

When we talk about product upgrades, always the product vendor provides tools by which the objects in the earlier version can be upgraded to the latest version. Yes we would see some objects failing through while using such tools; these are the ones that would need rework after the upgrade process.

When we talk about product migration like moving from Cognos to Business Objects or Business Objects to Cognos, there is good scope for us to look for some ways to automate the code migration. Earlier discussions have been on how to leverage the metadata for understanding the environment, now we are looking at an option on how to manipulate or transform the metadata so that an object in platform ‘A’ becomes compliant to platform ‘B’.

Steps involved in building an automated product migration process

Perform metadata level object mapping between the two platforms, determine the gaps. This would actually be a ‘by product’ of ‘Step 2’ in Impact Assessment

Build individual components that would

• Read the metadata from the source platform and prepare a repository
• Have the knowledge of the match & gap between the platforms, could be reference tables
• Transform the ‘source’ metadata and write out as understood by the ‘target’ platform by using the reference tables

Benefits of Automated Migration

• Helps avoid creation of objects from scratch
• Ensures availability of time for testing (core task) than code development
• Enables team to have a flexible skillset
• A faster way of delivering things when a ‘one to one’ migration from the source platform is seen as a must

Automated Migration Challenges

Transforming the source metadata to the target platform would be a challenge, especially with data manipulation functions. Having a good understanding of the gaps will help; a reference table mapping the functions between the platforms would be useful. In scenarios where a function cannot be converted to the target platform, a comment can be written into a log file enabling quicker attention.

Have seen good success in writing such automated migration components though not 100%. With almost every products providing good SDK kits for reading and as well writing metadata and as well with the support for XML structures, writing such bridges for object migration are getting easier.

Whether the objects in a product are migrated/upgraded in an automated way or not, the following activity of ‘Validation’ plays a key role in ensuring the final quality, next time let us discuss on some of the means for effective validation ….

Read More about Business Intelligence Challenge

Informatica 8.6 Enhancements for Developers – 1

Informatica has released its latest version 8.6 covering all the hot fixes it released for the prior version 8.5 and including few new features. Since version 8, a Unified Admin Console has been designed for managing Integration and Repository services. These were discussed in earlier Blogs.

What does PowerCenter 8.6 bring new for the developers? Let us discuss PowerCenter 8.6 Client enhancements which will be useful to the developers.

1. Creating Targets from Transformations

We can create targets based on transformations in the workspace or navigator.

To create a target,

1. Right-click the transformation in the workspace and select the Create and Add Target option.

2. Alternatively, we can drag and drop the transformation in the Target Designer.

The target that is created has the same port definitions as the transformation from which it was created. We can edit the target definitions later. In addition, the target type is the same as that of the repository used.

2. Invalid/Invalidated renamed

In PowerCenter 7, the two states of objects were known as Invalid and Invalidated.

The exact meaning of these states is as follows:

Invalid – an object will not run,

Invalidated – an object may be invalid or may not run.

The difference between the two terms was not very clear. Therefore, to avoid any confusion, in PowerCenter 8.6, the two states have been renamed as Invalid and Impacted. While the Invalid state still implies that an object will not run, Impacted means that an object is affected by a change, and therefore, may not run.

Apart from the naming convention the icons are also changed in PowerCenter 8.

3. Propagating Port Descriptions

In the Designer, in addition to the other properties of port propagation, we can edit a port description and propagate the description to other transformations in the mapping.

4. Environment SQL Enhancements

In PowerCenter 8, environment SQL can be used to execute an SQL statement at the beginning of each transaction. The Integration Service executes transaction environment SQL at the beginning of each transaction. Environment SQL can still be used to execute an SQL statement at each connection to the database.

Use SQL commands that depend upon a transaction being opened during the entire read or write process. For example, the following SQL command modifies how the session handles characters:

ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR

5. Flat File Enhancements

PowerCenter 8 includes enhancements for handling flat files. Some of these improve performance.

Flat files can now use Integer or Double data types.

In addition, target partitions can be merged. The flat file target merge options include:

Read More about Informatica 8.6

Informatica PowerCenter 8x Key Concepts -3

dministration Console

The Administration Console is a web application that we use to administer the PowerCenter domain and PowerCenter security. There are two pages in the console, Domain Page & Security Page.
We can do the following In Domain Page:

o Create & manage application services like Integration Service and Repository Service

o Create and manage nodes, licenses and folders

o Restart and shutdown nodes

o View log events

o Other domain management tasks like applying licenses and managing grids and resources

We can do the following in Security Page:

o Create, edit and delete native users and groups

o Configure a connection to an LDAP directory service. Import users and groups from the LDAP directory service

o Create, edit and delete Roles (Roles are collections of privileges)

o Assign roles and privileges to users and groups

o Create, edit, and delete operating system profiles. An operating system profile is a level of security that the Integration Services uses to run workflows

4. PowerCenter Client

Designer, Workflow Manager, Workflow Monitor, Repository Manager & Data Stencil are five client tools that are used to design mappings, Mapplets, create sessions to load data and manage repository.

Mapping is an ETL code pictorially depicting logical data flow from source to target involving transformations of the data. Designer is the tool to create mappings

Designer has five window panes, Source Analyzer, Warehouse Designer, Transformation Developer, Mapping Designer and Mapplet Designer.

Source Analyzer:

Allows us to import Source table metadata from Relational databases, flat files, XML and COBOL files. We can only import the source definition in the source Analyzer and not the source data itself is to be understood. Source Analyzer also allows us to define our own Source data definition.

Warehouse Designer:

Allows us to import target table definitions which could be Relational databases, flat files, XML and COBOL files. We can also create target definitions manually and can group them into folders. There is an option to create the tables physically in the database that we do not have in source analyzer. Warehouse designer doesn’t allow creating two tables with same name even if the columns names under them vary or they are from different databases/schemas.

Transformation Developer:

Transformations like Filters, Lookups, Expressions etc that have scope to be re-used are developed in this pane. Alternatively Transformations developed in Mapping Designer can also be reused by checking the option‘re-use’ and by that it would be displayed under Transformation Developer folders.

Mapping Designer:

This is the place where we actually depict our ETL process; we bring in source definitions, target definitions, transformations like filter, lookup, aggregate and develop a logical ETL program. In this place it is only a logical program because the actual data load can be done only by creating a session and workflow.

Mapplet Designer:

We create a set of transformations to be used and re-used across mappings.

Read More about Informatica PowerCenter 

Few tips related to Informatica 8.x environment

Let us discuss some special scenarios that we might face in Informatica 8.x environments.

A. In Informatica 8.x, multiple integration services can be enabled under one node. In case if there is a need to determine the process associated with an Integration service or Repository service, then it can be done as follows.

If there are multiple Integration Services enabled in a node, there are multiple pmserver processes running on the same machine. In PowerCenter 8.x, it is not possible to differentiate between the processes and correlate it to a particular Integration Service, unlike in 7.x where every pmserver process is associated with a specific pmserver.cfg file. Likewise, if there are multiple Repository Services enabled in a node, there are multiple pmrepagent processes running on the same machine. In PowerCenter 8.x, it is not possible to differentiate between the processes and correlate it to a particular Integration Service.

To do these in 8.x do the following:

1.      Log on to the Administration Console

2.      Click on Logs > Display Settings.

3.      Add Process to the list of columns to be displayed in the Log Viewer.

4.      Refresh the log display.

5.      Use the PID from this column to identify the process as follows:

UNIX:

Run the following command:

ps –ef grep pid

Where pid is the process ID of the service process.

Windows:

1. 1. Run task manager.
   2. Select the Processes tab.

Scroll to the value in the PID column that is displayed in the PowerCenter Administration Console.

B. Sometimes, the PowerCenter Administration Console URL is inaccessible from some machines even when the Informatica services are running. The following error is displayed on the browser:

“The page cannot be displayed”

The reason for this is due to an invalid or missing configuration in the hosts file on the client machine.

To resolve this error, do the following:

1. Edit the hosts file located in the windows/system32/drivers/etc folder on the server from where the Administration Console is being accessed.
2. Add the host IP address and the host name (for the host where the PowerCenter services are installed).

Example

10.1.2.10 ha420f3

1. Launch the Administration Console and access the login page by typing the URL: http://<host>:<port>/adminconsole in the browser address bar.

It should be noted that the host name in the URL matches the host entry in the hosts file.

Read More about Informatica 8.x

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