-
What is a three tier data warehouse?
butair
- Aug 3rd, 2009
Client:-* GUI/Presentation logic* Query specification* Data Analysis* Report formatting* Data accessApplication/Data Mart Server:-* Summarizing* Filtering* Meta Data* Multidimensional view* Data accessDW Server:-* Data logic* Data services* Meta data* File services
shayona
- Jun 1st, 2009
Three tier data warehouse contains three layer. 1st layer does all the data extraction and 2nd layer does transformation and in 3rd layer there is loading of clear data.
-
How can we use mapping variables in Informatica? Where do we use them?
navaneethvr
- Jun 9th, 2009
In the Designer, you can create mapping variables in a mapping or mapplet. After you create a mapping variable, it appears in the Expression Editor. You can then use it in any expression in the mappin...
Praveen
- Aug 14th, 2005
After creating a variable, we can use it in any expression in a mapping or a mapplet. Als they can be used in source qualifier filter, user defined joins or extract overrides and in expres...
-
How do we extract SAP data Using Informatica? What is ABAP? What are IDOCS?
dtennes
- Jun 9th, 2009
You cannot use Informatica to extract data from SAP via ABAP. Only the SAP/Business Objects Data Integrator product can do this.Informatica uses a data dictionary interface, with clear SQL instr...
rasmi
- Apr 3rd, 2008
Hi,We can extract SAP data by using informatica with the powerConnect option.We need to install and configure the powerConnect tool . Import the Source in to the Source Analyzer. Here PowerConnect is ...
-
Data Cleansing Phase
What are some typical considerations during the data cleansing phase
SQLGal
- Jun 7th, 2009
The ultimate goal of data cleansing is to improve the organization's confidence in their data. First set the bar for what kind of quality you are trying to obtain. I usually shoot for a 99% level ...
smsreddy
- May 20th, 2009
Before start the cleansing process, do some quality assement for the source data for eg: NULL values for some important columns and -ve or zero values.
-
In What scenario ETL coding is preferred than Database level SQL, PL/SQL coding? What is the methodology and process followed for ETL testing in Data warehouse environment? Develop a mapping to extract the employee drawing maximum salary where deptno = department drawing... If LKP on target table is taken, can we update the rows without update strategy transformation? How you capture changes in data if the source system does not have option of storing date/time field... What is the Difference between a ODS and Staging Area What is the effect of the OPTIONS statement ERRORS=1 What are push and pull ETL strategies? How do you tell aggregator stage that input data is already sorted Can anyone please explain why and where do we exactly use the lookup transformations. Thanks in advance Thanks Nayana...
SQLGal
- Jun 7th, 2009
The simple answer is, when it is cost effective.? ETL tools save time but can be expensive.??They are more?efficient than writing code?because the?code is built using drag and drop.??These cod...
Rambabu.Gali
- Nov 26th, 2007
We should go for an ETL tool when we have to extract the data from multiple source systems like flat file, oracle , COBOL etc at one instance. where PL/SQL or SQL can not fit.2. we can update the targ...
-
In what scenario ETL coding is preferred than Database level SQL,PL/SQL coding?
pvsunil123
- Apr 15th, 2009
I assume ETL coding means using ETL tools like Informatica.If the data is coming from completely different source systems as flat files, cobol files, RDBMS, XML, etc., it is better to use some intelli...
sri_test
- Dec 16th, 2008
Usually Warehouse is preferred in banking,insurance,health care domains. In these environments especially in banks output is provided by business person in excel,notepad,word,Access format. Suppose if...
-
What is the methodology and process followed for ETL testing in Data warehouse environment?
sri_test
- Dec 16th, 2008
Like ETL SPEC,they create a document containing the source table (Schema name) and a target table (another schema name) with the logic used in transforming source to target. We have to write database ...
balaji.m
- Nov 26th, 2007
Hi, Yes, We Can Test By Using Debugger before u run the session in Informatica
-
Develop a mapping to extract the employee drawing maximum salary where deptno = department drawing maximum sum(salary) group by dept.sources are cobol files / flat files.
This is the recent question faced in ibm.
sri_test
- Dec 16th, 2008
It is not possible to combine two tables with different sources,so first convert the department table normal oracle targetMapping 1:1: For cobol dept source use normalizer to convert to oracle target2...
Mamta11
- Oct 14th, 2008
This can be done in informatica mappinsg by using the Aggrgator transformation.Select all the ports from source qualifier joining on departmetn into aggregator .Group by departmetn no and select max(salary)
-
What are the various transformation available?
snareshinfo
- Nov 24th, 2008
Aggregator Transformation Expression Transformation Filter Transformation Joiner Transformation Lookup Transformation Normalizer Transformation Rank Transformation ...
naveen
- Feb 16th, 2007
Can u plz send the detailed description of each transformationthanx
-
What is Informatica Metadata and where is it stored?
Answered by kiran on 2005-05-08 09:20:30: Informatica Metadata contains all the information about the source tables, target tables, the transformations, so that it will be useful and easy to perform transformations during the ETL process. The Informatica Metadata is stored in Informatica.
VIJAYdw9
- Nov 14th, 2008
Metadata is simply documentation for a digital geospatial dataset. It is a text document that describes the who, what, when, where, why and how questions about the data, so that a potential data user can decide whether or not the data is appropriate for his/her use."
Thananjayan
- Dec 6th, 2007
All informations about the mappings,workflows such as Source,target,transformations are converted into metadata format and it is stored in the Repository database.The repository database is a open database,which can be accessible by the Repositoy server and Informatica server.Cheers,Thana
-
Session Execution
What is the first one executed in a session? Is it the Truncate Target Table option or the Post SQL? Explain
Mamta11
- Oct 14th, 2008
Truncate table will be executed first.Trunc. table gets esecuted before the table starts loading and post SQL executes after the session is completed (table loaded)
nova84
- Jun 23rd, 2008
Truncate Target table:Integration Service truncate target tables before running a session.Depending on the target database and primary key-foreign key relationships in the session target, the Integra...
-
ETL Transformations
When can we use stored procedure transformations.
rohit.khatod
- Sep 14th, 2008
Whenever you need value from SP, that time you have to use store pro. transformation. Or whenever you would like to preform some activity on the DB after loading the data or before loading data through ETL process. It can be done through Shell scripts as well.
-
What are the various test procedures used to check whether the data is loaded in the backend, performance of the mapping, and quality of the data loaded in INFORMATICA.
njvijay
- May 12th, 2008
Need to have reconciliation strategy to very input and output data with reject counts
Anil
- Nov 9th, 2006
Hi, u can check out the session logfiles for the total number of records added number of records udated and number of rejected records and errors related to that so this is the answer the interviewer is expecting from usk
-
What are parameter files ? Where do we use them?
Roger_009
- May 12th, 2008
Parameter files are used for static variable exectuion of a task. this file can b modifies/updated for later change in the parameter. say for ex, xyz="RAJAT" is defined in the parameter file...
sithusithu
- Jan 10th, 2006
You can pass a parameter value to a task using a file, Mostly it will be used in dynamic value assignment Sithu
-
What are snapshots? What are materialized views & where do we use them? What is a materialized view log?
jryan999
- May 1st, 2008
A Materialized View is just that. A view which has been materialized to become a "table". The big difference between a View and a Materialized View, is a View executes a query on-t...
naresh sharma
- Nov 2nd, 2006
Materliazed view log is for audit trail purpose on table. This concept was introcuded in Oracle 9. Thsi will help to trace the record based on it's dml operation.
-
When do we Analyze the tables? How do we do it?
jryan999
- May 1st, 2008
In a warehouse I would recommend analyzing the data either after each load has completed, or periodically (week-end or month-end) depending upon the data volumes and therefore the effort required. You...
-
Can anyone please explain why and where do we exactly use the lookup tranformations.thanks in advacnethanksnayana
jryan999
- May 1st, 2008
You need Lookup Transformations because Dimension Tables are linked to Fact tables by Surrogate Keys. The Surrogate Key is automatically generated within the warehouse, and the ONLY way to f...
Naresh
- Oct 5th, 2006
Look Up Transformation is generally used when a fixed data is not present in the mappings we use but is required in the ware house or look up is more importantly used to compare the values...ex...
-
What is ODS (operation data source)
Kalpana gopinath
- Apr 3rd, 2008
ODS is nothing but operational data source. It contains most recent data .it contains around 30-60 days data It is placed in between source system and staging area.. reports can also be taken in ODS .
saurabh verma
- Mar 8th, 2007
Operational Data Store (ODS): An ODS is an integrated database of operational data. Its sources include legacy systems and it contains current or near term data. An ODS may contain 30 to 60 days of information, while a data warehouse typically contains years of data. it is before staging
-
What is Global and Local shortcut. explain with advantages
sriluck
- Mar 3rd, 2008
Global shortcuts are across multiple repositories,Local shortcuts are across multiple folders in the same repository.
-
What is the effect of the OPTIONS statement ERRORS=1
komalahanumantharao
- Dec 26th, 2007
Stop on errors=1 (if you set this option to 1 the session will be stopped after occurance of 1 error row. if it is 0 the session will not be stopped even u got n number of errors.
ETL Interview Questions
Ans