212)What is difference between dimention table and fact table and what are different dimention tables and fact tables
In the fact table contain measurable data and less columns and meny rows,
It's contain primarykey
Diffrent types of fact tables:
additive,non additive, semi additive
In the dimensions table contain textual descrption of data and also contain many columns,less rows
Its contain primary key
213)How to export mappings to the production environment?
In th e designer go to the main menu and one can see the export/import options.Import the exported mapping in to the production repository with replace options.
214)When do u use a unconnected lookup and connected lookup....
what is the difference between dynamic and static lookup...y and when do v use these types of lookups ( ie...dynamic and static )
In static lookup cache, you cache all the lookup data at the starting of the session. In dynamic lookup cache, you go and query the database to get the lookup value for each record which needs the lookup. static lookup cache adds to the session run time....but it saves time as informatica does not need to connect to your databse every time it needs to lookup. depending on how many rows in your mapping needs a lookup, you can decide on this...also remember that static lookup eats up space...so remember to select only those columns which are needed
215)With out using Updatestretagy and sessons options, how we can do the update our
target table?
In session properties, There is an option
insert
update
insert as update
update as update
like that
by using this we will easily solve
216)In realtime which one is better star schema or snowflake star schema the surrogate will be linked to which columns in the dimension table.
In real time only star schema will implement because it will take less time and surrogate key will there in each and every dimension table in star schema and this surrogate key will assign as foreign key in fact table.
217)What is the mapping for unit testing in Informatica, are there any other testings in Informatica, and how we will do them as a etl developer. how do the testing people will do testing are there any specific tools for testing
In informatica there is no method for unit testing. There are two methods to test the mapping.
1. But we have data sampling. set the ata sampling properties for session in workflow manager for specified number of rows and test the mapping.
2. Use the debugger and test the mapping for sample records.
218)What is hash table informatica?
In hash partitioning, the Informatica Server uses a hash function to group rows of data among partitions. The Informatica Server groups the data based on a partition key.Use hash partitioning when you want the Informatica Server to distribute rows to the partitions by group. For example, you need to sort items by item ID, but you do not know how many items have a particular ID number.
219)In Dimensional modeling fact table is normalized or denormalized?in case of star
schema and incase of snow flake schema?
In Dimensional modeling, Star Schema: A Single Fact table will be surrounded by a group of Dimensional tables comprise of de- normalized data Snowflake Schema: A Single Fact table will be surrounded by a group of Dimensional tables comprised of normalized dataThe Star Schema (sometimes referenced as star join schema) is the simplest data warehouse schema, consisting of a single "fact table" with a compound primary key, with one segment for each "dimension" and with additional columns of additive, numeric facts.The Star Schema makes multi-dimensional database (MDDB) functionality possible using a traditional relational database. Because relational databases
are the most common data management system in organizations today, implementing multi-dimensional views of data using a relational database is very appealing. Even if you are using a specific MDDB solution, its sources likely are relational databases. Another reason for using star schema is its ease of understanding. Fact tables in star schema are mostly in third normal form (3NF), but dimensional tables in de-normalized second normal form (2NF). If you want to normalize dimensional
tables, they look like snowflakes (see snowflake schema) and the same problems of relational databases arise - you need complex queries and business users cannot easily understand the meaning of data. Although query performance may be improved by advanced DBMS technology and hardware, highly normalized tables make reporting difficult and applications complex.The Snowflake Schema is a more complex data warehouse model than a star schema, and is a type of star schema. It is called a snowflake
schema because the diagram of the schema resembles a snowflake.Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table. For example, a product dimension table in a star schema might be normalized into a products table, a Product-category table, and a product-manufacturer table in a snowflake schema. While this saves space, it increases the number of dimension tables and requires more foreign key joins. The result is more complex queries and reduced query performance.
220)We are using Update Strategy Transformation in mapping how can we know whether insert or update or reject or delete option has been selected during running of sessions in Informatica.
In Designer while creating Update Strategy Transformation uncheck "forward to next transformation". If any rejected rows are there automatically it will be updated to the session log file.
Update or insert files are known by checking the target file or table only.
221)How to use mapping parameters and what is their use
In designer u will find the mapping parameters and variables options.u can assign a value to them in designer. comming to there uses suppose u r doing incremental extractions daily. suppose ur source system contains the day column. so every day u have to go to that mapping and change the day so that the particular data will be extracted . if we do that it will be like a layman's work. there comes the concept of mapping parameters and variables. once if u assign a value to a mapping variable then it will change between sessions.
222)What is difference between stored procedure transformation and external procedure transformation?
In case of storedprocedure transformation procedure will be compiled and executed in a relational data source.U need data base connection to import the stored procedure in to u’r maping.Where as in external procedure transformation procedure or function will be executed out side of data source.Ie u need to make it as a DLL to access in u r maping.No need to have data base connection in case of external procedure transformation.
223)Can u copy the session to a different folder or repository?
In ad dition, you can copy the workflow from the Repository manager. This will automatically copy the mapping, associated source,targets and session to the target folder.
224)Discuss the advantages & Disadvantages of star & snowflake schema?
In a STAR schema there is no relation between any two dimension tables, whereas in a
SNOWFLAKE schema there is a possible relation between the dimension tables.
225)What is a time dimension? give an example.
In a relational data model, for normalization purposes, year lookup, quarter lookup, month lookup, and week lookups are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called TIME DIMENSION for performance and slicing data.
This dimensions helps to find the sales done on date, weekly, monthly and yearly basis. We can have a trend analysis by comparing this year sales with the previous year or this week sales with the previous week.
226)
1)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.
2) What are the common problems developers face while ETL development
If you want to know the performance of a mapping at transformation level, then select the option in the session properties-> collect performance data. At the run time in the monitor you can see it in the performance tab or you can get it from a file. The PowerCenter Server names the file session_name.perf, and stores it in the same directory as the session log. If there is no session-specific directory for the session log, the PowerCenter Server saves the file in the default log files directory.
Quality of the data loaded depends on the quality of data in the source. If cleansing is required then have to perform some data cleansing operations in informatica. Final data will always be clean if followed.
227)Explain about Recovering sessions?
If you stop a session or if an error causes a session to stop, refer to the session and error logs to determine the cause of failure. Correct the errors, and then complete the session. The method you use to complete the session depends on the properties of the mapping, session, and Informatica Server configuration.
Use one of the following methods to complete the session:
· Run the session again if the Informatica Server has not issued a commit.
· Truncate the target tables and run the session again if the session is not recoverable.
· Consider performing recovery if the Informatica Server has issued at least one commit.
228)How can u recover the session in sequential batches?
If you configure a session in a sequential batch to stop on failure, you can run recovery starting with the failed session. The Informatica Server completes the session and then runs the rest of the batch. Use the Perform Recovery session property To recover sessions in sequential batches configured to stop on failure:
1.In the Server Manager, open the session property sheet.
2.On the Log Files tab, select Perform Recovery, and click OK.
3.Run the session.
4.After the batch completes, open the session property sheet.
5.Clear Perform Recovery, and click OK.
If you do not clear Perform Recovery, the next time you run the session, the Informatica
Server attempts to recover the previous session.
If you do not configure a session in a sequential batch to stop on failure, and the
remaining sessions in the batch complete, recover the failed session as a standalone session.
229)Which is better among connected lookup and unconnected lookup transformations
in informatica or any other ETL tool?
if you are having defined source you can use connected, source is not well defined or from
different database you can go for unconnected We are using like that only
230)How u will create header and footer in target using informatica?
If you are focus is about the flat files then one can set it in file properties while creating a
mapping or at the session level in session properties
231)What are mapping parameters and varibles in which situation we can use it
If we need to change certain attributes of a mapping after every time the session is run, it will be very difficult to edit the mapping and then change the attribute. So we use mapping parameters and variables and define the values in a parameter file. Then we could edit the parameter file to change the attribute values. This makes the process simple. Mapping parameter values remain constant. If we need to change the parameter value then we need to edit the parameter file . But value of mapping variables can be changed by using variable function. If we need to increment the attribute value by 1 after every session run then we can use mapping variables . In a mapping parameter we need to manually edit the attribute value in the parameter file after every session run.
232)What will happen if you are using Update Strategy Transformation and your session
is configured for "insert"?
What are the types of External Loader available with Informatica?
If you have rank index for top 10. However if you pass only 5 records, what will be
the output of such a Rank Transformation?
if u r using a update strategy in any of ur mapping, then in session properties u have to set treat source rows as Data Driven. if u select insert or udate or delete, then the info server will not consider UPD for performing any DB operations. ELSE u can use the UPD session level options. instead of using a UPD in mapping just select the update in treat source rows and update else insert option. this will do the same job as UPD. but be sure to have a PK in the target table.
2) for oracle : SQL loader
for teradata:tpump,mload.
3) if u pass only 5 rows to rank, it will rank only the 5 records based on the rank port.
Print this post
No comments:
Post a Comment