This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 3 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

Friday, 28 February 2014

informatica

Informatica Scenario based interview questions (Convert row into columns)
This is the one of most frequently asked informatica scenario based interview question
We have source data like below in table
A
B
C

Output required is
A
B
C

I have seen rarely a candidate answer this question correctly. Their answer is mostly a crammed (by reading on various website) without understanding core concept of informatica transformations.
I will explain three methods to achieve this then we will find out which one is best method
1.       Router Transformation
2.       Normalizer Transformation        
3.       Java Transformation

Router Transformation : This method take advantage of the fact that router is an active transformation and one row can fall into multiple groups i.e. if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.
Step1)
Suppose we have a source with three input columns

Step2)  Create an expression Transformation with field name FLD_OUTPUT to concatenate three fields separated by # sign
Step3)  Create a router transformation with three output groups with condition
GROUP1 : substr(FLD_OUTPUT,1,instr(FLD_OUTPUT,'#')-1)=FLD1
GROUP2 : substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#')+1,instr(FLD_OUTPUT,'#',2)-1)=FLD2
GROUP3: substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#',3)+1)=FLD3
Purpose of this is that row will fall into three groups ..we are extracting string from concatenated string are comparing to respective field.i.e value A will be in first group and B in second group and so on…
Step4) Create union transformation to combine output from three output group from router..Take FLD1 from first group, FLD2 from second group ,FLD3 from third group

Output from union will be
A
B
C

Core Concept: if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Easy isn’t it …No need to cram. We will now move to other methods…
Normalizer  : Normalizer transformation has a property  “Occurs : The number of instances of a column or group of columns in the source row. “
You can define number of occurrence of source field, Once you define number of occurrence then informatica automatically creates that number of input ports. In this case 3
Connect FLD1, FLD2 and FLD3 from source or expression in step2 of first method and FLD as output connected to target.

Output from normalizer will be
A
B
C

Core Concept: Normalizer transformation has a property “Occurs: The number of instances of a column or group of columns in the source row. “
Let us move to final and best method
Java Transformation: We will take advantage of property, Java transformation is most underutilized transformation of informatica, If used properly it can do magic
Generate Transaction
The transformation generates transaction rows. You can enable this property for active Java transformations.

Create a java transformation with one input and one output field

Now connect concatenated field (A#B#C) from expression in step2 of first method to FLD_INPUT of java transformation
Now go to java code and copy below
String[] str_fld_nm=FLD_INPUT.split("#");
for(int i=0; i<str_fld_nm.length; i++){
                FLD_OUTPUT =str_fld_nm[i];
                generateRow();
}

It is using java functionality of array to break input field and multiple fld and then taking advantage of generateRow() to produce multiple rows.

Now connect FLD_OUTPUT to target and it will be
A
B
C

Core concept: Java functionality to break concatenated string into multiple fields and generateRow() to produce multiple rows
Now we have covered all three features. Let us see which one is best

Feature
Router
Normaliser
Java
Flexiblity
Low (As you have to hardcode number of fields)
Low (As you have to hardcode number of occurrence )
High (No Hard coding as it can handle any number of fields)
Ease
High as developer are comfortable with using router transformation
Medium as developer don’t use it frequently 
Low. As most developer are not from java background. It is difficult for them to understand it

If you need xml of mapping then please send a mail to support@itnirvanas.com or lalits77@gmail.com

I request again …don’t cram answers..Instead understand the concepts..

Wednesday, 26 February 2014

Informatica Scenario based interview questions (Convert columns into rows)

This is the one of most frequently asked informatica scenario based interview question.

We have source data like below in table
A
B
C

Output required is
A
B
C

I have seen rarely a candidate answer this question correctly. Their answer is mostly a crammed (by reading on various website) without understanding core concept of informatica transformations.

I will explain three methods to achieve this then we will find out which one is best method

1.       Router Transformation
2.       Normalizer Transformation        
3.       Java Transformation

    Router Transformation : This method take advantage of the fact that router is an active transformation and one row can fall into multiple groups i.e. if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

    Step1)
    Suppose we have a source with three input columns



    Step2)  Create an expression Transformation with field name FLD_OUTPUT to concatenate three fields separated by # sign


    Step3)  Create a router transformation with three output groups with condition
    GROUP1 : substr(FLD_OUTPUT,1,instr(FLD_OUTPUT,'#')-1)=FLD1
    GROUP2 : substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#')+1,instr(FLD_OUTPUT,'#',2)-1)=FLD2
    GROUP3: substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#',3)+1)=FLD3


    Purpose of this is that row will fall into three groups ..we are extracting string from concatenated string are comparing to respective field.i.e value A will be in first group and B in second group and so on…
    Step4) Create union transformation to combine output from three output group from router..Take FLD1 from first group, FLD2 from second group ,FLD3 from third group



    Output from union will be
    A
    B
    C

    Core Concept: if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

    Easy isn’t it …No need to cram. We will now move to other methods…

    Normalizer  : Normalizer transformation has a property  “Occurs : The number of instances of a column or group of columns in the source row. “

    You can define number of occurrence of source field, Once you define number of occurrence then informatica automatically creates that number of input ports. In this case 3 


    Connect FLD1, FLD2 and FLD3 from source or expression in step2 of first method and FLD as output connected to target.


    Output from normalizer will be
    A
    B
    C

    Core Concept : Normalizer transformation has a property “Occurs: The number of instances of a column or group of columns in the source row. “

    Let us move to final and best method

    Java Transformation : We will take advantage of property, Java transformation is most underutilized transformation of informatica, If used properly it can do magic

    Generate Transaction
    The transformation generates transaction rows. You can enable this property for active Java transformations.

    Create a java transformation with one input and one output field



    Now connect concatenated field (A#B#C) from expression in step2 of first method to FLD_INPUT of java transformation

    Now go to java code and copy below

    String[] str_fld_nm=FLD_INPUT.split("#");
    for(int i=0; i
                    FLD_OUTPUT =str_fld_nm[i];
                    generateRow();
    }

    It is using java functionality of array to break input field and multiple fld and then taking advantage of generateRow() to produce multiple rows.


    Now connect FLD_OUTPUT to target and it will be

    A
    B
    C

    Core concept : Java functionality to break concatenated string into multiple fields and generateRow() to produce multiple rows.

    Now we have covered all three features. Let us see which one is best

    Feature
    Router
    Normaliser
    Java
    Flexiblity
    Low (As you have to hardcode number of fields)
    Low (As you have to hardcode number of occurrence )
    High (No Hard coding as it can handle any number of fields)
    Ease
    High as developer are comfortable with using router transformation
    Medium as developer don’t use it frequently 
    Low. As most developer are not from java background. It is difficult for them to understand it

    If you need xml of mapping then please send a mail to support@itnirvanas.com or lalits77@gmail.com

    I request again …don’t cram answers..Instead understand the concepts..


    Tuesday, 25 February 2014

    Informatica Common Errors :The Target Definition has more than one Transaction Control point connected to it

    Error : The Target Definition has more than one Transaction Control point connected to it

    Scenario :


    Create a mapping with two streams( one with Transaction control and Second without Transaction control)..You will get  above error

    Src1 - SQ1- EXP1- TGT1

    Src2 - SQ2- Exp2-TC-TGT2

    Reason :

    Informatica processes transactional data row wise, hence,while using Transaction control for one of the targets to define the transaction point, Informatica fails to identify the transaction point for the other targets,This results in "The Target Definition has more than one Transaction Control point connected to it".

    Solution :

    You need to define Transaction Control transformation in other pipeline with property TC_CONTINUE_TRANSATION.

    Friday, 14 February 2014

    Issue with informatica client in Dual Monitor

    Problem

    Recently i started with informatica in dual monitor and found few issues (Like Expression editor size and sq editor getting greyed out)

    Solution

    At commend prompt type regedit and  go to below path (Please take backup of registry before making any changes)

    HKEY_CURRENT_USER\Software\Informatica\PowerMart Client Tools\9.1.0\Designer\Options\Global\Editor\SQL

    Change the values of the following to 0

    Expression Editor Position
    Expression Editor Splitter Position
    SQL Editor Position
    SQL Editor Splitter Position

    Please restart your pc after changing the registry values.

    Hope it helps to solve your issue.

    Monday, 18 November 2013

    IDQ Interview Questions Part1


    Q1 What is the difference between the Power Center Integration Service and the Data Integration Service? 

    The Power Center Integration Service is an application service that runs sessions and workflows.
    The Data Integration Service is an application service that performs data integration tasks for the Analyst tool,the Developer tool, and external clients. The Analyst tool and the Developer tool send data integration task requests to the Data Integration Service to preview or run data profiles, SQL data services, and mappings. Commands from the command line or an external client send data integration task requests to the Data Integration Service to run SQL data services or web services.

    Q2.What is the difference between the PowerCenter Repository Service and the Model Repository Service?

    The PowerCenter application services and PowerCenter application clients use the PowerCenter Repository Service. The PowerCenter repository has folder-based security.

    The other application services, such as the Data Integration Service, Analyst Service, Developer tool, and Analyst tool, use the Model Repository Service. The Model Repository Service has project-based security.
    You can migrate some Model repository objects to the PowerCenter repository.

    Q3 What is the difference between a mapplet in PowerCenter and a mapplet in the Developer tool? 

    1. Mapplet in PowerCenter and in the Developer tool is a reusable object that contains a set of transformations. You can reuse the transformation logic in multiple mappings. 
    2. PowerCenter mapplet can contain source definitions or Input transformations as the mapplet input.  It must contain Output transformations as the mapplet output. 
    3. Developer tool mapplet can contain data objects or Input transformations as the mapplet input.  It can contain data objects or Output transformations as the mapplet output. 
     A mapping in the Developer tool also includes the following features:

    1. You can validate a mapplet as a rule. 
    2. You use a rule in a profile. 
    3. A mapplet can contain other mapplets. 

    Q4 What is the difference between a mapplet and a rule? 

    You can validate a mapplet as a rule. A rule is business logic that defines conditions applied to source data when you run a profile. You can validate a mapplet as a rule when the mapplet meets the following requirements:

    1. It contains an Input and Output transformation. 
    2. The mapplet does not contain active transformations. 
    3. It does not specify cardinality between input groups. 

    Q5 What is the difference between a source and target in PowerCenter and a physical data object in the Developer tool? 

    In PowerCenter, you create a source definition to include as a mapping source. You create a target definition to include as a mapping target. In the Developer tool, you create a physical data object that you can use as a mapping source or target.


    Q6 What is the difference between the PowerCenter Repository Service and the Model Repository Service? 

    The PowerCenter application services and PowerCenter application clients use the PowerCenter Repository Service. The PowerCenter repository has folder-based security. The other application services, such as the Data Integration Service, Analyst Service, Developer tool, and Analyst tool, use the Model Repository Service. The Model Repository Service has project-based security.
    You can migrate some Model repository objects to the PowerCenter repository.



    IDQ Functionality


    Use the IDQ to design and run processes to complete the following tasks:

    Profile data : Profiling reveals the content and structure of data. Profiling is a key step in any data project, as it can identify strengths and weaknesses in data and help you define a project plan.

    Create scorecards to review data quality : A scorecard is a graphical representation of the quality measurements in a profile.

    Standardize data values : Standardize data to remove errors and inconsistencies that you find when you run a profile. You can standardize variations in punctuation, formatting, and spelling. For example, you can ensure that the city, state, and ZIP code values are consistent.

    Parse data :  Parsing reads a field composed of multiple values and creates a field for each value according to the type of information it contains. Parsing can also add information to records. For example, you can define a parsing operation to add units of measurement to product data.

    Validate postal addresses : Address validation evaluates and enhances the accuracy and deliverability of postal address data. Address validation corrects errors in addresses and completes partial addresses by comparing address records against address reference data from national postal carriers. Address validation can also add postal information that speeds mail delivery and reduces mail costs.

    Find duplicate records : Duplicate analysis calculates the degrees of similarity between records by comparing data from one or more fields in each record. You select the fields to be analyzed, and you select the comparison strategies to apply to the data. The Developer tool enables two types of duplicate analysis: field matching, which identifies similar or duplicate records, and identity matching, which identifies similar or duplicate identities in record data.

    Create reference data tables : Informatica provides reference data that can enhance several types of data quality process, including standardization and parsing. You can create reference tables using data from profile results.

    Create and run data quality rules : Informatica provides rules that you can run or edit to meet your project objectives. You can create mapplets and validate them as rules in the Developer tool.

    Collaborate with Informatica users :The Model repository stores reference data and rules, and this repository is available to users of the Developer tool and Analyst tool. Users can collaborate on projects, and different users can take ownership of objects at different stages of a project.

    Export mappings to PowerCenter : You can export mappings to PowerCenter to reuse the metadata for physical data integration or to create web services.

    Tuesday, 29 October 2013

    IDQ Parser Transformation

    IDQ Parser Transformation 

    In this article we are going to cover parser based transformation .It is one of most important transformation used in IDQ. Parsing is the core function of any data quality tool and IDQ provides rich parsing functionality to handle complex patterns.

    Parser transformation can be created in two mode


    • Token Parsing Mode 
    • Pattern Based Parsing


    Token Based Parsing : It is used to parse strings that match token sets regular expression or reference table based entries.We will use a simple example to create a token based parser transformation.Suppose we have email id coming in a field in format "Name@company.domain" and we want to parse this and store it in multiple fields 
    NAME COMPANY_NAME DOMAIN

    Suppose we have input data coming as below 

    Rahul@gmail.com
    Sachin@yahoo.com
    Stuart@yahoo.co.uk

    We will create a token based parser transformation having email id as input ,After creating transformation go to properties and strategies tab and click on new 

    Token Based Parsing :It is used to parse strings that match token sets regular expression or reference table based enteries.
    We will use a simple example to create a token based parser transformation.Suppose we have email id coming in a field in format "Name@company.domain" and we want to parse this and store it in multiple fields 
    NAME
    COMPANY_NAME
    DOMAIN
    Suppose we have input data coming as below 

    Rahul@gmail.com
    Sachin@yahoo.com
    Stuart@yahoo.co.uk

    Step1 : We will create a token based transformation having email id as input ,After creating transformation go to properties and strategies tab and click on new 



    Step2 : Click on Token Based

    Step3 : Select Regular expression (As we want to have multiple output port)

    Step4)  Select email parser or you can create your own regular expression to parse different type of transformation


    Step5) Create three output port and click on OK then finish


    Below is output from Parser transformation Name ,company and email id parsed into separate fields.


    Pattern Based Parsing : Pattern based parsers are useful when working with data that needs to be parsed apart or sorted and the data has a moderately high number of patterns that are easily recognized.
    Parser Based Transformation need to have output from Label Transformation which will provide two outputs LabelData and Tokenised data
    Suppose we have a field named as PATTERN_DATA in source which contains name ,empno and date in it and we need to parse into three seperate fields
    Step1 ) We will first create a label transformation with delimiter as , and below properties by creating new strategies 


    in second tab choose execution order and assign label


        Output of Label transformation will be

    Step2 ) Connect both LabeledOutput and Tokenized data to pattern based transformation
    and create three new output port in port tab as shown below

      
    Step3 ) In Pattern Tab define below (As per Label defined in Label) 


    You can preview Parser data broken in three fields NAME EMPNO DOB

    Hope this post make Parser transformation more clear..In case of nay question please send mail to support@ITNirvanas.com or leave your comment here.