data warehousing interview qa-3
20. When do u create the Source Definition ? Can I use this Source Defn to any Transformation?
Ans: When working with a file that contains fixed-width binary data, you must create the source definition.
The Designer displays the source definition as a table, consisting of names, datatypes, and constraints. To use a source
definition in a mapping, connect a source definition to a Source Qualifier or Normalizer transformation. The Informatica
Server uses these transformations to read the source data.
21. What is Active & Passive Transformation ?
Ans: Active and Passive Transformations
Transformations can be active or passive. An active transformation can change the number of records passed through it. A
passive transformation never changes the record count.For example, the Filter transformation removes rows that do not
meet the filter condition defined in the transformation.
Active transformations that might change the record count include the following:
• Advanced External Procedure
• Aggregator
• Filter
• Joiner
• Normalizer
• Rank
• Source Qualifier
Note: If you use PowerConnect to access ERP sources, the ERP Source Qualifier is also an active transformation.
/*
You can connect only one of these active transformations to the same transformation or target, since the Informatica
Server cannot determine how to concatenate data from different sets of records with different numbers of rows.
*/
Passive transformations that never change the record count include the following:
• Lookup
• Expression
• External Procedure
• Sequence Generator
• Stored Procedure
• Update Strategy
You can connect any number of these passive transformations, or connect one active transformation with any number of
passive transformations, to the same transformation or target.
22. What is staging Area and Work Area?
Ans: Staging Area : -
- Holding Tables on DW Server.
- Loaded from Extract Process
- Input for Integration/Transformation
- May function as Work Areas
- Output to a work area or Fact Table
Work Area: -
- Temporary Tables
- Memory
23. What is Metadata? (plz refer DATA WHING IN THE REAL WORLD BOOK page # 125)
Ans: Defn: “Data About Data”
Metadata contains descriptive data for end users. In a data warehouse the term metadata is used in a number of different
situations.
Metadata is used for:
• Data transformation and load
• Data management
• Query management
Data transformation and load:
Metadata may be used during data transformation and load to describe the source data and any changes that need to be made. The advantage of storing metadata about the data being transformed is that as source data changes the changes can be captured in the metadata, and transformation programs automatically regenerated.
For each source data field the following information is reqd:
Source Field:
• Unique identifier (to avoid any confusion occurring betn 2 fields of the same anme from different sources).
• Name (Local field name).
• Type (storage type of data, like character,integer,floating point…and so on).
• Location
- system ( system it comes from ex.Accouting system).
- object ( object that contains it ex. Account Table).
The destination field needs to be described in a similar way to the source:
Destination:
• Unique identifier
• Name
• Type (database data type, such as Char, Varchar, Number and so on).
• Tablename (Name of the table th field will be part of).
The other information that needs to be stored is the transformation or transformations that need to be applied to turn the source data into the destination data:
Transformation:
• Transformation (s)
- Name
- Language (name of the lanjuage that transformation is written in).
- module name
- syntax
The Name is the unique identifier that differentiates this from any other similar transformations.
The Language attribute contains the name of the lnguage that the transformation is written in.
The other attributes are module name and syntax. Generally these will be mutually exclusive, with only one being defined. For simple transformations such as simple SQL functions the syntax will be stored. For complex transformations the name of the module that contains the code is stored instead.
Data management:
Metadata is reqd to describe the data as it resides in the data warehouse.This is needed by the warhouse manager to allow it to track and control all data movements. Every object in the database needs to be described.
Metadata is needed for all the following:
• Tables
- Columns
- name
- type
• Indexes
- Columns
- name
- type
• Views
- Columns
- name
- type
• Constraints
- name
- type
- table
- columns
Aggregations, Partition information also need to be stored in Metadata( for details refer page # 30)
Query Generation:
Metadata is also required by the query manger to enable it to generate queries. The same metadata can be used by the Whouse manager to describe the data in the data warehouse is also reqd by the query manager.
The query mangaer will also generate metadata about the queries it has run. This metadata can be used to build a history of all quries run and generate a query profile for each user, group of users and the data warehouse as a whole.
The metadata that is reqd for each query is:
- query
- tables accessed
- columns accessed
- name
- refence identifier
- restrictions applied
- column name
- table name
- reference identifier
- restriction
- join Criteria applied
……
……
- aggregate functions used
……
……
- group by criteria ……
……
- sort criteria ……
……
- syntax - execution plan
- resources ……
……
24. What kind of Unix flavoures u r experienced?
Ans: Solaris 2.5 SunOs 5.5 (Operating System)
Solaris 2.6 SunOs 5.6 (Operating System)
Solaris 2.8 SunOs 5.8 (Operating System)
AIX 4.0.3
5.5.1 2.5.1 May 96 sun4c, sun4m, sun4d, sun4u, x86, ppc
5.6 2.6 Aug. 97 sun4c, sun4m, sun4d, sun4u, x86
5.7 7 Oct. 98 sun4c, sun4m, sun4d, sun4u, x86
5.8 8 2000 sun4m, sun4d, sun4u, x86
25. What are the tasks that are done by Informatica Server?
Ans:The Informatica Server performs the following tasks:
• Manages the scheduling and execution of sessions and batches
• Executes sessions and batches
• Verifies permissions and privileges
• Interacts with the Server Manager and pmcmd.
The Informatica Server moves data from sources to targets based on metadata stored in a repository. For instructions on how to move and transform data, the Informatica Server reads a mapping (a type of metadata that includes transformations and source and target definitions). Each mapping uses a session to define additional information and to optionally override mapping-level options. You can group multiple sessions to run as a single unit, known as a batch.
26. What are the two programs that communicate with the Informatica Server?
Ans: Informatica provides Server Manager and pmcmd programs to communicate with the Informatica Server:
Server Manager. A client application used to create and manage sessions and batches, and to monitor and stop the Informatica Server. You can use information provided through the Server Manager to troubleshoot sessions and improve session performance.
pmcmd. A command-line program that allows you to start and stop sessions and batches, stop the Informatica Server, and verify if the Informatica Server is running.
27. When do u reinitialize Aggregate Cache?
Ans: Reinitializing the aggregate cache overwrites historical aggregate data with new aggregate data. When you reinitialize the
aggregate cache, instead of using the captured changes in source tables, you typically need to use the use the entire source
table.
For example, you can reinitialize the aggregate cache if the source for a session changes incrementally every day and
completely changes once a month. When you receive the new monthly source, you might configure the session to reinitialize
the aggregate cache, truncate the existing target, and use the new source table during the session.
/? Note: To be clarified when server manger works for following ?/
To reinitialize the aggregate cache:
1.In the Server Manager, open the session property sheet.
2.Click the Transformations tab.
3.Check Reinitialize Aggregate Cache.
4.Click OK three times to save your changes.
5.Run the session.
The Informatica Server creates a new aggregate cache, overwriting the existing aggregate cache.
/? To be check for step 6 & step 7 after successful run of session… ?/
6.After running the session, open the property sheet again.
7.Click the Data tab.
8.Clear Reinitialize Aggregate Cache.
9.Click OK.
28. (i) What is Target Load Order in Designer?
Ans: Target Load Order: - In the Designer, you can set the order in which the Informatica Server sends records to various target
definitions in a mapping. This feature is crucial if you want to maintain referential integrity when inserting, deleting, or updating
records in tables that have the primary key and foreign key constraints applied to them. The Informatica Server writes data to
all the targets connected to the same Source Qualifier or Normalizer simultaneously, to maximize performance.
28. (ii) What are the minimim condition that u need to have so as to use Targte Load Order Option in Designer?
Ans: U need to have Multiple Source Qualifier transformations.
To specify the order in which the Informatica Server sends data to targets, create one Source Qualifier or Normalizer
transformation for each target within a mapping. To set the target load order, you then determine the order in which each
Source Qualifier sends data to connected targets in the mapping.
When a mapping includes a Joiner transformation, the Informatica Server sends all records to targets connected to that
Joiner at the same time, regardless of the target load order.
28(iii). How do u set the Target load order?
Ans: To set the target load order:
1. Create a mapping that contains multiple Source Qualifier transformations.
2. After you complete the mapping, choose Mappings-Target Load Plan.
A dialog box lists all Source Qualifier transformations in the mapping, as well as the targets that receive data from each
Source Qualifier.
3. Select a Source Qualifier from the list.
4. Click the Up and Down buttons to move the Source Qualifier within the load order.
5. Repeat steps 3 and 4 for any other Source Qualifiers you wish to reorder.
6. Click OK and Choose Repository-Save.
29. What u can do with Repository Manager?
Ans: We can do following tasks using Repository Manager : -
è To create usernames, you must have one of the following sets of privileges:
- Administer Repository privilege
- Super User privilege
èTo create a user group, you must have one of the following privileges :
- Administer Repository privilege
- Super User privilege
èTo assign or revoke privileges , u must hv one of the following privilege..
- Administer Repository privilege
- Super User privilege
Note: You cannot change the privileges of the default user groups or the default repository users.
Ans: When working with a file that contains fixed-width binary data, you must create the source definition.
The Designer displays the source definition as a table, consisting of names, datatypes, and constraints. To use a source
definition in a mapping, connect a source definition to a Source Qualifier or Normalizer transformation. The Informatica
Server uses these transformations to read the source data.
21. What is Active & Passive Transformation ?
Ans: Active and Passive Transformations
Transformations can be active or passive. An active transformation can change the number of records passed through it. A
passive transformation never changes the record count.For example, the Filter transformation removes rows that do not
meet the filter condition defined in the transformation.
Active transformations that might change the record count include the following:
• Advanced External Procedure
• Aggregator
• Filter
• Joiner
• Normalizer
• Rank
• Source Qualifier
Note: If you use PowerConnect to access ERP sources, the ERP Source Qualifier is also an active transformation.
/*
You can connect only one of these active transformations to the same transformation or target, since the Informatica
Server cannot determine how to concatenate data from different sets of records with different numbers of rows.
*/
Passive transformations that never change the record count include the following:
• Lookup
• Expression
• External Procedure
• Sequence Generator
• Stored Procedure
• Update Strategy
You can connect any number of these passive transformations, or connect one active transformation with any number of
passive transformations, to the same transformation or target.
22. What is staging Area and Work Area?
Ans: Staging Area : -
- Holding Tables on DW Server.
- Loaded from Extract Process
- Input for Integration/Transformation
- May function as Work Areas
- Output to a work area or Fact Table
Work Area: -
- Temporary Tables
- Memory
23. What is Metadata? (plz refer DATA WHING IN THE REAL WORLD BOOK page # 125)
Ans: Defn: “Data About Data”
Metadata contains descriptive data for end users. In a data warehouse the term metadata is used in a number of different
situations.
Metadata is used for:
• Data transformation and load
• Data management
• Query management
Data transformation and load:
Metadata may be used during data transformation and load to describe the source data and any changes that need to be made. The advantage of storing metadata about the data being transformed is that as source data changes the changes can be captured in the metadata, and transformation programs automatically regenerated.
For each source data field the following information is reqd:
Source Field:
• Unique identifier (to avoid any confusion occurring betn 2 fields of the same anme from different sources).
• Name (Local field name).
• Type (storage type of data, like character,integer,floating point…and so on).
• Location
- system ( system it comes from ex.Accouting system).
- object ( object that contains it ex. Account Table).
The destination field needs to be described in a similar way to the source:
Destination:
• Unique identifier
• Name
• Type (database data type, such as Char, Varchar, Number and so on).
• Tablename (Name of the table th field will be part of).
The other information that needs to be stored is the transformation or transformations that need to be applied to turn the source data into the destination data:
Transformation:
• Transformation (s)
- Name
- Language (name of the lanjuage that transformation is written in).
- module name
- syntax
The Name is the unique identifier that differentiates this from any other similar transformations.
The Language attribute contains the name of the lnguage that the transformation is written in.
The other attributes are module name and syntax. Generally these will be mutually exclusive, with only one being defined. For simple transformations such as simple SQL functions the syntax will be stored. For complex transformations the name of the module that contains the code is stored instead.
Data management:
Metadata is reqd to describe the data as it resides in the data warehouse.This is needed by the warhouse manager to allow it to track and control all data movements. Every object in the database needs to be described.
Metadata is needed for all the following:
• Tables
- Columns
- name
- type
• Indexes
- Columns
- name
- type
• Views
- Columns
- name
- type
• Constraints
- name
- type
- table
- columns
Aggregations, Partition information also need to be stored in Metadata( for details refer page # 30)
Query Generation:
Metadata is also required by the query manger to enable it to generate queries. The same metadata can be used by the Whouse manager to describe the data in the data warehouse is also reqd by the query manager.
The query mangaer will also generate metadata about the queries it has run. This metadata can be used to build a history of all quries run and generate a query profile for each user, group of users and the data warehouse as a whole.
The metadata that is reqd for each query is:
- query
- tables accessed
- columns accessed
- name
- refence identifier
- restrictions applied
- column name
- table name
- reference identifier
- restriction
- join Criteria applied
……
……
- aggregate functions used
……
……
- group by criteria ……
……
- sort criteria ……
……
- syntax - execution plan
- resources ……
……
24. What kind of Unix flavoures u r experienced?
Ans: Solaris 2.5 SunOs 5.5 (Operating System)
Solaris 2.6 SunOs 5.6 (Operating System)
Solaris 2.8 SunOs 5.8 (Operating System)
AIX 4.0.3
5.5.1 2.5.1 May 96 sun4c, sun4m, sun4d, sun4u, x86, ppc
5.6 2.6 Aug. 97 sun4c, sun4m, sun4d, sun4u, x86
5.7 7 Oct. 98 sun4c, sun4m, sun4d, sun4u, x86
5.8 8 2000 sun4m, sun4d, sun4u, x86
25. What are the tasks that are done by Informatica Server?
Ans:The Informatica Server performs the following tasks:
• Manages the scheduling and execution of sessions and batches
• Executes sessions and batches
• Verifies permissions and privileges
• Interacts with the Server Manager and pmcmd.
The Informatica Server moves data from sources to targets based on metadata stored in a repository. For instructions on how to move and transform data, the Informatica Server reads a mapping (a type of metadata that includes transformations and source and target definitions). Each mapping uses a session to define additional information and to optionally override mapping-level options. You can group multiple sessions to run as a single unit, known as a batch.
26. What are the two programs that communicate with the Informatica Server?
Ans: Informatica provides Server Manager and pmcmd programs to communicate with the Informatica Server:
Server Manager. A client application used to create and manage sessions and batches, and to monitor and stop the Informatica Server. You can use information provided through the Server Manager to troubleshoot sessions and improve session performance.
pmcmd. A command-line program that allows you to start and stop sessions and batches, stop the Informatica Server, and verify if the Informatica Server is running.
27. When do u reinitialize Aggregate Cache?
Ans: Reinitializing the aggregate cache overwrites historical aggregate data with new aggregate data. When you reinitialize the
aggregate cache, instead of using the captured changes in source tables, you typically need to use the use the entire source
table.
For example, you can reinitialize the aggregate cache if the source for a session changes incrementally every day and
completely changes once a month. When you receive the new monthly source, you might configure the session to reinitialize
the aggregate cache, truncate the existing target, and use the new source table during the session.
/? Note: To be clarified when server manger works for following ?/
To reinitialize the aggregate cache:
1.In the Server Manager, open the session property sheet.
2.Click the Transformations tab.
3.Check Reinitialize Aggregate Cache.
4.Click OK three times to save your changes.
5.Run the session.
The Informatica Server creates a new aggregate cache, overwriting the existing aggregate cache.
/? To be check for step 6 & step 7 after successful run of session… ?/
6.After running the session, open the property sheet again.
7.Click the Data tab.
8.Clear Reinitialize Aggregate Cache.
9.Click OK.
28. (i) What is Target Load Order in Designer?
Ans: Target Load Order: - In the Designer, you can set the order in which the Informatica Server sends records to various target
definitions in a mapping. This feature is crucial if you want to maintain referential integrity when inserting, deleting, or updating
records in tables that have the primary key and foreign key constraints applied to them. The Informatica Server writes data to
all the targets connected to the same Source Qualifier or Normalizer simultaneously, to maximize performance.
28. (ii) What are the minimim condition that u need to have so as to use Targte Load Order Option in Designer?
Ans: U need to have Multiple Source Qualifier transformations.
To specify the order in which the Informatica Server sends data to targets, create one Source Qualifier or Normalizer
transformation for each target within a mapping. To set the target load order, you then determine the order in which each
Source Qualifier sends data to connected targets in the mapping.
When a mapping includes a Joiner transformation, the Informatica Server sends all records to targets connected to that
Joiner at the same time, regardless of the target load order.
28(iii). How do u set the Target load order?
Ans: To set the target load order:
1. Create a mapping that contains multiple Source Qualifier transformations.
2. After you complete the mapping, choose Mappings-Target Load Plan.
A dialog box lists all Source Qualifier transformations in the mapping, as well as the targets that receive data from each
Source Qualifier.
3. Select a Source Qualifier from the list.
4. Click the Up and Down buttons to move the Source Qualifier within the load order.
5. Repeat steps 3 and 4 for any other Source Qualifiers you wish to reorder.
6. Click OK and Choose Repository-Save.
29. What u can do with Repository Manager?
Ans: We can do following tasks using Repository Manager : -
è To create usernames, you must have one of the following sets of privileges:
- Administer Repository privilege
- Super User privilege
èTo create a user group, you must have one of the following privileges :
- Administer Repository privilege
- Super User privilege
èTo assign or revoke privileges , u must hv one of the following privilege..
- Administer Repository privilege
- Super User privilege
Note: You cannot change the privileges of the default user groups or the default repository users.
posted by dwhfaq | 1:21 PM
<< Home