CSV Files in Data Stage
CSV format :- (comma Separated Values)
TO read the xls file in Sequential file, convert xls ta. CSV format
How to convert .xls to .csv format :-
- Go to excel
open emp. Xls Save as other format Select CSV (comma delimited) emp .CSV
Properties file = emp .csv columns load while import select file type .CSVimport ok
Tara data with ODBC
- Start Tara data Service control Minimize it
- Go to start Control panel Administrative Tools serviceClick on Tara data data base Initiator start close it
Default values
User id = td user
Password = td user
Server = 127.0.0.1
Creating DSN for Tara data
Start Control panel Administrative Tools Data base (ODBC)System DSN Add Tara data finish Name = chaladi2, IP address = 127.0.0.1 user name = td user name password = td user Ok DSN created chaladil
ODBC Enterprise — Data set
Properties Read Method = Table, Data source = chaladil, User = td user, Password =td user Columns load Table Definition importPlug in Meta Data definition click on Tara data okTara data director = 127.0.0.1,User name = td user, Password = td userNextData base = financialSelect only Table Next Select table customer import Table Definition plug in Tara data customer ok ok
Now, go to properties
Now, view Data
Dynamic RDMS
This is the only stage that supports n inputs and n outputs
Oracle can read multiple tables using join but not independently.
Using Dynamic RDBMS , we can read multiple tables independently.
Properties click on DBMS Type Select oracle connection name (oracle) user (scott)
Password (tiger) click on output name = emp columns click on columnsLoad change Data to time stamp click on output name = DeptClick on columns load change data to times stamp
- Now compile and Run
- EMP table will be displayed in new.ds
- DEPT table will be displayed in new1.ds
Transformer
It is a processing stage which has 1 input and n outputs
It is the Heart of Data stage.
It is an All – in –one stage(Can perform the process of column generator, copy operator)
Difference between Basic Transformation and Parallel Transformer:
Basic Transformation
- Comes from Server Environment
- Effects the performance
- Jobs with Basic Transformation can run up to SMP Machines (Platform dependent)
- Basic Transformation can call the routines only in
- Basic language
- SHELLSCRIPT
Parallel Transformation
- Comes from parallel Environment
- Does not Effects the performance, but effects the compilation
- Jobs can run any Machines (Platform Independent)
- Parallel Transformation can call the routines in any language like Java,
C++,VC++,VB,>net Shall Script, pearl Script
- Transformation is used to call a routine in to data stage
Calculating Net Salary by Adding
Properties Read Method = table
Table = EMP
- Click on stage
- Data connection = load Data connection
- When you load, it will automatically update the user, password, and re mode Server
- Click on ColumnsàLoad àokàChange the date to Times stamp
Data set
- Properties Browse the fileGive a file name with .ds extension
Transformer
- Drag all the fields from in to out in the upper part
- In the bottom part, out
Add a new column
Net Sa Integer 5
click on ok
- It will be added to the out of upper part
Double click Right click Select input Column Select Sal in. SalRight click , Select ‘+’ in.sal + Right function Null Handling
- Null to Zero in .al + Null to Zero (% input %) Right click Input column Select Can Sal + Null to zero (in. COMM)OkNow Compile and run
These core tutorials will help you to learnthe fundamentals of Tableau.
For an in-depth understanding and practical experience,
explore Online DataStage Training.
Stage Variable:- (Temporary Variable)
Stage variable is a temporary variable which he it’s the value until the process complete,But does send the value to the output.
If Net Sa > 2000 then Re home = Net Sa – 200
Else
Take home = Net Sal +200
Transformer
At the bottom. Out, Now create an New column name ‘Take home’
Column Name SQL Type length
Take home Integer 8
- Now click ok
- It will be added to top out
- Now click on show / Hide Stage variable symbol
- Stage variable dialogue box opens
Double click write the logic here in . Sal + Null Zero (in.Comn) Now go to out
Drag the stage variable to out at Net Sal
Double click Right click If then else if Right click Stage variables If Net Val > 2000
Then Net Val – 2000 else Net Val + 200 okCompile and RUN
- Create a table with fields
SID, S Name, M1, M2, M3, M4
Stage variables
| In. M1 + in. M2 + in. M3 + in .M4 | tot |
| Tot/5 | Avg |
| If Avg >= 60, then “Pass” else “fail” | res |
| If res = “pass” then “A” else “B” | gra |
If tot > 401 and tot < 500 then “A” else if
Tot > 302 and tot < 400 then “B” else “c”
For indepth understanding of DataStage click on