ETL_INATICA_POWERCENTER_面试问题.docx

1. Where do we use connected and unconnected lookups?If return port is only one then go for unconnected. More than one return port is not possible with Unconnected. If more than one returns port then go for Connected. 2. 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.The best procedure to take a help of debugger where we monitor each and every process of mappings and how data is loading based on conditions breaks 3. What is the difference between ETL tool and OLAP toolsETL tool is meant for extraction data from the legacy systems and load into specified database with some process of cleansing data. Eg: Informatica, data stage .etcOLAP is meant for Reporting purpose in OLAP data available in Multidimensional model. so that u can write simple query to extract data fro the data base.Eg: Businee objects, Cognos.etcETL tools are used to extract the data from different sources & OLAP tools are used to analyze the data ETL tools are used to extract, transformation and loading the data into data warehouse / data martOLAP tools are used to create cubes/reports for business analysis from data warehouse / data mart 4.What is ODS (operation data source)?ODS - Operational Data Store.ODS Comes between staging area & Data Warehouse. The data is ODS will be at the low level of granularity.Once data was populated in ODS aggregated data will be loaded into EDW through ODS.ODS is the Operational Data Source which is also called transactional data ODSis the source of a warehouse. Data from ODs is staged, transformed and then moved to data warehouse.An updateable set of integrated operational data used for enterprise- wide tactical decision-making. Contains live data, not snapshots, and has minimal history retained can we lookup a table from source qualifier transformation. ie. unconnected lookupYou cannot lookup from a source qualifier directly. However, you can override the SQL in the source qualifier to join with the lookup table to perform the lookup.5.What are the different Lookup methods used in Informatica?In the lookup transformation mainly 2 types1) connected 2)unconnected lookupConnected lookup: 1) It receive the value directly from pipeline2) It will use both dynamic and static3) It return multiple value4) It support user defined valueUnconnected lookup: 1)it receives the value : lkp expression2)it will be use only dynamic 3)it returnonly single value4)it does not support user defined values 6. What is a mapping, session, worklet, workflow, mapplet?Session: A session is a set of instructions that tells the Informatica Server how and when to move data from sources to targets.Mapplet : Mapplet is the set of transformation which we can make for reusability.It is a whole logic.Workflow: it is the pipeline, which pass or flow the data from source to target.7.What is the difference between Power Center & Power Mart? Power Mart is designed for: Low range of warehousesonly for local repositoriesmainly desktop environment. we can connect to single and multiple Repositories, generally used in big Enterprises. Power mart is designed for: High-end warehousesGlobal as well as local repositories ERP support. Power Mart: we can connect to only a single Repository.8. What are the various tools? - Name a fewThe various ETL tools are as follows.InformaticaData stageBusiness Objects Data IntegratorOLAP tools are as follows.CognosBusiness Objects9.What are snapshots? What are materialized views?Materialized view:Answer 1.Materialized view is a view in which data is also stored in some temp table.i.e if we will go with the View concept in DB in that we only store query and once we call View it extract data from DB. But In materialized View data is stored in some temp tables. Answer 2. Materialized view means it stores pre calculated data, it is a physical representation and its occupies the spaceSnapshot:Answer 1. A snapshot is a table that contains the results of a query of one or more tables or views, often located on a remote database. Answer 2.Snapshot is a specific interval of data,10.What is partitioning? What are the types of partitioning?Partitioning is a part of physical data warehouse design that is carried out to improve performance and simplify stored-data management. Partitioning is done to break up a large table into smaller, independently manageable components because it:1. Reduces work involved with addition of new data.2. Reduces work involved with purging of old data.Two types of partitioning are:1. Horizontal partitioning. 2. Vertical partitioning (reduces efficiency in the context of a data warehouse).11.What are the modules in Power Mart?1. Power Mart Designer2. Server3. Server Manager4. Repository5. Repository Manager12.What is a staging area? Do we need it? What is the purpose of a staging area?Staging area is place where you hold temporary tables on data warehouse server. Staging tables are connected to work area or fact tables. We basically need staging area to hold the data, and perform data cleansing and merging, before loading the data into warehouse.In the absence of a staging area, the data load will have to go from the OLTP system to the OLAP system directly, which in fact will severely hamper the performance of the OLTP system. This is the primary reason for the existence of a staging area. In addition, it also offers a platform for carrying out data cleansing.According to the complexity of the business rule, we may require staging area, the basic need of staging area is to clean the OLTP source data and gather in a place. 13.How to determine what records to extract?Data modeler will provide the ETL developer, the tables that are to be extracted from various sources.When addressing a table some dimension key must reflect the need for a record to get extracted. Mostly it will be from time dimension (e.g. date = 1st of current month) or a transaction flag (e.g. Order Invoiced Stat). Foolproof would be adding an archive flag to record, which gets reset when record changes.Draw the inferenceif slowly changing dimension and based on theType 1/2 or3tables defined.14.What are the various transformation available?Transformation plays an important role in Data warehouse. Transformation is used when data is moved from source to destination. Depending upon criteria transformations are done. Some of the transformations are The Various Type Of Transformation In Informatica Source Qualifier Aggregate Sequence Generator Sorter Router Filter Lookup Update Strategy Joiner Normalizer Expression Rank Stored Procedure15.What is a three-tier data warehouse?Three-tier data warehouse contains three tiers such as bottom tier, middle tier and top tier.Bottom tier deals with retrieving related data or information from various information repositories by using SQL.Middle tier contains two types of servers.1.ROLAP server2.MOLAP serverTop tier deals with presentation or visualization of the results.The 3 tiers are:1. Data tier - bottom tier - consists of the database2. Application tier - middle tier - consists of the analytical server 3. Presentation tier - tier that interacts with the end-user16.How can we use mapping variables in Informatica? Where do we use them?After creating a variable, we can use it in any expression in a mapping or a mapplet. Also they can be used in source qualifier filter, user defined joins or extract overrides and in expression editor of reusable transformations.Their values can change automatically between sessions.17.What are the various methods of getting incremental records or delta records from the source systems?Getting incremental records from source systems to target can be doneby using incremental aggregation transformationOne foolproof method is to maintain a field called Last Extraction Date and then impose a condition in the code saying current_extraction_date last_extraction_date.Using mapping parameters and variable or type1we can easily define from where parameter willstart and how variable will changeas deltas will get fromOLTP systems18.Can we use procedural logic inside Infromatica? If yes how, if now how can we use external procedural logic in Infromatica?We can use External Procedure Transformation to use external procedures. Both COM and Informatica Procedures are supported using External procedure TransformationCan we override a native sql query within Informatica? Where do we do it? How do we do it?we can override a sql query in the sql override property of a source qualifier19.What is latest version of Power Center / Power Mart?The Latest Version is 7.220.How do we call shell scripts from informatica?You can use a Command task to call the shell scripts, in the following ways: 1. Standalone Command task. You can use a Command task anywhere in the workflow or worklet to run shell commands. 2. Pre- and post-session shell command. You can call a Command task as the pre- or post-session shell command for a Session task. For more information about specifying pre-session and post-session shell commandsThere is a task named command task, using that you can write or call Shell script, DOS commands or BAT files21.What are active transformation / Passive transformations?Transformations can be active or passive. An active transformation can change the number of rows that pass through it, such as a Filter transformation that removes rows that do not meet the filter condition. A passive transformation does not change the number of rows that pass through it, such as an Expression transformation that performs a calculation on data and passes all rows through the transformationActive transformationsAdvanced External ProcedureAggregatorApplication Source QualifierFilterJoinerNormalizerRankRouterUpdate StrategyPassive transformationExpressionExternal ProcedureMaplet- InputLookupSequence generatorXML Source QualifierMaplet - Output 22.When do we analyze the tables? How do we do it?When the data in the data warehouse changes frequently we need to analyze the tables. Analyze tables will compute/update the table statistics that will help to boost the performance of your SQL.23.Compare ETL & Manual development?There are pros and cons of both tools based ETL and hand-coded ETL. Tool based ETL provides maintainability, ease of development and graphical view of the flow. It also reduces the learning curve on the team. Hand coded ETL is good when there is minimal transformational logic involved. It is also good when the sources and targets are in the same environment. However, depending on the skill level of the team, this can extend the overall development time.Can anyone please explain why and where do we exactly use the lookup transformations?You can use the Lookup transformation to perform many tasks, including: Get a related value. For example, your source includes employee ID, but you want to include the employee name in your target table to make your summary data easier to read. Perform a calculation. Many normalized tables include values used in a calculation, such as gross sales per invoice or sales tax, but not the calculated value (such as net sales). Update slowly changing dimension tables. You can use a Lookup transformation to determine whether rows already exist in the target.Lookup Transformation can be used mainly for slowly changing dimensions and for getting related valuesLook Up Transformation is generally used when a fixed data is not present in the mappings we use but is required in the warehouse or look up is more importantly used to compare the values.Ex1) in the transactional data we have only nameand custid . but the complete name (with first and last is required by the biz user.) andthere is aseparate table (either in source or target data base) that has the first n last names in it.Ex2) u need to compare the prices of the existing goods with its previous prices (referred as type3 ) a look up table containing theOLAP data could be handyIn real time scenario where update strategy transformation is used? if we DML operations in session properties then what is the use of update strategy transformation ?We can use Update strategy transformation in two ways .1.Mapping level.2.session level.Importance of Update strategy transformation in both cases as follows.In real time if we want to update the existing record with the same source datayou can go for session level update logic.If you want to apply different set of rules for updating or inserting a record, even that record is existed in thewarehouse table .you can go for mapping level Update strategy transformation. It means that if you are using Router transformation for performing different activities.EX: If the employee X1234 is getting Bonus then updating the Allowance with 10% less. If not, inserting the record with new Bonus in the Warehouse table.Lets suppose we have some 10,000 odd records in source system and when load them into target how do we ensure that all 10,000 records that are loaded to target doesnt contain any garbage values.24.How do we test it? We cant check every record, as number of records is huge?Select count (*) From both source table and Target table and compare the result.25.What is Entity relation? How is works with Data warehousing ETL modeling?Entity is nothing but an Object, it has characteristics. We call entity in terms of Logical view. The entity is called as a table in terms of Physical view.The Entity relationship is nothing but maintaining a primary key, foreign key relation between the tables for keeping the data and satisfying the Normal form.There are 4 types of Entity Relationships.1.One-One,2.One-Many,3.Many-One,4.Many-Many.In the Data warehouse modeling Entity Relationship is nothing but, a Relationship between dimension and facts tables (ie: Primary, foreign key relations between these tables).The fact table getting data from dimensions tables because it containing primary keys of dimension tables as a foreign keys for getting summarized data for each record.26.Where do we use connected and un connected lookupsIf return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected. 27.Explain the process of extracting data from source systems, storing in ODS and how data modeling is done.There are various ways of Extracting Data from Source Systems. For example, you can use a DATA step; an Import Process .It depends with your input data styles. What kind of File/database it is residing in. Storing your data in an ODS can be done thru an ODS stmt/export stmt/FILE stmt, again which depends on the file & data format, you want your output to be in.IDP is the portal for display of reports, stored process, information maps and a whole bunch of thing ideally required for a dashboard reporting.IMS, is the GUI to help u convert your technical data and map it to business data (change names, add filters, add new columns etc)28.What is the difference between ETL tool and OLAP toolsETL tool is meant for extraction data from the legacy systems and load into specified database with some process of cleansing data.ex: Informatica, data stage .etcOLAP is meant for Reporting purpose. in OLAP data available in Multidimensional model. so that u can write simple query to extract data fro the data base.ex: Businee objects,Cognos.etcETL tools are used to extract the data from different sources & OLAP tools are used to analyze the data . ETL tools are used to extract, transformation and loading the data into data warehouse / data martOLAP tools are used to create cubes/reports for business analysis from data warehouse / data mart29.What are the various tools? - Name a few1) ETL Tools IBM Web sphere Information Integration(Ascential Data stage) Ab Initio Informatica2) OLAP Tools Business Objects Cognos Hyperion Microsoft Analysis Services Micro strategy3) Reporting Tools Business Objects (Crystal Reports) Cognos Actuate30.What is the difference between Power Center & Power Mart?Power Mart is designed for:Low range of warehousesonly for local repositoriesmainly desktop environment.Power mart is designed for:High-end warehousesGlobal as well as local repositoriesERP supportPower Center : we can connect to single and multiple Repositories, generally used in big Enterprises.Power Mart : we can connect to only a single Repository.Informatica Power Center is used to maintain the Global Repository, But not in the case of Informatica Power mart. For more you can analyse the architecture of InformaticaPowermart:We can register only local repositoriesPartitioning is not available heredoesnot support ERP PowerCentre:We can make repositories to GLOBALPartitioning is availableSupports ERP31.What is Entity relation? How is works with Data warehousing ETL modeling?Entity is nothing but an Object, it has characteristics. We call entity in terms of Logical view. The entity is called as a table in terms of Physical view.The Entity relationship is nothing but maintaining a primary key, foreign key relation between the tables for keeping the data and satisfying the Normal form.T