Cache memories in Informatica

All the people know that there are three different types of cache memories viz..

1.Static Cache,

2.Dynamic Cache,

3.Persistent Cache.

Let me explain how exactly these caches work and when they should be used.

Static Cache

This has session lifetime and once the session is complete,the cache is deleted. It brings entire records from the database into the cache. Its default cache for lookup tables. Informatica takes time to create cache and affects performance. It should be used only when the tables used in the mapping aren’t used anywhere in the workflows.

Dynamic Cache

It should be used when they databases are large. The principle behind it is,it will bring records one by one into the cache from the database. If the record is present already(Identified using key columns),then the particular record is not brought into the cache. It will help when the database has lot of redundant records.

Persistent Cache

The lifetime of this kind of cache is entire workflow. When a particular table is being used in many sessions across the workflow, the table can be made as persistent cache in the lookup table properties. It will be improving the performance considerably.

Disadvantages of Qlikview Part II

The most important point which we should not forget is that we need to spend 70% of work is needed to be done on DATA MODELING. If  this is done then dashboard will follow on like an add on.

The next big thing in BI

No need for ETL tools even if we use more than one type of datasources. For complex(Large Databases in terms of size we need ETL tools)

Since it uses In-Memory analytics,all the data is brought to RAM of server and only unique data is stored leading to compression of data.

To refresh the data that is in RAM,they use REAL TIME LOAD, which they say its not recommended and will be removed in forthcoming versions.

It has inbuilt history drivers for handling SCD(Slowly changing dimensions) but needs to be confirmed.

Integration with SAP is possible using SAP Connector.

No version controlling is available and parameter files can be used for data connections.

Uses AJAX to view published reports using browser.(Its obvious sorry)

Performance Tuning in Informatica

We use Informatica for data integration and the choice is primarily due to its efficiency. But sometimes due to some wrong decisions we aggravate the situation through wrong decisions.

Below are some of the things which I came across my project so that Efficiency can be improved, leading to good efficient mapping.

1. Remove unused ports in the source qualifer.

2. If you use aggregator transformation, enable sorted input option.

Two ways are there,

2.1. Keep a sorter transformation before aggregator, sort the key columns alone.

2.2. You can avoid sorter by using order by clause in the SQL override option of source qualifier.

3. Enable persistent cache of the lookup tables that are to be used in the lookup transformation, if the same tables are          being used in the upcoming mappings.

4. Use Parameter files for relational connections in the source and targets by specifying parameter variables like                    $Source and $Target. Specify the path of the files in UNIX server in the workflow properties. It eliminates the need          setting connections manually and it becomes tedious when you move the mappings and sessions across the various        environments like QA,PROD and DEV.

5. Restore the tracing levels to Normal in the tranformations, so that no data is collected at repository after testing.

6. Enable version controlling so that we control the changes that are being done. In case of Unix servers create unix            groups so that only users of that particular group have access.