Name four output files that informatica server creates during session running?
- Session Log
- Workflow Log
- Errors Log
- Badfile
A stored procedure transformation is an important tool for populating and maintaing databases.
What is the difference between static cache and dynamic cache?
Dynamic cache decreases the performance in comparision to static cache.
Static cache do not see such things just insert data as many times as it is coming
Define maping and sessions?
Maping: It is a set of source and target definitions linked by transformation objects that define the rules for transformation.
Session : It is a set of instructions that describe how and when to move data from source to targets.
What is a command that used to run a batch?
pmcmd is used to start a batch.
What is Datadriven?
The informatica server follows instructions coded into update strategy transformations with in the session maping determine how to flag records for insert, update, delete or reject.
What is power center repository?
The PowerCenter repository allows you to share metadata across repositories to create a data mart domain.
What is parameter file?
A parameter file is a file created by text editor such as word pad or notepad. You can define the following values in parameter file.
- Maping parameters
- Maping variables
- Session parameters.
- Static cache: You can configure a static or readonly cache for only lookup table. By default, informatica server creates a static cache. It caches the lookup table and lookup values in the cache for each row that comes into the transformation. When the lookup condition is true, the informatica server does not update the cache while it processes the lookup transformation.
- Dynamic cache: If you want to cache the target table and insert new rows into cache and the target, you can create a look up transformation to use dynamic cache.The informatica server dynamically inserts data to the target table.
- Persistent cache: You can save the lookup cache files and reuse them the next time the informatica server processes a lookup transformation configured to use the cache.
- Shared cache: You can share the lookup cache between multiple transactions. You can share unnamed cache between transformations in the same mapping.
- Recache: If the persistent cache is not synchronized with he lookup table, you can configure the lookup transformation to rebuild the lookup cache.
Stored Procedure transformation is an Passive & Connected or UnConnected transformation. It is useful to automate time-consuming tasks and it is also used in error handling, to drop and recreate indexes and to determine the space in database, a specialized calculation.
What is fact table?
The centralized table in a star schema is called as fact table. Fact tables are three types
- Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
- Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
- Semi Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Source Qualifier transformation is an Active and Connected transformation. When adding a relational or a flat file source definition to a mapping, it is must to connect it to a Source Qualifier transformation. The Source Qualifier performs the various tasks such as overriding default SQL query, filtering records; join data from two or more tables etc.
What is difference between maplet and reusable transformation?
Maplet consists of set of transformations that is reusable. A reusable transformation is a single transformation that can be reusable.
What is Update Strategy transformation?
Update strategy transformation is an active and connected transformation. It is used to update data in target table, either to maintain history of data or recent changes. You can specify how to treat source rows in table, insert, update, delete or data driven.
How many types of dimensions are available in informatica?
There are three types of dimensions.
- Star Schema: A star schema is the one in which a central fact table is sourrounded by denormalized dimensional tables. A star schema can be simple or complex. A simple star schema consists of one fact table where as a complex star schema have more than one fact table.
- Snowflake Schema: A snow flake schema is an enhancement of star schema by adding additional dimensions. Snow flake schema are useful when there are low cardinality attributes in the dimensions.
- Galaxy Schema: Galaxy schema contains many fact tables with some common dimensions (conformed dimensions). This schema is a combination of many data marts.
- Quick parsing
- Thorough parsing
By Using Bulk utility mode at the session level and if possible by disabling constraints after consulting with DBA; Using Bulk utility mode would mean that no writing is taking place in Roll Back Segment so loading is faster. However the pitfall is that recovery is not possible.
What are the limitations of handling long datatypes?
When the length of a datatype (e.g varchar2(4000)) goes beyond 4000, Informatica makes this as varchar2(2000).
What are the types of OLAP?
- ROLAP (Relational OLAP) - Users see their data organized in cubes and dimensions but the data is really stored in RDBMS. The performance is slow. A storage mode that uses tables in a relational database to store multidimensional structures.
- MOLAP (Multidimensional OLAP) - Users see their data organized in cubes and dimensions but the data is really stored in MDBMS. Query performance is fast.
- HOLAP (Hybrid OLAP) - It is a combination of ROLAP and HOLAP. EG: HOLOs. In this one will find data queries on aggregated data as well as detailed data.
What is Meta data?
Data about the data, contains the location and description of data warehouse system components such as name, definitions and end user views.
How does the recovery mode work in informatica?
In case of load failure an entry is made in OPB_SERV_ENTRY(?) table from where the extent of loading can be determined.
What is Aggregate Awareness?
Aggregate awareness is a feature of DESIGNER that makes use of aggregate tables in a database. These are tables that contain pre-calculated data. The purpose of these tables is to enhance the performance of SQL transactions; they are thus used to speed up the execution of queries.
When should you use a star schema and when a snowflake schema?
A star schema is a simplest data warehouse schema. Snowflake schema is similar to the star schema. It normalizes dimension table to save data storage space. It can be used to represent hierarchies of information.
What parameters can be tweaked to get better performance from a session?
DTM shared memory, Index cache memory, Data cache memory, by indexing, using persistent cache, increasing commit interval etc.
Why we are going for surrogate keys?
- Data tables in various source systems may use different keys for the same entity.
- Keys may change or be reused in the source data systems.
- Changes in organizational structures may move keys in the hierarchy.
- Definitely at the database level
- at the source Qualifier query itself
- rather than using Joiner transformation
By checking Collect performance Data check box.
What is Dimension Table?
It contains data used to reference data stored in the fact table.
- Fewer rows
- Primarily character data
- One primary key (dimensional key)
- Updatable data
A connection is a set of parameters that provides access to an RDBMS. These parameters include system information such as the data account, user identification, and the path to the database. Designer provides three types of connections: secured, shared, and personal.
What are all the types of dimensions?
- Informational Dimension
- Structural Dimension
- Categorical Dimension
- Partitioning Dimension
Click below links for more Informatica Interview Questions and Answers
Thanks for the answers. really helpful one for my interview! Informatica Online Training
ReplyDelete