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.
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:
- 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
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
- Informatica Administrator Home Page : http://localhost:7009
- Domain name : localhost
- Host name : localhost
- Port number : 7006
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
- Connection String
- Data Access Properties
- Connection String
- Originally : servername@dbname
- Revised : DBPROD@HRDB
- Connection String
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
- Add Data Object – Source as read
- Add Data Object – Destination as write
- 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
- Originally :- Test_TestTriggers
- 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]
Output
Explanation
- We had 4 records before running the job
- Now we have 9 records
- 5 records representing new entries in the source since 2016-02-25
References
Administrator
- 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
- Informatica PowerCenter Express- Getting Started
http://oracleedq.com/informatica-powercenter-express-getting-started/
Webinar
- 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
- A First Look at Informatica’s New PowerCenter Express
http://smartbridge.com/a-first-look-at-informaticas-new-powercenter-express/
ETL Tools
- Top 15 Free Extract, Transform, and Load ETL Software
http://www.predictiveanalyticstoday.com/top-free-extract-transform-load-etl-software/
[…] that post we copied data between two MS SQL Server […]