Working with Datawarehouse in Informatica PowerCenter 10
From Installation to Getting Started:
Seven major steps, listed as below, to follow before Informatica PowerCenter 10 becomes ready to work.
- Informatica Server Installation:
Login into the Oracle Software Delivery Cloud location at https://edelivery.oracle.com/. Search for "PowerCenter".
Select the result - Oracle Business Intelligence Data Warehouse Administration Console and Informatica PowerCenter 11.1.1.7.0.
Select your OS (I selected Windows 64 bit) and Continue. Check the agreement formalities and Continue.
Download the zipped parts V860964 from 1 to 8 for PowerConnect Adapters (10.1.0). These are for Informatica PowerCenter 10 version. (There will be a prompt for Akamai downloader. You can use that.)
After download is completed extracted all the eight part one by one in a folder inside the Download folder.
Assuming that Java is up and running, first we need to install the Informatica Server. Do not double click the installer inside Server folder to install as usual. This will (or may) throw a Java error. Instead, go to Command prompt and CD into the path where the Server installer (install.exe) is located i.e., "..\dac_win_11g_infa_win_64bit_101\1010_Server_Installer_winem-64t\Server". Then use the install command as following:
C:\<folder path>\1010_Server_Installer_winem-64t\Server>install.exe -i GUI LAX_VM "C:\<java path>\bin\java.exe"
During installation, do not check Enable Kerberos network... Provide the path to the licence key provided with the Oracle downloads, and wait for the installation to complete. - Oracle Users Domain and Repository for Informatica:
We will need two Users in Oracle database for Domain and Repository in Informatica PowerCenter. So before configuring Informatica, create two such Users using Oracle Developer. Assuming Oracle Developer is up and running and you have Admin rights, go to the main Connection say "System" or "ORACLEXE", go down to "Other Users", right click and select "Create User...".
First to create the Domain User: User tab give a User Name say "INFO_DOM", add password as same say "info_dom, select Default Tablespace as "USERS", Select Temporary Tablespace as "TEMP".
In Roles tab: Select all 3 checks for CONNECT, DBA, and RESOURCE.
In Quotas tab: Select Users. In the SQL tab you may see the SQL code generated. Click Apply.
Similarly create a Repository User as "INFO_REPO" with same selections as above. - Configuring Informatica Domain Service:
Select "Create a domain". Uncheck “Enable HTTPS for Informatica Administrator” and click Next.
To configure the Domain, select the Database Type as "Oracle"
- Enter the Database user ID as "INFO_DOM" that we created above.
- Enter the Database host name as "localhost"
- Enter the Database port number as 1521, which is default
- Enter the Database service name as "orcl" (or whaever you have as yours)
Test the Connection. If connection is OK, click Next.
Enter the Encryption key information, as say Info@123, and click Next and OK
Enter user name and password for accessing the Informatica Domain as following:
- Domain User Name : Administrator
- Domain Password : Administrator (say), and click Next and wait for the configure settings.
- Do not check "Run Informatica under a different user account"
On setup completion, the success dialog box will show. Click on Informatica Administrator Home Page link provided at http://localhost:6008/ - Configuring Informatica Repository Service:
After login into the Informatica Administrator Home Page link first configure the Repository Service.
Select Domain under Domain Navigator:
- In the same Domain Navigator window, select Action -> New -> PowerCenter Repository Service...
- Provide the Repository Name same as the User we created in Oracle above i.e., "INFO_REPO". Select Licence and Node from the drop downs and click Next.
Provide the Oracle database parameters that we set above:
- Database Type as Oracle, Username as INFO_REPO, Password as info_repo (that we used), Connection String as "orcl", Code Page as default shown
- Select "No content exists under specified connection..."
- Check Enable version control..., and click Finish.
After configuration is complete and Repository Service is showing as available, make a final setting. Change Operating Mode from Exclusive to Normal. Else there will be an error. - Configuring Informatica Integration Service:
After configuring the Repository Service, immediately configure the Integration Service.
In the Domain Navigator window, select Action -> PowerCenter Integration Service...
- Provide a logical name of the Integration Service as say "INFO_SERV". Select Licence and Node from the drop downs and click Next.
- Select the running Repository Service, Provide Username and Password selected and click Finish.
- Also select the default code from the Specify Code Pages dialog box.
Afte configuration is complete, Integration Service is created but it is disabled. Enable it by clicking the Enable service button on top right. - Installing the Informatica Client:
To install the Client use the same Command prompt as shown in step 1 above, as Windows default installation throws Java error. Use the install.exe file present at the location ..\1010_Client_Installer_windows10_em-64t\Client
- Use the following command in Command prompt:
C:\<folder path>\1010_Client_Installer_windows10_em-64t\Client\>install.exe -i GUI LAX_VM "C:\<java path> \bin\java.exe"
- Select all or only these two needed: Informatica Developer and PowerCenter Client
- On installation completion, the Success dialog box will be shown. - Configuring the PowerCenter Repository Manager:
When we first open PowerCenter Repository Manager, we see that the repository is either not loaded or is empty. To connect to the domain and repositories to load the databases, we finally we need to configure the PowerCenter Repository Manager as following:
- Open PowerCenter Repository Manager and click on Repository -> Configure Domains...
- Click the first button with a box sign to add domain
- Enter Domain Name as "INFO_REPO", added in step 4 above.
- Enter the Gateway Host as "localhost" and Gateway Port as "6005" and click OK
- Check the Repository INFO_REPO and click OK
Now the repository INFO_REPO shows under Repositories. Double click on it to connect to the repository. Enter the Username and Password of Informatica Administrator.
- Verify the connection in repository service in Informatica Administrative console. See the table listed in Domain Properties.
By default the repository INFO_REPO will be empty without any folder. First we need to create a work folder that will contain the repository of data that we will be working on.
- In PowerCenter Repository Manager, select the repository INFO_REPO, from toolbar click Folder -> Create..., provide a folder name (My_Folder), and click OK.
- Launch PowerCenter Designer, and check that the new folder is showing under the repository INFO_REPO. Expand the new folder My_Folder and check the default containers - Business Components, Sources, Targets, Cubes, etc., which will hold any respective objects created in a project going forward.
Creating and Managing Mapping, Sessions and Workflows:
To quickly see the functionality of Mapping data from Source to Target and sample Workflows in Informatica PowerCenter, just download the Data Warehouse Sample Project from this Informatica Marketplace link (need login), and extract in a local folder and follow the following steps:
- In PowerCenter Repository Manager create a new folder under INFO_REPO as DWS_Demo (meaning Data Warehouse demo)
- From toolbar select Repository -> Import Objects...
- Browse to the extracted folder, select the XML file, click Open and then click Next
- In the Select objects to import dialog box, select "Add All >>" as shown below. This will import all Source and Target tables and Workflows of the sample project.
- Keep clicking Next until you reach this dialog box, where it asks for selecting the repository folder to copy the imports. Select the DWS_Demo folder that we created above, as shown below:
- When any object with the same name as in Source folder is found in the Target folder, there will be conflict. In order to resolve the conflict, select "Replace" in a next dialog box.
- Import Conflict Resolution Summary dialog box will show up. Click Close.
- Keep clicking Next for few dialog boxes until the Import button is seen. Click Import.
- You may look into messages in the output window for more information. When done click Done.
- In Repository Manager, right click on DWS_Demo and click Refresh. Expand the objects and see the objects - Sources, Targets, Mappings, and Workflows. You may now play around to see the files, Mappings and Workflows.
Assigning Integration Service to Workflows:
After quickly reviewing the imported Sources, Targets, Mappings, and Workflows, lets dive in.
In the PowerCenter Workflow Manager, in general proceed in this order:
- Workflow -> Edit -> Integration Service, select INTE_SERV, the Integration Service and click OK
- Workflow -> Validate, to validate if the Workflow steps are fine.
- Repository -> Save, if any changes are made the Workflow, it can be saved.
- Workflow -> Start Workflow
In the PowerCenter Workflow Monitor, check the progress of the workflow as show in the image below:
Page 2 | Page 3