This post is a follow-up from the other post on the Talend ETL tool. The current version of the PowerCenter Express tool provides support to a limited number of database platforms only (e.g. no MySQL of all kinds yet – Community, Enterprise, Amazon Aurora, etc.). However, it is sufficient enough to handle your simple data manipulations, and transformations to create high quality datasets to feed into your BI visualization dashboards.
You can download the software, and register for a license key from here https://marketplace.informatica.com/solutions/pcexpress.
The installation is simple and quick – do not forget the installation guide here. At the end of the installation, you will need to provide it with the relevant jdbc drivers for the kinds of database platforms you want to connect to in your ETL. Only the Talend Studio would take care of this thing for you automatically so in the meantime you have to do your own beg-and-borrow for the driver! See this post for some of the popular drivers that you could shorten your search.
Below are the directories that you need to place your jdbc drivers for the tool to work.
At the end of the installation, you will find the following new icons set up
- Start and Stop Informatica Services.
- Launch Informatica Administrator
- Launch Informatica Developer
You will want to click on Stop Informatica Services, followed by Start Informatica Services to cleanly restart the Informatica Services.
Then Launch Informatica Administrator to review information on your host, domain, and status of services.
Click on the ‘License‘ to confirm that the tool is free for you to use!
You can start setting up database connections from here to make them ready for your ETL code later.
You will notice that the range of databases will be limited, and MySQL is obviously missing (for now you have to go through their MySQL Connector toolkit and build out your own MySQL adapter). I would rather wait until next year for their upgrade release instead!
Building an ETL in Informatica takes you through a similar sequence of steps as described in the Talend post. These Informatica steps are as follows:
- Build database connections either in the Administrator, or the Developer tool.
- Retrieve metadata of objects of interest into the Developer tool (i.e. build a list of ‘Physical Data Objects’)
- Create a mapping to tie the source object to the target object to complete the data flow (i.e. your main ETL code).
Below is a screenshot of the workspace seen in the Informatica Express Developer for a sample workflow that moves data from a table in SQL Server 2012 to the target in Oracle 12c.
You can follow these comprehensive refresher courses in Informatica from the Help/Help Contents menu option
and have fun in creating lots of datasets for your BI dashboards.