Informatica

All about Inforamtica Lookup

2012-06-26T04:43:00.000-07:00

All about Inforamtica Lookup

A Lookup is a Passive, Connected or Unconnected Transformation used to look up data in a relational table, view, synonym or flat file. The integration service queries the lookup table to retrieve a value based on the input source value and the lookup condition.

A connected lookup recieves source data, performs a lookup and returns data to the pipeline;
While an unconnected lookup is not connected to source or target and is called by a transformation in the pipeline by :LKP expression which in turn returns only one column value to the calling transformation.

Lookup can be Cached or Uncached. If we cache the lookup then again we can further go for static or dynamic or persistent cache,named cache or unnamed cache .
By default lookup transformations are cached and static.

Advanced Properties of Lookup Transformation:

Lookup policy on multiple match: "Lookup Policy on Multiple Match option" is used to determine which rows that the lookup transformation returns when it finds multiple rows that match the lookup condition. You can select lookup to return first or last row or any matching row or to report an error.
Connection Information: Query the lookup table from the source or target connection. In case of flat file lookup we can give the file path and name, whether direct or indirect.

Output Old Value On Update: This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.
Re-cache from lookup source When checked, integration service rebuilds lookup cache from lookup source when the lookup instance is called in the session.
Insert Else Update: Insert Else Update option applies to rows entering the lookup transformation with the row type of insert. When this option is enabled the integration service inserts new rows in the cache and updates existing rows when disabled, the Integration Service does not update existing rows.
Update Else Insert:Update Else Insert option applies to rows entering the lookup transformation with the row type of update. When this option is enabled, the Integration Service updates existing rows, and inserts a new row if it is new. When disabled, the Integration Service does not insert new rows.

Dynamic Lookup cache:
A Lookup cache does not change its data once built. But if the underlying table upon which lookup was done changes the data after the lookup cache is created, Is there a way so that the cache always remain up-to-date even if the underlying table changes, at this scenario we use dynamic lookup cache.

Dynamic lookup cache: It updates/ inserts data into cache before updating the target table. It maintains the cache up to date.

When the lookup is configured to Dynamic lookup cache by default a NewLookup Row port will be created. which in turn returns 0,1,2

When NewLookup Row port returns
- 0 = Integration Service does not update or insert the row in the cache.
- 1 = Integration Service inserts the row into the cache.
- 2 = Integration Service updates the row in the cache.
Note: While using a dynamic lookup cache, we must associate each lookup/output port with an input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache. The Designer associates the input/output ports with the lookup/output ports used in the lookup condition.

The properties of dynamic lookup are shown in below image

The lookup/output port output value depends on whether we choose to output old or new values when the Integration Service updates a row:
Output old values on update: The Integration Service outputs the value that existed in the cache before it updated the row.

Output new values on update: The Integration Service outputs the updated value that it writes in the cache. The lookup/output port value matches the input/output port value ( we don't have any property like Output new values on update, if we unchecked Output old values on update, then the integration services works as Output new values on update)

Handling Null Values in dynamic lookup:

Suppose in our source there are some null values and we had checked Ignore Null inputs for Update property. Than the lookup ignores the null value which is not inserted into cache and it inserts into target,

In this case the cache and that target table get unsynchronize, to eradicate this we need to sync the target table and the cache, by inserting null value or to ignore it, for that we have two options.

Insert null values: The Integration Service uses null values from the source and updates the lookup cache and target table using all values from the source.
Ignore Null inputs for Update property : The Integration Service ignores the null values in the source and updates the lookup cache and target table using only the not null values from the source.

More details on Dynamic Lookup:

Dynamic lookup cache compares the values in all lookup ports with the values in their associated input ports by default and determine whether or not to update the row in the lookup cache. When a value in an input port changes from the value in the lookup port, the Integration Service updates/inserts the row in the cache as well as in a table,

But if we need to compare only certain ports not all the ports

This scenario comes when the source data includes a column that indicates whether or not the row contains data we need to update.

We can choose the ports that we want the Integration Service to ignore when it compares ports.

The Designer only enables this property for lookup/output ports when the port is not used in the lookup condition.

We can improve performance by ignoring some ports during comparison.

Select the Ignore in Comparison property for all lookup ports except the port that indicates whether or not to update the row in the cache and target table.

More on informatica lookup cache

Sequential cache: The Integration Service builds lookup caches sequentially. The Integration Service builds the cache in memory when it processes the first row of the data in a cached lookup transformation. The Integration Service builds caches for unconnected Lookup transformations as sequentially.
Concurrent caches: The Integration Service builds lookup caches concurrently. It does not need to wait for data to reach the Lookup transformation.
Pipeline lookup transformation:A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookup source.
Persistent Cache: If the lookup source does not change between session runs, then you can improve the performance by creating a persistent cache for the source. When a session runs for the first time, the integration service creates the cache files and saves them to disk instead of deleting them. The next time when the session runs, the integration service builds the memory from the cache file.
Shared Cache: We can configure multiple Lookup transformations in a mapping to share a single lookup cache. The Integration Service builds the cache when it processes the first Lookup transformation. It uses the same cache to perform lookup for subsequent Lookup transformations that share the cache.
Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the Integration Service shares the cache by default. You can only share static unnamed caches.
Named cache: Use a persistent named cache when you want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

Some steps on improving performance tuning

Create an index on the columns used in the lookup condition
Place conditions with equality operator first
Cache small lookup tables.
Join tables in the database: If the source and the lookup table are in the same database, join the tables in the database rather than using a lookup transformation.
Use persistent cache for static lookup.
Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER By clause on the required columns.
For flat file lookup, provide Sorted files as lookup source.

That's all for today, will be posting more on persistent caches and performance tuning in my coming posts.

2012-06-25T04:40:00.000-07:00

Clustered and Non-Clustered Index in SQL 2005

1. Introduction

We all know that data entered in the tables are stored in the physical drive in the form of database files. Think about a table, say Customer (For any leading bank India), that has around 16 million records. When we try to retrieve records for two or three customers based on their customer id, all 16 million records are taken and comparison is made to get a match on the supplied customer ids. Think about how much time that will take if it is a web application and there are 25 to 30 customers that want to access their data through internet. Does the database server do 16 million x 30 searches? The answer is no because all modern databases use the concept of index.

2. What is an Index

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations.

3. Clustered Index:

Consider a table with certain columns as mentioned in the below image. The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.
When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand the Indexes. You will see the following index created for you when the primary key is created:
With the use of the binary tree (b-tree index), now the search for the student based on the studid decreases the number of comparisons to a large amount. Let us assume that you had entered the following data in the table student:
The index will form the below specified binary tree. Note that for a given parent, there are only one or two Childs. The left side will always have a lesser value and the right side will always have a greater value when compared to parent. The tree can be constructed in the reverse way also. That is, left side higher and right side lower.
Now let us assume that we had written a query like below:

Select * from student where studid = 103;
Select * from student where studid = 107;

Execution without index will return value for the first query after third comparison.
Execution without index will return value for the second query at eights comparison.
Execution of first query with index will return value at first comparison.
Execution of second query with index will return the value at the third comparison. Look below:

Compare 107 vs 103 : Move to right node
Compare 107 vs 106 : Move to right node
Compare 107 vs 107 : Matched, return the record

If numbers of records are less, you cannot see a different one. Now apply this technique with a Yahoo email user accounts stored in a table called say YahooLogin. Let us assume there are 33 million users around the world that have Yahoo email id and that is stored in the YahooLogin. When a user logs in by giving the user name and password, the comparison required is 1 to 25, with the binary tree that is clustered index.
Look at the above picture and guess yourself how fast you will reach into the level 25. Without Clustered index, the comparison required is 1 to 33 millions.

Got the usage of Clustered index? Let us move to Non-Clustered index.

4. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, AccountType column of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.
Follow the steps below to create a Non-clustered index on our table Student based on the column class.

After expanding the Student table, right click on the Indexes. And click on the New Index.
From the displayed dialog, type the index name as shown below and then click on the Add button to select the column(s) that participate in the index. Make sure the Index type is Non-Clustered.
In the select column dialog, place a check mark for the column class. This tells that we need a non-clustered index for the column Student.Class. You can also combine more than one column to create the Index. Once the column is selected, click on the OK button. You will return the dialog shown above with the selected column marked in blue. Our index has only one column. If you selected more than one column, using the MoveUp and MoveDown button, you can change order of the indexed columns. When you are using the combination of columns, always use the highly repeated column first and more unique columns down in the list. For example, let use assume the correct order for creating the Non-clustered index is: Class, DateOfBirth, PlaceOfBirth.
Click on the Index folder on the right side and you will see the non-clustered index based on the column class is created for you.

5. How Does a Non-Clustered Index Work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column on the database.
This can be easily explained with the concept of a book and its index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says all about C#. When you glanced at the book, it has all beautiful color pages and shiny papers. But, that is not only the eligibility for a good book right? One you are impressed, you want to see your favorite topic of Regular Expressions and how it is explained in the book. What will you do? I just peeped at you from behind and recorded what you did as below:

You went to the Index page (it has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on page Number 17.
Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.
Your first target is Page 407. You opened a page in the middle, the page is greater than 500.
Then you moved to a somewhat lower page. But it still reads 310.
Then you moved to a higher page. You are very lucky you exactly got page 407.
That’s all, you started exploring what is written about Regular expression on that page, keeping in mind that you need to find page 816 also.

In the above scenario, the Index page is Non-Clustered index and the page numbers are clustered index arranged in a binary tree. See how you came to the page 407 very quickly. Your mind actually traversed the binary tree way left and right to reach the page 407 quickly.
Here, the class column with distinct values 1,2,3..12 wi ll store the clustered indexcolumns value along with it. Say for example; Let us take only class value of 1. The Index goes like this:
1: 100, 104, 105
So here, you can easily get all the records that have value for class = 1. Map this with the Book index example now

DATA MODELLING CONCEPTS

2012-06-21T22:48:00.003-07:00

Why Should People Learn Data Modeling?

Consider this problem. You have just landed in a strange airport. You need to get into the city.
What do you do?
• Hire a taxi? The driver knows the city … He'll take you.
• But you need a your own car to Rome the city while you are there
• OK. Rent a car? But how do you find your way?
• Get a City Map - or better still - a City/Street Directory.

With a Street Directory the inside cover, shows the indexes and layout of the city, by a grid network pages defined for each street in detail. You can then find where you are, where yo want to go, and how to get there. But there may be many routes you can choose:

1) Depending on traffic rules and traffic conditions
2) Depending on the time requirements (ie. Performance)
3) Depending on technology: taxi, car, bus, train, walking.

So how do business managers know about the data that is required for decision making?
• Ask the end-users of the data. But who are they?
• And what if the data exists in different versions? Which version is correct?

Similarly, a data model is the "Corporate Street Directory". It includes data maps and data details
in a Repository. It is analogous to the city map and details of streets. A data model, therefore:
• Depends on business rules and business conditions
• Depends on the time requirements (ie. Performance)
• Depends on the technology used: mainframe, micro, network etc

Today, a data model is an essential prerequisite for development of Application Data Bases, Data
Warehouses, for Internet/Intranet systems and for business transformation to take advantage of
the Internet and corporate Intranet technologies.

Methodology:
There are two major methodologies used to create a data model: the Entity-Relationship
(ER) approach and the Dimensional Model.

Steps In Building the Data Model:
While ER model lists and defines the constructs required to build a data model, there is
no standard process for doing so. Some methodologies, specify a bottom-up development process were the model is built in stages. Typically, the entities and relationships are modeled first, followed by key attributes, and then the model is finished by adding non-key attributes.
sequence used for this is:
1. Identification of data objects and relationships
2. Drafting the initial ER diagram with entities and relationships
3. Refining the ER diagram
4. Add key attributes to the diagram
5. Adding non-key attributes
6. Diagramming Generalization Hierarchies
7. Validating the model through normalization
8. Adding business and integrity rules to the Model.

In my coming posts, i will explain in detail about the ER model and dimensional model.

2012-05-09T04:58:00.002-07:00

Hi All,

Let us see how can we import an excel sheet into informatica and loading into an excel with respective business requirement.

For importing an excel file into informatica there are certain pre requisites

1) We need to select the list of column we need to import into informatica and name it as a table (because informatica treats excel as relational table), it can be done by clicking formulas tab and naming it.

2) For the columns which are numeric or decimals make it as numeric else informatica consider it as nvarchar, this can be achieved by select the column and going to format cells and in numbering placing certain decimals to it as shown below.

3) Now creating ODBC connection. Go to ODBC Data Source Administrator and creating a connection for pulling excel files.

4)Similarly creating target connection for the target excel file

Once after the above process is done import the excel files into inforamtica

5) Now importing it into informatica source analyzer and target designer

6) Similarly Import the target excel file

7) Once after creating mapping with the given requirement, to run the workflow need to create ODBC connections
8) It should be created with the following details
    Username: PmNullUser
    Pwd: PmNullPasswd
    Connection String: The name used for ODBC driver creation

9) Similarly create ODBC Connection for target

10) Once after creating these connections assign these connections for source and target in the session

11) After giving respective connections run the workflow.

Thanks,
Ameen.

2012-05-08T05:41:00.002-07:00

Hi All,

I just found a different behavior of expression transformation in inforamtica and was eager to post it.
You all were aware of informatica expression transformation, like creating output ports, variable ports and all, lets see the below scenario how it works.

Suppose there are few inputs ports in and out ports dragged from Source Qualifier, we have created a variable port and assigned a input port value to it and then we have created a output port below the variable port and assigned the variable to it. Now when we debug it the variable port contains the input port value and the output port contains the input port value it self. But when we make the variable port is below the output port. For the first record the variable port returns null value then in the output port contains null value. For the Second record the variable port contains the previous first record value, and it goes like wise.
For any assistance Post me

Thanks,
Ameen.

Informatica

All about Inforamtica Lookup

All about Inforamtica Lookup

Advanced Properties of Lookup Transformation:

Dynamic Lookup cache:

Handling Null Values in dynamic lookup:

More details on Dynamic Lookup:

More on informatica lookup cache

Clustered and Non-Clustered Index in SQL 2005

1. Introduction

2. What is an Index

3. Clustered Index:

4. Non Clustered Index

5. How Does a Non-Clustered Index Work?

DATA MODELLING CONCEPTS