Download - Normailzed Tranformation
-
7/30/2019 Normailzed Tranformation
1/63
IBM DataStage 8.5 Beginners Guide - Lab#13
Aggregate Transformation
Description: BISP is committed to provide BEST learning material to the
beginners and advance learners. In the same series, we have prepared
a list of beginners guide and FAQs for IBM Data Stage. We have built
complete financial Data Model and various data transformation
techniques. Download many such learning documents, student guide,
Lab Guide and Hands-on practice materials. This document describesin step by step manner how to de-normalized and convert transaction
model to OLAP model. Data is coming from multiple flat files sources
and loading into Target tables Join our professional training to
learn from Experts.
www.bispsolutions.com www.bisptrainigs.com Page 1
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
2/63
Contents
Contents.......................................................................................................................... 2
Source Data Model:-........................................................................................................ 3
Target Data Model:-......................................................................................................... 3
Steps to Perform Normalized Transformation..................................................................9
Loading Product Dimension........................................................................................... 26
Loading Supplier Dimension.......................................................................................... 38
Loading Employee Dimension........................................................................................47
Loading Store Dimension...............................................................................................55
www.bispsolutions.com www.bisptrainigs.com Page 2
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
3/63
Source Data Model:-
Target Data Model:-
www.bispsolutions.com www.bisptrainigs.com Page 3
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
4/63
Customer Source Model and Mapping Sheet:-
www.bispsolutions.com www.bisptrainigs.com Page 4
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
5/63
Product Source Model and Mapping Sheet:-
www.bispsolutions.com www.bisptrainigs.com Page 5
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
6/63
Supplier Source Model and Mapping Sheet:-
www.bispsolutions.com www.bisptrainigs.com Page 6
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
7/63
Employee Source Model and Mapping Sheet:-
www.bispsolutions.com www.bisptrainigs.com Page 7
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
8/63
Store Source Model and Mapping Sheet:-
www.bispsolutions.com www.bisptrainigs.com Page 8
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
9/63
Steps to Perform Normalized TransformationStep-1 :- In Windows Click Designer Client of DataStage
Step-2 :- New Window open than click Jobs than click Parallel because we use parallel
jobs.
www.bispsolutions.com www.bisptrainigs.com Page 9
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
10/63
Load the Customer Dimension
Step-1:- First go on Database Palette than scroll the cursor and choose oracle
enterprise stage and drag them to Parallel Job and rename it.
www.bispsolutions.com www.bisptrainigs.com Page 10
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
11/63
Step-2:- Choose Join stage from processing palette this is used for making relationship
between input tables.
Step-3 :- For making connection between them we choose link from general palette.
Step-4:- Double click on oracle stage and choose directly load table from database.
Now specify your table name and credentials for accessing this table from database.
www.bispsolutions.com www.bisptrainigs.com Page 11
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
12/63
Step-5:-There's one more option here once you click on connection tab then remote
server options are popup then we have to specify our server name of oracle.
Step-6 Go to Columns tab and enter same column names as declared in Input file and
put data type, length then click on View Data. This will show your Input Data.
www.bispsolutions.com www.bisptrainigs.com Page 12
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
13/63
Step-7:- Now click on View Data and Ok. If your data is shown like this that means you
have made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
Step-8:- Now Apply same thing in customer_type table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 13
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
14/63
Step-9:- Now click on View Data and Ok. If your data is shown like this that means you
have made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
Step-10:- Now Apply same thing in customer_type table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 14
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
15/63
Step-11 :- Now click on View Data and Ok. If your data is shown like this that means
you have made successful connection between input table and DataStage otherwise it
will give some error like column mismatch error.
Step-12:- Now Apply same thing in customer_type table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 15
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
16/63
Step-13:- Now click on View Data and Ok. If your data is shown like this that meansyou have made successful connection between input table and DataStage otherwise it
will give some error like column mismatch error.
Step-14:- The Join stage is a processing stage. It performs join operations on two or
more data files input to the stage and then outputs the resulting oracle table. The Join
stage is one of three stages that join tables based on the values of key columns.
www.bispsolutions.com www.bisptrainigs.com Page 16
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
17/63
Step-15:- Now there are basically four types of joins, in this we used inner join.
Step-16:- Here we specify partition as we choose auto partition technique with the
help of this datastage automatically choose best partition technique for our job.
www.bispsolutions.com www.bisptrainigs.com Page 17
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
18/63
Step-17:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
Step-18:- This is second join we used choose key value and apply join,
www.bispsolutions.com www.bisptrainigs.com Page 18
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
19/63
Step-19:- Now there are basically four types of joins, in this we used inner join.
Step-20:- Here we specify partition as we choose auto partition technique with the
help of this datastage automatically choose best partition technique for our job.
www.bispsolutions.com www.bisptrainigs.com Page 19
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
20/63
Step-21:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
Step-22 :- Now double click on Oracle enterprise stage than it looks like it and wehave to enter our table name here in which table we want to insert our data and there
multiple options if we directly want to load data than we simple used write method as
load otherwise we manually put queries on it and also we have to specify
username/password on it this password should be matched with oracle
username/password..
www.bispsolutions.com www.bisptrainigs.com Page 20
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
21/63
Step-23:- We don't have target table that's why we choose create mode here and
apply credentials.
www.bispsolutions.com www.bisptrainigs.com Page 21
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
22/63
Step-24:- See here my server name is ORCL and username password and table name
than ensure you put correct information than you able to load the data into table..
Step-25:- Click on column tab and see all the columns are showing or not.
www.bispsolutions.com www.bisptrainigs.com Page 22
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
23/63
Step-26:- This shown like this and now click on button for compiling our job.
Step-27:- If this shown that means your compilation is done otherwise it shows error
and now click on run button or (CTRL+F5).
www.bispsolutions.com www.bisptrainigs.com Page 23
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
24/63
Step-28:- Now click on Run tab and notice some options available here. We have
selected No limit of warnings and we can validate before run the job.
Step-29:- After click on run wait for a while than it shows GREEN line that means your
transformation is successfully done otherwise if it shows RED Line that means not Done
and BLUE Line means Under Process.
www.bispsolutions.com www.bisptrainigs.com Page 24
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
25/63
Step-30:- Now Go to SQL Console and connect with the same login credentials as I
mentioned in oracle enterprise stage src_creditcard/password so you can choose your
own username and password. Before loading, remember to check structure of this file
is available and then you should be able to load the data. then simple query:
www.bispsolutions.com www.bisptrainigs.com Page 25
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
26/63
Loading Product Dimension
Step-1 :- See choose files and oracle stage from palette and now for this product
model we have to apply same process.
www.bispsolutions.com www.bisptrainigs.com Page 26
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
27/63
Step-2:- Double click on Input file and browse an input file that can be .txt, .csv any
test file.
Step-3:- Go to column tab and put all product columns and view data, if it shows like
this that means we successfully able to load data from flat file to datastage.
Step-4:- Now Apply same thing in packaging table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 27
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
28/63
Step-5:- Now click on View Data and Ok. If your data is shown like this that means you
have made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
Step-6:- Now Apply same thing in brand table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 28
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
29/63
Step-7 :- Now click on View Data and Ok. If your data is shown like this that means
you have made successful connection between input table and DataStage otherwise it
will give some error like column mismatch error.
Step-8:- Now Apply same thing in category table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 29
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
30/63
Step-9:- Now click on View Data and Ok. If your data is shown like this that means you
have made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
Step-10:- The Join stage is a processing stage. It performs join operations on two or
more data files input to the stage and then outputs the resulting oracle table. The Join
stage is one of three stages that join tables based on the values of key columns.
www.bispsolutions.com www.bisptrainigs.com Page 30
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
31/63
Step-11:- Now there are basically four types of joins, in this we used inner join.
Step-12:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
www.bispsolutions.com www.bisptrainigs.com Page 31
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
32/63
Step-13:- The Join stage is a processing stage. It performs join operations on two or
more data files input to the stage and then outputs the resulting oracle table. The Join
stage is one of three stages that join tables based on the values of key columns.
Step-14:- Now there are basically four types of joins, in this we used inner join.
www.bispsolutions.com www.bisptrainigs.com Page 32
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
33/63
Step-15:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
Step-16 :- Now double click on Oracle enterprise stage than it looks like it and we
have to enter our table name here in which table we want to insert our data and there
multiple options if we directly want to load data than we simple used write method as
load otherwise we manually put queries on it and also we have to specify
www.bispsolutions.com www.bisptrainigs.com Page 33
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
34/63
username/password on it this password should be matched with oracle
username/password..
Step-17:- We don't have target table that's why we choose create mode here and
apply credentials.
See here my server name is ORCL and username password and table name than
ensure you put correct information than you able to load the data into table..
www.bispsolutions.com www.bisptrainigs.com Page 34
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
35/63
Step-18:- Click on column tab and see all the columns are showing or not.
Step-19:- This shown like this and now click on button for compiling our job.
www.bispsolutions.com www.bisptrainigs.com Page 35
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
36/63
Step-20:- After click on run wait for a while than it shows GREEN line that means your
transformation is successfully done otherwise if it shows RED Line that means not Doneand BLUE Line means Under Process.
www.bispsolutions.com www.bisptrainigs.com Page 36
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
37/63
Step-21:- Now Go to SQL Console and connect with the same login credentials as I
mentioned in oracle enterprise stage src_creditcard/password so you can choose your
own username and password. Before loading, remember to check structure of this file
is available and then you should be able to load the data. then simple query:
www.bispsolutions.com www.bisptrainigs.com Page 37
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
38/63
Loading Supplier Dimension
Step-1 :- See choose files and oracle stage from palette and now for this suppliers
model we have to apply same process.
www.bispsolutions.com www.bisptrainigs.com Page 38
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
39/63
Step-2:- Now Apply same thing in suppliers table specify table and credentials.
Step-3:- Now click on View Data and Ok. If your data is shown like this that means you
have made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
www.bispsolutions.com www.bisptrainigs.com Page 39
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
40/63
Step-4:- Now Apply same thing in supplier_type table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 40
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
41/63
Step-5 :- Now click on View Data and Ok. If your data is shown like this that means
you have made successful connection between input table and DataStage otherwise it
will give some error like column mismatch error.
Step-6:- The Join stage is a processing stage. It performs join operations on two or
more data files input to the stage and then outputs the resulting oracle table. The Join
stage is one of three stages that join tables based on the values of key columns.
www.bispsolutions.com www.bisptrainigs.com Page 41
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
42/63
Step-7:- Now there are basically four types of joins, in this we used inner join.
Step-8:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
www.bispsolutions.com www.bisptrainigs.com Page 42
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
43/63
Step-9 :- Now double click on Oracle enterprise stage than it looks like it and we have
to enter our table name here in which table we want to insert our data and there
multiple options if we directly want to load data than we simple used write method as
load otherwise we manually put queries on it and also we have to specify
username/password on it this password should be matched with oracle
username/password..
www.bispsolutions.com www.bisptrainigs.com Page 43
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
44/63
Step-10:- We don't have target table that's why we choose create mode here and
apply credentials.
See here my server name is ORCL and username password and table name than
ensure you put correct information than you able to load the data into table..
Step-11:- Click on column tab and see all the columns are showing or not.
www.bispsolutions.com www.bisptrainigs.com Page 44
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
45/63
Step-12:- This shown like this and now click on button for compiling our job.
Step-13:- After click on run wait for a while than it shows GREEN line that means your
transformation is successfully done otherwise if it shows RED Line that means not Done
and BLUE Line means Under Process.
www.bispsolutions.com www.bisptrainigs.com Page 45
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
46/63
Step-14:- Now Go to SQL Console and connect with the same login credentials as I
mentioned in oracle enterprise stage src_creditcard/password so you can choose your
own username and password. Before loading, remember to check structure of this file
is available and then you should be able to load the data. then simple query:
www.bispsolutions.com www.bisptrainigs.com Page 46
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
47/63
Loading Employee Dimension
Step-1 :- See choose files and oracle stage from palette and now for this employees
model we have to apply same process.
Step-2:- Now Apply same thing in employee table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 47
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
48/63
Step-3:- Now click on View Data and Ok. If your data is shown like this that means youhave made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
Step-4:- Now Apply same thing in department table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 48
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
49/63
Step-5 :- Now click on View Data and Ok. If your data is shown like this that means
you have made successful connection between input table and DataStage otherwise it
will give some error like column mismatch error.
www.bispsolutions.com www.bisptrainigs.com Page 49
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
50/63
Step-6:- The Join stage is a processing stage. It performs join operations on two or
more data files input to the stage and then outputs the resulting oracle table. The Join
stage is one of three stages that join tables based on the values of key columns.
Step-7:- Now there are basically four types of joins, in this we used inner join.
www.bispsolutions.com www.bisptrainigs.com Page 50
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
51/63
Step-8:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
Step-9 :- Now double click on Oracle enterprise stage than it looks like it and we have
to enter our table name here in which table we want to insert our data and there
multiple options if we directly want to load data than we simple used write method as
load otherwise we manually put queries on it and also we have to specify
username/password on it this password should be matched with oracle
username/password..
www.bispsolutions.com www.bisptrainigs.com Page 51
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
52/63
Step-10:- We don't have target table that's why we choose create mode here and
apply credentials.
See here my server name is ORCL and username password and table name than
ensure you put correct information than you able to load the data into table..
www.bispsolutions.com www.bisptrainigs.com Page 52
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
53/63
Step-11:- Click on column tab and see all the columns are showing or not.
Step-12:- This shown like this and now click on button for compiling our job.
www.bispsolutions.com www.bisptrainigs.com Page 53
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
54/63
Step-13:- After click on run wait for a while than it shows GREEN line that means your
transformation is successfully done otherwise if it shows RED Line that means not Done
and BLUE Line means Under Process.
Step-14:- Now Go to SQL Console and connect with the same login credentials as I
mentioned in oracle enterprise stage src_creditcard/password so you can choose your
own username and password. Before loading, remember to check structure of this file
is available and then you should be able to load the data. then simple query:
www.bispsolutions.com www.bisptrainigs.com Page 54
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
55/63
Loading Store Dimension
Step-1 :- See choose files and oracle stage from palette and now for this store model
we have to apply same process.
Step-2:- Now Apply same thing in store table specify table and credentials.
www.bispsolutions.com www.bisptrainigs.com Page 55
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
56/63
Step-3:- Now click on View Data and Ok. If your data is shown like this that means you
have made successful connection between input table and DataStage otherwise it will
give some error like column mismatch error.
www.bispsolutions.com www.bisptrainigs.com Page 56
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
57/63
Step-4:- Now Apply same thing in store_region table specify table and credentials.
Step-5 :- Now click on View Data and Ok. If your data is shown like this that means
you have made successful connection between input table and DataStage otherwise it
will give some error like column mismatch error.
www.bispsolutions.com www.bisptrainigs.com Page 57
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
58/63
Step-6:- The Join stage is a processing stage. It performs join operations on two or
more data files input to the stage and then outputs the resulting oracle table. The Join
stage is one of three stages that join tables based on the values of key columns.
www.bispsolutions.com www.bisptrainigs.com Page 58
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
59/63
Step-7:- Now there are basically four types of joins, in this we used inner join.
Step-8:- Now Go to output tab and create mapping, select all columns and drag and
drop in output space.
www.bispsolutions.com www.bisptrainigs.com Page 59
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
60/63
Step-9 :- Now double click on Oracle enterprise stage than it looks like it and we have
to enter our table name here in which table we want to insert our data and there
multiple options if we directly want to load data than we simple used write method as
load otherwise we manually put queries on it and also we have to specify
username/password on it this password should be matched with oracle
username/password..
Step-10:- We don't have target table that's why we choose create mode here and
apply credentials.
See here my server name is ORCL and username password and table name than
ensure you put correct information than you able to load the data into table..
www.bispsolutions.com www.bisptrainigs.com Page 60
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
61/63
Step-11:- Click on column tab and see all the columns are showing or not.
Step-12:- This shown like this and now click on button for compiling our job.
www.bispsolutions.com www.bisptrainigs.com Page 61
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
62/63
Step-13:- After click on run wait for a while than it shows GREEN line that means your
transformation is successfully done otherwise if it shows RED Line that means not Done
and BLUE Line means Under Process.
Step-14:- Now Go to SQL Console and connect with the same login credentials as I
mentioned in oracle enterprise stage src_creditcard/password so you can choose your
own username and password. Before loading, remember to check structure of this file
is available and then you should be able to load the data. then simple query:
www.bispsolutions.com www.bisptrainigs.com Page 62
http://www.bispsolutions.com/http://www.bisptrainigs.com/http://www.bispsolutions.com/http://www.bisptrainigs.com/ -
7/30/2019 Normailzed Tranformation
63/63