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:

Informatica – PowerCenter
SAS – Enterprise Data Integration Server
Business Objects – Data Integration
Pentaho – Data Integration
Talend – Open Studio

PowerCenter Express

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

There are two versions of PowerCenter Express

PowerCenter Express – Personal Edition ( Free )
PowerCenter Express Professional Edition ( Cost )

Product Comparison

Informatica PowerCenter Express – Data Sheet ( Here )

Pricing

Here is current pricing

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.

marketplace

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

DownloadCenter

Details

We chose to download:

PowerCenter Express Server/Client Windows 64-bit version
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

ServerClientFolder

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.

LicenseAgreement

Welcome

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

Welcome

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

LicenseAndInstallationDirectory

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

License-txt-installbinary

The license key file is not valid

TheLicenseKeyFileIsNotValid

Actual License Key

LicenseKeyActual

Specify License Key File and Install Directory ( Corrected )

LicenseAndInstallationDirectory-Actual

Pre-Installation Summary

PreInstallationSummary

Installing …

Installing-20160315-0907AM

Configuring Installation

ConfiguringInstallation

Domain and Repository Configuring

This is a very important step.

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

DomainAndRepositoryConfiguring-After

Post Install Summary

Informatica Administrator Home Page : http://localhost:7009
Domain name : localhost
Host name : localhost
Port number : 7006

Post-InstallSummary

Preparatory Work

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

Identify Source & Target Object
- Source :- DB Server, Database name, Tables and Views
- Target :- DB Server, Database name, Tables and Views
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
Review and grant permission to Database Objects
- Source – Grant read permission to source objects
- Target – Grant write permission to targeted objects
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:

Access Domain \ Connections
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:

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

New Connection – Step 2 of 3

Before

After

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
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

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

Create a new project
Add Data Object
- Add Source table
- Add Target table
Add Mapping
1. Add Data Object – Source as read
2. Add Data Object – Destination as write
3. Link Destination and Source Data Objects
Review data in Source
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

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

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

Select a Resource

Before

After

Changes

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]