Informatica
Terradata

 
Data Warehousing products
Home Page......




How do you transfer the data from data warehouse to flatfile?

You can write a mapping with the flat file as a target using a DUMMY_CONNECTION. A flat file target is built by pulling a source into target space using Warehouse Designer tool.
How can u work with remote database in informatica?did u work directly by using remote connections?
You can work with remote,
But you have to
Configure FTP
Connection details
IP address
User authentication
What is difference between IIF and DECODE function?
You can use nested IIF statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is zero or negative:
IIF( SALES > 0, IIF( SALES < 50, SALARY1, IIF( SALES < 100, SALARY2, IIF( SALES < 200, SALARY3, BONUS))), 0 )
You can use DECODE instead of IIF in many cases. DECODE may improve readability. The following shows how you can use DECODE instead of IIF :
SALES > 0 and SALES < 50, SALARY1,
SALES > 49 AND SALES < 100, SALARY2,
SALES > 99 AND SALES < 200, SALARY3,
SALES > 199, BONUS)

What are the new features of the server manager in the informatica 5.0?

You can use command line arguments for a session or batch.This allows you to change the values of session parameters,and mapping parameters and maping variables.
Parallel data processing: This feature is available for powercenter only.If we use the informatica server on a SMP system, You can use multiple CPU’s to process a session
concurently.
Process session data using threads: Informatica server runs the session in two processes.

Can we use aggregator/active transformation after update strategy transformation?

You can use aggregator after update strategy. The problem will be, once you perform the update strategy, say you had flagged some rows to be deleted and you had performed aggregator transformation for all rows, say you are using SUM function, then the deleted rows will be subtracted from this aggregator transformation.
what is the best way to show metadata(number of rows at source, target and each transformation level, error related data) in a report format?
You can select these details from the repository table. you can use the view REP_SESS_LOG to get these data
Can you start a batches with in a batch?
You can not. If you want to start batch that resides in a batch,create a new independent batch and copy the necessary sessions into the new batch.

How can you create or import flat file definition in to the warehouse designer?

You can not create or import flat file defintion in to warehouse designer directly.Instead you must analyze the file in source analyzer,then drag it into the warehouse
designer.
When you drag the flat file source defintion into warehouse desginer workspace,the warehouse designer creates a relational target defintion not a file defintion.If you want to load to a file,configure the session to write to a flat file.When the informatica server runs the session,it creates and loads the flat file.
In my source table 1000 rec's r there.I want to load 501 rec to 1000 rec into my Target table ?how can u do this ?
You can overide the sql Query in Wofkflow Manager. LIke
select * from tab_name where rownum<=1000
minus
select * from tab_name where rownum<=500;
This will work fine. Try it and get back to me if u have any issues about the same.

How to Generate the Metadata Reports in Informatica?

You can generate PowerCenter Metadata Reporter from a browser on any workstation, even a workstation that does not have PowerCenter tools installed.
What r the methods for creating reusable transforamtions?
You can design using 2 methods
using transformation developer
create normal one and promote it to reusable
What are the diffrence between joiner transformation and source qualifier transformation?
You can join hetrogenious data sources in joiner transformation which we can not achieve in source qualifier transformation.
You need matching keys to join two relational sources in source qualifier transformation.Where as you doesn’t need matching keys to join two sources.
Two relational sources should come from same datasource in sourcequalifier.You can join relatinal sources which are coming from diffrent sources also.

Can any body write a session parameter file which will change the source and targets for every session. i.e different source and targets for each session run.
You are supposed to define a parameter file. And then in the Parameter file, you can define two parameters, one for source and one for target.
Give like this for example:
$Src_file = c:\program files\informatica\server\bin\abc_source.txt
$tgt_file = c:\targets\abc_targets.txt
Then go and define the parameter file:
[folder_name.WF:workflow_name.ST:s_session_name]
$Src_file =c:\program files\informatica\server\bin\abc_source.txt
$tgt_file = c:\targets\abc_targets.txt
If its a relational db, you can even give an overridden sql at the session level...as a parameter. Make sure the sql is in a single line.
In a sequential batch can you run the session if previous session fails?
Yes.By setting the option always runs the session.
Can you use the maping parameters or variables created in one maping into any other reusable transformation?
Yes.Because reusable tranformation is not contained with any maplet or maping.
Can you generate reports in Informatcia?
Yes. By using Metadata reporter we can generate reports in informatica.
There are 3 depts in dept table and one with 100 people and 2nd with 5 and 3rd with some 30 and so. i want to diplay those deptno where more than 10 people exists
Yes! the answer provided is absolutely right. by an SQL application(Oracle).
If you want to perform it thru informatica, the Fire the same query in the SQL Override of Source qualifier transformation and make a simple pass thru mapping.
Other wise, you can also do it by using a Filter.Router transformation by giving the condition there deptno>=10.
Can you copy the session to a different folder or repository?
Yes. By using copy session wizard You can copy a session in a different folder or repository. But that target folder or repository should consists of mapping of that session.
If target folder or repository is not having the maping of copying session ,
You should have to copy that maping first before you copy the session.
Can Informatica be used as a Cleansing Tool? If Yes, give example of transformations that can implement a data cleansing routine.
Yes, we can use Informatica for cleansing data. some time we use stages to cleansing the data. It depends upon performance again else we can use expression to cleasing data.
For example an feild X have some values and other with Null values and assigned to target feild where target feild is notnull column, inside an expression we can assign space or some constant value to avoid session failure.

The input data is in one format and target is in another format, we can change the format in expression.
we can assign some default values to the target to represent complete set of data in the target.
Can batches be copied/stopped from server manager?
Yes, we can stop the batches using server manager or pmcmd command
Can Informatica load heterogeneous targets from heterogeneous sources?
Yes it can. For example...Flat File and Relations sources are joined in the mapping, and later, Flat File and relational targets are loaded.
What is tracing level?
Ya its the level of information storage in session log.
The option comes in the properties tab of transformations. By default it remains "Normal". Can be
Verbose Initialisation
Verbose Data
Normal
or Terse.
Can we run a group of sessions without using workflow manager
ya Its Posible using pmcmd Command with out using the workflow Manager run the group of session.
as per my knowledge i give the answer.
How do you create single lookup transformation using multiple tables?
Write a override sql query. Adjust the ports as per the sql query.
How can we join the tables if the tables have no primary and forien key relation and no matchig port to join?
without common column or common data type we can join two sources using dummy ports.
1.Add one dummy port in two sources.
2.In the expression trans assing '1' to each port.
2.Use Joiner transformation to join the sources using dummy port(use join conditions).
hope this will help.
Describe two levels in which update strategy transformation sets?
Within a session: When you configure a session, yoYou can instruct the Informatica Server to either treat all records in the same way (for example, treat all records as inserts), or use instructions coded into the session mapping to flag records for different database operations.
Within a mapping: Within a mapping, you use the Update Strategy transformation to flag records for insert, delete, update, or reject.
How do you handle decimal places while importing a flatfile into informatica?
While importing flat file definetion just specify the scale for a neumaric data type. in the mapping, the flat file source supports only number datatype(no decimal and integer). In the SQ associated with that source will have a data type as decimal for that number port of the source.
source ->number datatype port ->SQ -> decimal datatype.Integer is not supported. hence decimal is taken care.
How do you handle decimal places while importing a flatfile into informatica?
while importing flat file definetion just specify the scale for a neumaric data type. in the mapping, the flat file source supports only number datatype(no decimal and integer). In the SQ associated with that source will have a data type as decimal for that number port of the source.
source ->number datatype port ->SQ -> decimal datatype.Integer is not supported. hence decimal is taken care.
What is parameter file?
When you start a workflow, you can optionally enter the directory and name of a parameter file. The Informatica Server runs the workflow using the parameters in the file you specify.
For UNIX shell users, enclose the parameter file name in single quotes:
-paramfile '$PMRootDir/myfile.txt'
For Windows command prompt users, the parameter file name cannot have beginning or trailing spaces. If the name includes spaces, enclose the file name in double quotes:
-paramfile ”$PMRootDir\my file.txt”
Note: When you write a pmcmd command that includes a parameter file located on another machine, use the backslash (\) with the dollar sign ($). This ensures that the machine where the variable is defined expands the server variable.
pmcmd startworkflow -uv USERNAME -pv PASSWORD -s SALES:6258 -f east -w wSalesAvg -paramfile '\$PMRootDir/myfile.txt'
What is aggregate cache in aggregator transforamtion?
When you run a workflow that uses an Aggregator transformation, the Informatica Server creates index and data caches in memory to process the transformation. If the Informatica Server requires more space, it stores overflow values in cache files.
Why you use repository connectivity?
When you edit,schedule the sesion each time,informatica server directly communicates the repository to check whether or not the session and users are valid.All the metadata of sessions and mappings will be stored in repository.
Briefly explian the Versioning Concept in Power Center 7.1.
When you create a version of a folder referenced by shortcuts, all shortcuts continue to reference their original object in the original version. They do not automatically update to the current folder version.
For example, if you have a shortcut to a source definition in the Marketing folder, version 1.0.0, then you create a new folder version, 1.5.0, the shortcut continues to point to the source definition in version 1.0.0.
Maintaining versions of shared folders can result in shortcuts pointing to different versions of the folder. Though shortcuts to different versions do not affect the server, they might prove more difficult to maintain. To avoid this, you can recreate shortcuts pointing to earlier versions, but this solution is not practical for much-used objects. Therefore, when possible, do not version folders referenced by shortcuts.
What is source qualifier transformation?
When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier represents the rows that the Informatica Server reads when it executes a session.
Join data originating from the same source database. You can join two or more tables with primary-foreign key relationships by linking the sources to one Source Qualifier.
Filter records when the Informatica Server reads source data. If you include a filter condition, the Informatica Server adds a WHERE clause to the default query.
Specify an outer join rather than the default inner join. If you include a user-defined join, the Informatica Server replaces the join information specified by the metadata in the SQL query.
Specify sorted ports. If you specify a number for sorted ports, the Informatica Server adds an ORDER BY clause to the default SQL query.
Select only distinct values from the source. If you choose Select Distinct, the Informatica Server adds a SELECT DISTINCT statement to the default SQL query.
Create a custom query to issue a special SELECT statement for the Informatica Server to read source data. For example, you might use a custom query to perform aggregate calculations or execute a stored procedure.
What is a source qualifier?-
When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier represents the rows that the Informatica Server reads when it executes a session.
What is source qualifier transformation?
When you add a relational or a flat file source definition to a maping,U need to connect it to a source qualifer transformation.The source qualifier transformation represnets the records that the informatica server reads when it runs a session.
What is incremantal aggregation?
When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes only incrementally and you
can capture changes, you can configure the session to process only those changes. This allows the Informatica Server to update your target incrementally, rather than
forcing it to process the entire source and recalculate the same calculations each time you run the session.
If you are workflow is running slow in informatica. Where do you start trouble shooting and what are the steps you follow?
When the work flow is running slowly u have to find out the bottlenecks
in this order target source mapping session system
What is exact use of 'Online' and 'Offline' server connect Options while defining Work flow in Work flow monitor? . The system hangs when 'Online' server connect option. The Informatica is installed on a Personal laptop.
When the repo is up and the PMSERVER is also up, workflow monitor always will be connected on-line.
When PMserver is down and the repo is still up we will be prompted for an off-line connection with which we can just monitor the workflows
Explain about perform recovery?
When the Informatica Server starts a recovery session, it reads the OPB_SRVR_RECOVERY table and notes the row ID of the last row committed to the target database.
The Informatica Server then reads all sources again and starts processing from the next row ID. For example, if the Informatica Server commits 10,000 rows before the
session fails, when you run recovery, the Informatica Server bypasses the rows up to 10,000 and starts loading with row 10,001.
By default, Perform Recovery is disabled in the Informatica Server setup. You must enable Recovery in the Informatica Server setup before you run a session so the
Informatica Server can create and/or write entries in the OPB_SRVR_RECOVERY table.