INFORMATICA : Truncate Logs

There was an unusual behavior when i proceeded with repository manager to truncate the existing session logs. Option "Truncate Log" under "Edit" menu was disabled. Selecting/expanding the required folder also did not help.

Since my informatica server was on AIX with client on windows, i did the following to truncate the logs:

$ pmrep
pmrep>connect -r Powercenter -d Domain_server1 -n Administrator -x Administrator

pmrep>truncatelog -t all
truncatelog completed successfully.

Informatica 8.6 : issue with pmcmd

On AIX 5.3 pmcmd command fails with following error:

$ ./pmcmd

Could not load program ./pmcmd:
Could not load module /u01/Informatica/PowerCenter8.6/server/bin/libpmser.a.
Dependent module /lib/libz.a could not be loaded.
The module has an invalid magic number.
Could not load module pmcmd.
Dependent module /u01/Informatica/PowerCenter8.6/server/bin/libpmser.a could not be loaded.
Could not load module .

This issue has been fixed on one of the latest hotfixes.

To workaround the issue, rename /lib/libz.a so that libz.a which is shipped with Informatica is picked up.

Informatica : Lookups Versus Outer Joins

A plain lookup over a dimension (fetching ROW_WID) can be replaced by an outer join over the dimension in the parent sql itself.

I have created a prototype to demonstrate this.

SILOS.TEST_BULK_SIL mapping is created as a copy of SILOS.SIL_PayrollFact (loads W_PAYROLL_F from W_PAYROLL_FS).

Original mapping had a mapplet mplt_SIL_PayrollFact. This mapplet has 12 lookups over various dimensions. It takes input (datasource_num_id, integration_id etc) from the parent sql, looks up the ROW_WID and loads into the fact table.

I removed this mapplet completely and incorporated all the 12 dimensions in the parent sql itself, Outer Joining them to W_PAYROLL_FS. All the expressions which were built in the mapplet were taken care in the main mapping itself (some of them may require more finishing).

Following are the results:

Mapping	Records Loaded (million)	Time Taken (hr.)	RPS (Reader)
SIL_PayrollFact (uses lookup)	183.3	16.3	3132
TEST_BULK_SIL (uses Outer Join)	183.3	6.02	8429

Results show that Outer join based mapping ran approx 2.7 times faster than the one based on lookups.

Again, lookups which involve some complex calculations may not be replaced by outer join.

DAC/Informatica src files connection

Informatica Server Variable > $PMSourceFileDir must be set exactly the
same as the DAC System Properties > InformaticaParamatereFileLocation as
follows: C:\oracleBI\DAC\Informatica\parameters (NB***no spaces in folder
name****)

Copy all files from OracleBI\dwrep\bin\Informatica\LkpFiles to the location specified in $PMSourceFilesDir

Copy all files from OracleBI\dwrep\bin\Informatica\SrcFiles to the location specified in $PMSourceFilesDir

Both Informatica and DAC must be able to access the folder specified above.

Informatica shared memory

Informatica Server setup has a Load Manager where you can define the Shared Memory. This the total memory which server allocates to a particular session (defined by session parameter DTM Memory Buffer) default value 12000000.