DATASTAGE 8.5 Real Time Scenarios

How to send Common Data to other Dataset from source

I/P dATA

FileA                                                                  FileB(10,11,12,13,14,15,16,17,18,19,20)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15


O/P1-------> File contain only uniq data which is not matched with FileB
o/P----------> fILE cONTAin only data which is not matched with FileA
o/P3-----> fILE cONTAIN oNLY cOMMON data From fILEA and File B



sOLUTION------------? NEXT pAGE

8.5 First character and Last character is Upper casex

First character and Last character is Upper case In Datastage 

get the first letter using Substring and use upper and then get the remaining string and then concatenate the string to first uppercase letter and vice versa for Last letter

Do apply below code in xtf stage appropriate column

Thanks & Regards
Siva

8.5 How to remove duplicate record using transformer stage?

 How to remove duplicate record using transformer stage?


-------->First of all data should be sorted before processing into Xtf Stage

-------->  use two stage variables, sv1,sv2

--------> first variable  do the comparison  ex: sv1 = sv2

--------->Secound variable to store the key valueis..whatever the value u need to be set for duplicate values

--------> ex-- > sv2 = column values(empid,deptno) anything as per business need

please follow below snapshots for  more clarification

Thanks & Regards
Siva

@ ITERATION : Datastage 8.5 Enhacements

Transformer looping has been included in the transformer stage by I.B.M

@ITERATION Used in the looping Machanism

There are two types of looping  available here.

Scenario  with Example:-

I/P

Source File

stdId,Subjects
101,maths/science/English
102,english/social/maths
103,soical/science
104,network/games
105,maggi

Out/put

stdId,Subjects
101,maths
101,science
101,english
102,english
102,social
102,maths
103,social
103.science
104,net work
104,games
105.maggi


please check below snapshots

 here m using two xf-funcctions

Dcount , filed and looping variable

 Expected Results

Thanks & Regards,
Siva

I have source file which contains duplicate data,my requirement is unique data should pass to one file and duplicate data should pass another file how?

i have a source table with distinct and duplicate rows of data. i want to load the data in target like one target to be only distinct rows of data and other target to be only duplicate rows of data

 Pass the data to Aggregator and count the number of records for the Key Combination. Collect the Key and Count into a intermediate file

. Read the Intermediate file and filter the records which are having Count<=1, then join with the master input file based on the keys

                                          Now yu can see in out put Unique values in one output

Duplicate Values in Another Output 

Thanks & Regards

Siva

Architecture ODS Model

ETL-ODS Model

end-to-end data warehouse system data flow (ETL – Extract, Transform & Load). The first step (Extract) shows the process of extracting data from different data sources and consolidating into one centralized database (ODS). Source x represents same data structure implemented in multiple instances, each local instance holds a different set of data (e.g.:  separated DB for each subsidiary/customer). Source y represents an enterprise OLTP system DB (e.g.: global ERP, CRM, etc.)

Loading a Fact Table Using Dimension Table

You can create a job that uses the Lookup transformation, which loads fact data from a source table and uses a lookup process to load foreign keys from the dimension table.

The lookup process compares the business key in each source row to the business keys in the dimension table. When the business keys match, the foreign key from the dimension table is loaded into the fact table target.

This sample job assumes that you have already loaded data into your dimension table before you run the job that loads your fact table. Loading the dimension table first ensures that new foreign keys are available in the dimension table.