BISP is committed to provide BEST learning material to the beginners and advance learners . In the same series , we have prepared a complete end-to en...
BISP is committed to provide BEST learning material to the beginners and advance learners. In the same series, we have prepared a complete end-to end Hands-on Beginner’s Guide for Oracle Data Int...
BISP is committed to provide BEST learning material to the beginners and advance learners . In the same series , we have prepared a complete end-to end Hands-on guide for building financial data mo...
Percobaan Integrator menggunakan Op-Amp 741Full description
Full description
Deskripsi lengkap
functionFull description
INVERTINGFull description
rangkaian integratorFull description
LaporanFull description
LaporanDeskripsi lengkap
sp 16
Integrator DifferensiatorFull description
okeDeskripsi lengkap
Data Migration is a procedure where data is sent to and from in between computer storages. It is usable in making a new system or enhancing an existing one. Data Migration is where data gets derived from an old system and infused into a new system. C
Oracle Exadata
The Transparent Data Encryption feature was announced in Oracle 10g Database Release 2 to simplify the encryption of data within datafiles, tables stopping access to it from the operating system. Tablespace encryption extends this technology, allowin
ora
OracleFull description
oracle
Description complète
Oracle Data Integrator 11g Transformation Example Using Aggregate Function
Delivery By :
Description: BISP is committed to provide BEST learning material to the beginners beginners and advance learners . In the same series , we have have prepared a complete end-to end Hands-on Hands-on guide for building financial data model in Oracle Data Integrator.The document focuses on how the real world requirement should be interpreted. The mapping document template with very simplified steps and screen shots makes the complete learning so Easy . The document focuses on how to calculate the aggregate function .Join our professional training program and learn from experts.
S.NO
TITLE
SLIDE NO.
1
Introduction
4
2
Data Model
5
3
ETL Mapping
10
4
Database Connectivity
13
5
Bringing Metadata
19
6
Creating Interface
23
7
Required Output
28
8
Q&A
30
Introduction Calculate Aggregate Aggregate Function in Target Target in Oracle Data Integrator .
In this example, we calculate aggregate function in Target . There are following Step to follow to calculate aggregate function in Oracle Data Integrator. Create master and work repository in Oracle Data Integrator. Import source database and Target table . create mapping and drag and drop source and target table into mapping designer window. Establish a connection of source and target technology with that of particular database. Create a model and Reverse Engineering the datastore according to the technology. Create new interface inside project and then come to mapping tab and make a mapping mapping between source and target datastores. Then create aggregate transformation to group by column. Then come to flow tab and define the knowledge module based on source and target.
Below Is the Data Model of GoSales
Source Table Structure This is my Table Table Structure for the source table t able ORDER_DETAILS in oracle database.. database
Source Data
Target Table Table Structure Below is the Table structure of the TRG_OR TRG_ORDER_DE DER_DET TAILS target table in oracle database.
TARGET DATA DATA
What is ETL Mapping document ? ETL mapping Documents contains source , staging area , target and the Business Rules Information and it is most important document for the developer . And Below is my Mapping Document which contains Source File Name Source Field Name Source Field Data Type Source Field Length Source Field Description Business Rule Staging Area Target Table Name Target Field Name Target Field Data Type
BEFOREMAPPING_ORDERDETAIL.xlsx AFTERMAPPING_ORDERDETAIL.xlsx After Mapping we have to Review from the EndUser and then sign off From them.
Source
: Used Oracle as my Source Database.
Staging Target
Area : Used oracle as my Staging Area.
: Used Oracle as my Target Database.
Staging Area:
A place where data is processed before entering the warehouse. Area is temporary location where data from source systems is Staging Area copied. A staging area is mainly required in a Data Warehousing Architecture for timing reasons. In short, all required data must be available available before data can be integrated integrated into the Data Warehouse Warehouse Here
I have used Scott schema as staging table and you have to define staging table inside the physical schema at the time when you are establishing connection between oracle database and oracle data integrator.
DATABASE CONNECTIVITY IN ORACLE DATA INTEGRATOR
Now I have to establish a connection between oracle and ODI .For establishing A Connection come to the Topology Navigator .select Oracle as Technology Technology ,Right Click Click oracle oracle and select New DataServer .In this this we have to provide value to Name , Instance , Dba UserName , Password Password and JDBC Driver.
This will establish a connection between oracle oracle and ODI
And Now Right click DataServer and select Physical schema . Here you have To fill information like Schema and work Schema.
After creating Physical Schema in DataServer which helps in establishing connection With a particular schema , Now Create a context like Development , production and Testing Testing Which helps to make a mapping between physical physical and logical Architecture.
How to create context ? Just click on the context tab tab and then on the Right side of context tab you will find new Context options ,select that “New Context” and create a context for Development ,Production and Testing and If anything else is there than create that one also.
After Context create a Logical Schema for Oracle . How to create Logical Schema ? Click on Logical Architecture ,Then select oracle technology .Right Click oracle and Select logical schema and provide information as shown in below diagram.
HOW TO BRING METADATA FROM DATABASE TO ORACLE DATA INTEGRATOR
After establishing a connection connection between oracle and ODI ,go to the Designer Navigator Here you have to bring the metadata of customertrans datastore .For this you have to create Model folder and sub model for organizing datastore .Right click LOAD_ORACLE ,
And Select New Model and fill all details provided in previous previous sheet and and then select Reverse Engineering Button ,It will bring all the datastore available in the sales schema
After bringing bringing metadata from particular particular datastore on the the model tab , click on the Project tab and here her e we have to apply the Required Business Rules using interfaces How to implement business rule in project tab ? Go to the Project tab , Expand first folder f older .Here you will find interface . Right click interface and select new interface .It will open interface window , here you will provide Name of the Interface , Optimization context . Now click on the mapping tab . Here you will get 2 window one for fo r the source and other othe r for target like I am showing in the next Screen .
CREATING INTERFACE IN ODI
Here I have inactivate all the columns which I don‟t required by just click Particular column it will open property window and there uncheck Active Mapping.
Click on the flow tab tab there you have to define required required knowledge module and Make the option true or false depend on your requirement and then save and execute.By default staging area is in the target
Below is the background background code executed by odi for Aggregate Function min using group by clause.
REQUIRED OUTPUT
After successfully executing the interface interface select again target target interface , Right Right Click target interface and select „data‟ .It will show the required output.
