Informatica Architecture

Informatica Architecture is divided into two components:
1. Server Components --> Server Components in Informatica are:
a. Repository Server
b. Informatica Server
2. Client Components --> Client Components in Informatica are:
a. Workflow Designer
b. Workflow Manager
c. Workflow Monitor
d. Repository Manager
Here is the Architecture Flow Diagram of Informatica:
Now, let`s talk about the flow in detail:
A developer writes a program in the Designer. The program is a GUI-Based program and is called Mapping. Once the mapping is created, the mapping needs to be saved into the repository.
Once the program(mapping) is saved, the next step is to execute the program. To execute the program WorkFlow Manager tool is required. You cannot execute the mapping directly, you need to create a session task for the mapping. Once the session task is created it needs to be saved into the repository.
Also, you cannot execute the session task directly. For execution you need to create a Workflow using the Workflow Manager tool. Once created you need to save the workflow to the repository. Workflow will be executed from the workflow manager.
During the execution of the workflow, you can monitor the activities using the WorkFlow Monitor.
In the next tutorial, we are going to define the Informatica Client Tools.

Informatica 8.1 Architecture Diagram

Informatica 9.1: Unleash the Full Business Potential of Big Data

If your organization is like most, you’re confronted with an explosion of data, Big Data, coming from everywhere: traditional transactional systems, as well as large amounts of interaction data such as social media data, call detail records, large image files, or device and sensor data.
But instead of being overwhelmed by Big Data, you can be empowered by it.

Big Data Unleashed: Turning Big Data into Big Opportunities

Size up the challenges and opportunities of the Big Data phenomenon. This white paper outlines the emergence of Big Data and innovations in Informatica 9.1 developed expressly to enable enterprises to leverage Big Data for competitive advantage.
Informatica 9.1 converts Big Data into trustworthy, actionable, and authoritative data that can be used to gain competitive insights and improve operations.

Informatica Real time Scenarios.

Scenario 1: How can we load first and last record from a flat file source to target?
Solution:
Create two pipelines in a mapping
1st pipeline would capture the first record, and 2nd one for last record.
1st Pipeline:
src-> sq-> exp(take a variable port with numeric data type and pass through a output port 'O_Test')->filter(pass if only
O_Test =1)->tgt
2nd pipeline:
src->sq->agg(No group it will pass only last entry)->tgt
In session for 2nd instance of target enable 'Append if Exists' option
Scenario 2: How to find out nth row in flat file...we used to do top N analysis by using  rownum & some other functionalities  by using rowid when source is table .and my query is how to achieve the same functionalities when my source is flat file?
Solution: In the Mapping designer, go to Mappings-> Parameters and Variables.
Here we have two things - Parameters(constant values passed to the mapping) and variables which are dynamic and can be stored as a metadata for future runs(for example you want to do an incremental load into a table B from table A. So you can define a variable which holds the seqid from source. Before you write the data into target , create an expression and source the seqid from source as input and create a variable Max_seqid as output. Now update this value for each row. when the session finishes informatica saves the last read seqid and you can use this in your source qualifier when you run the mapping next time. Please see Infa doc for setmaxvaribale and setminvariables.
In this case, we have to just make use parameters to find the nth row.
Create a parameter(type) - Last_row_number and select datatype as integer or double.
Now you have to create a parameter file on unix box before you call the workflow.
something like this
echo '[<FOLDERNAME>.WF:<WorkflowName>.ST:<SessionName>]'
count=`wc -l filename`
echo "\$\$MappingVariable="$count
Name the parameter file as <workflowname>.par and copy the complete path of the file name and update the "Parameter filename" field under Properties tab in workflow edit tasks.
You can then use this variable in your mapping wherever you want. Just proceed it with two $$.
Scenario 3: How to create flat file dynamically?
SRC FILE             TRGT 1                                  Trgt 2
---------------- --------------------------------     --------------------------------------
Eid Name Sal      Eid Name Sal                          Eid Name Sal
10 a 100             10 a 100                                 20 b 100
20 b 100             10 c 200                                 20 d 300
10 c 200
20 d 300
Solution :
1. Sort the data coming from the source based on EID.
2. Create a variable in an expression transformation that would track the change in EID e.g. in your case if the data is sorted based on EID then it would look like
EID     Name      SAL     
10     a               100
10     c               200
20     b               100
20     d               300
Whenever there is a change in EID the variable would track it
variable1= IIF(EID = PREV_EID, 0, 1)
3. Add a transaction control transformation in the map with a similar condition
IIF(variable1 = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
this would create a new file whenever there is a change in the EID value.
4. Add a "filename" port in the target and then pass on a value as per your requirement so that the filenames get generated dynamically as per your requirement.
Scenario 4: I HAVE A SOURCE FILE CONTAINING
1|A,1|B,1|C,1|D,2|A,2|B,3|A,3|B
AND IN TARGET I SHOULD GET LIKE
1|A+B+C+D
2|A+B
3|A+B
Solution:
Follow the logic given below in the expression and you will get your output.
Please ensure that all the ports you mentioned below are variable ports and the incoming data should be sorted by key,data

VarPorts	Assigned	Row1	Row2	Row3	Row4
V_CURNT	KEY	1	1	1	2
V_CURNT_DATA	DATA	a	b	c	d
v_OUT (variable port)	IIF(isnull(v_PREV_DATA) or length(v_PREV_DATA)=5,v_CURNT_DATA,iif(V_CURNT = V_PREV, V_PREV_DATA||'~'||V_CURNT_DATA,NULL)	a	a~b	a~b~c	d
o_OUT	v_OUT	a	a~b	a~b~c	d
V_PREV	V_CURNT	null	1	1	1
V_PREV_DATA	v_OUT	null	a	a~b	a~b~c

And After the expression transformation, you have to add an aggregator tx with group by port as 'key'. this will return the last record with the key.
Hi all the above scenario’s have been taken from informatica communities.Incase any one needs any info about the scenarios discussed then they may contact for clarifications.

Read more: http://informaticatutorials-naveen.blogspot.com/2011/05/informatica-real-time-scenarios.html#ixzz1qcSYxG2o
Under Creative Commons License: Attribution Non-Commercial

Informatica Interview Questions on Lookup Transformation

1. What is a lookup transformation?
A lookup transformation is used to look up data in a flat file, relational table, view, and synonym.

2. What are the tasks of a lookup transformation?
The lookup transformation is used to perform the following tasks?

    Get a related value: Retrieve a value from the lookup table based on a value in the source.
    Perform a calculation: Retrieve a value from a lookup table and use it in a calculation.
    Update slowly changing dimension tables: Determine whether rows exist in a target.

3. How do you configure a lookup transformation?
Configure the lookup transformation to perform the following types of lookups:

    Relational or flat file lookup
    Pipeline lookup
    Connected or unconnected lookup
    Cached or uncached lookup


4. What is a 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 lookups source.

5. What is connected and unconnected lookup transformation?

    A connected lookup transformation is connected the transformations in the mapping pipeline. It receives source data, performs a lookup and returns data to the pipeline.
    An unconnected lookup transformation is not connected to the other transformations in the mapping pipeline. A transformation in the pipeline calls the unconnected lookup with a :LKP expression.


6. What are the differences between connected and unconnected lookup transformation?

    Connected lookup transformation receives input values directly from the pipeline. Unconnected lookup transformation receives input values from the result of a :LKP expression in another transformation.
    Connected lookup transformation can be configured as dynamic or static cache. Unconnected lookup transformation can be configured only as static cache.
    Connected lookup transformation can return multiple columns from the same row or insert into the dynamic lookup cache. Unconnected lookup transformation can return one column from each row.
    If there is no match for the lookup condition, connected lookup transformation returns default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged. If there is no match for the lookup condition, the unconnected lookup transformation returns null.
    In a connected lookup transformation, the cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. In an unconnected lookup transformation, the cache includes all lookup/output ports in the lookup condition and the lookup/return port.
    Connected lookup transformation passes multiple output values to another transformation. Unconnected lookup transformation passes one output value to another transformation.
    Connected lookup transformation supports user-defined values. Unconnected lookup transformation does not support user-defined default values.


7. How do you handle multiple matches in lookup transformation? or what is "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.

8. What is "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.

9. What is "Insert Else Update" and "Update Else Insert"?
These options are used when dynamic cache is enabled.

    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 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.


10. What are the options available to configure a lookup cache?
The following options can be used to configure a lookup cache:

    Persistent cache
    Recache from lookup source
    Static cache
    Dynamic cache
    Shared Cache
    Pre-build lookup cache


11. What is a cached lookup transformation and uncached lookup transformation?

    Cached lookup transformation: The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service stores condition values in the index cache and output values in the data cache. The Integration Service queries the cache for each row that enters the transformation.
    Uncached lookup transformation: For each row that enters the lookup transformation, the Integration Service queries the lookup source and returns a value. The integration service does not build a cache.


12. How the integration service builds the caches for connected lookup transformation?
The Integration Service builds the lookup caches for connected lookup transformation in the following ways:

    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.
    Concurrent caches: The Integration Service builds lookup caches concurrently. It does not need to wait for data to reach the Lookup transformation.


13. How the integration service builds the caches for unconnected lookup transformation?
The Integration Service builds caches for unconnected Lookup transformations as sequentially.

14. What is a dynamic cache?
The dynamic cache represents the data in the target. The Integration Service builds the cache when it processes the first lookup request. It queries the cache based on the lookup condition for each row that passes into the transformation. The Integration Service updates the lookup cache as it passes rows to the target. The integration service either inserts the row in the cache or updates the row in the cache or makes no change to the cache.

15. When you use a dynamic cache, do you need to associate each lookup port with the input port?
Yes. You need to associate each lookup/output port with the 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.

16. What are the different values returned by NewLookupRow port?
The different values are

    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.


17. What is a 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.

18. What is a shared cache?
You 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 lookups for subsequent Lookup transformations that share the cache.

19. What is unnamed cache and named 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.


20. How do you improve the performance of lookup transformation?

    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 lookups.
    Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER By clause on the required columns.
    For flat file lookups, provide Sorted files as lookup source.

Separate rows on group basis

In Dept table there are four departments (dept no 40,30,20,10). Separate the record to different target department wise.
Solution:
Step 1: Drag the source to mapping.
Step 2: Connect the router transformation to source and in router make 4 groups and give condition like below.

router transformation

Step 3: Based on the group map it to different target. The final mapping looks like below.

router to target

Data Warehousing Solutions from SAP

With SAP NetWeaver Business Warehouse (SAP NetWeaver BW), you can tightly integrate data warehousing capabilities on a comprehensive and scalable platform, while leveraging best practices to drive business intelligence predicated on a single version of the truth.
An edition of SAP NetWeaver BW powered by SAP HANA is also available to help you supercharge your enterprise data warehouse, simplify your IT landscape, and enable your business users to make decisions faster.

By combining a scalable and layered architecture, a rich set of predefined business content based on established best practices, and key enterprise information management topologies, SAP NetWeaver BW can help you achieve:

• Reliable data acquisition – Tightly integrate data across all applications and business processes in SAP Business Suite, and enrich accessibility of heterogeneous data sources and data quality.
• Business-oriented modeling – Enable quick and sustainable implementations through modeling patterns based on established best practices and rich predefined business content that match your business processes. Deploy all models across different subject domains and enable a single version of the truth across your complete data warehouse.
• Robust analytical capabilities – Support online analytical processing and provide a robust foundation for computing multidimensional business data across dimensions and hierarchies. Benefit from a framework for building planning applications tightly integrated with your enterprise data warehouse.
• Enterprise-ready life-cycle management – Benefit from sophisticated life-cycle management functionality at three different levels – system life-cycle management, metadata life-cycle management, and information life-cycle management.

SAP NetWeaver Business Warehouse can help you reap significant benefits:

• Rapid implementation – SAP NetWeaver BW ships with business content, such as ready-made ETL routines, metadata, InfoCubes, information models, and reports to support reporting and analysis right out of the box.
• Robust scalability and performance – Ensure faster decision making with accelerated data loads and rapid delivery of queries using SAP NetWeaver BW Accelerator.
• Efficient development and reduced TCO – Automated data flow design and graphic data modeling tools speed development, enabling you to reduce the total cost of ownership for managing business data.
• Streamlined operations – Manage and monitor operations with functionality to actively push critical events – and actionable recommendations for recovery and self-healing – to administrators. Ensure compliance with corporate policies, while ensuring high data manageability and consistency.

Informatica Strikes a Big Data Partnership

Informatica this week inscribed another notch in its Big Data belt by inking a partnership agreement with MapR, one of the leading Hadoop distributions in the marketplace. The partnership further opens Hadoop to the sizable market of Informatica developers and provides a visual development environment for creating and running MapReduce jobs.
The partnership is fairly standard by Hadoop terms. Informatica can connect to MapR via PowerExchange and apply PowerCenter functions to the extracted data, such as data quality rules, profiling functions, and transformations. Informatica also provides HParser, a visual development environment for parsing and transforming Hadoop data, such as logs, call detail records, and JSON documents. Informatica has already signed similar agreements with Cloudera and HortonWorks.
Deeper Integration. But Informatica and MapR have gone two steps beyond the norm. Because MapR's unique architecture bundles an alternate file system (Network File System) behind industry standard Hadoop interfaces, Informatica has integrated two additional products with MapR: Ultra Messaging and Fast Clone. Ultra Messaging enables Informatica customers to stream data into MapR, while Fast Clone enables them replicate data in bulk. In addition, MapR will bundle the community edition of Informatica's HParser, the first Hadoop distribution to do so.
The upshot is that Informatica developers can now leverage a good portion of Informatica's data integration platform with MapR's distribution of Hadoop. Informatica is expected to announce the integration of additional Informatica products with MapR later this spring.
The two companies are currently certifying the integration work, which be finalized by end of Q1, 2012.

COMPARATIVE ARCHITECTURES SEMINAR

For years there have been two leading approaches to building the data warehouse environment – DW 2.0 “hub and spoke” architecture and the dimensional model/bus architecture. Both architectures have their advantages and disadvantages. There are places where one approach fits best.
Come hear Bill Inmon discuss the DW 2.0 approach and hear Scott Hirst discuss the dimensional modeling/bus architecture approach.
Then, hear how the two architectures can be blended, yielding a “best of all worlds” approach.
Bill Inmon is well known for articulating the hub and spoke, top down approach to building the data warehouse. Scott Hirst is an experienced practitioner who has employed the dimensional modeling/bus architecture approach in building numerous data warehouses. Equal time will be given to both approaches. Finally, an architecture that blends the best of DW 2.0 with the best of dimensional modeling is discussed.
Here are some topics that will be covered in the seminar:

• What is DW 2.0? Dimensional modeling/bus architecture?
• Dimensional modeling and building reports quickly
• DW 2.0 and the lifecycle of data within the data warehouse
• Dimensional modeling – a case study
• DW 2.0 – building the unstructured data warehouse

HOW THIS SEMINAR IS DIFFERENT

One-On-One Sessions
Our speakers are offering free, one-on-one personal sessions where you can sit and discuss your architecture, database design approach, your issues, your challenges. When our speakers are not presenting, they will be available for private one-on-one sessions of approximately one hour each. No other seminar offers you the possibility of having private sessions with the industry thought leaders. Take advantage of it.
Colorado Skiing
Skiing in the Rocky Mountains is an unforgettable experience. With ski resorts open into May and sometime June, we will surely have great skiing and snowboarding weather in early April and most likely plenty of brilliant sunshine. Denver averages 300+ days of sunshine a year. Some people play golf in Denver, then go skiing in the mountains. Where else can you do that in one day!

TDWI's Best of Business Intelligence Volume 9

Welcome to the ninth annual TDWI’s Best of Business Intelligence: A Year in Review. Each year we select a few of TDWI’s best, most well-received, hard-hitting articles, research, and information, and present them to you in this publication.
Stephen Swoyer kicks off this issue with a review of major business intelligence (BI) developments. In “2011 in Review: From Tablets to Takeovers,” he names social BI and the success of tablets and mobile devices as some of 2011’s trends. Swoyer also calls 2011 “the year in which social media emerged as one of several forces … that will fundamentally transform BI as we know it.”
In “2012 Forecast: The Evolution of Big Data Analytics and the Future of BI,” TDWI Research analysts Philip Russom and David Stodder share their predictions for the coming year. Russom offers insight on how the evolution of big data analytics will impact business intelligence and data warehousing professionals, and Stodder shares five trends he sees shaping the future of BI.
Other selections in this year's issue of the Best of Business Intelligence include:

• Excerpts from the past year’s Best Practices Reports
• Ten Mistakes to Avoid When Setting Your Cloud Business Intelligence Strategy
• Articles from TDWI FlashPoint, BI This Week, and TDWI Experts about BI life cycle management, agile requirements gathering, mobile BI, and working with data analysts
• Two Business Intelligence Journal articles: “How Gamification Will Change Business Intelligence” and “BI Experts’ Perspective: Integrating Structured and Unstructured Data"