Forward

In an online article, Robert Sheldon, reviews a couple of ETL tools.

The article is titled “Moving beyond SSIS: 5 third-party SQL Server tools for ETL“. And, it is available here.

He compares a hand fold of ETL Tools; here is his list:

1. Informatica – PowerCenter
2. SAS – Enterprise Data Integration Server
3. Business Objects – Data Integration
4. Pentaho – Data Integration
5. Talend – Open Studio

 

PowerCenter Express

In this post, we will go over Informatica PowerCenter Express.

There are two versions of PowerCenter Express

1. PowerCenter Express – Personal Edition ( Free )
2. PowerCenter Express Professional Edition ( Cost )

 

Product Comparison

Informatica PowerCenter Express – Data Sheet ( Here ) 

Pricing

Here is current pricing

1. Single User License – $8000

Download

Journey to https://marketplace.informatica.com/solutions/pcexpress and click on the download link.

Login

You will be asked to login with an existing account or create a new user.

As I did not have an existing account, entered my hotmail email address.  But, a day later still no email in my hotmail.com.

Thank goodness for my Google email account has that one promptly received a link that I clicked on and continued with my download.

Media

Details

We chose to download:

1. PowerCenter Express Server/Client Windows 64-bit version
2. PowerCenter Express Client Windows 32-bit version

 

The Client install comes bundled with the Server version, but we chose to download and have it for keepsake.

License key

We will need a license key during the install, and one was sent to the email address we registered.

Installation

Server Client Folder

Here is the Server Client Folder Structure

Please initiate install by double-clicking on the install.bat file

License Agreement

Accept the License Agreement by checking the “I accept the terms of the license agreement.” button.

Welcome

In our case, as this was a fresh install, we chose the “Install Informatica PowerCenter Express”

 

License And Installation Directory

Specify the folder where you saved the License Key file.  And, the folder you want to target the install.

Specify License Key File and Install Directory

License Key File is not valid

If you try to use the License Key file that comes with the install binary, you will get the error pasted below.

License.txt

The license key file is not valid

Actual License Key

Specify License Key File and Install Directory ( Corrected )

Pre-Installation Summary

Installing …

Configuring Installation

Domain and Repository Configuring

This is a very important step.

Please specify and confirm the Domain password.  Note the Repository name.

Post Install Summary

1. Informatica Administrator Home Page : http://localhost:7009
2. Domain name : localhost
3. Host name : localhost
4. Port number : 7006

 

Preparatory Work

Let us itemize some of the prep work we need to have in place:

1. Identify Source & Target Object
   • Source :- DB Server, Database name, Tables and Views
   • Target :- DB Server, Database name, Tables and Views
2. Identify Authentication Mechanism and Credentials
   • SQL Server principally support two authentication modes – Windows Authentication and SQL Server Authentication
   • We will utilize SQL Server Authentication
   • Identify SQL User along with password
3. Review and grant permission to Database Objects
   • Source – Grant read permission to source objects
   • Target – Grant write permission to targeted objects
4. Backup Database Objects
   • As we be modifying the targeted DB, please make sure you backup all of the targeted tables

Administrator

Data Sources

Configure Data Sources

Let us configure the data sources

To do so we will launch a web browser and enter the URL – http://localhost:7009/administrator

Connections – Shell

The only connections listed is ProfilingWarehouseConnection

 

Connections – Add New Connection

Via the panel, let us create a new connection

Initiate

To do so, let us do this:

1. Access Domain \ Connections
2. From the Domain Navigator bar, click on Actions \ New \ Connection

 

New Connection

Connection Type

The New Connection window appears …

From the list of supported databases, we chose Sql Server

New Connection – Step 1 of 3

We entered the following info:

1. Name – The network name of the Primary Database Host
2. ID – The ID defaults to same, we can change to a new name for whatever reason that may be desired
3. Description – Left empty
4. Username – Enter username
5. Password – Entered password

 

 

New Connection – Step 2 of 3

Before

 

After

 

1. Metadata Access Properties
   • Connection String
     • Originally :- jdbc:informatica:sqlserver://<hostname>:1433;SelectMethod=cursor;databaseName=<dbname>
     • Revised :- jdbc:informatica:sqlserver://DBPROD:1433;SelectMethod=cursor;databaseName=HRDB
2. Data Access Properties
   • Connection String
     • Originally : servername@dbname
     • Revised : DBPROD@HRDB 

 

There is an important difference between the Metadata Access Properties and Data Access Properties

1. Usage Scenario
   • The metadata access properties is used during design mode; in essence during metadata discovery
   • While the Data Access properties is used during data viewing and processing

 

New Connection – Step 3 of 3

The third page allows for more advanced customized.

We will leave as is for now.

 

Connections – Additional Connection

Please repeat for the destination data source.

 

Information Developer

Usage

Outline

1. Create a new project
2. Add Data Object
   • Add Source table
   • Add Target table
3. Add Mapping
   1. Add Data Object – Source as read
   2. Add Data Object – Destination as write
   3. Link Destination and Source Data Objects
4. Review data in Source
5. Map data

 

Repository

Connect to Repository

Upon initial launch of Information Developer, one needs to connect to a repository.

To do so click on the menu item – File \ Connect to Repository …

 

Choose Service

On the Connect to Repository window, click on the Browse… button

 

The Domain_localhost\ModelRepository service is shown.

Please select it.

Choose to Repository – Select a Repository – Post Repository Selected

Once a repository is selected, it is reflected once we return to the “Select a Repository” window.

Click the Next button selected.

Connect to Repository – Open Project

Choose any project that you will like opened.

Click the “Finish” button.

 

Project

New Project

Let us create a new project.

To do so, click on the menu items New \ Project.

 

Create Project

 

1. Name
   • Give the Project a new name.
   • In our case, we chose syncData

 

Data Object

New Data Object

Using the menu, please click File \ New Data Object…

Select a Wizard

From the list of Wizards, choose “Relational Data Object”

 

Select a Connection

Before

After

Changes

1. Connection
   • We chose the Connection we defined in the Administrator window
   • In our case DBPROD
2. Resource
   • { Click the Browse button to select a resource }
3. Name
   1. Originally :- Test_TestTriggers
   2. Changed :-  Test_TestTriggers_Source

 

 

Select a Resource

Before

After

 

Changes

1. Uncheck “Show Default Schema Only”
   • If you do not do so, the only objects that will be shown are those that match the Schema indicated in the Administrator web screen
   • As we did not specify a Schema, we were not offered any objects to choose from

 

Overview

The overview screen is shown.

This screen shows the table’s structure.

 

Mapping

New Mapping

Using the menu, please click File \ New Mapping…

Mapping – Before

Mapping – After

 

Mapping – Empty Canvas

Here is an empty Canvas that we will drag and drop into.

 

Mapping – Add Destination

Drag the source, DBBETA in our case, into the Canvas.

 

In the Add To Mapping window, choose the Read button.

This is the right option because we are specifying the Source.

 

Mapping – Add Destination

In the Add To Mapping window, choose the Write button.

This is the right option because we are specifying the Destination.

 

 

 

Mapping – Auto Link

Our sample is easy, and we will go the Auto Link path.

Before

 

After

Select both items to autolink the two.

 

Data Viewer

Access Data Viewer

Choose the Source object and the menu changes to allow you to access the Data Viewer.

Data Viewer – Output

Here is our source data….

Run Mapping

Access Run Mapping

Click on an empty spot in the Mapping Canvas.

And, click on the menu items ( Run \ Run Mapping ).

We only have a few records and so completed very quickly.

 

Review Job Run History

Administrator Console

Access the Administrator Console, http://localhost:7009/administrator/#monitoring/Domain_localhost:$$:DIS:$$:jobs, and review job run history.

 

 

Data Review

Backup Destination

Of course, before running anything against a database you took a DB level backup, or in our case a table level backup

   use [HRDB]

   SELECT
		   [tID]
		  ,[vID]
		  ,[triggerID]
		  ,[record_created]
		  ,[record_last_updated]

		--into [DBBackup].[MarketingBackup].[Test_TestTriggers.201603150438PM]
		into [DBBackup].[MarketingBackup].[Test_TestTriggers.201603150440PM]

  FROM [Marketing].[Test_TestTriggers]

Compare Backup against Current

SQL

	SELECT
		   [sourceData] = 'Before'
		  ,[tID]
		  ,[vID]
		  ,[triggerID]
		  ,[record_created]
		  ,[record_last_updated]


	from [DBBackup].[MarketingBackup].[Test_TestTriggers.201603150440PM]

	SELECT
		   [sourceData] = 'After'
		  ,[tID]
		  ,[vID]
		  ,[triggerID]
		  ,[record_created]
		  ,[record_last_updated]

  FROM [Marketing].[Test_TestTriggers]

Output

Explanation

1. We had 4 records before running the job
2. Now we have 9 records
   • 5 records representing new entries in the source since 2016-02-25

References

Administrator

1. MS SQL Server Connection Properties
   https://network.informatica.com/onlinehelp/analyst/961HF2/en/index.htm#page/analyst-tool-guide/GUID-3849D181-CE1C-4EFC-8B40-076762C57817.1.16.html

 

Developer

1. Informatica PowerCenter Express- Getting Started
   http://oracleedq.com/informatica-powercenter-express-getting-started/

 

Webinar

1. Introducing PowerCenter Express: 10 Minutes to Enterprise-Class Data Integration
   Andrew Taylor, Informatica and Lalitha Sundaramurthy, Informatica
   https://www.brighttalk.com/webcast/10477/99285/introducing-powercenter-express-10-minutes-to-enterprise-class-data-integration

 

Evaluation

1. A First Look at Informatica’s New PowerCenter Express
   http://smartbridge.com/a-first-look-at-informaticas-new-powercenter-express/

 

ETL Tools

1. Top 15 Free Extract, Transform, and Load ETL Software
   http://www.predictiveanalyticstoday.com/top-free-extract-transform-load-etl-software/