Informatica – PowerCenter Express – Data Sync – Introduction

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

ProductComparison

Pricing

Here is current pricing

  1. Single User License – $8000

PowerCenterExpressProfessionalEdition

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:

  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

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

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

 

Post-InstallSummary

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

 

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

Connections-Empty-New-Cropped

 

New Connection

Connection Type

The New Connection window appears …

Connnections-NewConnection-Sql Server

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

 

DBPROD-Page1

 

New Connection – Step 2 of 3
Before

DBPROD-Page2-Post-v2

 

After

DBPROD-Page2-PostChanges

 

  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.

DBPROD-Page3-v2

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 …

ConnectToRepository-BeforeRepositorySelected

 

Choose Service

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

ChooseService-0548PM

 

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.

ConnectToRepository-SelectARepository-0551PM

Click the Next button selected.

Connect to Repository – Open Project

OpenProject-20160315

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

NewProject-After-Cropped

 

  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”

SelectAWizard

 

Select a Connection

Before

RelationalDataObject-SelectAConnection-Before

After

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

SelectAResource-Before

After

SelectAResource-TestTrigger

 

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.

Overview

 

Mapping

New Mapping

Using the menu, please click File \ New Mapping…

Mapping – Before

Mapping_Before

Mapping – After

Mapping_After

 

Mapping – Empty Canvas

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

Mapping_EmptyCanvas

 

Mapping – Add Destination

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

DragDrop-AddSource

 

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_AddDestination

 

Mapping – Auto Link

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

Before

AutoLink-Before

 

After

Select both items to autolink the two.

AutoLink-After

 

Data Viewer

Access Data Viewer

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

RunDataViewer

Data Viewer – Output

Here is our source data….

RunDataViewer-Output

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.

 

Review-Administrator-Jobs

 

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

compareData

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/

One thought on “Informatica – PowerCenter Express – Data Sync – Introduction

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s