I have a task in which I need to take a flat file as my source and use oracle's sequence generator for a column and store into target flat file with sequence numbers including.
EXample:Source flat file
abc 1000
xyz 2000
def 3000
Target flat file
1 abc 1000
2 xyz 2000
3 def 3000
Note : The sequence numbers should come only from ORACLE (not from informatica's sequence generator Transformation).
Sunday, March 23, 2008
Saturday, March 22, 2008
Do's and Dont's while using Sorted Input in Aggregator transformation
In general, follow this check list to ensure that you are handling aggregator with sorted inputs correctly:
1. Do not use sorted input if any of the following conditions are true:
a.The aggregate expression uses nested aggregate functions.
b.The session uses incremental aggregation.
c. Input data is data driven.
You select data driven for the Treat Source Rows as Session Property, or the Update Strategy transformation appears before the Aggregator transformation in the mapping.
If you use sorted input under these circumstances, the Informatica Server reverts to default aggregate behavior, reading all values before performing aggregate calculations.
Remember, when you are using sorted inputs, you pass sorted data through the Aggregator.
Data must be sorted as follows:
1. By the Aggregator group by ports, in the order they appear in the Aggregator transformation.
2. Using the same sort order configured for the session.
If data is not in strict ascending or descending order based on the session sort order, the Informatica Server fails the session.
1. Do not use sorted input if any of the following conditions are true:
a.The aggregate expression uses nested aggregate functions.
b.The session uses incremental aggregation.
c. Input data is data driven.
You select data driven for the Treat Source Rows as Session Property, or the Update Strategy transformation appears before the Aggregator transformation in the mapping.
If you use sorted input under these circumstances, the Informatica Server reverts to default aggregate behavior, reading all values before performing aggregate calculations.
Remember, when you are using sorted inputs, you pass sorted data through the Aggregator.
Data must be sorted as follows:
1. By the Aggregator group by ports, in the order they appear in the Aggregator transformation.
2. Using the same sort order configured for the session.
If data is not in strict ascending or descending order based on the session sort order, the Informatica Server fails the session.
Friday, March 21, 2008
Why staging area is needed?
Unlike OLTP systems that create their own data through a user interface, data warehouses source their data from other systems.
There is physical data movement from source database to data warehouse database.
Staging area is primarily designed to serve as intermediate resting place for data before it is processed and integrated into the target data warehouse.
This staging are serves many purpose above and beyond the primary function.
a.The data is most consistent with the source.
It is devoid of any transformation or has only minor format changes.
b. The staging area in a relational database can be read/ scanned/ queried using SQL without the need of logging into the source system or reading files (text/xml/binary).
c. It is a prime location for validating data quality from source or auditing and tracking down data issues.
d. Staging area acts as a repository for historical data if not truncated
There is physical data movement from source database to data warehouse database.
Staging area is primarily designed to serve as intermediate resting place for data before it is processed and integrated into the target data warehouse.
This staging are serves many purpose above and beyond the primary function.
a.The data is most consistent with the source.
It is devoid of any transformation or has only minor format changes.
b. The staging area in a relational database can be read/ scanned/ queried using SQL without the need of logging into the source system or reading files (text/xml/binary).
c. It is a prime location for validating data quality from source or auditing and tracking down data issues.
d. Staging area acts as a repository for historical data if not truncated
Query to display Rank
Hi,Suppose we have a table with fields
Rollno Name Marks
1 A 95
2 B 90
3 C 75
4 D 80
5 E 85
Write a query to display rollno,name,marks,Rank in ascending order of rank.
Wednesday, March 19, 2008
Backup of mappings?
Hi,
Could anyone tell me how to take the backup of all the mappings in a repository?Is it the job of an ETL-developer?
Could anyone tell me how to take the backup of all the mappings in a repository?Is it the job of an ETL-developer?
What is sequence generator transformation?
The seq gen trans generates numeric values.We can use the seq gen trans to create unique primary key values,replace missing keys or cycle through a sequential range of numbers.
Subscribe to:
Posts (Atom)