Flat Files In Datawarehouse

In Datawarehouse the source data can be either DBMS tables or Flat files.

Now what is a Flat file??

Flat files are data stored in the form of columns and rows on our file system to emulate a DBMS table.The data in Flat files will be in ASCII format.

In the Datawarehousing projects where we use ETL tools like Informatica for extraction of source data we can use Flat files because using ETL tools we can easily manipulate the flat files into source data and it will be much more faster to handle flat files compared to DBMS tables .If we are using SQL scripts instead of ETL tool for extraction then its easy to go with DBMS tables.

Below image show the initial step in Informatica while importing a flat file.

How Important is Datatype conversion in Informatica?

Datatype conversion might look simple but this can play a role in the performance of a session.When you do unnecessary conversion of datatype from varchar to number and then back it create more load for the Informatica server and hence can slow down the workflow.So its better to avoid such conversions and do it only if neccessary else avoid doing such conversions

Another intresting point htat i would like to point out is that ,sometimes datatype conversions can also improve the session.For example in situations where you use Lookup Transformations and Filter Transformations its better to use the integer quantities for condition.So in such situation if we have any string then its better to convert them into integer so that it will speed up the session

What Are Concurrent batches ?

Concurrent batches greatly improves the performance of the session.There will be many session in Informatica server which if we run indiidually might take a long time to complete.In such cases we can use concurrent batches to load many sessions.

One single sessions can have different sources and different mappings for different targets.These session can be put under one single concurrent batch.When you come across complex mappings then its better that we create seperate mappings and then create seperate session so that it can be put under one concurrent batch

Below image will show how concurrent batches are run

Commit Interval In Informatica

Commit interval means the interval at which Informatica server will commit or write into the Database.We can adjust the commit interval by clicking the Advanced Options button from session properties.If commit interval is less then it reduces the performance becasue Informatica server will have to commit the data frequently.When the commit interval is more informatica will get more time gap for commiting the data.This makes performance faster.

Also you do have to note that when you increase the performance of the workflow by increasing the commit interval you have the risk of loosing the data while recovery since most of the buffers will be holding the data to be commited.