Oracle Data Integrator(ODI) 12C Developer Cource | Saad Qureshi | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Oracle Data Integrator(ODI) 12C Developer Cource

teacher avatar Saad Qureshi, Online Instructor

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

51 Lessons (10h 7m)
    • 1. Introduction

      1:33
    • 2. ODI Architecture

      5:35
    • 3. JDK 8 Installation

      5:13
    • 4. ODI 12C Installation

      9:12
    • 5. Oracle Database 12C Installation Steps

      8:22
    • 6. Oracle SQL Developer

      5:21
    • 7. ODI REPOSITORY

      5:21
    • 8. Creating Master Repository

      9:42
    • 9. Establishing Connection and Creating Work Repository

      19:57
    • 10. Topology:Dataserver, Physical and Logical Schema creation in ODI

      22:06
    • 11. Designer:Model,Datastore & Mappings in ODI

      11:01
    • 12. Designer:Mappings,Procedures & Packages Overview

      14:04
    • 13. Oracle Data Integrator Knowledge Modules LKM & IKM

      19:05
    • 14. Oracle Data Integrator Knowledge Modules LKM & IKM Part-02

      15:16
    • 15. ODI Mapping: Loading Data from Flat File (csv) to Table

      17:24
    • 16. CKM with FlowControl, Static Control in mapping

      22:27
    • 17. JKM Journalization: CDC Simple Implementation

      19:11
    • 18. EXPRESSION Transformation

      13:02
    • 19. Join Transformation,Distinct,Filter,Sort

      19:42
    • 20. ODI Split Component

      9:58
    • 21. ODI Set component

      9:13
    • 22. ODI Lookup Component

      26:01
    • 23. ODI Aggregate Component

      12:16
    • 24. Pivot ODI Component

      8:28
    • 25. Unpivot ODI Component

      4:14
    • 26. ODI Procedures

      16:18
    • 27. ODI Variables

      12:10
    • 28. Sequence in ODI Part-01

      8:04
    • 29. Sequence in ODI Part-02

      9:34
    • 30. Procedures with Sequence

      12:56
    • 31. Reuseable Mapping

      22:35
    • 32. Lock,Version in ODI

      7:05
    • 33. Scenario in ODI

      5:43
    • 34. Packages in ODI Part-01

      21:55
    • 35. Packages in ODI Part-02

      9:51
    • 36. Packages in ODI Part-03

      10:50
    • 37. Packages in ODI Part-04

      7:01
    • 38. Packages in ODI Part-05

      11:58
    • 39. Packages in ODI Part-06

      9:48
    • 40. Packages in ODI Part-07

      7:35
    • 41. Variable In Package

      11:09
    • 42. Declare Variable

      13:11
    • 43. Packages in ODI Part-08

      13:48
    • 44. Packages in ODI Part-09

      4:54
    • 45. SCD Type-01 Implementation in ODI

      6:01
    • 46. SCD Type-02 Implementation in ODI

      10:02
    • 47. Creating Master and Work Repository with RCU

      11:40
    • 48. ODI Agent

      15:31
    • 49. Standalone Agent and J2EE Agent Configuration

      10:09
    • 50. How to Start Standalone Agent

      3:49
    • 51. Load Plan

      19:19
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

30

Students

--

Projects

About This Class

What is Oracle Data Integrator (ODI) 12C?

Oracle Data Integrator 12C (ODI) is an extract,load and transform (ELT) (in contrast with the ETL common approach) tool produced by Oracle that offers a graphical environment to build, manage and maintain data integration processes in business intelligence systems.

What are we learning in the course?

  • ODI 12C Architecture and Installation Steps

  • ODI 12C Repositories

  • ODI 12C Components

  • Knowledge Modules with Examples

  • Procedures,Variables,Reusable Mapping,Scenarios and Sequence

  • ODI 12C Packages

  • SCD Implementation in ODI

  • ODI Agent and Load Plan

  • ODI Security,Profile and User Creation

Career Perspective:

If you want to pursue a career as an ETL developer,you should definitely consider taking this course.Most importantly,If you learn one ETL tool whether it's ODI 12C or Informatica,learning other ETL tools won't be difficult.Also,the demand of ETL developer is keep on increasing as the data size is increasing. 

After this Course:

Once you are done with the course,you will have maximum knowledge of ODI 12C concepts that you can implement in the industry.

Cheers..!!

Have a Great Learning..!!!

Meet Your Teacher

Teacher Profile Image

Saad Qureshi

Online Instructor

Teacher

Background:

Computer Scientist with overall 5 years of industry and several years of freelancing experience. Other than this,I am passionate about teaching and guiding students learning programming languages.

Life Philosophy

Do not dwell in the past,do not dream of the future,concentrate the mind on the present moment.

Service to others is the rent you pay for your room here on earth..

 

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: welcome to my course on article attentive greater to Elsie. It is any kill tool produced by article, get offers a graphical environment, toe build, manage and maintain that integration processes in business intelligence systems. So what are we learning in the course? So we'll start with, oh, day architecture and installation steps. So once they're done with the installation steps, will will start with these components designer operators, security, anthropology. So first we're going to go in the topology section, will create that'sa rules, then the logical architectures. After this section, we are going to go indeed, designer section here. We're going to create a new project. So once you're going to create a new project, you will get a new folder with name First Folder inside this, although you will get packages map ings, reusable map, Ing's procedures will discuss all these components. So in the later part of the course will discuss lured plans scenarios. We learn how to create an agent. Furthermore, we're going to learn how to implement a city type one a city type zero, to in order here also, the important thing is we're going to learn the concept off change data captured. So this is going to be a comprehensive Corson or D. I 2. ODI Architecture: Hello, everyone. In this lecture of your going to discuss article, they can take greater to Elsie in its architecture. So what is article that integrator too? Well, See, it is an extract Lord and transform tool produced by article that off first, a graphical environment toe buried, manage and maintain data integration processes in business intelligence systems. So the sister traditional ideal approach that we follow. And this is the OD approach. In the traditional ideal approach that I've extracted from multiple heterogeneous sources, it is transformed in a separate server. This is the separate it'll server where that is transformed, then monster that is transformed. It is loaded into a target date of Eros. Okay, desisted traditional detail approach in the traditional detail approves. We have a separate ET al server to transform the data, and this is the OD approach. In the ODI approach data is extracted in the target date of their house in the target that our house that not only data is extracted, but it is transformed their in the target that of their health. But when it comes to this traditional approach that is transformed in a separate several here that ice transformed indie targets of this is the targets of moving that is transformed. So here you will have the separate temporary tables were that I will be transformed so monster data is transformed. Then it is loaded in a separate database which is located here in this particular service. Okay, in this particular server, you will have temporary tables where that I will be transformed. Also, you will have tables were d transformed that I will be stored. Okay, this is the OD approach. Now let's look at the architecture off. Oh dear. So this is the article that integrator to Elsie Architecture. This are OD. Architecture has 40 years. The first year is deck stop. Second is VEB logic or application server thirties repositories for 20th sourcing targets. So this is the first tier deck stop. So here in the first year you will have Woody a studio O'Day's todo for there s four components designer operator, topology, security. We will discuss all four components. Okay, along with odious to deal, you will have Java globally application. Also you will have or d a s digging. So, using this odious decay user can automate tasks, work flows accelerate or delay implementations. So this is the deck stop. The second component is VEB Logic Server in the web Logic several, you will have Jabbar Jato, doubly agent which will be located in the vab logic several. You have to start the Java agent through the server. Okay, then you will have the OD a console or D A s T gay Javert ability application. So, Javert verbally application and od if decays also present Aaron the Web logic sever Okay, A didn't diss components. You will have Web services, containers, data services. So the services are used for in walking any OD A jobs. So then you have the repository. So there are two types of repositories Master repository and the work repository. So in this particular course, you will have the details session on this repositories. So you will learn how to create mustard and worked repository. So also you will learn to create master and worked repository. True geotel ity are See you through our see you last but not least, you will have source and target so you can integrate or d A with multiple databases like you can integrate it with legacy systems files application er piece multiple databases so or gay gives you the flexibility off integrating or davit multiple databases. Then you will have the standalone agent. This stand alone agent can be configured anywhere. It can be configured your local system, or it can be configured in any database. So you will have the comprehensive session on this agent. But right now I'm skipping this concept off agent. So in this particular course, the majority section is on this odious to do it. If designer operator, topology and security 99% of the courses on this topic ordered a studio the rest like Web Logic server, you don't use this very often. Okay? You will also learn how to create reported trees. Master involved Repository. Also, you will integrate Woody A with multiple databases like you can integrate it with files looking integrated with article database. Also in the court 3. JDK 8 Installation: Hello. Everyone in this particular section will be learning how to download and install articles. They're trying to get her to Elsie on your computer system. So the first step is to download and install Do you DK in your computer system? So, first off, all I'm going toe open the Google Chrome browser here. I will in the search but I will write down Lord do DK 64 bit So first I will download do decay 64 back So Java Sea Development kit It selected this link So this is the first step off installing ah article that I integrator 12 c Okay, okay Java etc development kit Okay, except license agreement Get to this Windows 64 bit So my machine is 64 bit So I will select this particular file eggs if I live down Lord, this exit file So look so first I will have to log in Okay I will have to provide my past third here Look download process has been started four minutes It will take some time about 5 to 6 minutes. Eso I have already downloaded this file in my system so I will cancel the download process so can sell this. Ah, here in the down lords for that I have already downloaded it. So J d K let me say the eggs if I So this is defiled. Okay. Click to display. Yes, Yes. No eyes already installed, please Uninstalled adjudicate and run the installer again. So Okay, I have to first uninstall this uninstall. Do you DK Okay, this I have to first uninstall this. Yes. Okay, now I'm going to installed this do two gay again? No. Let me show you how I can install this click. Just Burton De So let me just delivered this, okay? It has been deleted. Okay, Next preparing to install change the director. I want to install it in my gay directory. De, do you DK it? Okay, okay. Look. Installed toe district trick click. Next Good. Remove. So the installation process is I know where. Okay, it's successfully in start. Next step. Okay. Glynor excess tutorials. AP documentation Developer Guards, if you warrant to access tutorials, documentation, you conflicto this next step. So it has Ah, Java Sidwell print kit. It's successfully installed. Now I'm going to close this. Okay, not control panel uninstall Let me show you. It didn't start. Look. Okay, I have installed in my gig directory Java it name is this. It has been installed. So this is the first step off installing article that are integrated. Well, see. So in the next lecture, we will learn how to install article that I integrator 12 Celia, Computer system. First step is done invested. Okay. Thank you so much and have a great learning. 4. ODI 12C Installation: Hello everyone In this lecture we're going to learn how to download and install article Gotye integrator 12 c So you have to follow these steps so the first step is done. Industri installed Adjudicate. We have already installed DJ Decay in my system. Okay, second step is download Oracle that integrator quells So this is my third step for 50 six and say OK, so the second step is Don't know the article that I integrator quality. So I'm going toe open the Google hearing the search bar I will write down Lord article They're trying to greater 12 c Okay, Article Titantic, Greater download. So I will click this link. Okay, so this is the latest version off Oracle that integrator. So in my system I have this version. So if you want, you can down your decision. Um so in my system, I have destroyed, so let me just show you. Ah, this article that turned dictator Well, see? Okay, so I have already downloaded dispersion directory downloads or D I installation. Okay, so it has two files. These two files Disc one in this grand I have two files. This is discord. Okay. In this too I have only one file. Okay, so if you want, you can download this version de later. So you so I'm not going to download. Ah, this brilliant since I already have it. So now next step is open the command prompt as administrators. So such command prompt. Ah, run as administrator. Yes. Okay, now I'm going to open or the installation. Okay, these to file. So next step is copy. Did this to filing toe disc one for the okay in this too. Look, this this stoop, I have only one file. Just copy this and based here. Okay, so now I'm going to have three files now. I have already corporate. They said in the directory. So look, look, This is my fault Compete folder in which I have all these files. Look, or did George are fighting? This is the desk one filed these to our discount files. Okay, this one is just too far, so I have complete files in my folder. Now Open the environment. Variable and check. Drove. Underscore. Home directory. Okay, now here in the search. But I will cert environment a good this system environment, credible environment, variables. Okay, here I will search Java underscore home. So this is the directory where my J d case present in this particular directory Just copy this. Let me just show you deejay Dick. So look, the city directory off Jay Dickey. Okay? No, what I'm going to do. Okay, go toe d j d k Part from the command from CD, Then depart. So this is the part off my Dogg enter. So first I have to go Toe de direct. Okay, CD spoken. Now, look. So this is the part. So now goto the part off jacket could not execute the command jabber dark jar. Then the part of fight. So the part of finalists like me to show you the part of file E directory Here, look. So this is the 1st 1 or D a dark yard. So first ever write java dark jod Dendy. But so you have to click this, then press shift button shift button. Then press the right click right click. Okay. Copy s part. Okay. So you have to just base the party. Okay? Now remove this press. Enter extracting the installer, so this may take a few minutes to open up the installer window. - So Now you have to click the next button. Okay, Our top gate. Skip these update. Click next. So installation location. So I don't want to install in my city directory. I will change this. So I will install in my e directory. So now let me just give the part here he directory. Let's make a four live here or a good well see. Just copied apart. Place the part here. Okay. Article. Well, see. Okay. So this is the part where this article they're trying to greater twelves. He's going to install. Okay, Click next. Okay. Stand alone installations in the Enterprise installation. I will select the enterprise installation. So look, with enterprise installation, I will get these components. I'm not explaining the difference between stand alone and enterprise installation. Just follow the steps. Okay. Click. Next music. Requisite ticks. Okay, click. Next installation. Okay, Somebody in stall. So the installation process is started, so the installation process is 100 person computer. Look, all tick marks. Okay, so now I will click next Burton, Click next button here. Okay. Installation complete. Okay. Click finish. So now let me just show you the folder where the oracle. Ah, well, See article. Attentive critical season story, Article 12 So this is the folder where I have installed Oracle attempted radicals, and now I'm going to open the Ode EI Studio or D I or D a studio click to this or the ace to deal or did a studio, so look. 5. Oracle Database 12C Installation Steps: Hello, everyone. In this lecture, I'm going to show you how to download and install article database to Elsie on your computer system. So for discourse will be using Oracle database to Elsie. So first off, all I'm going to open the Google search engine. So in the Google search engine, I will write Oracle database. Well, sit down. So I will click toe this particular link Oracle database to Elsie released to. So since my machine is 64 but I will collect Oh, this particular leg. So if you have 32 big machine, you will have to download Oracle database eleventy because Oracle database to Elsie version is not present for 30. Dupe it machine. So you will have to down Lord 11. Gee, Okay, so I will take tow this link. So? So next step is to provide the user name and password, so I will provide my user name and password here. If you do not have an account, you can create it from here. Okay, create account. So signing. So let me just provide the user name and password again. Look, download process has been started. So since I already have this in my computer system, so I will cancel. This can sell this. So now I'm going to show you where this vilest present. Okay, He Derek creep. It is. Look. So this is defied. Okay, Database, this is the exit file. So now, in order to install Oracle database 12 c, I will click to this particular eggs if I lick. Yes. So it will take some time to open up the installer window. So here you will provide the email address if you want to receive security update so you can provide the email address here. But I'm not going to provide my email address, sir, because I don't want the security updates, so I will skip this step click next to skip this step. So you haven't provided an email address? Do you wish to remain uninformed? Okay, guess. Click guests. So I want to skip this step. So have played Yes. No. Here. You goingto have three different options. Create and configure database. I was select the first option. This will automatically create and configured the database. Okay, I'm not going to select these option. Just ignore these options. Click next. So here you goingto have two different versions deck Stop. And so? So since I'm going to install the oracle on my deck Stop. So I will choose the first option. Look, installing on laptop or deck Stop. So you have to select the first option. If you're installing on a laptop are Dexter So click next. Look here. I'm going toe Select this option. I will create a new windows user. So if you want to use the existing user you can select this option. But I will create a new user. So my new user name will be, but it call home. What home? Well, see who are home to l c Passer. I will give eggman eggman so I will save this so later in case I'm required to use this user name either user. Okay, So blood home Well, see, pastored is eggman. Click next. Okay, Normally has been given. Okay, cancer. So article based. So these are the directories in this particular directory article. Get the best one c will be installed. Okay, So look, the director in my directory. So if you want to change your directory, you want to change it from here. Okay? So if you want any other directory where you want to install this database? You can change it from here. That investigation is Enterprise Edition 60 B. Okay, leave this as default. Global database name? Let's say what who are then? The article database or D B? Well, see, Bossert is eggman, so I will say Bastard As eggman eggman. Confirm password. Click Next click Next. Okay, Must enter. Does not confirm toe the Oracle recommended standards. I haven't said my password as recommended by article. So I will ignore this message. So has set very simple passers which is eggman So I just want to ignore this message. I want to set my password as eggman lead. Yes, Okay. Prerequisite checks. So it is performing Prerequisite checks Checking memory physical memory size. Okay, these are some prerequisite checks. Okay. Eso this is this summary. Okay, click next installed click install. So installation process has been started. It will take some time. In my case, it took around 40 to 45 minutes. So you have to wait for a wife So the installation process is completed. Looked All the processes are done in the state. So next I'm going to click next button. Click next So click close. No. Next step is let me show you the service Nen, which we have sex search every in the search bar services. So the service name will be Look, Oracle. Look, de that the process is better up and running. Look, Oracle Service or D B 20. This is how you install article database to Elsie in your computer system. In the next lecture, we're going to learn how to use SQL developer client, So thank you so much and have a great lightning. 6. Oracle SQL Developer: Hello, everyone. In this lecture, we're going to learn how to download and install Oracle SQL developer Oracle. Estelle developer is a client tool in which we're going to write SQL queries. Okay, but first off, all I'm going to open the chrome browser here. I will write down Lord. Article esque really developed. So I will click toe dazzling oracle SQL Developer downloads Click care. Okay. Ah, Windows 64 back with Dedicate eight included. So click down Lord, except download SQL Developer, click the Dislike. So I have to first give my pastor there signing. Look, download process has been started. So since I already have this in my computer system so I will cancel the download process can sell it. Let me show you were this still develop arrest present? He did a creep. Look, ask your developer. Look, I already have it in my system Now what I'm going to do, I'm going to just click a stroll Developer Dart exit file, click SQL developer dot Eggs If I d still open upper do you interface. Okay, so meanwhile, I'm going to check services Article services service If article so Oracle Services Look Deezer all article services. This okay? All article services, Oracle Services is are up and running. Okay, they're up and running now. Next step this to have a new connection. It's a new connection. Click to this plus button. Okay. Now give a nice name to this new connection. Oracle connection is already president, so I'm going to give article people and okay, you the name I will give system system is the default user name. So system with the user name default you the name which already existed article Get a bit. Okay. The password I have given the passer admitting. So I'm going to give the passer eggman. Okay, Vile. Installing the article database to lt I have I had said the password admin. So I will give the passer eggman here. Okay. Whose name is local host? Port number 15 to 1. So I'm going to show you how I can confirm this board number. So here in the d directory, I will go app. So this is the directory where the oracle database to l. C is installed or d home dardick. So give me home necked work. Okay. Here. I will go install. No, here I will go. Eggman, eggman DNS names. Dark aura. I will open this file addict with north packed look. Service name is or D B 12 c. So this is my service name or Gaby? Well, see, in the port number is 15 to 1. So it is confirmed that my port numbers 15 to 1 closed their style. No. Okay. System is the default user name? Yes. Okay. Status. Success. Okay, connect password eggman. You will get the passer eggman. Hair click. Ok, look. So the connection has been established. So this is how you don't look and established the database connection? Thank you so much and have a great learning. 7. ODI REPOSITORY: everyone in this lecture, we're going to understand the concept off repositories in article that integrator to Elsie . So what is the concept off the repository? So basically, the purpose of creating a repository is to maintain the information off different components that are available in article that 90 greater 12 c So, therefore, components security designer operate apology. Okay, so let me just open a note by there. So here I'm going to write a definition off the repository forward if or d A repository for the A depository is database schema and the database schema. Did this used to maintain Menkin The information off? Different component data are available in for D A. The water these companies so therefore component First component is security designer security designer Security operator Anthropology Anthropologie So therefore component designer operator Security anthropology. So now let's understand. Different types off repositories, types off repositories. There are two types off repositories Master repository in the work repository, some master repository ville maintain the information off these two components topology and security. Okay, so the 1st 1 is master repository. Some master deposit reveal maintained the information off apology and security The second type off repository is so this is the first type. Let me just write the first type here And this is the second time. The second type is work repository repository. So we didn't work. Repository, There are two types. So there two types within work repository Execution work repository end development work repository So execution work repository. The 2nd 1 is development work repository work repository some master deposit, Trivial mint and the information off these two components Various work repository will maintained De information off designer and operator. Okay, These two components Just copy this based here these two components. So what is then? What is the difference between execution work repository and development Work reported tree So execution work reported tree will only maintain effective execution work repository It will only maintain the information off operator. It will only maintain the information off operated various when it comes to development work repository April, Maintain the information off designer An operator Ok, boot designer and both. So this is the difference between these two. Okay, let me just be is too so the first I place Master repository So Master Repository will maintain security. So the security anthropology Let me just drag your okay. Security Anthropology. So these two components belong toe master repository. So when it comes to World repository, designer and operator, the information off these two components will be maintained by vertical positive. If it is execution vert repository, it will only maintain the operator information. But if it is development work, repository equal, maintain boat designer an operator. 8. Creating Master Repository: Hello, everyone. So we're done with the concept off or the air repositories. So in this particular lecture, we're going to learn how to create master and work repository. First, we're going to learn how to create a master repository. So in orderto create a master repository, first of all, I'm going to establish the database connection. Now, in order to establish database connection, I will click plus Burton. Okay. Connection name. So let's say my connection name is Ah, master underscore connection. So this is the name off connection? You the name? I will provide sis as Cisse db if my user name. So this is the default. You the name. So the password, which I'm going to give Edmund So admitted the password which I said violence stalling the article database. Okay, now host name local. Oh, sport number 15 to 1 service name. So service name is or D beat. Well, see, so in orderto confirm the service name, I will go in the directly where my Oracle databases installed the AP home product. Okay, get a best home network. Ed Men, DNS names Dark. Tora. Let me just open this side. So the service name is who are gave it. Well, sea port number is 15 to 1. Who stays local lost. Okay, now close this. So look. Okay. Service name or D B 12 c now test connection. Success status, success connect. So the connection has been established, so connection is master underscore Connection. Okay, it is connected. No. Now the next step is to create two new users. One do there will be for master repository in another user will before work repository. So first we're going to create and new you therefore master repository So I will execute this command Let me just copy paste the Kamar Just be his tear. Okay, create user you the name? Let's say my user name is master 2030 is the user name Bastard Edmund One to treat the passer. Okay, now let me just execute disc amount Executor Dis command So I'm getting this error. So if you're getting this saddle you will have to execute disc amount first. Okay? No, in orderto I decided I will execute the first command alter session Said equals two article scripted close to true. Okay, now I'll be able to execute disc Amand. No, I can easily create a user with this command. Look, master 2030 you there have been created. Next step is to provide the DB privileges. So a master repository user should have DDB access db A privileges. So I will Grant db writes toe this user No, Let me just paste the user name here. Okay. John succeeded. So this is how you create a user. Next step is to create another user for work repository. So I'm creating another user for work Repository work 2030 is my user Okay? Work now It's a cute okay Work repository user has been created. No, execute this tomorrow. Okay? Privileges have been given toe disuse. Now next step is to create a master repository. So in order to create a master repository first off all, I will go in deep or the ace to get well. See here I will go in the file. New. Okay. Create a new master repository. No, I will create a new master repository. No technology. I'm going to select Oracle Since I'm using Oracle database, you are in local host Who's name is local. Oh, sport number is 15 to 1 Service name is Org beat. Well, see, No, let's just give Dicle. Whose name? Local host board number Dendy Service name or Gaby. Well, see, So you can also write like this. Okay, I'm using this format. User name. So you the name is this work? 2030 d mustered 2030. The user Bossert is eggman. 123 eggman 123 d b a you that IHS fish Assis dbs Asus Asus db is duty fort you the name off article. Okay, Password Edmund is deposited. Test connection. Okay, 6/5. Next supervisor is de bastard. Okay, Supervisor. So this is the order A user supervisors or d A user so you can create order a user from the steps Security tab. I'm setting the bastard as supervisor. Okay, Next. So I'm selecting the first option. Click finish Odious. Creating your master repository. Look at these logs. Odiah has started creating the master repository. Now what I'm going to do, I'm going to log in tow this Ah use it! Okay, in order to log in tow this user plus sign Okay, Master User, this is Master User. No, you The name is Master during Teeter t. So this is the user name password Is this admin? Ventilatory Ed Ming Verne to three. Okay, local host 15 to 1. Service name or db. Well see Best success connect. So look, it is creating your master repository. Look, these are the logs, So look So this so I'm connected toe Ah, this use of master Repository user So now basically it is creating tables. It is creating tables where ah, get off! Look Masters repository is responsible for storing Get off these two components Security anthropology So basically article the tank greater is creating tables select star from So it looked like me Just give you look Tables are creating. It starts, It s and B okay, started SNP underscored No, let's look at the count off this Look, it is increasing Count 74 tables Okay, it is creating. So look, master Repository has been successfully created. So the message is you cannot connect to the master repository and create a work repository India Topology, Navigator under the repository. Okay, click. Ok, so look, Master repository creation is successful. So in the next lecture, we're going to look at how to create work repository. So thank you so much and have a great learning 9. Establishing Connection and Creating Work Repository: Hello, everyone. So in the previous lecture we created Master Report Victory. So, in this particular lecture first we're going to establish connection with the master repository user. Then afterwards we're going toe create work repository. So first off, all I'm going to open the oracle the Tangka graters todo 12 c. So, in order to establish the connection, click file new. Okay, create a new order. A repository log in Afflicted this. So I have set my volatile address. Bolic buffered. Okay, so I will give DiVall IQ password admin. 123 So this is depart furred, which I said Okay, maybe you wouldn't get this in the wallet. Pastored screen. Ok, ok. Okay. Repository connection information. So in this particular section Oracle Data Integrator connection, I will provide a the article information. So logging name can be anything. Okay, lets a master underscore logging. So this is the log in name. So it can be anything. You the name. So you the name will be supervisor. So I said article that integrator user name Supervisor white at creating the master repositories supervisor will be my user for this is the article or d A to L. C. user. Okay, Now pastored a supervisor. Suppose third will be small letters supervisor user name Oracle that integrator you The name is Supervisor. So this section will be database connection. You the name you the name will be deep Master User. Okay, now let me just logging toe this user muster Wit ah must look it This is the user mastering disco you pass it is eggman. 123 Okay, fine Death connect. So this is the master user. So in the master user how I have do tables Look, these are our tables created by OD A vile creating the master repositories for total I'm going tohave select. Let's count the number of table Select count, I guess 74 tables Deb total 74 table. Ok, so the user name is this Master Frankie coated the user name password is admin Montel Tree So eggman 123 Driver list Oracle J D B C Driver You are ill. Okay, local Hoeft 15 to 1 is deport number, then the service name which is or depict. Well, see it now I'm going to select the first option. This will only create Master re positive test connection success. Click OK, Log in. Enter Different log in name. Ah, log in Name. Okay, Log in. Name Master Underscore. Log in 2030. Okay. Bastard eggman. 123 Okay, apology going. Get apology. Okay. Connect to the repository. Eggman. 123 Log in. Name is Master and disco Log in quantitative. So this is the logging name. User name is supervisor pastorally supervisor Click. Ok, okay. So you can add it from here if you want you Can I get it? Okay, fine click. Ok, look, Logan has been established. No repositories. Master repository. Okay, now questioning Master Repository. So this is the master depository user. This one Master underscore you that Master deposit to user Total number off table 74 deserter tables. So no next step is to create virtuosity. So in order to create vert repository, I will go here. Right Click New work Repositories of apologies. A section looked Apologies The section where you can find your repositories. Okay, in the topology tape now. Vert repository, create new work repository article since I'm using Oracle database. Okay, use the name. Okay. I will provide the user name four Ah virtual posit e for work. Bookie. No established connection for work. Work underscored user work. 20 turkey eggman. 123 Okay, No. A service name or db? Well see Best good night. Okay, so the connection has been established. So this is the work and difficult not connection. Okay, Work and just could look No. Right now there are no tables, so no, this is the work repository users. So the user forward repository Okay, this work 2030 is the work repository user and bastard is Edmund von two tree Best connection success. Okay, The use of local host Ah, I was naming you do is Nordic man result this name credible. You like toe Continue. Yes. Okay. Word repository one. So there are two types of work repository development and the execution. So I have already told you in the development work repository you're going tohave to tap designer an operator so designer and operator information will be stored Now I'm selecting the development. Okay, finish so days creating the work repository right now. So this is the user off work repository So I have created separate users for repository So this user will before master repository dis user will before connection. Disconnection will before verte positive Would you like to create a log? And for the repository? No, I don't want to create a log. And if you want, you can click. Yes, Let No, I'm not seeking any passer So look, a new road repository has been created. Now go ahead or d A disconnect. So in order to create a connection, click connected the repository. Ah, valid password eggman 123 Okay, so master underscore Log in Master and disco log in quantity No addict So look, logging name is death Currently with dissecting If I take Okay, I'll be I'll be connecting toe only Master Repository But if I select this option Work repository This will connect diesel establish connection toe mastery positively as the less work repository. So I will have to provide the vert repository name So this is the work repository Master Repository setting Is this OK? So here I am providing the verdict Depository name Now I'll be connecting with Master Repository as a less work repository. Okay Click Ok click Ok now Odious! Connecting to your work repository Now I'll be connecting boat with now Look designer Okay , now wait Look So this is the development in the development work Repository designer and operator. Okay, Designer and operator. Since this is the look, this is D development work. You positive? Okay for master repository security, anthropology security Look so I'm connecting security Anthropology. Look, both of these components are active Now look, I can see the ah different components within dis company it okay, security and apology for Master repository since I'm connecting Towboat Master Repository as the last e vert repository. So this is the scheme off. This is the master repository Schema for total tables are 74 No, let me just execute this command here in the work repository skimmer So total tables 1 87 so you can view the tables. Hey, wrestle! No, look, let me just show you the repository so I can create another work repository so you can have only one master deposit fee. But you can create multiple work repository. So if I want, I can create a new worker Positive one more worker poverty New worker Positive So let's create another user for my new worker Poverty. Let's say just copy this Master underscored connection bookie 2030 underscored zero toe So this is my second work repository Underscore Zero toe. This is the user executor Disc Command. Okay, I have to First executor death No. Execute this created use that have been created now SQL uh blast sign. Okay, where is D? No plus sign. Ah, work, Anger Score. User zero toe. Ah, user name is Let me just you. The name is work. 2030 Underscore zero toe egg men 123 or D B 12 c ah. Test. Okay, connect. Okay. No. So the user is this. Just copy this Biest here. Okay, Pigment fine test Gonna clinic will be fine. Next. Yes. Okay, this is the second were reported to finish. I wanted toe create execution work repository. So let's create another you therefore execution work repository. So I created the development of oak repository again. Well, you know I don't want logging. Okay, now create new work repository. So let's create 03 03 Fine. Creating a networked repository user name will be this Do the name will be this eggman going to treat the pastured desk connection Success? No. This time around, I will select the execution work repository in the execution work repository. Look, only operator time will be active. Okay, in the execution work, let me just repeat this only operator Tab will be active. Designer tab will not be active. Okay, now I've been sure. Do you finish? No, I don't want to create. Okay now or d I disconnect now Connected The repository. No! Look at 123 Okay, Addict! No! So this is the master repository which I'll be connecting Toe divert Repository which I'll be connecting toe this time around is work repository dishonor. Okay, one toe. This is the turbo work repository. I guess so. This is the execution worker Positive. These two. These two are development work repository like me. Just connectors. Okay, now I'll be connecting toe Master work repository as the less This one this execution working positively not the development work the positive if I select this option so I'll be selecting the master repository as the last de development work repositories. So I want to connect toe the execution work repositories in the execution work repository in the execution world Repository Onley This component will be active. Ah dis component will not be active. Okay Designer component Will will not be a tip. I will show it to you. Okay, Click OK, Supervisor Passer, The supervisor click. Ok, no, look Apology designer look designed This is no protective, only the operator part. So this these two components are for work repository. So since this is execution work repositories so only the operator company will be active. Now look, look at the components within this component Operator OK, session list hierarchy. So look apology here Inject Apology section you will find the repositories So I have Master repository and the work repositories. So this is the execution work A positively not the development work repository Let me just disconnect again Now if I connect, let me just pigment 123 Click Ok Ah click addict. So if I select this option, let's say the first option So I'll be connecting toe Master Virtue positively as the last de development work repository If I select only dis option, I'll be only connecting Toe Master Repository not the work repository Only the master ripples Let me just select this option This time around I'll select the working for the Trayvon So for work repository one user name is scheme Eyes this for work repository, scheme Eyes This this one. Okay, work 20 to 30 for, uh, execution work repository, scheme. Eyes this. Okay, so one more important thing I want o tell you that there is a table called in the master schema mastered by the tree schema. So this is this came a master underscore user. Okay, work and ethical user, This isn't Let's disclose this. So I have the master repository and the work of olive trees. So let me just show you the tables. A master underscore User master underscore User Okay, tables the what's the name of people? So SNP underscore r e m underscore are repeatable in the master schema will contain all your work Repository. So you're stunned. Name this select star from this table. Okay, let me just execute this Look, Sir Total I have three work repositories. User is or dear you very supervisor. This is the global I d. Okay, so this will give me the list off work repositories. So hope you're industry the concept off the repositories. Thank you so much and have a great learning 10. Topology:Dataserver, Physical and Logical Schema creation in ODI: Hello, everyone. In this lecture, we're going to discuss the first component, the first ODI, a component called topology. So if you have to extract data from so system and perform some transformation steps and finally lorded it and do the target system, the first step is to create connection with sore system as the less the target system. So from topology, you can create connection with source system as the less the target system. Okay, in the designer component in this designer tap, we can create different map ings. It'll processes in the operator. We can see the logs off your it'll processes. Indyk Apologies Action weekend established connections with source systems as the last the target systems. We can create multiple connections. Security Tab is responsible for creating multiple users profiles, etcetera for topology and security. This took components are managed by o DEA administrator or case, apology and security. So the first step is to create connection with sore system as a last the target system. So within the topology component, we have different sections medical architecture, different components, physical architecture, context, logical architectural in the language for these are the languages available in or D A Okay . Java python. Ah, OK, Sir Paschal, you can integrate. Oh dear. With these languages okay, you can integrate or deal with Java by concept s school grow V Ok, these are the list off languages available in or D A. To integrate the or gate with these languages. So the first step is to create connection. So here in detector technology section we have different technologies available. Amazon Red shift. If the new technology added in Oracle to Elsie, we have big data technologies like hive her Duke Ah, we have Ah, Kafka. Okay, then the Microsoft Technologies Microsoft Access Excel SQL Server. You can integrate. Oh, dear, With these technologies, Okay, these are different technologies. So the first step is to create data server. So here, I'm going to select the article technology. So right, click new data server. So name of data server. So now let me just give a name here, so name will be source underscore production for the first several will be fourth source and discord production underscored ideas. So this will be the name off my data server, user name and password. Now, let me just create a new user for this Greater suburbs. So since I'm using the Oracle database, so I will just execute dis commands in order to create a new user. So now let me just create a new user for this source underscored production and the Guardia's So thoughts underscored production. So this is my production underscore DS. So I'm creating a new user. Okay, Now, let me just execute this command first. Now, executor death. Just copy this past year. Okay? So I'm creating a new user. The tester Various. What is good Bossert is Edmund. Wanted to be now do BBC. Okay. Local host 15 to 1 or D B 12 c is the service name now. Test connection. Your data will be safe before testing connection. Do you want to continue? Yes. I want to continue at least once. Physical skimmer to your data. Serra. So once the data Severus Hysterical. Okay, test connection. Let me just test disconnection. First connection field unknown Host specified. Let me just cancel this. Okay. Local. The local host. It's not local. Local host now. Test connection. Yes. Best connection. Successful. Okay, Now save this. Save on close This So next step is look, data source has been established Next step is to create toe, create a new physical schemer, so scheme my name and work schema. So what is the difference between disc in mind? Disc Emma? But before deck, the second step is create physical schema. Just mentioned the name off schema where you want to start it up. So the name of scheme eyes This So this is the name of schema. Source Underscore production Underscore Dia's So this is the name of schema. Just give the name off Soar schema So source underscored production underscore DS source Underscore production So what is the difference between discrim and a schema? So when it comes to this schema, we normally store main tables. Okay, when it comes to work scheme of the store tables like other tables lording staging tables. So this works came eyes for staging In this the first came ice for storing main tables. Okay, loading error tables, temporary indexes triggers views These kind off tables we store in the works schema. Normally the the 1st 1 and the works came out. The work scheme is different. Okay, this is actually the staging schema. But in this particular case, I'm going toe have the same scheme. I'm not specifying any other scheme. Okay, now. Okay, fine. Saved this. No safe. No context. Right now, I'm not providing any context. So I will come into this concept of context. Click. OK, now, look. So the first data server has been set up. Next, I'm going to create another one like this for my target system. So this is my source system. I'm going to create another one for my target system. So for target system, I'm going to give d r G okay for target system underscored production. So this is the production environment. Underscore Dia's. So this is my name. So I'm going toe create. Ah di. So look for target systems. I'm going to create a new user for target system. No, create user for target system. Just execute this for target system. De are de Roda Children. Guardia's eggman. 123 DRG. Okay, so name will be like this production production. Okay, convention is DRG. No executed this okay? No, just copy paste here. So the first you there. So the first usually force underscored production. So this is my source user. The 50 dog. So now just given use the name here. So this is also a schema. Eggman ventilatory eggman. 123 Do DBC article local host 15 to 1. Uh, or D b 12 c test connection. Okay. Test connection. Connection field unknown host. Okay, fine. Unknown. Whose specified test connection look. Death success. Local host 15 to 1 back slash core gave it. Well, see for Steve Death Seaver, it is automatically saved now. Target production underscored ideas. You have to click this. Then you have to select new physical schemer. Okay, Now you have to pry the physical schema where the data is going to be stored. So the ski my name is this PRD prg the deep production. And if Guardia's, the deeper the Children Difficult deace Fine schema the for disc Amanda work schema. Both are same. No saved this. So in order to save this, you have to click this No context OK specified for this Qimin. Now close this look. So scheme a disc embezzling toe this. Get a source disc a missing to distort a source. Data sources article in both cases. Okay, now I'm going to create another data thought this one is a production system now I'm going to create it for development. New force underscore death. This one is for development D s now I will create for development Now create another user you can create for deaf. You can create Ah development be source development. Likewise you will create for target system. DRD grd Okay, just execute this command executor this particular come on. Both users have been created. Now I will just follow the steps. Okay for source I will use this dear DBC local host 15 to 1 back slash or D B 12 c test connection. Yes. Okay. Gassed success. Next step is no Next step is okay. Source Deaf. Next step. If this ISOS death next step is to, um create a new physical schema. So physical schema Source Underscore development fourth under core. Okay, this sores underscored FDs. Okay. No, save this. Okay. Physical schema has been created 44 death. Likewise. I will create another. Get a server for target for target. I will give the convention. TRD underscored death underscored B s Okay. You that I will give this PRD underscored f underscored es egg men want to treat the password. Okay. J d B c Local host 15 to 1 backslash or DB Quincy desk connection. Okay, Best success. Fine. So forced Deaf Fine. Target Deaf deace Distant. Select this option then rightly new physical schema. So for physical scheme, I will provide this ski. My name Target deaf DS Okay for work scheme. I will provide the same name. So in the work schema, all these tables will be stored so safe. Okay, No context has been provided. Now look, Sores Death source production Let me just source target death Can I know? Okay, now I have So these Look now get us ever physical schema. So this one is deep Data sever for development source in the target for this target is source target deaf. This is Target. Okay. Source Death DS target deaf deals for production This is another environment. Production is altogether another environment. Sores underscore Production underscored es No Stargate A Stargate system. Is this so? Like why you can create another data server for test environment, okay or staging environment. So I'm just creating two environments development candy production source Andy targets are also there in the in these physical architecture. Okay, now next step is to create logical schema. But before that, we have toe create a context. So what if the concept off context So basically contacts will give meaning to different shovels. For example, you have a much people development servers? Let's say so. The contacts will be development. So in the context, you will have multiple developments ever. Okay, so my contact will be development safe. So let's say you have multiple production servers. So Indy, so you will have contact production for multiple productions of with Okay, save this. Let's say you have multiple development servers, but they're located in USA. So you will have contacts like this. New Context USA underscored development. Okay, Saved. If so, let's say, let me give you another example. Let's say, do you have multiple, uh, development servers? But they're located in you keep so you will have another contact like UK Underscore development. This will be linked. Toe server state are located in Uki Development Service. Okay. Saved if ah see here you will have to contexts production and development. Okay, so I'm not using these three oneto global so global context will be for everyone. Okay. No. How you will link toe next step is to create a logical schema. Logical skimmers. Nothing but a name off your alias off your physical schema. So you will have to link Logical scheme are toe a physical schema wire context. So I'm going toe, show you the step here. Okay, Now next step is to create a logical schema. So to create a logical schema, you have to go into technologies. So since we're using Oracle Technology so I will select Oracle right click New logical schema so provide new logical ski market. So the name off logical schema will be like this unless on that score deaf. So let's a production The first we're creating logical skim off a production underscore source So development. Okay, food production the production look So here I will have different options. Mm. For the Children source with this production Brewer, that soon source production. I will have this. So this will building to logical schema is will be linked toe this physical scheme of wire context which is called production. Okay, this is how you link logical scheme or to a physical schema? Physical steam allies here in the physical architecture, various logical schema will have a separate section Okay, here you can clear D logical schema. No Save death. Save on now I will create So the 1st 1 has been created. I will create another logical schema. Logical schema underscored death underscore source. Okay, now this will be connected Toe, this is development. So I will select the first context development Physical schema will be source. It is fourth in it is development We resource source and development. So the physical schema will be this. So l s turn for logical schema. The E V stands for development environment. SRC stands for sore system So I'm doing it for sore system. No, save this so likewise I will create some more logical scheme us Another one This one source Now, unless underscore production, This one is for target Trg So production. This one is for target production. DRD production. Okay, so now this logical schema will be linked to this physical scheme of wire. This context production No, save this. So this is how the concept off context comes into play. It basically connects your logical scheme. Are too. Do your physical schema. Now close this! No! I will create another one new logical schema. Now unless underscored. So production is done. Production source. Production target now unless death underscore target So deaf development contexts is development. Okay, deaf target underscored death. Okay, No saved this. No look. Logical scheme. I have mean created. Look. 1234 This is death source. Disip Deaf target. This is production. So this is production target. So de logical skimmers link toe your physical schema via contexts. This is how the this is the importance of contexts. Okay, so we have created different contacts like development production. So these two context are off. No use. We can also delete it. It is It was just for the understanding Purpose. No, I'm deleting it Just delivered this. Okay, In the next lecture, we're going to look at the look at another component which is called designer in this particular company ankle will create different map Ing's Okay, So thank you so much and have a great learning 11. Designer:Model,Datastore & Mappings in ODI: Hello, everyone. So we're done with the component called apology. So in this particular lecture, we're going to create any girl mapping. So using the equal mapping, they're going to Lourdes that I into a target table. So from source table, we're going to load that trying to a target table. So I was used this company called Designer. So in this particular component, we will use these two sections, projects and models. So first of all, I'm going to create a table in my a source system. Okay, so let's create a table. So this is the table product. I will create the stable in my source system. So look, create able product product. I d name category I d unit price unit cost. These are the attributes of the stable. Now, execute this command in order to create this table table has been created. Okay, Now, next, I'm going toe insert values in this table, OK? Have you used this command in certain? Took a mind now copied is based here. Okay, Now execute. Oh, execute. Okay, now select star from product. The product of the table. No. Let me just count the number of Ruth. So how many rules. Let's check. So there are 15 rules in this product table. So next what I'm going to do, I'm going to create table in my target system. So just copy this Biest here. Okay, I'm going Toe egg. Another column, which is ghost Underscore. Def. So this column is the difference between unit costs and the unit price. So this is my product. Okay, let me just product underscore TBL. Let's it 2020. Okay, this is my table. No, executor, death now able has been created successfully. Now the table is empty. So the scenario is that I'm going to so from source system, I'm going to Lourdes. They're trying to my target table, which is this Okay, Right now it is empty days, nor data. It is empty. So let me just query desk able. It has 15 rolls. Okay, there, 15 rows in this product table. Now I'm going to create eagle mapping. So to do that, I will first create models. So what is the concept off models? So the concept off more journalist model is a place where all the matter that I will be stored. Okay. Ah, So we're going to create a new model. Okay, so rightly care. New model in order to create a new model source Underscored product. Okay, technology, oracle, logical scheme, eyes and less production. So So this is the logical schema. So the concept off model is it is a place that all the matter that I will be stored metadata is nothing but information about your data about your tables. Okay? Right now, what I'm doing is I'm bringing the I'm bringing the table structure off product table in tow. O'Dea, This concept is known as reverse engineering. So using this concept, I'm going to bring the table structure off this product table in tow. OD I So this is the concept off reverse engineering. Bringing a table structuring toe od is known as reverse engineering model is a place where all the matter that I will be stored like your attributes. You're constraints. So this is matter data. Now, let me just add the table structure here in the morning product. But since sign OK, now, all cables get start with Director P. Okay, right now, let me just check this option. Select this option existing that just fine product. So I will select the 1st 1 product. Since I'm I'll be using this product table. OK, so I will select the only product Herbal reverse engineering context is production. So since I'm using the production system environment Okay, definition. If they saved this in order to save Clegg death now reverse engineering. So the process off reverse engineering is that reverse engineering brings their data structure table structuring toe into mortal section. Okay, click this. Now look. Look at the table. Structure it. Product, This is data source and this is mortal Mortally source and disco product. No attributes. This is metadata. Attributes. Constraint. This is mattered it OK, look, there is no constraint right now. There are no constraints for this table. Okay? Now likewise, I will do it for my target system. So, uh, DRD under of course product. So name off. Okay, product, let's give a name to order. Okay. Technology Oracle, Unless underscored production on the score target. This is the logical schema, which I'm going to select. Reverse engineering production. Fine. B percent all table states. Start with character pizza. I want Ah, okay. There is only one table in my schema right now. Okay. Product on the score Table 2020 which I created. Reverse engineer. Okay, Yes. Look, table structure has been added. Now, next processes to create a new project. So in the project section will have all the map ing's procedures. Except let me just create a project D wh underscore Project. Save this Look in the project section. So the project has been created. You will have a default folder called First Folder. This is the different folder underneath this folder. You will left packages map ing's reusable map ings procedures, different components. Okay, so in this particular lecture, we will discuss the map. Ings okay. Only will come to this thes parts later. Now I'm going to create a new mapping right now. No mapping, is there? Okay, I will create a new mapping. So to create a new mapping click new mapping. So ideal mapping. So I'm creating a new mapping so that I will load my data into my target table for from source system. I've a load, My dad. Time to a target table. Okay. Create empty dirt. Assert, select this option. This will create empty data set. Look. So in this particular section, I will place my source data in this particular section. I will place my target data. My source that has this product. Let me just drag this. This is my source table. OK, now, my target. They believe this. Let me just connect this with this table. Connection has been established on the basis off name matching will matching will take place on the basis of name Click. Ok, look, product I d similar product name. Okay. Similar categoria de unit price unit cost. This column is extra hearing the target table. So what I'm going to do this cost underscored if it's the difference between unit costs and unit price. So in order to calculate the difference so I'm performing a transformation step here. Okay. Click here. Target, this is General Target Click here, which is expression advanced Agutter product. Okay, unit cost minus unit price. Apply. Click. Ok, now save this. So, in order to execute this, you will have to first click here, then press this green button. Okay, Run. Okay, context So evil. Select the context production because the answer D context production for production environment. Ok, all OK, now click. OK. Ah, Session started. Klay Cookie. Now, now let me. Just check if the data is loaded or No, this is my target table, which waas empty. So now let me just execute this if their days loaded or not. Look that ties lorded successfully. Not in order to check whether their ties loaded or not. There is another process. You have to go ahead in the operator. Here. You will go in the date you will check the logs here. Okay. Vetter, Digitize lorded, successfully engaged. The reason era. You You can see the other message here. Okay. Aaron, Logger, look, this mapping has been successfully, so the 1st 1 refreshed this. Okay, refresh this. No. Click the first lock view. Look. Number off. Insert number of fruit. There are 15 rose. Number of fruit inserted. 15 so you can look at the logs here. Okay, now it is confirmed that that is loaded in my target table. Now look at the target table, So total count. Number of rules. Okay, 15 Number of Ruth 15. Okay, So this is how you lured that? Are from source system tow target system. This is my soul system. This is my target system. So thank you so much and have a great learning. In the next lecture, we're going to look at few more concepts 12. Designer:Mappings,Procedures & Packages Overview: Hello, everyone In this lecture, we're going to explore a few more options in O D a. So So this is my target table. Let it so in my source table. Distance my source table. Let's say I've warned only the data were category ideas Three. Okay, so I want only this data. Only four Ruth in my target table. OK, so first I will truncate this table. Use drunk it table. So deck, I will insert data again in my target table. So Ivan, I will apply filter. OK, so now I'm going to apply Salter toe get district, get agree I d costa tree. So here. How can I apply filter? I want apply filter on this category I d. So what I'm going to do? I'm going toe drag this in place here in the panel placing. Okay, filter. So this is the fader. A name? So this here. I'm going to specify the filter value. So what you have to do you have to click this property window will appear ok, in case this property vendor disappears. Where you going To get this property window? You have to go here and check this option. Then it will appear it again. Okay, in case it gets disappeared. Now click this filter. So filter condition. This is filter condition. Look at this blue starshine. Okay, this is filter condition. Advanced filter. OK, click. This equals two. Let's it three apply. Okay, so save this. No, execute this now. I will only get the the four rows. Okay, Now, let's just execute this production context. Production decision started. So look at the logs here. Okay. Today cranking engine for these are the logs view, right? Click and view. Look, let me just please here. Okay? Now number off insert. Therefore Ruth get are inserted number of floors for Okay, so let me just Ah, okay. You have to go. Look all the way down. Hit. Okay, Acma, just check the number of rows in my target table. So now I'm going to have How many, Ruth? Only four Ruth category. I d with categoria Get three. Okay. No, Lexi, if I've weren't so filter. Uh, okay. In case, let's say five. Ah, apply filter like this equals to tree no en gifts. If this eagle mapping feels what will happen apply. Okay. Drunk it. This table first cable truncated now, I don't have any value on my table, so let me just execute this Now, save this. Let me just execute this so production. Okay? Fine. Sessions started now, Operator, in order to check if this mapping is successfully executed or not, refresh this. Look at it now. It is showing error. No. Click this. You have to go all the way with the problem. Look, this is the problem. Identified the problem here. What is the problem? Look, this is how you identify, the better in certain rose. Okay? Missing expression. So the reason it is giving me edit because I have given equally cool, which is incorrect. I have toe right like this. Okay, apply. Click. Ok, saved this. Okay, now I will have to execute death. Okay. Noro. Now executor, this run. Okay, click. OK, session started now. Fine. Now it is successfully executed. Okay, so this is how you identify the error message. So let me just show you one more concept. Okay? So, designer Okay, map ing's Okay. This this project. So every time you're doing some changes, you are applying some filters. What? You are doing your first truncating your table. Then you are executing the executing the mapping. Okay, so we have to execute this the command manually. This is what we are doing. So, in orderto avoid this, what we're going to do, we're going to use the concept off procedures. So the basically the concept of procedures, it is a place there. We can write the deal. India milk a month. Okay. De dealing demon commands like create updated daily, trunk it, inserting toe disc amount. So instead, off instead of manually executing the command truncated. Commander, delete Command. What we're going to do, we're going to create a procedure in in the procedure revealed. Right, Dick Command. Okay, now So now, instead off now, I will use the delete command, so I'm not going to use the trunk It Come on, lets you delete command. Okay, Now what I'm going to do, I'm going to create a new procedure. So instead, off manually executing the trunk it or delete command, I will create a procedure in which lets a procedure name if let me just place here. Okay? Procedure Name is Duleep the doors. Okay, so every procedure has a tough So in this definition is this name of this task. Ah, click this plus sign Task name. Click through This is the properties. This section is for properties. So look, journal Task name. So what is the last name? Task? Name is Let's see. Delete. Come on. And if the task name that's supposed don't get Come on. Technology severe Using Oracle technology here. Okay, Article. Okay. Context, production. Logical schema. You have to provide the logical schema. So in my case, logical scheme Eyes this less underscore production underscore source nor target. So this is the production and escort target. So this is my production scheme. Okay? Everything else is optional. Okay, now here I will provide the command. You have to click here. Click here. You can write command here. Delete. Okay, you will write this command. This will delete all the roof from the table. So instead of deleting it or truncating it manually, I can write delete commander or truncate commanded. Apply Click. Ok, no, save this. Okay. Delete command. No, Richt dark. Okay, this Come on, delete from product. So I'm not specifying any semi colon, Gordon. Okay, save. So now I'm going to execute this in orderto so before executing the detail mapping. I will first execute d execute the procedure. Okay, now I'm not executing the delete commanding my article SQL Developer Instead, I will execute hair delete delete procedure Andy or D I now execute this. You must save just safe. Draw the chin. Okay. Station started. No checked logs here. Refresh this daily growth. Do look number off road now Check. So now check if the debt is deleted or not. Execute this. Okay, that eyes dilated. Now you can check. Look Ah. Okay. Procedure daily Truth view. Okay. Their ties deleted. Fine. Just remove this. So this is how you create a procedure. So we will discuss the concept of procedure in more detail later. But here in this particular Lecter, I'm just giving you the overview of the concept of procedures. We will discuss the concept in detail. OK, so we will discuss all these options. So this is how you create any deal mapping, and this is how you could add the face. Okay. Next. So what we're doing here is we are executing the delete Rose Command first were first deleting rose from the table. Then we are executing the detail mapping. So this is what we're doing in step in deep intersection here, we can use the concept off packages. So if you have to execute the OD a steps or the objects in sequence, you will have to use the concept off packaging. So right now what I'm doing, I'm executing the d literal this procedure first. Then I'm executing the eternal mapping. So you think this I will create a flu? Okay, now new package, ideal flow together. And, of course, Florida's given name here, 01 Okay, Now, in this particular package section, I will just drag and drop. So first, I will look procedure delete. Ruth, I will drag this object here. Okay, then what I'm going to do, I'm going to dragged this mapping, okay? No, this okay, sign like this. Now join these two steps. So the first step that we're going to execute OD, I will execute the first step. Delete Rose den. It will execute the eagle mapping. So saved. If save this. So right now, let me just check, Okay? First, I will just execute the mapping. First Executive D mapping production started booking now. Ah, I have in my target table. I have that I would get degree Idea three. Let me just change the filter condition here. So let's change it to four. Apply. Okay, Save this. No. When I'm going toe execute this package here. What it will do, it will delete the dis table. OK, delete all the data from this table in the production. Look, I have only four rows in my production table. Target table. Ok, well, Gillete the rose from this table. Then afterwards, it will execute detail mapping. So after deck, once the eagle mapping is executed once the it'll mapping is done industry I will have a debtor in my target table with category i d four. So now let me just execute this in order to execute this Save the first, then executed this it'll package. Okay, production. Okay. Click. OK. No, let me just check Organization started. Let me check the logs. Here, refresh this. Fine detail flow 01 has been successfully executed. No, like media look, number of throw seven Now in this part particular table, I will have that of it. Can't agree. I d four for total number off rose. Okay. 123 for total number off rose three. Okay, we get. Agree I d. Number off. Inserts three. Okay, fine. 13. Oracle Data Integrator Knowledge Modules LKM & IKM: Hello, everyone. In this lecture, we're going to discuss about the concept off knowledge models in Oracle that integrator to Elsie. So what is the concept Off knowledge model? So let's look at the definition off a knowledge model. So knowledge models are court templates. Each knowledge model is dedicated to an individual cast candy overall greater integration process. So there are six different types off knowledge models. So under need the project section you will find out the different models present. So look first is reverse engineering loading. L came stand for lording knowledge model are came Stand for reverse engineering Knowledge model Jack Phy Cam Stanfel Check knowledge model. I came Integration alit model generalizing knowledge model service Knowledge model. So we're going to look at this scenario in which we're going to discuss these knowledge models. So the important one is reverse. Engineering is nothing but getting data structure in tow OD I So, for example, if you have a table letter, I haven't employee table. So using the process off using Lett's leg star from Tab Ah, let me just execute this. I have Okay, let's say dis table. Let me just copy this based here Brodet underscore Table going, Tonto. So s so This is my table. So using the using the concept off reverse engineering I can I can get the table structure . I can get the table structure. OK, so I can get the table structuring or d I with the help of this knowledge, more deal are gay am. Okay, next is El came. So we'll discuss all these knowledge more deals one by one. So first we're going to look at this scenario. So with scenario, by looking at this scenario, I'm going to explain the knowledge model. So let me just explain this scenario, okay? Let me just open excel file. So let's say I have two files. Ah, file number one, which is off employees. So in my file notes, this is filed one. So in this file it I have implied data imply data. This file is present in my local computer system. Okay, so let's support I have another file. Uh, let me just draw another shape here. So this particular file contains department department, so imply in department to do either text signs. Okay, so So my scenario is I have to fight such a present in my local computer system. So this is my Let me just This is my target table, which is present in my article database. Okay, let's say mm implied table. OK, employ underscored, Let's say, in four table, which is present in my article database. Let me just make it bigger. No. So I want tojoin Let's I want to join these two on the basis off department I d. So in this file department, I have department idea in department name. I want tojoin on the basis of department I d. In this particular file. I have department I d here. I have department. I did. I want to join on the basis of department I d. So here. I want to have a joint, so this can't be possible. Two files cannot be joined, so you have to have a table in order to make a joint. Okay, So this is the scenario which I want to implement in my O'Dea article that I integrator too low. Okay, I want to implement it. So let me just okay, now let me just Okay, This is my target table, which is present in this is article able modern thinker. diese XT five dext finds present in my local computer system. So let me just ah, text files, employees, department employees and discarding for this table is present in my article Dark abyss. Okay, I want to have a joint, but this is not possible. In orderto in orderto make this scenario possible. I have to use OD I So this is my logical Morgan logical model is death. This is my requirement. I want to implement it. So I have to do it in physical temp. So let me just show you here. If I open the mapping here, you will find to temps. Um let me just viewed the mapping. So here you will find two steps. This is logical mapping and this is physical mapping. Let me just if a look, this is physical mapping. So this is how o d a. Will implement this scenario. There to fight is known us physical mapping. So now let's locate physical mapping. How od a veil Implement this scenario physical. Okay, physical mapping. Now let me just copy paste all this now I know. Join. Let me just remove this. So in order, ei oracle Tanking record. Well, see I will have a staging schema. Okay, here I will have a staging schema. Let me just draw staging schema here Staging schema Now. After this, I will have a target table. OK, target table, this is staging schema. Let me just write Staging schema, Merge in center This is Stargate Table Margin center No. Now let's look at how od Evel implement the above scenario Logical scenario so or d I will make temporary tables in the staging Schema, let me just show you d partof temporary scheme. Okay, so hair topology, Look, this is source. Okay? Not remove this. So look, this is the man Skimmer. This is diverse schema, which is also known as staging schema. So OD I will make o d a will make the temporary tables here in this staging schema. Okay, so whatever the staging schema, you will provide their hors d. I will make tables there. Okay, let's say if you provide this staging schema staging schema will be this and or D A will make tables here in this particular scheme up. So first odi, I will make two tables, one for this text file and run for this. So the first table will be see dollar table. So let me just make here. See dollar imply table imply table and see dollar department table. Let me just Biest here, See, dollar department table Indy, Look, let me just This is C dollar loading. So So the first step is O D A will. So there's a concept called loading knowledge model. So the concept off loading more knowledge model comes into play. So what OD I will do OD I will select all the data from employee file and next ODS will select all the data from department filed. Then it will inserted a time see dollar employee table. And afterwards it will insert the time see dollar department table So days eso Who will do this task? L came loading knowledge model. So let me just draw loading knowledge More do here loading knowledge model. So the concept off l k m will come into play. Okay, So now let me just This is this table and this is insert this table, OK. L came. This is done by l came. So what is the responsibility off loading knowledge model loading nor lit more. Dude, l gay am This morning. Let me just show it to you. L game. Ah, l came lording. We're discussing this modern. So the job off l chemise if see donor table is already present. Already present in the staging area in the staging A DEA Drop it. Okay, second step with So this is the first step off. L came second step is so let me just cookie drop it. Second step is l Campbell create tables in the staging area. L came will create see dollar tables in this staging area. So, as in this case there to file so it will create two table see dollar tables. Okay, afterwards Turks step is and gamble. Select data from source and insert into in tow. See dollar table, Insert their time to see dollar table. Select all data from sores and inserting too. See dollar tables. Okay, this is done by L. K M. So once target is inserted in see dollar tables. So the next knowledge model vehicle plates Part is I game. Now let me just draw I gave me so I can will insert that are into target table. So from staging schema, it will insert their time to a target table here. The roll off I came. Okay, I game. Let me just make it bigger. Okay? Ah, Insert into target table. Which is there? Employees, Employees underscore. Let's say in four is my target cable. This is my target table implying the score and for which is an article data base. So here what I can really do I came Will. So next I came. What? What if the roll off I camp integration knowledge model knowledge More dude. So integration More knowledge. More dune if I dollar table is present. So I'm explaining the concept off integration knowledge model If I dollar table is present is present Indy staging area already present in the staging area. April Drop it. Okay, it will DA picked second step with I, Kim will create will create I dollar table I can will create I Dollar table Indy staging a DEA Tarts Step is I am well select all data from see dollar tables and in certain to in tow and in certain toe I dollar table So here I came will perform I came can perform a transformation steps like aggregation Join, merge delete ok s as here I have to join this So I came Can look from here. I came cane, perform joint steps here and can put the data here here in the I don't her table. So I dollar table structure the same as the employee underscored in four table. Ok, duck here. Look. What about l came? L came will create she dollar table. So structure off, See, dollar table will be same as de structure Off structure off source table. Ok, implied table imply. Look, this this file call a d header headers and the headers off this table will be same table structure will be same. Okay, so So what I can do I can will look So I came Well, I came I came will select all the data from see dollar tables and in certain toe I dollar table Ok, so look, let me just drag here control legs so I can will create another table which is see dollar table here from here I came Well so this is this is I dollar table. Ok, so monster dirty is inserted in the eye dollar table I came build an inserted ETA into target table from my dollar table Ok, so look from here, I can will. So this is the step, so I can will insert the get up in tow this target table from my dollar table. OK, it will select all the data from my dollar table and Eric will insert it into a target table. So this is my staging schema. So this is the concept off I came Integration knowledge model in the next lecture will continue dissension. 14. Oracle Data Integrator Knowledge Modules LKM & IKM Part-02: everyone in the previous lecture were discussed about different knowledge models we discussed. L came. I came. L came Stanford lording knowledge model. I can stand for integration Knowledge model. So I asked for the definition off knowledge model knowledge model you this different court template to perform a task. So in this particular example, l came with Lord, They're tough in tow. Temporary table. OK, so it will read data from text files and it will load that time to a temporary table. It was you certain template to Lord that I'm to a temporary table. So let's say in this case, since these are text file, so l can will use template like l came fine. Two article. So this staging table, these tables are present in my article database. So let's say equals use Templar. This temperature will use this template. L came file toe Oracle Oracle db template. Okay, let's say l came will use this template. I came. I can will also use any template. So as for the definition knowledge more guild you this court template. So let's say equal juice temperate like I game. Let's say article do article D B Okay, it could. Okay, imagine, uh, if Elkem has to read data from a terror data. Okay? Teradata database. So in this case, for example, convince you the template called margin center. Uh, l game de de to Oracle baby hit. Ah, l Campbell juice. Because these tables are present in article database. I came. I came here. I came. Oracle toe. Good, baby. So do either different templates that are present in my ODI. I okay, in my ODI it So let me just open mapping here. So this is the mapping, So Okay, this is logical representation and the 50 physical representation it is opening. Okay, look in the physical. If I have to execute this, let me just first executed this logical executor death. Okay, Physical. Okay, so this is the physical model. So this is the staging. Look, l came Oracle toe Oracle, since this table is present in my article database. So it is. L came Article two article. Okay, so dis table is also present in the stating their indie oracle database in this default temporary tables. This is the default location where temporary tables will be stored. So look, l came article toe article. Elkem Oracle's Oracle. This will be a few selected. I came Oracle toe article. This will also be I camp. So the job off I came is to insert Decker into a target table. OK, so these are different templates. So let me just show you the templates here. Ah, loading knowledge model. So these are different templates. Article two article. Okay, SQL So these are different templates used by OD I So let me show you more Templars. If you have tow import more Templars in tow O'Dea you will have to go in the location where your article 12 c is installed or D a Ah, a sneaky book XML reference. So these are all templates. OK? Knowledge, model templates. So this is game knowledge model underscore. L came. This is l came template. These are all l game templates. L a m So these are all l came templates. Okay. Oracle Elkem Article to Oracle l came SAB beat a blue oracle. Okay, so now you have the understanding off This definition knowledge model uses different court Templars. So these are different templates. Each court template perform certain tasks. Okay, so if you If you want to import this. You can go here loading. Okay, I want to import. L came. You have toe. Okay, Filic Death Right click Important on late model here you will have to provide the part and select if you want. Right now I'm not importing it. Okay, so this is the job off. This is how l came and I can works. OK, it you this different court templates, different templates to Lord that time to it, I can load it into a target table. L Campbell order timeto into staging temporary tables. So one more important thing about l came and I came instead. When source in target having same skin modern, there will be no roll off. L came than in that case, I can will directly read data from a source. Okay, this is an example when 14 fourth in target, having different schema. Okay, so let me just right here. This is an example when source and target having different schema. So when sourcing target having same schema, then I came will directly read that are from source then, uh then in that case, l Kim will not be used. Okay, so this is important. Led here sourcing target having same schema then, uh, game will not be used. So v visit example off sourcing target having same schema when sourcing target having different schema, then boot l game then l came will be used. L Kim will be used. OK, this is important point about l game Four and Fife. So you must remember this when source and target having same schema than l kim will not be used. Okay, but when source and target having different skin But then l came will be used. So this is the important point about l came. So next knowledge model that I'll be discussing is she came, which is Jack knowledge model. It performs. She came, performs better validation checks. Okay, so there are two types off Sikkim. So now we are discussing about c came. She can flow control c came flow control. So now understand the steps off Sikkim and she came static control. So first we're going to look at the static flow control example of flow control. So the first step is Sikkim seeking will create a dollar table e dollar in a simply table, these two tables begin will create these two tables dollar table and smp underscore checking the score. Tap table tables. Indy Staging schema Second step If now this is the second step. Second step If Fiqh in Ville Jack Duplicate values, duplicate values. Indy I Dollar table. Okay, well check duplicate Well is in the eye dollar table based on primary key or unique on the basis off on the basis off prime Ricky or unique. So this is the second step about see Kim's. He can really check the duplicate values here in the eye dollar table based on primary or unity. Third step is if duplicate value exists in the table equipped, deleted, it will delete it and exist in the eye dollar table. It will delete it and in certain toe this table dollar table. So inserting toe all values in the dollar table in certain to eat dollar table. OK, so this is the third step, If duplicate, really, With distant T, I don notable it will delete it and will in certain toe and will in certain toe i e dollar table. OK, fourth step is Indy in this particular table Indy SNP check table. It will insert and her message number in the interface number. Okay, so this is the step off Sikkim before in certain values in the in the target table, she can will perform these checks. Okay. No, that is another. She came started control. So, Sikkim, static control will also create. Okay, if you are choosing this option, she came will also create a dollar table. It SNP check table in this staging schema. So this is the first step. Now I understand the difference. We'll check. Duplicate were loose. Now the second step. Look at the second step. Will check duplicate. Well, if Indy target table. Not Andy in the north. Andy. Ah, I dollar table. So if you have selected this option, static control I came will insert their time to a target table. Then she came will perform these steps. Okay. Seeking vulture duplicate fellas in the target table on the basis off primary key or unique . So if the duplicate values exist in the card step, if if the duplicate values just Indy target day, but it will, it will not delete it. Okay? It will not delete it and will insert will copy duplicate values. Indeed. I will copy. Duplicate Where lives in the in this dollar table so forth Step if four step is in the a distant, but Okay, well, so SNP SNP. So in this particular table, it will insert and her message number in the interface number. Okay, So, if duplicate, really existing here in the target table So equally a trillion circuit or gay here, Indy in this particular table E dollar table smp underscore Detectable, which is present in my staging scheme. Okay, it will insert it from target table. So this is an example off static control, This is Flo Control. And there is no unique or primary key constraint in the target table. You will use the concept off she came static control. Okay, this is my This is my Turk module knowledge model, which is C k m. No Dick M, which is generalizing knowledge model. This knowledge model is used for change data capture. We're going to look at the example of this model. Is that service knowledge model? If you want to read data from different web services, then this knowledge model is used. So we're going to look at the example off J came in the future electives. Okay, also, we're going to look at the example off he came, started controls, he came flow control. We're going to look at the practical examples off these more youth. Okay, in 15. ODI Mapping: Loading Data from Flat File (csv) to Table: Hello, everyone. In this lecture, we're going to implement this particular scenario in Oracle that integrator 12 c. OK, so this scenario So we're going tohave to files employees in department. We're going to join it, and then finally, we're going to load it into my target table, which is employed. So this is my table. So first of all, I'm going to create the imply table in my target database. Execute disc amount table has been created successfully, like me. Just execute this query. Look, this is the table structure, OK? Off this employee table. Now let me just show you my files, which I want to Ah, Lord Data so implied or text. I will read this particular fight. And secondly, I will read this fire department Fire two files. OK, now let's create a scenario here in Oh dear. So So, first of all, I'm going to go in the topology here in the physical architecture, I will select the technology file. So since our source system is using file so I will select the technology file, create new data store new data service. So let's a source underscore five. Did ABC Connection default? Leave this as artist J D B C u l This is setting Okay, leave this Advocates test connection. Just provide the name here. Okay, Desk connection Successful. You have to just provide the name here. You don't have to provide the user name and password. Okay? Since the I good thing defile So close this source side. Okay, so next what I'm going to do, right? Click New physical skim. I have to select the physical schema. So in the physical skimmer, you have to provide the part where finalists present. So the file is present here in this particular part. Okay, directory schema just drift apart Here we're file is present. Okay, Save death. Okay. File is so dismissive. So the first step is done, Industri. So next step is to create the logical schema. Okay, in order to create the logical schema. So look, I have to select the file here. Five. Okay, logical architect to create new logical schema Now I will have to create a new logical schema endless underscore file The context is production physical schema here, rival pride this part Now I'm connecting my logical steamer with physical schema via context oil production. Okay, save this no logical schema has been created. Look, this is my logical schema Now. Next, what I'm goingto do, I'm going to create models. So in order to create a new model new model Ah, fourth underscore file 01 technology. I will select Filers Technology. Logical scheme. I will select this logical schema bitch I have just created. So since we are extracting data from a file so they will not use this mattered Reverse engineer here. Okay, so we're not extracting data from a table, but we're extracting that are from a file. So what I will do? I will just save this. I will not use reverse engineering tab here. Okay? Just save this now. A new model has been created. Now I will create a new debt. A store? Okay, A new data store. Ah, let's say employees. My file name, employee data store type. You have different that It store types. You have to select table s default. Okay, resource name Here. You will provide the part file name. OK, file him. You have to select the file name. I'm selecting. Implied or text implied our text. Okay. Name is employee data store. Name files, file. format. Deli meter now record separated. This is M s DOS file. Okay. Fear separator. A comma separated file. No text l A meter nor texters not limited. Okay, leave this attributes so I will select the option. Reverse engineer for the table structure has been added in or d A. Now let me just give a name here. Just close this. Do it once again with one important thing. Ah, heading number off lines one. So the 1st 1st Linus for had a distant for header. Now close this now I will reversing dinner again. Yes. Okay. Remove this first test again. Now look. Tables factor Data structure has been added in O d a i d. First is nomadic. First name string, last name string Jenga String. Each nomadic department nomadic saved this. Okay. Or it is already saved. Okay, so first model has been created. Likewise, I will create another model sores underscore. File leader to technology arrival Select file is technology logical Steamer, is this so here? I'm not going to reverse engineer. Okay. Save this. No. Closed This option here. I will select right click New data store. New data store here department is my file name Department here. I will select the file Part department or text file. Okay, files. It is delimited file. Formative de Limited number off line. So the first Linus for header records separated separator M s dos feel separate. Er this is coma. Separated filed. Fine attributes. Reverse engineer. Select this option. So reverse engineer will automatically bring the data structure in my ODI. Get you okay, Department. I gained department and these two columns look department again. Department name. So file structure as when I did using the concept called Reverse Engineering. Now, next time I'm going to create another model for my target table. New PRD underscored employee table, which is my target table technology for my target stabilised Oracle Ah, less understood production underscore Target. This is my logical schema for this. Okay, now reverse engineering since they're using table. So I will use the concept off reverse engineer here. Okay, so I will not select new data store like I selected vile creating a data model for files, so Okay, leave this. Advocates select all the options here Now from here, I will select only imply people. OK, reverse engineer. Yes, Equal. Bring the data structure in my OD. A look data structure has been added using the concept gold look target underscore Imply using the concept called reverse engineering. Okay, data structure resonated Next I'm going toe Create a new mapping. So in order to create a new mapping click care new mapping Fine Do table. So uncheck this option. Create empty data set Uncheck this option if you have checked this up if if this option is already checked you have tow uncheck this option. So I'm not getting into detail of this option. Write novels. We'll discuss this later somewhere in the course. OK, now click. OK, now here you going toe drag objects. So the first object it I will drop employees Text file This is my text fight source underscore file 01 2nd one is department This is department filed with the department idea in department name. 3rd 1 is employed table target My target table. So I want to join these two files on the basis of department I D. So I will select this option Join join death and giant this on the bases off on the basis off, Select this option. Okay, Joint condition. So if you have to provide the joint condition. Here. Join condition. Go where? Select the startups. Enjoying condition? Advanced Editor. So what? You will go. You will have to click this option. Condition journal, connector points condition going in the condition option. Join conditions here. You will probably join condition. So click toe this option. Join condition Advanced Editor. So you will have to provide the joint condition. So I'm going to join department. Idea of employee table. The department idea of department table. So this is my joint condition equal to department. I d implied our department ID equals two department or department. I apply this condition. Take. Okay. Now select. Okay. No. So these two files are joined. Now I want to select the imply table. So which column I want to select, I will select the I d. Okay, Joining first name, last name. Gender it in department Name the department name. I want to select this. Okay, so there is an alternate way of doing this. So uncheck this removed this. If you select this option, this will ask you match option by name or by position. Names are different. Look, I d imply i d so Look, if I select by position equal, select department with department and I don't want to select this department with department name, I want to select this name with this name. Okay, so let's say so. I wanted no this option, so instead I can just drag and drop here first Name, last name, gender it in department name. Okay, so this if you know another thing I want to discuss Ah is the concept off and game. And I came. So first I will execute this. Save this and execute this. Okay? Executor Death section started checked logs here. Did he fish this? Look, let me check if the details in, certainly in the table or not that ties inserted successfully. So now this is my logical schema. This is my logical schema and this is my physical schema. So let me just cross this. This is my physical skim. Okay, look. Physical schema. Look, if you look at the physical schema imply table. So here the concept off l came comes into play. Look, l came filed toe article l came filed or let me just select this option This table Diskin market physical look lording knowledge model lording knowledge model. This is the template l can file toe articles or d a. Has selected this Templar to perform loading into my temporary tables. So this is the look imply. Look, let me just like this option look lording. Ah, Knowledge model. L came filed too. Article So L game Look, this is another. This is another temporary and other Ah, temporary table. Ok, look, l came filed toe article so these two are temporary cables here. Now, if I select the imply option, you will see the concept off integration Knowledge model integration mall knowledge model will insert detained my target table. Here's the concept off integration knowledge model. Look, integration Alit model has used this former this template to load that into my target table . Article inside dot Global this template. Okay, check knowledge model. We will see the example of technology model later. Okay, so here in this example, we have seen this. We have implemented this particular scenario. So in this particular scenario, source and target having different schema, that is like the concept off l came comes into play. Otherwise only I came will be used if source and target having the same skimmers, sourcing target having the same schema then only I can will be used. Okay. So, look, this is my If you look at the logs here in third growth, I came. Number of throws insert. This is the cord. Okay. Insert into this imply table. Select. Okay, select this table from this table. Okay. From this table. So this is D court, which OD a have been generated. These are the log step Bester processes. Okay. How old? EA has implemented dis scenario Solo disses source source in the target are in different scheme. I look the other different scheme. Okay, So that is why the concept off l came comes into play. Look, if I see like this, look, uh, just take this look. L came five to article. It came from historical first. Can Vologda time that are temporary table here. Then it will join it. Then I can will join it. And then finally it is going to look at that time my target table. So this is the concept which I wanted to show you. Okay. Hope you understood this concept. Hope your industry the implementation off this scenario in ODI A thank you so much and have a great learning 16. CKM with FlowControl, Static Control in mapping: Hello, everyone. In this lecture, we're going to look at the example off Sikkim, which is check knowledge model. So check knowledge model is used to perform data quality checks. Okay, now NC came. We have two types. She came flow control and she came static control. So first, we are going to discuss He came flow control. So let me just right here. She can flow control. And she came static control. Okay, the first time gap. Really discuss He came flow control. So for this. So for this, I have created a cable. Employees underscore Tampin, my target production environment. Okay, let me just Ah, you've the select equity employ right now. Table is empty leaders nor their time. Their table. OK, execute table is empty. So this is the table structure, okay? It's a model for this table structure. Is this employer the score temp? So now I'm going to create a mapping for this example. Okay, go here. Mapping. Create new mapping file under school mapping. So I understand. Example off he came, I will create a new mapping. Okay, So it will read data from this implied or text file. Fine and other file equal read data from this department file. So let me just show you d imply file here. Ah, so this is my employee file. Eso in this particular file, I have a get some duplicate Wellies. Look, destroys duplicate destroys. Two rows are duplicate. Okay, look, duplicate values. This is my department table, which Okay, now next what I will do. I will have a joint. So let me just use this joint. Okay? Now, next, I will use this particular table. Employees underscore attempt, But before that, I will create constraint. Primary key constraint for this table. So what I'm going to do, I'm not creating stable level constraint. I'm creating or d a level constraint. So the basic purpose off creating time, Vicky constraint is that or the able identified duplicate values on the basis off dead time Ricky constraint. So now I'm creating in order to create a new constrained nooky. So prime Ricky. So I'm giving employees underscore Idea is the primary key attributes. So I'm selecting which attribute you want to select as a primary key. So I want to select employees. Underscore idea. The prime Ricky dragged this attribute here. Andy, in this particular section now. Control defining the database. Check this option. No. Right now, there no duplicate. Well is indeed. Okay. Just until this option. This this is valid for boat flow control and static control. OK, now would I will identify duplicate, believe on the basis of Prime Nicky. This is valid for board flow and static control. No. Fine. Save this. Already saved. Now I'm going toe drag this table. Look, primary key constraint has been created. Now I'm going to drag this employee table. So first I'm going to have a joint between these two tables. Join is going to be on the basis off department. I D. Okay. No, just drag and drop age named this. I want to select this name. Saved this. So look, mapping has been created. Next step is to select D template. Okay, So physical scheme up. Physical representation off this mapping. Okay, fine. This is the temporary table where debt off department table is going to be stored. So lording knowledge models by default Ah, loading knowledge module is selected. Is this this template? Now, let me just select some few camp, other template. So let's say if I select this template, let me just like this. El Cam l came ask your request. Will. Okay, for this table, I will select any came SQL dressed. Okay. So if this template is not present here, you can import it from here. No, let more deal any damn important or let more do. Let me just import few l came templates. Okay. L came. Let's say if I want to import this if I require death Important progress. Okay, close this. So in this way you can import a few more templars if you want. Okay, so I have selected this for this. I'm selected this next I will select the template for my I will select. I came template. So for I came Templar that the fourth template is selected as this. So let me just change it. I came a school control apparent so likewise you can import the I came template as well. So if this template if this particular template is not present here, if this template is not present, you can imported from here. Okay? No. In this particular template you have the option off in the idea, Mom Template In this particular option, you will have the option off flow control instead of control. OK, so the 50 ah I came template I have selected now I'm going to select the SC came template Okay, so let me see came article you can imports He came template as a If you want Let's say if I select she came as well so see chemist Will will do the following tasks drop better table The value set us fall so it will not drop ever table If I said like this value as truth identical Drop the other tables Drop check table so it will not drop Check table as the values their test falls Okay now integration knowledge model So this is integration knowledge model I came a school control a pen. Okay, so first I will select the flu control So it is a test. True. If you want to change it toe falls you concerted falls if you want to start a true you considered crew started Controllers status falls No save this so s but the definition off low control so it will create two tables You dollar table which is at her table and simply check table in the staging schema. Okay, this is low control. Then once the tables are created equal, check the duplicate values. Indie I dollar table. OK, so for flow control, equal check duplicate Well is in the eye dollar table, but for static control, it will check the duplicate rallies in the target table. Now, let me just execute this. So executed this, Okay? It has been executed successfully. Select. Okay, Executed. This Look that I have been inserted in my table. Now let's look at the logs off this execution, So refresh this. Now the acing the logs here. Okay. Variable. Okay. Mm. So look at the first step. Drop work table. So if the table is already present in my stating scheme Michael, drop it. Create worktable. So these are the steps off l came. Okay, let me just okay, create work table. So first of all, it will create the temporary table e dollar department table so it will create the department table. Disturbingly dollar department table, temporary table, my staging steamer. Then it This is loading de nickel Lord data in the department table. Ok, so, department that I will be loaded there in the temporary table, see, dollar department table. OK, in this particular table. Okay, Then it will create another table, which is dis table. Let me just scored equal Create, See dollar implied table. Let me just clue of it filled. This is Ah, next just Lord data. Then it will load the time d e dollar see dollar imply table. So hair el Kamel al Kara create tables. These two tables Indie staging steamer Dennett will insert their time in the stables. Okay, this is this is these are l came steps. So these are I came and see Kim steps. So look, drop fluke cable. Okay? It will drop table if already exist. Then it will create flow table. Look at these tables. So first of all, it will create I dollar table. Okay, look, this is the same takes off I dollar table. So we're discussing flow control. First we're discussing flow control. OK, so it will create I dollar table. Then it will insert values Indie I dollar table. Look, it will select all the better. It will have a joint between these two tables. Dainik will select all that airtime the Then it will insert all that airtime the high dollar table. Okay, this is D Grady for inserting that title I dollars table look identical. Create. After inserting only the kind the I dollar table, it will create two tables First, triple create detectable, which is de stable. Let me just SNPs underscore Detectable. Let me just created this. Okay, let me just creative. Okay? This is D data present in my SNP underscore. Detectable. So in this particular ah, a belittle. It could have number off air accounts. Enter count. This is the interface number entered. Message number. Uh, ski my name. OK, Schema name, etcetera. It will have Deve information. Ok, this is SNP check table. As a fact, it will create two tables SNP detectable and dollar table. No, we're create tech table Delete. Delete previous Check some occupying. Then it will create Enter table. Look, this is this in text off our table, which is e dollar imply in this contemptible, it could create this table e dollar table. Ok, discover So afterwards, Creator table, it will insert their time the editor table. So in the other table, it'll insert duplicate rallies. Okay, It will insert duplicate. Really? So it will select Get off from my dollar table. Ah, it will select duplicate. Well, it's from the i dollar table and it will insert eating toe here in the e dollar table, which is enter table. So if you created the error table, you will find duplicate values. Look, so did their duplicate. Really? Look, let me just show you D ah, Decca five imply. Look, duplicate values duplicate Well is okay. So let me just repeat it will select data from i dollar table so select which better it will select only the duplicate values. Look, this is the duplicate Kuwaiti. So duplication Look on the basis off primary key, which is imply underscore idea. Okay, this is the equating for duplicates. So it will select duplicate values from the i dollar table and a nickel insert duplicate rallies, Indy, this table enter table so at her table will have duplicate Well, if no next step is delete error from control cable. So look at this step gillete. Okay, So once death is inserted into inserting toe, enter table duplicate for loser inserted into every table equals delete. Afterwards, it will delete get a duplicate rallies from I dollar table. OK, First, it will insert data it will insert duplicate values in the in the other table. Then once duplicate for loser inserted, then it will delete duplicate values from the i dollar table. So this is dick waited Now I dollar table after this execution off this query I dollar table will not have any duplicate. Well, if Okay, then insert, check some den, It will insert values in the A simply underscore detectable. So, as I said in this particular table, it will have interface name, message number after context, etcetera. Okay, then commit transaction. It will perform the commit transaction. So these are all steps off flow control. Okay, she came. Look, this is he came SQL Okay, so look model responsible for doing this task of Sikkim SQL SQL Sikkim template. Okay. No, let me just remove this now. Next we're going toe. Look at the example off static control. We look at the steps off static control. So let me just tell you that in static control, duplicate values are checked in the target table. OK? Once all the data is inserted and the target table, then see Kim will perform duplicate value check. Okay, So there's a difference between flow control and started control and flow control. Duplicate values are check on the I dollar table. No, let's look at the example off. Static control. Go here in the Okay. Now I've been sick. Flo. Control Two falls. Static control to true. Saved it. Okay. No executor, Def. Now, I will check logs here imply in this core temp. Now, in this part correctable, it will have duplicate rallies. Okay, so there's so many doctors. So first, what I'm going to do, I'm going to use this step. Trunk it first. It will truncate this data. First, it will truncate this table de nickel insert. Really? Vinick? Okay, No, save this executor death. Okay, Now check how many of those now dis table will have duplicate values. OK, look Duplicate. Really? So now let's look at the steps off this execution. De first trying to tell you 215 Okay, so first it will create. Ah, two tables, see dollar department table, see, dollar imply table. Then you insert that kaindi in these two tables. Okay, so this is these are the steps off and came truncate table. First, it will truncate the target table. Okay, so this is a different step. Insert new Ruth. Now let's look at this. Insert new Ruth. Now what will happen? It will have a joint between these two. So let me just show you the mapping. Let me just drag air, okay? Look at this step. It will have a joint. So what will happen? It will have a joint between these two tables. So this is dickweed. Okay, then it will insert all the data and the in the target table instead. Off putting their 20 i dollar table, it will insert their tiny target table. So this is the step. Then it will commit transaction. So in the target table, I will have duplicate values. OK? In the target table. I will have to pick it will. Then it will create check table this table. Let me show you. Just herbal check table is SNP underscore? Detectable. Okay, then it will create check, table, desk, table. Delete previous checks. Okay, identical. Create better table. Delete previous error den. It will in certain errors. Good in certain toe. This Select this. Okay, so this is the duplication check. So from so from target table equal insert duplicate where I live in the at her table but look delete professor, but it will not delete duplicate values in the target table. OK, this is important. My final target table will have duplicate values. It will insert duplicate rallies in the error table, but it will not delete it. When it comes to flow, control equals deleted. It will delete the duplicate values from the I dollar table. Okay, when it comes to flow control, so insert. Then finally, it is going to insert well, even detectable. I already showed you were data it will insert in Detectable. So finally. Okay. Cleanup stepped. Delete. Okay. No. So this is my final table, which has some duplicate. We're losing it. So their differences. So the difference between she came flu controlling static controllers. Sikkim, Flo Control will check duplicate rallies in the eye dollar table. Okay, but when it comes to Sikkim static control, it will check. Duplicate well is in the target table. It will not delete any duplicate. Well, even the target table. OK, it will insert duplicate. We live in the in the dollar table, but it will not dilated from the target table. So But when it comes to flow control, it was deleted from the I dollar table Ok from this particular table. Then finally I came Model will insert the time the final target table. So when it comes to flow control, your final target table will not have any duplicate value. So duplicate really is checked on the basis of primary cure Unique. I already showed you the example. Okay, So hope you understood the concept in the EU's off these two flu control and static control . So thank you so much and have a great learning. 17. JKM Journalization: CDC Simple Implementation: Hello, everyone. In this lecture, we're going to implement the concept off CDC with help off generalized knowledge model. So this is my source table, and this is my target table. So the concept off CDC witches chain data capture the concept is whatever the changes happening in Mysore stable, it should be It should be there in my target table as well. Okay, let's suppose I have added a neuro in my soul stable. A new row should also be a getting my target table. To implement this concept, I will have to use the knowledge model called J came, which is generalized knowledge model. So now, first off, all I'm going to egg the table structure off my source table in my OD A. This is my target table. So now I'm going to create a new model first, Fourth underscore Simple technology. I'm selecting Oregon. Logical schema production and difficult sores. Reverse engineering. Fourth select. So table, which I'm going to select its source Angus core sample table. Ok, reverse engineer. Yes. Now next, I'm going to select this option generalizing in the generalizing tab. I will have to give the generalizing knowledge model right now. This knowledge model is not defined. So I have to import this knowledge model G came no late morning. So in order to important, I will go. J came import knowledge models. So here I will select game, sir Chair de game. So you will have two options. Dick m let me just select this article. J came Oracle consistent in j came article Simple. If you have only a single table, you will select the option J came article simple If you have multiple tables and you have parent child relationship Primary key foreign key relationship jewels used this particular J came j came article consistent in this particular example will be using J came simple version. Okay, so important Progress close this now select G came simple version since I'm selecting this simple So save this so for certified reverse engineer control. So this SC came knowledge models If you want to include anything came ordeal, you can include So right now I'm not including it Just Okay, fine, Close this So look source Underscore Simple new mortal Now I'm going to create a new mortal form A target table Underscore simple technology Oracle product Children Desker Target Reverse engineer. Okay. Targetting underscore. Simple reverse engineer. A guess. Control Generalizing So? So for my target table, it is not necessary to include d generalizing model. The reason eight, We going to implement CDC on my source table? Not on my target table, so just save this. No, this is my source table, and this is my targeted. But now I'm going to implement CDC on my source tables in order to implement CDC. Right click change data capture Acto CDC. Now what I'm doing, I'm including this table toe CDC. So I want dissed ableto capture the change. Yes, at to CDC. Then what I'm going to do, I'm going to start General Click Care Star General. So here, this is the default subscriber, so you can create multiple subscriber. So if you have multiple systems reading this data source data, you will create multiple subscribers. Right now, I have only a single mapping which is reading the source better. If you have multiple map ings, you will. You will have to create multiple subscribers because this subscribe a will read the letter extracted actor and wants to get eyes extracted. A quiz daily data. So you have to create multiple subscribers for multiple map ins Right now, since I have only a single mapping so I will sell a good default subscriber click. OK, okay. Generalizing require primary key Own sores underscore sample. OK, this is important Step. So you have to create the primary key constraint. I don't know the A level or on the be 11. If you have db level, you don't have to create constraint. Oh, no, dear level So? So let's create a primary key constraint. New Prime Vicky So my primary key constrained will be product i d. So product I d will be my primary key constraint underscore I d save this now created Now I'm going to start journal Star Journal. Okay, select the subscriber click. OK, so general has been started. Look, it is they do it did. Now let's look at the logs here. So if you look at the logs here, look, it will create subscriber table subscribe All table will re detained that if the data is read value one will be assigned okay, otherwise, very will be zero. So it will also create a trigger. So let me just show you the trigger. Create trigger So this is the trigger creator replaced Trigger, so it will clear trigger on my sore stable. So this is my source table. It will create trigger on this table. So if let's suppose if a debt is inserted on my source table so a trigger will be fired that will insert Dekel. Copy that. Attain my another table. OK, now close this. No. Next step is now. Next step is to create a mapping. Now, let me just show you d mapping steps. So does he. Mapping underscored mapping. Here, let me just paste the source. And this could imply Stargate underscored imply. Joined these two tables by position, no source. And this could imply end target and discourse source target. And, of course, no source underscored. Nor can play. I have to select which distributes source and, of course, sample table. Then I have to select target Underscore. Simple, irritable disturber. Okay, by provisions. Okay, fine. No next step is Let me show you D and some. Marty was here. Ah, physical. So Marty level select Integration Control Apparent journal. Okay. Okay. This murder, I will say like I came. L came escrito article Fine journal setting. Okay, Okay, So you have to click this you thought stable, then go in the journal setting there is an option called Generalized Data only. So if you enable this option and if you do not enable this option right now, it is not enabled. And then you execute this mapping. So what will happen? It will insert all values in my target table. It will select all values from source table and then equal insert all values in my target table. But if you check this option, if you check this option and then execute dis mapping equal only in 30 changed data. Okay, new data now. Right now, I am just unthinking. This option saved this. No executor. Dis click. OK? No, First I want to populate my target table. Look, Read it. Ah predictor, go Here it seems stated annettor Okay, Okay. Cook book One important thing. Go ahead, Flo. Control surface falls. Do some changes. Save now. One more thing, Jane, Death journal attribute. Just remove this. Ah, constraint Just removed this key right now. I just remove this. I don't want this. Okay, executor, this. Now look at the target. Cabe, Look. Data has been populated in my target table, OK? No. Next step is so. Next step is I'm going to enable detained that option. So go here in the source cable journal enabled the chain that asserting Click cve. Now let me just do some changes. Now I will insert new row in my source table. So inserting new in Mysore stable. So value 1 39 1 40 Del do Okay, Insert this value in my sort. Stable. Commit executed. This Kuwaiti. Okay, value has been in circuit successfully. So what I'm going to do? I'm going to execute this Look. Generalized. Data only off the death Only one road will be inserted. Only disrobe will be inserted in my target table. Not the whole gator. Okay, executed this. Look. 1 40 This row is inserted in my target table. This particular okay? Now, this is how you insert a change value. So let's say if I want to update this particular value, let's say if I want a beard, this room Abdic update source And of course, sample table set unit cost equals two. Let's say 11 50 north. 1100. There product ID equals two, 1 40 Let's say if I want to execute this particular Cody. But before executing this particular query, I will change my ah, no late models go here I came. Look, I can Astral control opened this particular option will perform the below operations, insert new value insert new growth through option is true. But this particular model, this particular template will not update anywhere. You can't update annually because this particular template doesn't have the option off Updated it, will. It can insert, commit Flo control value Cetus fall so it will not perform flow control steps, recycle letters falls it will drunk it. So this table so this particular model will truncate your target table if their taste present in your target table. If the value set is true, it'll trunk it will first truncate your target table. Okay. If the debt I spend delete, always delete all rose from the table. Okay, create target cable will create your target table. Ok, delete. Temporary object is satis toe. So what I'm going to do, I'm going to select another. I came template. Okay. So first I will go logical here. Go here Journal target So integration type of sectors control opened, so I will select the incremental type. So now go physical. Go ahead. I came already. Now I will have different options. Oracle Incremental update If these options are not present If these templates are not present you you can imported. You can import it from here. You will go in the I came ordeal go in the commodity important Alit models. You can import it from here if these options are not present it Okay Okay. I was selected I Camp Oracle Incremental Abdic Okay, so here I will have the option off up Get to save deception Source Go here Journal. Okay, Generalized data CDs. CDC option is enabled. Now if I'm goingto execute disc weighty, they celebrate my data. So Ovando has been updated. I have to perform this. Commit operation Commit select star from sore Stable? No, this is my source table in my source table. I have 1 40 product I given 40 value is 1150 Okay, so in my target table, I have value 1100. Now If I execute this mapping value will be objected in my target table. Okay, executor death Look 11 50 Values updated. No, Let's suppose if I delete this particular room. Let's perform. Delete operation here. Delete from V it. Product underscore. ID equals two 1 40 Wonder has been deleted. Commit no select star from 1 40 protector games when diluted. Here, product has been dilated. Now let me just execute this mapping sessions. Doctored, successfully executed. Look, product I d 1 40 destro has been deleted from my target table. OK, so this is how you implement CDC. First, you have to. First, you have to perform first. You have to add the J came knowledge model. Then you have to act to see Dizzy Star Journal. You have to start journal, okay? Subscriber is important if you have multiple mapping. So for multiple mapping is you will have multiple subscribers. Not now. Let's look at the logs here. The 1st 1 dilated step. Okay. These are the logs I game so you can explore these logs so hope you're industry. The concept in the EU's off CDC thank you so much and have a great learning 18. EXPRESSION Transformation: Hello, everyone. In this lecture, we're going to learn one off the transformation component, which is called expression. So transformation is basically changing their tough from one form to another. So let me just show you deep component, which we're going to discuss their in this lecture. So first, I'm going toe open the mapping, create new mapping, cross formation expression. So really discuss dis company expression. OK, so these are the different components. Get a used to perform transformation. Okay, so let me just show you the data models. So I have created a new folder called Transformation. In this particular folder I have created to data model source employees target imply. So this is the table which I'm going to create in my target system. Let me just execute this. So table missing. I'm missing something. Okay. Now execute this command for table has been created successfully. Let me just quickly. They're stable. Okay, so this table is present in my target system. Employees and discord detail table. So these are the columns off this table. So what I'm doing, I'm first. I'm going to extract data from an excel file, so let me just show you depart off Excel file employees underscore 50. This is my Excel file. This file has so many records. Total 50 records i d. First name, last name gender. Country age. Early celery. Okay, Now let me just Ah, book it first. Let me just click this. Ah, fine table. If this okay, they will live This imply underscore dictated reverse engineer. Okay, now close this. So this is my table. Okay, Attributes. So look at their tributes. Employee I d. Number first Name. Last name. Gender, Country. Water age yearly Salary number. Ah, OK. Number commission. Ah. Lent is 10 decimal, two decimal points. Monthly salary total int 10. And it is two decimal points. Company name. Okay, Varta. So these are the attributes off my ah target table. Mm. Okay, My target table. So this is my target table. These other attributes off my target table Safety nous now, just Ah, this is my source. Imply so imply Underscore 50. So in my in my excel file, I have these attributes. I'll get these attributes. No. So this fifth, the 1st 1 I'm reading Decker from May from an excel file from Saudi CS. If I looking second, implying the score detail. So this is my target table. So the attributes off target table are more than my source table. This is my source stable. So, in between these two, I'm going to add the component transformation company called expression. So when you have to perform calculations using this expression component, you can perform calculations you can add remove unwanted column. Okay, Like here. Let me just join it. So by name by position. Okay. By name. Click. Okay. As it is, I d. First name. Last name. Gender, Country age, Yearly salary. Now here I have some extra columns like commission monthly, salary company name. So click this expression column attributes David attributes of this. Okay, in the expression. Now I'm going to Xom three more columns. One column How many columns I will leg. Okay, Literally 1 to 3 columns. So good and three. So the first column is commission Okay. Data type for commissioners Number number data type is number maximum is 10. Second attribute is monthly salary. Last oneness Company Cos Watcher Monthly salary is monthly. Salary number number. Dan, ankle decimal point. Okay, for look, These are the attributes I d first name D there two books are coming from this table. Imply, which is a extracting data from May from C s. If I look in this particular okay. Now, look, See if this Okay, go ahead in the physical across this. Look expression. So, Richt, here, let me just select this expression. Now, I'm going to join this. OK, But first, Okay. Expression. Everything is okay. Connector. A case. First input. It is connecting from this. Implied. Okay, now what I'm going to do, I'm going to perform some calculations. So commission select this here. I will provide an expression commission. Should be 10% off yearly salary. Let's say King doing it by 100. Multiply by yearly salary. So these are the functions that you can use. Look, Cheever do you can use some key words if you want Matt operators, operations operators, like plus minus two. Worried. Okay, I'm using Mark. Implication doing in. Okay, now use the round function round two decimal place The fifth. Ah, expression for commission. Okay. Next monthly salaries in orderto kill, clear the monthly salary. I have to divide yearly salary directed by 12. This should be included. It's around this. Okay, so with the help off this transformation Step expression. You can perform some calculations. Okay? This no company. So here. If you provide the name here, let's say if I'm providing Teradata all values off, all values off this table will be terror Decca against all values. Teradata will be inserted. No company name will be Teradata Monthly salary will be yearly. Salary directly with 12 then round Comotto No, Next I'm going toe map this expression with this table. Let's join it. Okay, Mab. Dis last name should be This gender should be This country should be this. It should be Death Commission. Okay. Monthly salary company. So if I select this employee gay look expression dot Already this employee ideas coming from this expression. Likewise, the faces, like dislike lets the first name this is This is coming from this employ table. Okay, Now I want the first name last name in a parties. So upper first and last name should be in a per case. Last name up, but during that. Okay, save this. Now I'm going to Lourdes. My daytime. This particular cable. So which knowledge more do I have to select? Okay, this is, um, for this knowledge more. Do I have to select? Control the paint dirty Control the paint. Save this. No executor death. Okay, Flo. Control, let me just disable this. Ah, physical. I have to disable this first falls. Okay, here I will go. L came. I will select l came file and came fire. Go SQL I will say like this. Okay. Since I'm selecting from file to ask you save then execute decisions Doctored. Look at the logs here. No. First I have to check the data. Okay. Data has been inserted. Look at the company. Teradata Montali Salary two decimal place. Okay, look. Okay, so my dad has been populated. So this is how you use the expression? Ah, this is the transformation step director used to perform calculations in case if you want toe egg some Collins or if you want to remove it, you can use this transformation. So thank you so much and have a great learning 19. Join Transformation,Distinct,Filter,Sort: Hello, everyone. In this lecture, we're going to discuss few more transformation components. So these are the components kept. We're going to discuss it in this lecture. So first off, all I'm going to create a new folder here. This is my project D wh And Discord Project. Right Click new folder. So transformation. So here in this particular folder saved this. So a new folder has been created. So this particular for level before transformations. So now I'm going to create a new mapping new transformation transit over entrance 01 Okay, so in this particular session will discuss all these transformation component. So, first of all, I have to select my ah source. Okay, so I'm going to extract data from 2/3 files. So let me just show you the source file. This is the source. I'll implied or text. Okay, let me just 50 toe. This is the department door text. So let me just show you goody files, Guetta so implied or text department. Our text. So in my implied or text file, I have few duplicate values. Also, I have department I d Fife, which is not present in in this particular text Fire Department. Fife is not present in this particular textile department. Also, I have department I D six which is not present here in this particular employee. The Bartman six is not better. Okay, so dealer to file so defense. Okay, this is employees department. Next step is to select the join. So here I will select joint. So always joined this. Ah, select this. Select this. So there are different types of joins in a joint. Left out a joint right out or during cross trying natural joint. So by default, it is inner joins if you want to select. So this is inner joint here. You will provide the condition. Foreigner join we just department i d goes to this department idea. So let me just select my target table as well. So my target table is target and this could imply this. So this is my target table. So look, this is my target table. This one, Okay. No. Ah joint. So let me just select the left outer. Join by checking this option, you will select left outer join. But looking this option you will select the full over doing. Let's take this option right out of joint. This is right out to join the scroll across. Join natural joint. Okay, Let let's select the on tech disruption left out there doing so pride The condition here. Ah, imply equal toe this department idea apply. Okay, So Okay. Automatically mapped the i d mab Dis first name. Last name. Gender Age department name is not mapped. So let me just like this. So now it is mapped, so I'm using left outer. Join save this a particular def. No, Look at the logs here. Refresh. Refresh it. So these are the logs. Mm. Insert. Okay, select. Look left outer. Join in. This is Dick Condition on. Now let me just select D table. So in this particular table, I have some null values. I will have some null values. Look, 166 duplicate values, some duplicate values, null values. OK, since I'm performing left outer join left outer join with select all the values from the left table in the unmatched rules will have no values. Look, these two rules the lab Novelis. Okay, let me just select the right outer join. So likewise, you can select the right out of doing now for right auto join. You will have no values here in this particular employee table. Select this. So I want first I want to truncate this table. Let me just select the physical schema. So physical skin. My first. I want to truncate my target Table control append Flo Control set it. Toe falls started. Control falls drunk It true. Save this and executed this. So no executed this. Look, I will have no values there in this particular imply table. But if I do a left, join. So this is the example off the joint. This is the example off. Let me just This is the example Off right out to join. Okay, so in this particular case, all those off department, including the rules unpaid with imply. So this is the example Off, right for department is right and employees and left. So let me just see the Gord Book employees dissed Able employees left in this department is right. Okay, this department is right, and this is left. Look, I am having Right, So in this way you can have cross joined full auto join natural join? No, next what I'm going to do. So let's say left outer join. Okay, Right out. And I said This is the example. Off right out. I joined null values. Indirect herbal. So now department is left and implies, right? So now next transformation step is distinct. So from this Okay, so before Okay, now you've distinct Ah, okay. I d first name. Last name, Gender age. I want this to be deleted department. I d diluted and name. Name should be dis name. Hey, it's ok. Distinguished. Give me the unique values equal Gilly duplicate rallies from this table. So in this table I have these two duplicate rallies district duplicate rallies. So using this distinct, I will remove the duplicate values from the table. Okay, now distinct. Ah sort. Then I will. You thought Thought when Ah Victor sort you're column either in descending or ascending order. So by using this transformation step, you can sort your deck. I that ascending or descending order. You can use multiple columns as well to sort your data. So OK, SARC and then filter. You can also use fritter here. This so thought here you will provide the sort condition I have Want it descending order. So I want h Tobey in descending order, Apply second filter. I want just drag second figures that I have one. So here next I will apply a freighter. Dan Ivan. I would join. Now figure is filter conditions. Filter is like a were close. So if you point your cursor here, you will get the definition off. Filter this look. Define a subset off data by defining part off. Aware close. It's like a Faircloth. So filter condition. Now you think this will tell you will filter your Decca, so I want Ah. Okay. I want each it between too deep and 40 and some applying physical condition. And Tokyo 40 and department name he calls to say is department name. This is the department name equals two sales. Okay, so this is my filter condition. Apply. Click. Ok, so I'm starting this aging descending order saved this in my target table. Broke your physical deficit of flu controls that has fall strategy Control falls. Okay. Mm. L came filed toe. Ask your exchange it. Toe this. Save this. Join condition. So I'm having a joint I'm having with join, right. Outer join sorting on the basis off each filter I'm applying for Deron age and sales. Ah, department name Then finally I'm going to Lourdes that I in my target table which is implying now See if this and execute So if you want If you do not want to execute this and if you want to see the simulation, you can click. Ok, so this is how this mapping is going to work. So all the scripts are present here. You can see the simulation but if you want to run this, you can great care. Get the green button now click OK, sessions started. Refresh death, it seems. Did it annettor? What is exactly the error? Let's look at the other. Okay, Erez, insert new row. I came and still control appendage and error in. We're late Identifier writ and okay. Ah, drunk. You're dis table. Yes, I won't diss table Toby Truncated first it is truncated already truncated. Okay, I warrant Let's change the filter here for the condition. Let's say instead of this now Executor death insert neutral. I came or dio failing. Target connection Target production. Okay, Department name is invalid. Okay, this department name. Okay, this department name is invalid, so just drag and drop it. Okay, look, gender it is coming from this last name first name employee I d employee ideas also invalidating. Just drag and drop it. Okay, generally it I d Okay. First name, last name. Gender Age department Name is coming from. OK, you can verify it. Imply name. Okay. Masked with employee I d match with decided. First name, last name Gender Age department. Now see if this now execute this. No. Okay. Successfully executed chick number off inserts toe Only two does that inserted? Because the condition I have provided is this for a little condition is where department name is should be seals If I change the filter condition, let's change the physical condition Department name in sales that tough apply. No, I will have department name sales manager get executed this sales in a church. So let's say if I want only female fills in a charter only female. So I will have a condition like this and gender equality female apply. Okay, save this Sales in a chart and gender should be female. No, I would have scenes in a chart. Okay. Gender is female. Let's say if I want imply ID equals to one, then I will have only one drawer. Let's say let's ag another condition. And ah, employee, I d equals two. One or nine. I do in one or nine. Save this. Apply who, Greg? Okay, look, I will have one or nine sales or a chart. Female. So this is how you apply? Conditioned using the face, their transformation. So execution awning. So if you want to execute this filter step on fourth, you can give source by default. It s elected escorts. If you want to set on staging, you have to give stating or if you want to give target, you will have to select the target. So I'm selecting knowing it means that by the fault it is going to run on your source system. No executor death. Okay, Save this and exit group once again. So I will have only does because first it will truncate this table. Then it will inserted Decker. So I hope you invested the concept and use of different transformations in order gate. Thank you so much and have a great learning 20. ODI Split Component: everyone in this lecture, we're going to discuss split company. So using spread component, I can split my data in multiple tables. So let me just give you an example. But first, I'm going to create a new mapping New split, Let's say underscored transformation. So the office, the name off my mapping now I'm giving you an example. Off split component. So this is displayed component. Okay, split that into multiple parts. Okay. Now Ah, transformation. So Okay. So my thoughts if this implied our text, Okay. Now, first, I'm going to use an expression. So if you further want to enhance your data, you can use this company called Expression. Okay? Creating attributes. OK, let me just convert my first name, toe uppercase up, but okay. Jinger each department I first name last name gender. Each department next time going to use. Expect so displayed component will expect my daytime to multiple tables. So let's say if I've worn toe ah, split in three tables. So this is my first table. This is my second table injustice. My turkey able No. Okay. Just joined it. I'll put number one. This is I'll put to interred. This is our poetry. Okay? No, I d First name. Last name. Gender. Each department. Let me just select this option. So it will have split condition. This is input connector input. One expression. So this is so. This is input in this is expression. I'll put connectors. Things going out from this spirit company. So it is Output one is connecting toe imply one table output to which is this arrow sign is connecting toe imply to table all portrays connecting toe imply three table. So now it should have a condition. So this is output one. So which that I should place in my implies 01 table. This is my implies 01 table. So I'm reading this file. Let me show you Defile which I'm reading, implied or text file for Let's say I want o place. I d weren't to three in imply one table. So what I will do? I will give a condition head I will spread condition he had ever right expression. This is so this is disturbed taste coming from this turtles coming from this expression. So I will select expression. Okay, so I d imply i d in 123 So imply I d 123 will go in the imply one cable apply. Okay, Alberto, this imply to table will have this. Get me Put a condition I d. And 456 apply. Okay, 3rd 1 I will have a condition. 456 789 Expression I d. Seven 89 apply. So Okay. No imply. I d expression. Look, just match these two. I do it. I d Okay, expression, daughter. The first name match this first came with this expression or first name. This first name is implied. Or first name. Okay. It is coming from this employee last name. I'm matching this gender each in department. Likewise, I will match these last name each gender it department. Okay, No, look at the turn. But first name gonna these two last name Jenga it. Department name. Look, no, you have to confirm it as there i d. This ideas. Nine play D first name, last name, gender. Each department name I D. Which is coming from expression. First name, last name, gender, age and department name. So, like wife imply I d. First name. Last name? Gender Age department. Okay. Now look. Expression Door department This is coming from this expression. So save this physical now I will select the physical part. Mm. Employee, I d I came. I will select control Append. Ah, this is Sectors falls. Save it This imply toe. Let's select the ah control A ping flow controllers falls This one Cvik, Now executor this Look at the logs. Look at the data implies 01 Look. 123 now implies zero to look at. Their time implies 0 to 456 So six is duplicate implies zero tree They're kind implies 03 Ah, there is no they retain employees. The rotary. Why Look at the split condition. Okay. And okay, I have to in now. Apply this. So I have to first grancare this in orderto Ah, Okay. First I have good drunk your death. I came so let me just drank it. Drunk it. Let me just manually drunk it. All cables do And three now executor death actual Run now! Okay, now it could have data. Okay, Second Kurd one if 789 i d with nine destroys duplicate. So hope you understood the concept in the EU's off split component in odi A thank you so much and have a great learning 21. ODI Set component: Hello, everyone. In this lecture, we're going to discuss another component called said So said component will allow you perform operations like Union Union. All minors intersect, etcetera. So let's discuss this company. So these are my tables? Implies 01 So first will discuss union and union. All this is my implies irritable. These two tables are present in my target production system. So I'm going to take target system as my source. So just for the example, for the sake off example, I'm taking target system as a thought. So employees Vito one implies leader to Okay, so the ter table is empty implies you're three dis table is empty, so I'm going to use the set component. So first, let me explain the difference between junior and union. All so union all will include all the duplicate truth. But when it comes to union, it will not include duplicate Rose. So let's say union all so it will select. Get off implies 01 also considered get off implies zero to so ah ridiculed this Look, make me def correct me again. Executed. This This is union. All so union All will include the duplicate rallies. Look duplicate. Well, if but when it comes to union, it will include all the debt off implies 01 are certain. Will include all the debt off implies zero toe, but a lot include duplicate well, literature present between these two tables. Now execute this so only unique values Look, desole will be will occur only one time. But if I do union, all this really occur twice. Look Okay, Now we're going to implement this with the help of this component. So select this in port. Okay, this is the most important. This is second in particular to no next. I'm going to select the Kurt table, which is this. Let me just connect this table now. Employee I d. Finally first name, last name, gender. Each department name. Okay, map first name, last name, gender. Each department. Okay, let death. Ah, attributes. So these are the attributes ding journal information set. Okay. No, Look at this operator's input. Connectors in Allport connectors. Output connected is only one. It is connecting toe. This implies 03 table input connectors input one input to this table. And this in the second table. No. So I can so look imply zero to union Imply 01 Okay, implies zero to union implies 01 So if I want implies 01 Junin implies video toe. I can change it here. Ok, I can change it from here. Just uncheck this option. I want the 1st 1 Toby implied zero toe. I want 2nd 1 Toby implies 01 now I'm using the union All so union all will include the duplicate values. So in my final table I will have duplicate value. So right now the stable is empty after execution off. Ah, this mapping. I will have duplicate village in my final table. Execute this now Check implies zero tree table. So now let's look at the head of So I've got better. We're just enter now Let's look at the error message. Department name Invalid identifier. So I picked department name. Okay, Department name Fine. It imply I d Fine. First name, last name Jenga it in department. This one. First name last name gender. Ich and get Blackmon name? No look. First name last name. Last name. First name gender. Each department saved this. Now execute this again. Now check Employees Little three. Look, I will have their time my table look duplicate values. So if I do union instead off Union, all instead off Union on. If I do union, I will not have duplicate rallies here in my table. So let me just drunk in this table first drunk it Gabe implies 03 Commit the operation. Uh, already. Okay, not imply 03 table empty. So instead, off union, all I'm doing Union, save this exit your death again. No, look, I don't have any duplicate well in my table, so likewise you can use some other operators minus intersect. So when you are going to use the Intersect, it will select only the common values intersect. All will include also will include the duplicate value. So okay, intersect there some other Ah, operator state you can youth. So let me Just used the minus operator imply Vera one minus implies zero to So it means that this is my first implies. You're one minus implies leader to So now First, let me look at the imply zero to data. So if I have that are any room off implies zero to it should be eliminated. It should not be included with minus okay. Miners will exclude Rose off implies zero to Indy implies you're one table. So look, I think I will not have any room implies 01 minus ah implies zero toe. So, Ivan, have Victor implies you're one has three rows. There is not a single Ruoff implies 02 which is present in imply 01 cable. So I will have this value in my implies, the rotary table. So let me just select this drunk. You're dis table an executive. This mapping again. So now let me just check. So I will have just value. Okay, 456 implied. Zero one. Okay. 123 Okay to anyone. No. 456 So this is OK. This is not imply. 01 minus implied. Zero toe. This is implies zero to minus and price. Everyone like wiser tests implies zero to union implies 01 22. ODI Lookup Component: Hello everyone. In this lecture, we're going to discuss another component called Look up. Let me just drag and drop here. So look up! Component can take two inputs. Not more than two inputs are allowed Also look up Company can only do in a joint as a less left outer join. So if I compare this look up component with the join so join can do multiple inputs it can do more than two inputs. But look up Component can only do two inputs. Okay, so when we're going to use join, we can have in a joint cross join left outer join natural joint But when we're using look up component we can only have in a joint or we can have left outer join also when we're using Look up component we can have We can either have the Syntex and see Syntex or we can have a school expression Syntex Okay, now let me just connect to inputs with this company So the first input will be implied or text Second is department Okay let me show you Defile! So I have done some changes in my file Let me show you Defile here So implied or text file . So I have a get this extra extra row here. Okay? This extra dough department, dark texts. So this is my department file. I have added this extra go seven and commercial. So we're discussing this look up company. So now I'm going to join this company. So if I select this option, look up. Ah, connector points. So if I select this option so I will have to input the first input will be driver in port. Second input will be look up input. So if I select this, if I connect the implied table with this look up component, this will become my driver input. And then if I connect this with this Okay, let me just connect this. This will become my look up input. So this is the Let me just show you d Look up. So now this is the look up in produces the look up in port department, and this is the driver input. So look up, table is the one where we're going to search the data. So in this particular table, we're going to search the department. I d. Okay, so if I've learned to change it, I can change it after. So like if I uncheck this option So if I want this to be my look up, I contingent Aslund. So, department, let's say five want imply this will be my look up now. Department ideas going to search here in the level. Certainly department I gained deep employees file in this particular file. Okay, now this table is this table will be my look up table implies department will be my driver input. Okay, so since it is extracting data from a file So I'm calling this just for understanding purpose. I'm calling this other table. So as the information is stored in the form of table So I'm calling these two other table, OK, so do not confuse it. So now if I removed if, let's say if I connect this first No, let me just connect this first. Now this will become my A driver and this will become My employees will become my based able look up table. Look imply Let's remove this. So I've want this to be my look up. Okay, so this is my driver employees fight and this is my department file. Is my look up file. Okay, look up connected, General. Ah, all put connectors Output one it is right now. It is not connecting toe anyone So I'm going toe. Have ah able target table employees. Let me just connect this employee I d Connect decided with this first name last name Gender . Each department named with this name. Okay, No, select this option. No condition. I've owned department I d equal toe this department idea. The match rules My people match truth. So if I select the first option, this will be my This will be my left outer join. So if I select this option and return a rule with the falling default will leave. If I select this option, return Nauru, then it will become the inner joint. This mapping will become inner Join multiple match trolls. All rose? No! Mastro's return. Nauru. Okay, so this will become my inner join for Let me just select this option. So right now my target table is empty. So look, multiple match rules. Yes. Return all the match growth. No match rules if there is no match return. Noro! So this will be my inner joined. Let me just execute this executor. Death logs. Okay, successfully executed. Now let me just execute this look. Data has been inserted in my table. Let me show you D ah, logs. Mm data which is inserted in certain Euro Gourgue. Look, inner giant employees in a joint department. A condition is implied. Department I d goes to Department department idea. Okay, so this is my inner join the 1st 1 This is my energon. If I select this option returner over the folding default values so this will become my left Alka joint. So I will have some knowledge values in my table. If there is no match, I will have some nine values. So this is left outer join. So let me just execute death. No, like major. Okay, I will have some. Okay. First, I will truncate this and then I will insert values in the table. So here. Physical. Let me just select the models. Ah, I came Let selected control open. I want table Toby truncated. I want to truncate this table. OK, fall Cetus falls. Save this. Now execute this. This is left outer. Join Okay. Null values. Look, I have some knowledge values in it now. So what? I'm selecting physical If I show you the logs here. The 1st 1 Look truncate table identical Insert Well is in de table. Look left outer. Join and look at the condition. Okay, now next, what I'm going to do Look a match. Look, look up. Attribute. Default value in order by So returner bookie. So I have some not values in my table. I want D's not values to be replaced by anything. Okay, let's say if department name is null, replace it with any replace it with any. So, look, I'm not giving any default, will you? Here in the department. I did the reason because department ideas not included in my final target table. So let's say if I execute this, okay? Already executed. Look. Okay, let me just execute this, save this and executed deaths again. Because the reason I'm not giving it because department I d is not part off my ah, final table. Look any It is replaced with any. Let's say operations. Let's the operations department. So it is going to replace now with operations, save this and execute this again. Operations. So this is how you replace in al value. So if I look at the logs here. Insert look. Gay statement When department ideas annulled an operation Okay, in a dirty left outer. Join this. This is the condition. So we're using this index and see Syntex. Next work we're going to do we are going to explore some more options. So error multiple rows will cause a mapping failure. So let me just give you an example here. Let's say let's select this option. Let's say I have this. Ah, this table, this Fire department dark text. This is selected as my If I check this option condition connector points, this is selected as my look up department. So let's say if I put any duplicate value here, let's say commercial GAAP, Edith, let's say seven, Department 87 but department name is be a So this data is coming from source system. So now I don't know which department is correct. So it is not possible. Attack two department two departments having same department I d. So one is incorrect. Either this is incorrect or this is incorrect. Okay, So in order to white this kind of situation, I will select this option. I will select this option if any data that is coming like this with multiple values. So this mapping should not work. This should give me an editor. Okay, so I'm selecting This is descent ICS SQL expression. So I'm using a school expression. So if let's suppose if I'm selecting if I'm selecting this okay? If I'm selecting this and my my this Look up tables like this having duplicate values. Okay, One department is assigned to multiple department names. Then I This this rule will allow me to execute this mapping back. If I select dis option, this will not allow me to execute this mapping. This will give me another Soto provide this kind of situation. You can select this particular option Now let me just execute this executor death I will get then out of this will not allow me to execute. Look, I will get another look. It is giving me an error. Drunk it, Gord, what is the additive? Single rose up quickly returned More than wonder So it is not allowing me to execute this because I'm using this expression this in text This intakes will not allow me toe execute now if I select this option, select any single room. Okay, Select any single room. This will give me the first. True. This will give me this. Okay, this will ignore this. Okay, But if I cooperative based here, then it will give me this. Okay, My dad, I will have this value for seven. My daughter will have this be a But if I 40 year, then I will have this early in my data. Save this. Let me just edit this. Okay, Now see if this employed or text Let me just added this Fine no department, I will have commercial now execute. Select any single room. Remove this default value. Save this now execute, not values. Okay, look Commercial. I will have the 1st 1 this very 1st 1 Okay? If I select select anywhere you okay? Select any really? So it will select the 1st 1? No, executive this now I will have be a here. Look, Be a okay. Now let me select some other options. So select a first single word room. So if you select this option, you will have to first sort the data I did in ascending or descending. So let's say I want to start this department name in ascending order. Saved If so, if you look at the data here so department I d commercial n b a see commercial and be I So I want to sort this in ascending order. So be and then see, this will be the sequence. So from this sequence it will select the first. So it will select this. Be a okay. So equal sort did their time sending order. Okay. Name in ascending order then equals selectee first value. So the first rally will be this Be a So let me just execute death. So I will have be a here. So if I check descending so it will select Ah, it will select commercial because now this really will come This Roble come first because it has sorted the naming Descending order So now let me just execute death Now I will have commercial in my doctor. Okay, Execute death commercial Look commercial! Okay, now default Well, you know select selector Last single room name in descending order. Okay. Meaning descending order so commercial will come first. It will select b I because equal select The last true for the last row will be be a so be a actual select being let me first execute this Look, Connect this again. Okay, select the equal. Select last row It was descending order. So which road? Let me show you again. Descending order Commercial first. Then bh will select B A. Okay, Select B A Look B A Now let me select D and single Rose If you want to provide the value for end, let's say if you want the first value to be selected Descending order descending means dead commercial will come first. Okay, so it will select equals select commercial Because I have given the entire value one look commercial. So let me just select the A debt auctions. Let's say select any single room if I select this option and execute death Okay, let's say this option. Okay, so for deception, I don't have to provide Ah okay. Deadline descending or ascending order. It will just select the random first rule. Okay, Like you could select from this tactical selected first room. So now what I'm showing you I'm showing you d logs. So go here. Refresh this. Look at the logs here in certain euro gored. Look at the expression. So this is this thing takes off SQL expression. Finally it is going toe have left outer join. But ah oh dear You is using a scale expression. So when you're going to use this Syntex so you can use this in text. If you look up, table is very small and you're selecting fewer Collins like one or two columns. Then you can use this. It's still expression mattered Otherwise you will select this and see Syntex. Okay, are you can use joints so let me just show you the other. Let's say first, saved if and execute now In this case, let me just show you the long fear Look select then from So this is SQL expression Now this is rule number checking a duplicate values. So let me just simply fight So let's say I have two tables implying department So first name last name then I want department name as the So this is a school expression Sing kicks off SQL expression Select department name from department which is B e there the dark department i d he goes to a door department Underscore idea So this if left outer join So here I am doing leftover going so you can also write like this. Select start. This is same kicks off school expression. This is amusing. Left, Doctor doing and cease Index select Start from employees. A department Be he left outer. Join department. Oh, good. Join department. Be on condition on a door department. Underscore. I d Could Toby Dart department underscore I d. Here. I will select first name, last name in the department. Name, name, last name Deng. Department name so diff if and teething dicks. And if if a school expressions index every third, you will use this thing. Tex Estelle expressions in Texas, when you look up, table is very small and you have and you have to select few columns like you have to select one or two columns. Then it is advisable to use a still expression. Otherwise, you can use the Syntex. Okay, so this is the concept off. Look up. Hope you invested the concept in Do you use off all these rules? Okay, So thank you so much and have a great learning 23. ODI Aggregate Component: Hello, everyone. In this lecture, we're going to explore one more component called aggregate. So aggregate component is used when you have tow aggregate your data on the basis off some average Max Main, etcetera. Now, let me just open a new mapping here. New mapping, angry gig. Now we're going to discuss this component aggregate. So first off, all I'm going toe include my source data. So implied our text in department. Our text. So I have added a new column in my implied or text file. So let me just show you d implied our text try. Look, Salary column are, however, did this ality column department file is as it is. Okay, let me just remove this commercial addict. Okay, fine. Implied or text? If I let me just open it. Fine. No, I'm going to have a joint. So, first of all, I'm going to have a joint between these two files joint on the bases off department I d. In our joint. I'm going to have inner join. Okay. Ah, this department ID equals two department department. I d applied this. Okay. Next. Well, we're going to discuss the aggregate component so disconnected create attributes on target . No, I don't want toe. Include attributes here. Okay, So I will man release like their tribute to what I want. I want salary. No, I don't want salary. Right now. I've owned department name. Let me select the department name here. Okay. Next time. Going to create my own attribute. So after book name is total salary. So this is my target table in my target table. I've owned department name, total salary and number of employees on the basis of department name. So I will have total salinity. Doorbell salary. Nomadic salary will be numeric Next. I'm going tohave number off employees number of employees, data type nomadic. Okay, save death. So these two Collins have been a big next. I will provide an expression for total salary. Should be equal to some off dis ality. Okay, so aggregor death. Some used the some function salad e so you can use the other functions like Max Main apply . Okay. Next number off. Employees for number of employees, I will use the count function. Ah, gunk and the number of employees. I will use the idea here. Okay, for total salary goes to some of the salary number off implies equal toe count off decided . So this is my aggregate from chance will select this. So if I select this option in my journal sexting, I have a chap called Manuel Grew by close. So if you want or d a group debtor by itself, you will not provide any information here. So they will automatically go pure down the business off department name. So these two columns are in these two columns. Aggregate functions are used. So in this particular column, no aggregate function is used, so they will agree Gate on the basis off department name. Let's say if I include a few more columns here. First name, last name now. OD able group on the basis off name, first name, first name and last name. Okay? No, just remove this. So if you manually warned toe provide the columns that you want to grow by, you can at column there. Okay. Here you can add columns. So now let me just add the target table. So target table is this Okay? Creator to build No, on tick def department name. Total salinity number off employees Save this. Let me select the I came. I came Control Append. Okay, flow control falls. I want to truncate the debtor Truth Executor, this mapping So mapping has been executive. Now let me just check. Look that I have been inserted department at our number off employees working in a turn department three Onley to imply the working in sales department or tell salary 16,000 2800 for marketing number of employees working in marketing department toe. OK, so now let's look at the logs. Here. Refresh this. Look at the logs. Refresh. Okay, The 1st 1 mapping. Okay, Drunk it first. It will trunk it. Okay, This is my insertion. So it will grow by on the basis of department name. Okay, let's say fine. Include. And the age here it will group on the basis off name and age. Okay. Not executed this in third neutral gored group by on the basis of name and age data will be different. Okay, because it is grouping on the basis off name image. Let's say if you manually want to grow by different columns, you will provide the columns here which you want to grow by. So let's say I want to grow by on the bases off name in each. So I will provide the columns here. Name in it. Let's say only name. Department name. Okay, let's the only department I want to grow by only on the basis of department. See if this an executor death, Grady. So let's look at the result care. Okay, fine. So I'm groping on the basis of department name only. Look, it is going to select the department name. It is only going to select the department. I'm a little group on the basis of department name. Also in my target table. I don't have the age, so it will select the department name. This name total surreal. Very in the number of employees. And it will group on the basis off name. They will not take age into consideration. Look, the selected group by column is department name. Let's remove this. Okay, let's remove the ages. Save this an executive again. Okay, fine. No, I'm going to explore any other. Another option, having so having clauses used when you have to apply filter on the data which is already grouped. So this is the data which is already grouped a chart number off employees working in a child tree. Okay, if I want to further apply afraid that here I was used a having close. So let's say I've want count number of employees, aggregate count number of employees this greater than two imply I d greater then toe apply . I was used to a having close Okay, count further. So having close is used after group buy clothes executor death now look. So if I executor difficulty only one result greater than two. Good look having count imply I d directed in tow Okay group by having closets used after group by. So let me just using other clothes instead. Off this, let me use some Are facility a Greek gig Some salad e greater than 4000 Apply Save this total salary which is 16,000. Okay, says sales department only one department but its sales having salary greater than 4000. Ok, let's say only 16 talent. Let's say 2000. See if this look Dragon in Qatar departments having said it regretted in 2000. So let me show you the logs here. Mm. Look, having some of salary greater than to talk after group By so having clauses used one study ties group. Okay, if you want to further felt that the result you will do they have in close having some of celery grated in 2000. So hope you understood the concept in the youth off aggregate component in Saudi A thank you so much and have a great learning. 24. Pivot ODI Component: Hello, everyone. In this lecture, we're going to discuss, pivot and uncovered components. So basically, we used to pivot component when we have to summarize our data. So let me just give you an example here. So this is my source stable. So, in my source table, I have the here quarter sales. So for a year to 10. 15 quarters, first sailors 1200. So you turned 15 quarters. First Sailors 1000. This could be another porter. These two could be different parks. OK, so that is the reason why it is repeating, but sale is different here. 1000 and this one has 1200. Okay, So likewise, I have different years, number of quarters, their full quarters and differences. Okay, I have to products for this table. Look, quarter 2nd 1st product this a second product. Okay, Now we're going to discuss the parent company. First of all, I'm going to create new mapping. Privett, underscore. Let's say a mapping now. I will use my source stable. First source stabilised. Pivot, Underscore source and difficult Pivot in my target table. Where is my target table? Okay, this is my target table. So I want to present this information in the form off this. I want to present this like this. Okay. In which I should have here different years and quarter vices. Quarter 123 and four. So if you have to present if you have to present your daytime more summarized way, then you can use D company called Pivot. Look, I'm basically here in this example. I'm basically transposing quarter. Okay, so I'm basically transposing quarter this column in different quarters. Quarter 1/4 to quarter tree and quarter for so and then I have another column. Cordiant. Okay, now let's see an example. Now, I've used paper care. Perfect. Connect this to this target table. Okay, Select. No, connect this. No, actually, I don't want to connect this. Let's a match this. Okay. Plagued if attributes quarter one sale quarter 234 here. I will provide an expression for quarter one says so. Quarter this quarter will be this quarter one spill quarter, cree and food Click. This journal attributes quarter 1234 And the expressions matching. Though I will explain this later journal in the journal setting. Okay, pivot, aggregate function. So let's use the some functioning, So I will tell you why I'm using the aggregate function Role Locator? This is important here. You will provide the column that you want to transpose. I want to transpose quarter. So I want to present this column like this quarter 1/4 to quarter, 3/4 for so I will give walked out of here. Okay, Next give quarter. So I want to transpose this in. Drew, this is column. I want to present this column in different in in a row. Look, this is row. Got a 1234 OK, no. Next I will provide the values. 234 So quarter has different values. 123 and 4 4/4 So first quarter, second quarter, third quarter and fourth quarter. The first quarter will be assigned to this. Second will be discouraged. Will be the sport. Will be this how I will assign this. I will go in the attributes matching route quarter once is will be assigned to first quarter quarter to quarter to the and quarter. Food saved this now journal aggregate function some. So this is my source data. So what I'm going to do, I'm going to take some of these two These two are different products, but they belong to the same quarter and same year. Okay, so OD, I will add these two cells. So if I change it to a minimum or gable take minimum value from these two. So minimum values 1000. If I change it to average, it will take average off these two. Okay? Quarter the same since quarter it. Same Now, save this. Already saved. Let me select the integration knowledge model Control. Apparent flow Control falls. Okay, truncate first. Drunker. Dissed table. True saving. Now execute this mapping operator. Sexy, Executed. Execute this target. This is my target table. Look that I have been presented in the form off quarters and years for 2015 against 2015. Okay? Quarter. I have to give value a very quick quick. I'm doing some mistake here. I'm doing some mistake. I know what mistake I'm doing. So quarter one sales here, I will provide the sales, not the quarter, actually. Sales Remove this 30. Apply seals. Last one on drag this. Like this. Okay. Since now it is okay. Saved it. So first, or do you have a trunk? It my target table than it will insert Wellies. Look, I will have. Okay, look. Quarters quarter one cent for you to 10. 15 quarter to sell for year 2015. Look quarto one 2200 quarter 32 three, Tolerant. Okay, now let's change it to minimum. Okay, lets tend you to minimum. Save this. So from these two values, it will take minimum value. So from these two values, it will take minimum Valley with 1000 execute this look 1000 like 1300. Okay, between these two. So this is how you use deep erect function in orderto better summarize your data. Look okay. 25. Unpivot ODI Component: So let's discuss another company called on Perfect. So UNP every day's opposite off perfect company. So this is my target table. So look So what I'm going to do, I'm going toe take my project Able s source So where is my target table now? This is my target table and this is my source table So I have truncated my daytime muscles table Okay, Right now there is no their time myself stable So I will use the UNP pervert company. So now what I'm going to do I'm going to present this information we just present in my target table. I will present this information like this year's and quarter and say OK, so I will un perfect d warka What the one say this quarter to quarter 3/4 for I will uncovered So now on paper component Connect this to target, Create attributes on thoughts Yes, I want all these their tributes here. No. Here, select this on Peric attributes you dragged this year toe this but his ear dragged this year expression will become here okay for quarter and sales. I will not pro Ghani expression. Okay, so now journal name and perfect ruling locator. So I will define Ruler Kate Arrest quarter because I'm going toe unfair disc Warta I think it can transform. Okay, 34 Let's change it to quarto one water too. And 1/4 to 3 in the last four. So in this particular in this particular column, I will have 123 or four values. I will give sale. I feel quarter once is, which is one will become this. Okay, quarter to send quarter tree and Corta full quarter for Okay, gonna go point. So give fine Journal rule quicker quarter attributes. Okay, fine. Saved If no, execute this mapping. I perfect must be configured with Rollo Cater for what is the problem? I'm getting row Locator. Let's change toe undefined. If I execute this, I think it does not seem sort of. Now let's change it toe. God, I think it should work. Nobody. Yes. Okay. Yes, it is working. Successfully executed. Now let me check my source stable, which was empty. Now it has dead dozing to 1800. Okay, look, this is my toxic table. Year 2015 quarter over. Intelligent quarter one Tozser Dr Toe 1300. Okay, 1,400,000 Okay, So this is how you, on paper your debt are using the UNP revert company. So hope you're just the concept. Thank you so much and have a great learning. 26. ODI Procedures: Hello, everyone. In this lecture, we're going to discuss procedures in article that I integrator 12 c So when you open a project so this is my project, you will have a default folder. So when you first create a project that the four folder will be created inside this folder , you will have different options objects, packages, mapping, reusable map ings procedures. So we'll discuss procedures here in this lecture. The procedure is nothing but the execution off different tests step by step, execution off, different tests. So inside this procedure, you really define different tests. OK, now, the reason Ah, the prefer Normally we don't use procedures very often in or D A. We prefer mapping or reused packages. Okay. The reason we prefer these to objects over procedure is because of the fact that procedures court best execution and these two have objects are object based on execution. Procedure takes a lot of system resources. So the normally used mapping czar we used packages. We don't use procedures very often. So let's create a new procedure, right click, click toe this procedure and then right click new procedure Here you will provide the name . Let's a thought underscored. Let's hear Insert is my name procedure name multi connections mean that if you check this off in this mean that this procedure will manage multiple connections at the time. But if you uncheck this option, this means tech. It will manage only a single connection at that time. Okay, so by default, this option will be checked. So let's check this option cost. Here you will provide different tasks. So now, plus, I'm creating different tasks. Okay, first task journal, drop table cleanup option. So if you check this option, this will clean up your temporary tables. Temporary objects. Okay, Now, I don't want to clean up my temporary objects. So long, counter. So if you are using insert statement, you will juice the insert here. Low counter update. If you're using update, you will use up debt. So I'm using the drop, so I will select none. Okay, Log level 55 level. Ignore error. Right now. I don't want to check this option. Don't get come on. Dissection. ISS for writing DD El Gamel commands in my source command. I will use the select statement. So in this particular task for this task, I'm not choosing the source command. Since I'm I'm dropping my cable, so I will only used it. Target command, transaction isolation. So these are the list off different options. So look before I have different boards. Defour more values the db specificity. Beer driver specific. So if I check this option before more so since I'm using the oracle database, so article will use only the four can read committee is not supported their fuel. Okay, at this moment, Oracle will only support read, commit er cereal, See? Realizable. Okay, so article I didn't choose this option Read committed are dis option if a check different option so read committed means that let's okay, re committed. Recommitted means that transaction can only read that if if the connection is committed, it will only read data. If I'm, I'm going to check. Read uncommitted. It can read the uncommitted data. Look, read uncommitted transaction can read data not committed by another transaction. Okay, it can re debtor if it is not committed. So let's check the read committed option. You can have a look or look at these different option by yourself. Okay. So context. So if I take execution context a drunk time It will give you an option off selecting the contact. So I'm going to select the production. Logical. So for this table, I'm using my target table. So this is my target table. Okay, drop. They will drop this table. Nordic. So logical schema will be for my target table production in the court. Target here. I will provide the drop command drop table then The name of table Name of table is this drop table. They saved this technology arrival. Select Oracle, Save this source Command, I will not give any option to source command since I'm using a drop command in my target table journal. Okay, Saved this option. So this is my first step. Second step is create table, so create table cleanup counter law Counter not log level five dot Get Come on, let's select Read Committed. Brought a chain of using the production context. Logical. I'm using source, so I will create. Okay, so I will clear dis table in my target. Not in my source. For target production target here I will provide the Create Able Command So create table commanders. This let me just paste to create able command you table product underscored this product. I gay name unit price unit. Cost apply. Okay, so this is my career table. Command sores command. So I'm not bragging the source tomorrow because I'm only creating table in my target cable . So there is no use off source command. I received this option. Create table. Next, I will select the Lord data. Now, I'm going to Lourdes that time. My target table, which I have created, So I will select. Since I'm inserting, I will select Insert option log level five, Stargate Command Here. I will provide the article as technology Select Read Committed production Logical Schema Target. Okay, here I will provide the insert statement. Okay, Vic, let me just copy this. Biest here. I'm going to explain this. Insert statement Source command So source will be oracle. Read, committed. Brought a churn. Logical schema resource. Now what I'm doing, I'm selecting my source. I'm selecting my product table so this table is present in my source system. Okay, so I'm giving the logical skim off source. Look, does my logical schema here. I will provide disk amount. So this is my source system. And this is my target system. Select star from product target. Come on. Look at the target command in certain to product. I d name category I d unit price unit cost values, product ideas. This this source. Okay, product ideas off this. So if you only pride, let's if you pride three columns here, you will Probably three columns. Okay. Eso if you Let's say if you only provide If you want to provide product i d. Product name injure target table. You have only these three columns. So here in my source command, you will provide the product. I d underscored I d comma. That's okay, but right now I'm selecting all rules. So this is my target. This is dissing text. You have to follow product I d. Product name, Category 80 unit price unit cost. So journal is this insert save deception. So this is dick turd step, which I have created, So no drop cable so not executed this click session started Operator, I will get in ever the reason I'm going to get an error because I'm dropping this table, which is which is already not created table or who does not exist. So I'm dropping the table which is not present in my schema. I'm dropping this table. Select star. Look, I'm dropping this table, which is already not there. So what I can do? I can ignore this option. Ignored, ignore out of So what it will do. It will ignored the step. So then it'll go here and then it will load the data. Now select this. No, no. What is the error? Okay, Create Able Lord map toe. Any physical schema create table target, not map toe. Any physical schema? Mm. Okay. Wrong target. Understood for the Children. Disco target. I have to select this production production production. Okay, now execute this. Now look successfully executed. Look, the first step is ignored. Second equal. Create table Thurday for Lord that time. My target table. So my target table is this Look. 50 18 roads have been inserted in my target table. Okay, So look, it has ignored the first step. If I open this look table of you doesn't it's just back. It ignored the first step. Then it created the table, then included that time. My target table. Okay, so now we're going to explode. Option off options. So here I will provide for every step. I will provide an option. Let's create three steps so the first step will be dropped. Table drop table, then create table turd One is Lord data. Okay, type is bullion. The first option is set US fall second option ist successful third option is it nestles So basically the purpose of this option is Let's say if you don't want to execute these two steps you will set the value falls and you will set the last really toe to. And now if you execute this procedure, only the last step will be executed. These two up thes two steps will not be executed because the values attest falls. So no next What I'm going to do in order to do this first I will select my task here I will go in the journal. Okay, now select the options Always execute so I will take this option. So now it will not be executed So I will select drop table dis option this me instead it will be executed on the basis off value which I I'm going to provide. Let's say five Prague true value. It will be executed. If I provide false value, it will not be executed so it will be executed on the basis off value which I'm going to provide. Okay. Likewise, I will select this option unchecked. If dis option create table, check this option. This option will be executed on the basis of value which I'm going to provide. Okay, now. Next. Likewise. I do the same to the strip. No options. You know what I'm going to do? I'm going toe uncheck these two wells. Just I'm going to set this value to true. Okay, Save this option now. Executor. Def. So currently I half How many rows? 18. Ruth. Okay. After execution, I will have to get six Ruth after this execution execute Look, it has executed only one step. This one. And how many rules inserted? 18. So total, I will have 36 rules. Okay? It has executed only one step, So there are some other auctions. So if you want to provide the textually, you can provide it. Okay? If you want to provide any value, you can select this option. So better is to select the bullion option. You can also provide the choice here, but I will select the bullion option. Okay. Save this execution just refresh death. You will find execution off this procedure. Okay, now. So as I said, procedure is nothing but the execution off different steps. Execution of different processes. So the first processes, doc Table second is create table Third ist Lord table No. You ever industry the concept in the youth off procedure in in in articles that integrator 12 c thank you so much and have a great learning. 27. ODI Variables: Hello, everyone. In this lecture, we're going to discuss variables in article that integrator 12 c. So variables are used to store information, it can hold only a single value. So you can store either text. Indeed. Your date in a valuable. Okay, No. So first off or let me just opened this project. So in my project, I have a section or you call it component called variables. So if you have to create a new variable, you have to click this right leg and new variable. But first, before creating a variable. First of all, I'm going to open my mapping, so I will open this mapping. So in this particular mapping, I have used to Philly death here I have provided a value one toe category idee so category I d equals to one. Let me just open this. No. So instead, off you think this really one. You can also use a variable here, so But first you have to create a variable in order to youth variable here. Okay. So in order to create available, you have to click here, right? Like new variable. Very 1,000,000,000. Let's of art. 01 is your very blimp data type, you can select text No Marik Gate and Phantom Eric. So I'm using the pneumatic. I will store the indeed a value keep history. So if you want to keep history, you raise with the all values. So if you want only the latest value Toby maintain you will select the latest value. If you don't want to maintain the history, no history option is selected Secure value. By checking this option, it means that you're basically securing your variable value. So now this variable value will not be visible to a user by taking this often. If you uncheck this option and if you select all values now, all previous values will be visible. Toe were user. Let's say first time, you have selected the value too. Then after some time, you have changed a value. Let's a tree. Now, by selecting this option, all values so user can see all the previous values off this variable. So if you don't want to maintain, history will select no mystery option. So I want to maintain the history. So look now, user can not see the value of this variable. Okay, let's say if you have stored password in your variable. You can take this option so that user cannot see you value variable value. So now I'm going to uncheck this option. The four regular tempo I didn't get to Let's say see the fall 20 of this very ballistic test three Save this. So this is how you create a variable. Now variable has been created. Now I'm going to use this variable here in my filter. So click care. No, it is not visible. I have to just close this now I will open it again Now it should be visible to me. I will take care. Look, Project variables. Now I can see the project Variables there. Variable instead off Valley one. I'm going to provide this variable 01 the first days Project name which is DW return disco products. Second is very blame Wa 01 applied this. Okay, so now instead, off Valley one. I'm using the variable. See if this So before executing this mapping, let me show you my sourcing target table. So this is my source stable product in this source table. I have product I d name, category of the unit price unit cost. This is my target table. So I'm selecting category I d. What is the value? Have set variable value three. So I'm going to select the rules. The category A history. So no executor. Dis mapping. So now let's look at the dog table. Look, Can't agree. I get three. Okay, Rose with category 83. So now So what is the benefit? Refusing this variable. So the benefit is Let's suppose if you have to change the value And let's suppose it 100 map ings. So what you're going to do, you have to open every mapping and 300 mapping and you have to change it manually. Okay, Now the benefit of creating a variable is now what I'm going to do if I have to change it this value again, What I'm going to do, I'm just going to change the value here now lets it to. So I don't have to change this value in every mapping. I will just change this value in in a variable. Now execute this now, executive, Look toe, let's say four if I have pretended to for now, look so wherever this variable is used in the mapping, the value will be changed Okay, look. Okay, let's suppose this variable. It's used in their supporting 100 map ings. So if I change it, if I turned the value here, the value will be changing 100 mapping. Okay, so this is the benefit off creating a variable. So now execute this. First I have to execute this. I have seen the value I have to get before. Now execute this. I really get for okay. Get degree. I d equals two foot. Now let me just open this variable, okay? There is an option court. Refreshing. Okay. So if you want to take value from database, you will use this option here. You will provide the skin my name. So my skin, my eyes. This here I will provide d quickie. So the query is this? I want This is my okay. Alerts a star from warrant of court table. So this is my table from this table. I will get the category I devalue, so I will select var underscore value that they're Varnay. Michels took category I d now I will get let's say in this case I will get to Okay, no control. See Biest here. Dick quit is valid. Okay, So now you know how I'm going to execute this Kuwaiti For what I will do. I will click this refresh so you can click, refresh from here or save this or you can take You can right click and you can click Refresh now sessions started Now look at the logs here. Refresh. Okay, The 1st 1 variable value has changed. Okay, look refreshed. No executor this now the value will be too. This is my target table. Look, now the value will be do for different How you change the value So look Mm credible history Look, history latest value is what is the latest latest Really is this Now if I change again, let's see if I get this value in my table. Let's say MM up dick Third, get agree. ID equals two. Let's say four. Get agree. ID equals 24 We're var underscore Name equals two. Get agree. I d vit wasit var underscore value equal to four. We're volunteers. Courtenay Michels took category I d Now execute this query. I'm updating this category. I do value to four Wonder objected. I didn't the make No, The value will become four. Okay, No refreshed it. Now again, you have to execute difficulty In order to execute this query, you have to refresh this option. Okay? This value will be replaced here. Okay, The value waas for Let's suppose this valley Wall Street. Okay, Now, after executing this query after executing this query, this really will be replaced from the valley, which is present in my database. In my database, the values for four Well, three will be replaced by four. Okay, so now saved if again, just for the safe site. Again. Refresh this where you will be forward here. Okay, Valuable before here. Not execute the Kuwaiti food. Let's say if fighting this value toe let's of one again execute this wonder objected. Commit operation. Okay, now again, Refresh. This session started executed this Now I will have rows with category i d one stargate. Get agree idea of one. Okay, so this is how you get the data from a database. So you have to In order to execute this query, you have tow click the refresh. Then after clicking this refresh, the value will be objected here. Okay, so if you do not provide this section if you do not provide anything value, this really will be treated. Okay, so now. So this is how you use variables in a mapping. So when we are going to discuss packages, we will learn how to use variables in a package. So thank you so much and have a great learning. 28. Sequence in ODI Part-01: everyone in this lecture we're going to discuss about sequences, so sequences are database object, get are created to generate values for prime wiki columns. So let me just give you an example here. So in some cases, when the values off Prime Ricky columns are not known to US view sequence to generate values for Prime Ricky columns, for example, when customers do the transaction in a bank transaction, I d should be automatically generated transaction ideas not known. Tow us. It can be a primary key, but it is not known to us likewise, when customers order some product online or their numbers should be automatically generated against each product. OK, so in article data integrator direct three types off sequences standard sequence, specifics sequence, native sequence. So in this particular lecture were going to arrest us native sequence. Okay, No, First of all, I'm going to create a sequence. So this is my project. This is my project. By the fourth, the first world there is this first folder name is first folder. I have created another folder transformation. So in this particular folder, I have packages map ings, reusable map ings procedures. Also, I have a component called sequence. So in order to create a sequence, I will right. Click new sequence. I will give her name here. Let's say sequence. Name is native. So since I'm creating a native sequence native sequence. So here there are three types of sequences standard sequence, specific and native sequence. The first we're going to discuss native sequence. So if I choose this option native sequence, it means that I'm using the sequence that is created in my database. Okay, so here, I'm going to provide the schema. Let's say it is created in my target database. So my target databases this So these are the commands which I'm going to youth. Just competence based here. Target schema. Okay, So in order to create a sequence, I will use this command. Clear sequence in the name. Let's underscore energy stand. So native start with one increment by one. So it is going to start with one. Then it will start incriminating by one. So let me just execute this good find, So command has been executed. Okay. Next, I will just copy this baster. Okay. Name of secrets. Dark. Next value. Okay, so this will give me the next value off a sequence. For example, if the current well, you is one. If I'm going to execute this query, it will give me the next rally, which will be to So this will give me the current value off a sequence. So if I execute this current value, I will not get a current Wehrli. Okay, So first, I will have to execute this crazy, so it will start with one. So current value is one now one Okay, next values to let me just execute this next values too. Current, will you is now okay. If I execute death next really will become three. Okay, it will stop increments this value by one. Okay, so ah, sequence has been created in my database, so I will choose the sequence It is I have just created in my database. So I will choose production sequence, which I have created this and this. And Nikki, this one. So I will choose the sequence. Save this dog. So let me just open it again, OK? So native sequence. So native sequence is the sequence which is created in my database. But using this option, I'm saying I have to select the sequence. It is created in my database. So the database sequences this So I'm choosing this database sequence. So next what I'm going to do, I'm going to open a mapping. So this is the mapping, which I have created. Sequence mapping product. This is my product table which is present in my source cable. Okay, okay. Execute death. So this is my source table. OK, target table. Let me just truncate my target table in case if there is any data Truncate table. Okay, fine. Currently, there is no data in my target table. It is empty product and just go temp. Okay, this is my targeted. But now next, what I'm going to do. Look, or that I d. Ah, product product. I d name category A. The unit price unit cost. So in this particular component, I have a big another column called Order Underscore. I d. Okay. Now let me just provide expression here, so I'm going to use the sequence native sequence next. Well, you click. Ok, Saved this. So currently Matar gettable is empty. Now execute this mapping, so no. Look at the target table. Look. Each row is incriminated by one so value is started from value for than 5678 Because the current really with four currently lives three, It will start from four. Okay, so Florida damp 45678 Okay, Greinke too. If I'm going to execute this mapping again, it will start from 23 like me. Just execute this mapping again. It will start from going T. The first rule will be current eatery here. I will get printed. Really? To look 23 24 25. So it is not resetting sequence is not researching its value. 27 28 29 turkey. Okay, so next I'm going to execute this. It will start from 42. Now execute this look. Okay. It will start from 42. Or that i d 42. Okay, So this is how you used in native sequence. This is native sequence. So in the next lecture will discuss about standard sequence and the specific sequence, so thank you so much and have a great learning 29. Sequence in ODI Part-02: Hello, everyone. In this lecture, we're going to discuss the other types of sequences. So let me just create a new sequence. New sequence. The first we're going to discuss t standard sequence. So in this standard sequence, the current values stored in the repository we have another sequence. It is called specific sequence in this specific sequence current will use stored in the database Oracle that integrator reached overly from the database. These two are or the A level sequences. Okay, so first, we're going to create the standard to Quinn's. So, like, we just give the name here. Stander s e que. So, in the standard sequence, when I'm going to execute the mapping, all rules will have the same value. Okay, Now, standard sequence in agreement by one. So if you right toe, so value will be implemented by two. Let's say do okay, save this. So standard sequence has been created. Now I'm going to use the standard sequence in my mapping. So where is my mapping? I will use the same mapping sequence and the score mapping, okay. Or Gariety? So let me just remove this so I will use the standard sequence. I will use this next Inter next value. So these are or gay? Love the sequence. Okay, save death. No, Let me just select the mark. Okay. Um model have selected truncate table. Yes. Okay, fine. No executor this start there Underscored temp cable. Look click. Ok, look in my table. I have value toe. So if I look, all values are seen all rose having the same value. So if I execute this mapping again, I will have different well you So the value will be implemented by two. So I will have for so I have defined look standard sequence value to be incriminated by two . So if I execute this again, I will have sequence Look for Okay, Let's see if I advocated this again. Executor Toto, I will have six. All values will be same. Okay, six. Okay. But if I used this if I used this sequence in the procedure the behavior off the sequence will be different. So I will explain to you why the behavior will be different when I'm going to use the sequence in the procedure First, let me just explain to you the other sequence. So now let's create another sequence. New sequence. Now we're going to use this specific sequence. So the definition of specific sequences that it reads valley from the database. Okay, dudes, OD A will read the valley from database Current value stored in the RTB amiss. Okay. No. Ah. Name off the sequences. Specific sequence specific. Okay, I want it to be implemented by one. Okay, now, So here, I'm going to define the ah schema product target. So I will. So I have a table here in my target table. I have a table going. Name of table is Difficile its production Stargate production. And I have on table this table. I will use this table Select star from DRG underscore or D. I underscored. Execute this. So this is my table table. Our view does not exist. O d a. Okay, so in this table, I have ideal processes HR in marketing and the sequence number. So let's say I want to provide the sequence numbers. I want to provide the current value. This. So let's say if I want to select there, it'll process underscore process equal toe ector. So for Richard, the value is ideal. Underscore process equal to enter. So for Richard the value is one little toe. Okay, so I want the current. Really, Toby? 10 to the next. Well, you get your come is 103 So this specifics sequence gives me the flexibility to read data from a database. So if I want to read the sequence number from a database, I can use this. Okay, I can use this specific sequence so that our gets demise. This table name table name is this so the table name should be in Capital DRD and a Skoda underscore. Essa que It should be in capital. Next. I will provide the column. Name column name is the sequence number. It should also be capital sequence number. This is De Vere. Close their clauses. This it'll process equals to etch our so it will process the co stater validate this 10 to the current value is 10 toe. So that sequence will start from 10 tree. The next really will be 103 So if you want to read the value from a database current really from a database, you will used this specific sequence. Okay? By using the standard sequence, you will not able to do this. Okay? No. Once I'm going toe, use this sequence in my in my mapping, all values will be seen. So let me just select the specific sequence here in the expression click here, Just remove this Ah sequence. Project sequence. No, it is not chewing. So first I have to remove this. Now I'm going toe open this mapping again. Sequence mapping. No, it, uh Okay, now project sequence. Now it is suing me, De Standard six specific sequence. Next value apply. Okay. See if this no executive this. So the current value Eyes 10 to execute this. Look, it is starting from one directly. All rules will have the same value, which is 103 Okay, if I execute this again now, all rose will have value 104 Look, 104 Let me just execute this again. All rules will have value 10 Fife. Okay, this is how you use the specific sequence. So if you weren't toe, take. If you want to read that stuff from sequence number from from a database, you will use this sequence 30. Procedures with Sequence: Hello, everyone. In the previous lecture we discussed about specific sequence in the stranger sequins. So no eso This is the mapping which I'm going to execute in this particular mapping. I have used t standard sequence. If I'm going to execute this mapping, everyone will have the same value sequence. Every will have the same sequence number. Look, 2 58 to 58 to 58. Okay, Now, if I execute this again, look, I will get the same value, but it will be implemented by each row will be implemented by two. I will get to 60. Each row will have to 60. Okay, so the behavior off these two sequences will be changed when I'm going to use them in the procedure. So why is it that when I'm going to use the sequences in the procedure, why is it that the behavior will be different? How? Let me just use thes standards. You can send the procedure. So let me just open my procedure. So the 50 procedure I have created insert procedure. Okay. If I use drop table, creative a low table. So if I used this standard sequence in my procedure, the behavior will be totally different. So let me show you to you. Okay? Select star from product This is my source Command Target commanders insert in tow Table name values dandy. These values are coming from source table. OK, Select star. Now instead of product I d. I will give the Sequent standard sequence now in the product i d I will get the sequence number standard sequence next value. So let me just remove the hash now I will get this is so here in the product I d I will get this. Ah, sequence number. Okay, look fine Saved this Options Uh true. True! True. True. Okay, all options are true now execute. If so, my final table is product Underscore procedure is my final table like me. Just copy paste it. Select Start from this, it seems. Did isn't enter. Okay, Did is another. What is the editor? Okay, evict in certain toe this Rick, what is the error? Okay, Goma. No sign. Save this. Successfully executed. This is my final table product. And just go look to 62 to 64 to 66 each raise implemented by toe. Look now the behaviours different. Okay? It is that each other is implementing a value. So look to 64 to 66. Okay, now to 98. Let me just execute this again. Ah, Okay, executor, this again. Now I will get to 98. It will start from okay. 303. Letter to 303 10. Serve. Isaac. When I used the standard sequence in the mapping the behavior waas different Let me explain the reason. So let me just open the excel file Look So when we're going to use d mapping, this is how the flow is going. Toe work mapping. So when we're going to use the mapping, there's a concept off and came and I came so to knowledge models. So let's say this is my source table. This is my thought. And this is my Let me in search. This is my targeted but in between in between these two source and target, I will have the staging area. So data will be first data will be transferred there in the staging area from here. Get a will be processed transformed Then finally it is going to load in my target table. This is source, okay? And this is my target. This is my target. In between sourcing target, we have a staging. This is staging. Okay, staging. So first data will be cooperated in the staging area in the staging area that I will be transformed. So once they get a strong storm, data will be copied in the target tables. So this is how the flow off mapping is going toe work. OK, so here I'm going tohave Another company called Agent. Okay, The rule off agent is very minimum when it comes to mapping. Now, when we are using procedures, the floor will be altogether different. Procedures floor will be like this disses source, and this is target. And in between, I will have the agent. So in case of procedures, the rule off agent is very important. Data will be processed through agent Okay. My source that I will be processed through agent So agent is going to process. The data agent is going to transform the debt. And finally, it is going to Lourdes at that time. My source stable here, Agent is going to just check that the Taif today successfully completed successfully processed or not. Okay, but here agent is going to perform all the transformation steps Agent is going to Lord that they're kind my target table. So here in the procedures, Rohloff agent is very important. So each time the Royce processed, it will be implemented by one. So look, when the first Royce processed, when the first row is processed, sequence number will be incriminated by one when the second Royce processed agent will be triggered. Okay. Ah, and de Trove will be incriminated by whatever number you have defined. Okay, In this case, it will be incriminated by two. Then this world will be processed. Then the sequence number will be incriminated by two. But when it comes to map, ing's knowledge models will load at a time. My target table here in this case I came. So when the data is inserted in my target table, then the sequence number will be generated. So in that case, all the roads will have the same sequence number. Okay, So when data is processed to aging, then in that case, each row will have different sequence number. Look here in this case, how many rules? 19. Ruth. So agent will processed 19 Ruth each time agent is trigger sequence number will be incriminated. Okay, so look here. In this case, it will be incriminated by two s. You have defined the inclement value toe. So get is the reason why when we use the standard sequence in my procedure, every rule will have the different sequence number. Okay, so now let me show you one more thing in the options. But first, I'm going toe untech this drop table, OK, drop table. Okay. Always execute. Always execute dropped Ever. Always execute options daily. This options Okay, now I'm going to create another option A and option here. I'm going to provide the category I d get degree underscore i d value I will provide. That's it. Toe get degree ID equals two. Save this now the very received. So I want only category idea toe her words there category ID equals Toto. Okay, No, create a burly quickly a table Lord that tight will then Lord, it always execute drop table. Ok, now I'm going to use the category I d in my lord data fourth select star from product. Okay, here. Get agree underscore I d equals two or d a object. Ah, I will select the or gay object. Get option This gate option here I will provide the strange let's say string name is get degree underscore. I d get degree. Underscore. I d apply. Okay, now we're category underscore. ID equals two. This so I'm basically I'm calling this object different. Get agree. 80. And the value is toe. So here, I'm going to get the value. Select Star from product were category ID equals two. Save this. Mapping some mapping received. Now what I'm going to do, I'm going to Lourdes. My data execute desk sessions started. Now look at the logs here. Okay, The rules. How many rows? Three. Ruth. So in my final cable, I will have only Trudeau's Can't agree I d to. Okay, so let's say fighting the category I d to one. Let's see if I change it to one. Okay. To inject a one, then execute. Okay, then execute. Get degree. I d one. Okay, so that's how you change the value in the procedure. So you can also use the variables here. So instead, off calling this object, you can create a variable and conduce the variable here. Okay? So, hope you invested the concept. Thank you so much and have a great learning 31. Reuseable Mapping: Hello, everyone. In this lecture, we're going to discuss reusable map ing's. So in order, a reusable map ings are created so that it can be used in another mapping. So in older version off OD A, it is known as jello mapping. So now let's create new reusable mapping. So right, click New reusable mapping, reusable underscored mapping. Okay, so in this mapping, we have two new components Import signature in the output Signature input Signature is going to take input. I'll put Signature. We'll give output. So in this particular example, import signature is going to take input from my source product table and output signature is going to give up my target table, which is select star from product and get scored this table. OK, it is going to give are part of this table. Now let me define attributes, which I'm going to take input. So I'm going to take in port desire to boots product I d name category A. The unit price unit cost. So we product I d underscore i d product name, product, name. Get agree. I d on that score. I d unit price and unit cost unit price and unit cost. No. Marik get Agree. Erdene o Matic. Nomadic unit cost nomadic. Okay, so I'm going to take these input values. So in between input, signature, and I'll put signature, I'm going to use this expression. So you think this expression I'm going to Ah, trust on my data in my product name? I have some null values. I'm going to replace null values with any. So any stance are not applicable product. I didn't name category I d unit price unit. Cost target name replaced null values with any. So I'm going to use the n real function and realism SQL function. Okay, replace it. Quit and there apply bookie. So I'm going tohave Another attribute. Cost def. Numeric value. Okay, cost def is going to be unit cost minus. Unit price cost minus unit price apply. Okay, so I'm going to use different arrest. Used a filter filter. Is nothing back there. Close. Okay, you the first out here product? I did remove this. Okay. Product I d. Product name Get Agree. I d unit price. June. It cost unit price and unit cost unit price. No category. I d would get agree. 80. Unit price unit cost and cost. If so, product I d. Product name category A, the unit price unit Cost and cost if OK, cost if is going to be this, which is minus off unit costs my necessary price. So filter condition is so I want to take Rose. They're getting the ideas one in tow. So in port signature in their degree, I d in one in tow. I want to select Rose with category led one into So this is my filter condition. Save this. So uncooked. Grazed x exception during reusable mapping name is not unique. Okay, so this is the reusable mapping I have created. Now I'm going to create another mapping in which I'm going to use this mapping. So call underscored mapping. I'm going to use this mapping close this. Okay, This just drag and drop here. Okay? So this reusable mapping has important. I'll put signature. So I'm going toe select the source table, so my source table will be source product. Okay. In the target table Joined these product I d. So I'm giving input. Correct Name, category I d unit price and unit cost product. I D. So this is my target table. So I'm going toe output These attributes in my target table product name get Agree. I d. Unit price unit cost and the cost difference. See if this So this is my target table. This is my target table. So I'm going to take input from my source product table. So this is my source product table. This is my output here in this all put signature, I have attributes like product I d name category A unit price unit cost cost. If these attributes are going toe below Did in my debt off. These attributes are going to be loaded in my target table. OK, here. So now execute this. Okay, Executive dis mapping Now. Next, Look at the target table. This is my pocket there. But look, and the get agree I d want to only running toe. But product null values have been replaced with any. Okay, so this is how you use the reusable mapping. So this is the mapping, which I have created reusable mapping and this mapping I have fused in another mapping, which is this call and the score mapping. Okay, So in this particular mapping, let me just open my reusable mapping in this mark. Particular mapping. I have transformed this column. Product name I have I get another column. Cost, Def. Which is cost unit. Cost my nest unit price. Okay, then I have applied. Fator filter is going to select only roads were category ideas. Either one or tow. Okay, then I have created another mapping, which is call underscore mapping. In this mapping, I have used another mapping. Okay, which is reusable Mapping the reason it is court reusable mapping because this mapping can be used in another mapping. No. Let me just give you another example in which I'm going to use the rule number. An electrical functions. So this is going to be a complex example off reusable mapping. So let's create reusable mapping, Let's say reusable analytical function and electrical. Okay, So in this particular example, instead, off using the input signature, I'm going to use the sores source stable. Okay, source table is imply. Let me show you my source table. This is my source table employees in this table, I have Destructor employee I d. First name, last name gender, Each department named Salary. So I'm going to use the rule number. So if I used the wrong number here like Lexus salad E department name. Let me select few columns. There's salad E department name, rule number. So I'm using grow number. This isn't an electrical function over Key Bird. Then order by salary. So I'm applying. Grow number on solidity called descending order. So first it is going to start D salary in descending order den, it is going to apply Row number Rule number will apply e tro as unique. Really? So each row is going to have a unique value. When the next room will come, it will be implemented by one. So this is how Rule number works. So as ranking column. So let me just executive desk query. Look. 12345678 Okay, Total rose it Each row has a unique value. So when the next row comes, every row is implemented by one. Okay. Now, if I want to filter the result off filter district or a lecture there ranking less than equal to five so I will not able to execute this, I will get another like me. Just execute this Look. I will get an error. I cannot apply here with I cannot. Jews were with analytical function so I have to use the in line SQL in line. SQL is like this in Texas like this Select start from now this is this will be my table Select star from this So I have to use this bracket. So this is going to be my table. So this is going to be my table. Let me just give the alias name as stable employees under store table We're ranking now. I can use the very close less than equal to five No, I execute this drinking its capital Remove the s now execute death Look, I'm going to get the result. Okay, So this is the same text you have to use whenever you're going to use the an electrical function. Otherwise you will get an error you were not able to use the were close without using this . This syntax is known as in line SQL. Okay, so if I'm going to implement this scenario in the in the mapping I've inaudible to use it I will have to create the reusable mapping. So now this is my employee table. So let me just first show you my mapping which I have created. Okay, so I have created in in the transformation folder mapping underscore Analytical. So this is what I have created imply table and have used expression department name salary . I have created another column ranking in the ranking column. I have used T. Rowe number. Okay. No. Then de final target table, Let me show you my final target table. This is my target, which is target table name is ranking executed. This okay, now, if he if I execute this, let's removed. If not, if I execute this, I will get the result. Okay, I will get the correct result. OK, but if I used a very closer, I will not able to use it. Let me just give you an example here. I will get an error. I will not able to use it. Because with an electrical function, you cannot use the very close. So let's say condition is ranking less than equal to five expression. Okay, apply. I will get in a received this. I will get an error. Look, I will get an error in the editors from giver. I have to use this from Cheever head. I have toe use dis intakes. In order to use dis index, I have toe first created reusable mapping. No, let's created reusable mapping. First, let me show you how I can youth d via close. Okay, now, reusable mapping. So right, click me reusable Mapping if stance or an electrical mapping. Okay, now what I'm going to do, I'm going to use the employee table. Okay, the next I'm going to use the output signature. So next, I'm going to use the expression here. Okay, so my final target table is ranking department named Salary ranking. Okay. Department name. Remove the tribute department name, which is this salary, and I'm going to have another column called Drinking Cola. Ranking column is in America Values numeric numeric. Okay, fine. Save this. Now here. I'm going to use the analytical function. Rule number, rule number number, then the over keyword reckitt Order by salary or good bye. Employee salary descending order. Okay, apply. Okay. So I'm going tohave the rule number in my ranking. Next, I'm going to provide the I'm going to connect this with output signature. Okay, let me remove the extra columns are put signature. Just drag and drop department names. Salary and drinking. Okay. Save this. No, I'm going to use dis complete mapping in another mapping. Okay, so this is the mapping. This is this is D mapping, reusable mapping and discipline mapping, which I have created. So now I'm going to use this mapping in another mapping legs. I'm going to you here. Okay, So now let me just create another mapping instead of this mapping. I'm going to create another mapping. Ah, an electrical here. I'm going to use this mapping e underscore mapping, which is analytical mapping. Okay, the safety output. So next I'm going to use my target table. So this is my output. So I'll put is going to Lourdes in my target table. This is my drinking cable. Okay. No department name with this Sala de Ranking. Now, I can use a filter here so I can use a feel good here. OK, if I want lets you the first got here. Okay. Department named salary drinking filter condition is ranking less than equal to five. Apply. Okay, this is my favorite condition. Click here. Output journal sub select enable. So if you select this option, you can use the in nine Syntex, you can use the Syntex in your SQL query. Okay, Now I can easily get the result. I can easily get, uh, d reserved. So the condition is mapping less than equal to five. The body able used the online thing. That this index Okay, no see if this now execute this. No. With my target table. Target table is the ranking table. Look. 12345 It has given me only five results. Okay, If I change it toe less than equal toe, it will give me only two records. Okay, There are some duplicated records coming. No, no duplicates. Basically, I haven't applied truncate on my final target table. If I apply, drank it, I'll be able to get the correct result. I will get only two records. True. Now I will get only two records because the filter condition is less than equal toe. Look, I will only get to records. Okay, So this is how you use the were close with an electrical function. Now let me show you the logs Here. Look executed. Successfully insert Inco. Gord, look. Look at that. Look at the sink. This is in line. Think it's OK. We're ranking if this this is the inland look. This is the inland from than the brackets. Then they were close. This will be treated as one table here. This will be treated as one table. Ok? Dendy were close. Select the columns. This will be treated as one table from this table. Then we're close. The safety in nine Centex in line SQL Okay? 32. Lock,Version in ODI: Hello, everyone. In this lecture, we're going to discuss lock, version and scenarios and Nodia. So first, we're going to see lock in order to look any competent, like mapping reusable map ings, procedures, packages accept what you are going to do. You have to disconnect the component. Let's say I'm going to launch this file, underscore mapping this particular mapping. So what I'm going to do, I'm going to right leg and then lock logged this. Now, after performing this operation, other users cannot edit this mapping. They can only view it, but they cannot edit it. So this operation is performed for security purpose so that no one else can I get your mapping so you can also perform lock oppression on procedures, reusable map, ings, other components in other components. So let's have on too long this procedure. So what I'm going to do, I'm going to lock this lock. So if you want, unlock this, you really click Unlock. Okay, this is how you unlock any company. So next we're going to discuss versions. The version is going to make a new copy off your company. So let's say I want to create a version of this transformation. So what I'm going to do, I'm going to Right Click and version. Goodyear were version, so this will create a new copy off this mapping 1.0 dot 0.0 description First development mapping. This is my description. This is my William Click. Okay. A new version has been created. Voisin created. So in order to create another version, I can right click and then create William. So now instead, first, I'm going to do some changes in my mapping filter. Let's say ah, each should be greater than 40. Apply Click. Ok, so I have done some changes now what? So instead, company lets him Microsoft save this. So I have done some changes. Now I'm going to create a new version, so create okay. Could you do it? This is the refresh this Goodyear transformation of is, you know, create version. So I'm going to create a new version which is one point leader doors either 1.0.1. So second overlooked me. Jane, First change. So this is the 1st 1,000,000 This is the second version. Click ok. A new region has been created. Click OK now, right click version. Okay. If you want to compare two versions, you will click this compared with red version. Okay, Like this I want to compare. This wasn with decision. So I'm going to select this version. Click OK, comparison in progress. Okay. What? Changes have been made. So this is showing what changes have been made. Employees detail. You can see what 10 days have been made free death Look company Microsoft Company Terra Data. So this version has Microsoft. This is the old version which has targeted this. If the latest washer look Okay. Ah, some other changes have been made, which is not Ah, a pity Maybe here if I open it. Okay, So ah, dis compare version is going toe compare The current was in with the old William. Whatever the changes have been made, it is going toe displayed. It is going toe. Compare it cross now. So transformation. So this is how you create delusions off? Not in order to restore it. Let's I want to restore my current William toe previous, William. So what I'm going to do? I'm going toe version restored. So I want to restore my current was your previous. So I'm going to select this Okay. I want to restore to dissuade you. So this current William will become this lean. But first, let me tell you one thing. So when I'm going toe open this mapping. So this is going to show you the latest William. Okay, this is showing me the latest brilliant company Microsoft filter condition. Is this so? In order to restore the version, what I'm going to do, I'm going to click right click version. Okay, First I have to refresh it. Just refresh it. Okay? Refresher that right? Click version restore, restart the dissuasion. Okay. I want restore toe decision. Take Okay. Restoring aversion may cost missing reference. Do you want to proceed? Yes. I want to close with this. So no version. Could he get version? So this is d virgin current. Was eunice restored to a previous version? Look, each 35 company is Teradata for legal condition. Is this in cos terror data So ah, oil division has been restored 33. Scenario in ODI: Hello. Everyone in this lecture via going to discuss scenarios in O d. A. So a scenario is nothing but the compiled or in executable program. So let me just give you an example off in high level programming language like Java. So in job wagon, you save a program you you give finally like, let's say my file Amis, ABC, Dark Java. You used the extension Dark Java. So when you are going to execute this program, an executable file will be generated Which will be like this ABC dark class, the different BD executable file off Java. So this executable file is not creditable. You cannot do any changes in this fight. This is not creditable. Likewise in O. D. A. There is a concept called scenario scenario is nothing but the compiled or an executable program. You cannot do changes in a scenario. You can only execute a scenario. You can generate a scenario for package procedure, mapping variables. Okay, so now let's look at how toe generate a scenario for a mapping. So I'm going to use this mapping transformation. Okay, this is my mapping this particular mapping. Ah, I'm using a filter condition each greater than two D. Fife. Company name is Microsoft. This is the expression which I'm using. So, in order to create a scenario, I'm going toe klik the mapping right click generate scenario. So this is the This is the first version off this scenario, So version 001 click. OK, so it is going to generate a scenario for this mapping in executable file. Okay, Now, in order to give us an idea Look, this is the first scenario off this mapping. Now I'm going to click this and execute this click, OK? No, let me just look at my data company, Microsoft itch greater than Turkey five. Now what I'm going to do, I'm going to do some changes in my mapping. So from the condition is each less than 35 company name it is instead of Microsoft Terra Data. Save this against the Now, if I'm going to execute this this scenario, changes will not be appeared in my final target table. Okay, changes will not be appeared. Gender. Look that I still the same company Microsoft age greater than 35 greater than equal to 35. So in this case, I have two options either to create a new scenario off versions. A visitor to our I can regenerate dysentery versions, Little, little one. So if I'm going to regionally dysentery, you this is going to this is going to regionally dysentery. Okay, so then I'm going tohave the changes in my final target table. Now, if I'm going to create a new version of dysentery, you generate scenario letter. I'm having a new words in zero toe version zero to this is the new World. Okay, now I'm going to executive dispersion. New version. Okay, Teradata in age less than equal to 35 now. Executive Virgin 001 Executor dispersion Microsoft and needs greater than equal to 35. So whatever changes I have done in this mapping, I want these changes. Toby appeared in this version 001 as well. So what I'm going to do, I'm going to regenerate this version. Right? Click regenerate. It is going toe again. Generated dysentery. Okay. Regenerated. Okay, regenerated this. Okay, fine. Now I have to execute this in orderto get the changes. Okay, fine. Look, there are other time age left, an equal to 35. Okay, so let me just close this. So let me just open it. Lord Plans and scenario. So you're scenarios will be appeared in the in this particular section, I have created two scenarios. Volume 001 version zero leader toe. So likewise, you can create it's scenario for other components, like procedures mapping Zo variables. So hope you're industry the concept. Thank you so much and have a great learning. 34. Packages in ODI Part-01: Hello, everyone. In this lecture, we're going to discuss about packages in O'Dea, so packages are created so that multiple objects can be executed in sequence. So let me just create a new package. So right, click new package. Let's say package. Okay, Package. So in this package, reconnect multiple objects like the connect map, ings, procedures, scenarios, variables, package tools and can execute them in a sequence. So let's say in this package I can act multiple map ing's. So let's I can add Ah, dis mapping. Okay, then I Giner dis mapping. I can act. Proceed. Your look. I can act procedure and can execute these objects in a sequence. Now I'm going to connect this with this. So in order to connect this, I'm going to select this next step on success. If this process is successfully completed, it will go to the next step. Okay? Likewise, I'm going to connect this with this, but this is the purpose off creating a package that you can execute multiple objects in a sequence like in this particular package. First, this subject will be executed. Then this subject will be a execute er than this subject will be executed Okay, so here, I'm going to have different options de their called package tooth. So if I'm going to select this all, let me just remove this, okay? No. Now, first, I'm going to discuss package tools, okay? No, If I'm going to select all equals select all Okay. It will select all components off this tool. So let's say if I select files equal select, it will give me the option off. Only files. Okay. I can make a copy. A file? I can delete a file. I can create folders. So these are files related operations. Okay, First, we're going to discuss this fall if I'm going to select all the still open up all components, all options. Okay, look, all ups and self. We're going to discuss one by one. First, we're going toe, discuss files, related options. So first, I'm going to discuss Rich option. I'm going to discuss out fight. Where is the out file or gay out file? Dis option. Select this based here. Okay. No, What? This day out filed us. It will air content to ified. So let's say no. Select this option. You will have journal setting in the journal. Second you left Target file. Ah, bank. If you want toe a pen, the content you will have to select You will have to check this. Let's say let me just ah, make a full got here. Okay, Data here. I'm going to make a folder. Let's make a folder here. Let's say Ah, fine. Okay, so here, I'm going to create a file. Let's create a file, Find one. Okay, so in this particular file, using this company, I'm going to write some content. So target file here. I'm going to provide the target file part. So my target file part is this OK, right. Click go ps. But so I'm going toe give part here. File Vandore Text open. Okay. Filed. Wonder text upend. So if you want Oh, a So, for example, first time this step is executed. Then again, you want to execute this step and you execute this step against second time. If you select the upend option, the content, it will develop in the content. Otherwise it will overwrite it. So let's I don't want to upend the content. Okay. Next. So what takes you want to write any file? So let's I want to write Hello, world Welcome toe or D I learning. Let's I want to write this in my file Apply. Okay, Garrod return. Okay. Default. Default. Is this OK? Target Logical Hadoop schemers I'm not using the big decker technology there. Okay, so let's save it. So execute. Okay, now, in order to execute this, I will click, Run, execute This session started. Check the logs. Okay, Successfully executed. Now let me check the file. Look. Hello world Welcome toe or D A learning. So if you want to if you select the upend option it will upend. Otherwise it will overwrite the content. Let's say I want to change it Welcome instead of hello world, I will write Welcome toe od a learning apply. Okay, It's a good this look. Welcome toe or day learning. Okay. Next. Ah, I'm going to discuss which od a file copy. I'm going to discuss for the A file copy, drag and drop here, so this will create a copy of file or gay file copy. So let me just connect this. So if this step is successfully executed, it will move toe this step. Okay, Now select or de a file copy you have tow. Give the source directory Source. Directory Source files Target directory, Target file. Okay, first, I'm going to give my source directory. My source directory is here. I'm going to provide my source directory. Thought target. Let me make my target directory here. Target. Okay. Let me just play some files in my source directory. Let's say not one side. More than control. C control. Let's make multiple copies in my source. Okay, this is my source. I let me just copy the part. So this is just copy the month cpq amount in Lenox Source directory. Is this target directive where I've warned toe copy. Defiles. I've want to copy the files in my target directory. Waiters, this is right now. Target directories empty. So this is how you Okay, this is my target directory. So copy Subdirectories. If you give the value, Gasic will copy the subject. Please. I don't want to copy The subdirectories lurks in no over right files. Okay, if if file is already present. Equal. Overrated? Que sensitive. No. Okay, source. Logical schema. Leave this advocates. So let me just save it. Now what I'm going to do, I'm going toe klik This I'm not going to execute this step because I have already executed this. So right click first that make this first step. So what they will do so they will execute this step only it will not execute this. Okay, now save. So they will execute this. Step on. Okay, file. Copy. Now look at my target directory. Look, file has been copied. Look, Source directory. Do either Mysore size in the files have been copied. Okay, let's look at some other options in the file. Copy, step. So? So instead of providing the directory, if you want toe, specify the patron you can provide the party. So let me just give you an example. Here, let me just remove the files here. Okay? Now, my target directories empty. This is my source directory. So let's create some other files. Employees, employees. Let's say hello. Okay. Control. C control. Let's make a copy of the the employee file. Okay? No, I'm going to remove the source directory, so just copy this. Copy this source. I So the patroness imply e m p and the star e m p start dark text e m p. Start. So what? Or do you ever do Or Gable copy On leading imply five, these three files, OK, it really ignore difference, because the file the sore side, the part is that the name the name should be e M P. Then it can be anything after the MP. Okay, so it is going to extract only three files now, a quick opiate here in my target directory. Right now. Target directory is empty. Okay, Save this. And executive look, it will only copy. Tree feels. So this is how you copy multiple files. So, no, Let's stick. Explores some other options. So we have another step called owed a file it. So if you want to delete files, you will use this option or D A fiery delete. Okay, select this and paste here. Okay. Or day five Gillet. This will dilute your files. Let me just connect this with this. Select this option directory here. You're going to provide the directory of which directory you want to. Uh, you want toe from with director. You want to remove files, so I want to remove files from this directory. It will remove all files. All five future president in my directory. Okay. Delete subdirectories If you want to delete subdirectories vessels, you will select. Yes, Gay sensitivity is no enter if no file phone Currently I have three files. If there is no file present in this target folder, it will give me an error if I select the yes option. So I don't want to select. Yes, I don't want to get an error if no file from start date and this is important. But if you want to delete files off specific period of time so you can use this option, Let's say I want to delete files off fifth Feb this time. Stamp tail. Let's say 67 it for days. So I will choose this time stamp. OK, so right now I don't have much filed and I don't have files off this time stem. So I'm going to remove this fourth if and execute. No. First I will select this and then I will select first step. This will only executor the step. No, Save this and execute DiCillo. Delete. Uh, D target. Okay. Not only the file, but also the directory. It will deleted directory years. Well, okay, look. So if you want to delete any specific file you will give a part here. Let's say let me just create an a dirt for their target. Let me just based some files. This is target source Target. Let me just play some files. So I want to delete files off employees. Ah, target store. So this will only delete for these three file. Okay, These 35 Steve and execute Look, files have been violated, So this is very important. When you want to delete all files, you can use this step. Okay, But you think the step you can perform housekeeping, you can delete all files. So now next step, which I'm going to discuss, is or they filed elite or the A file move. So by using move command, you can either ah, rename a file or you can move fired from one directory to another directory. So we're going to look at file move, select this. This. Okay, So by using this, I'm going to I can actually rename a file. First, we're going to look at how to rename a file. So let's say we have this file filed or I'm going to rename this file file one copy door text. This is my target file. Target directory. Okay, this is a target directory directory filename, Target directory and target fight. So find him Five languages defied him. Let's sit this final. Him copious part. This is direct Rivet. Ah, filing This is directly without. Finally, I'm here. You're going to provide the foreigner without file. Um Okay. Ah, directory, Target directory. Uh, here you can provide the target directly. Target files you that you want. Oh ah, Rename. So I want to rename this file renamed work. This is what I'm going to give. So I want to rename it to start, get fight So I want to rename it to employees door text. Okay, so look, Target directory filename. So in this perimeter, I'm going to provide the compute part with filing target file in this directory, I'm goingto provide the filing, which I want to change. I want this file, Toby, this implied or text file. Vandort, this way. Look at this to be employed or text. So see if this execute. Now, look, look. Name has been changed. No. Next step is I want to move five if you want to move files you can use. So if you want to move all files. You will use the directory option. Let's say let me provide the source. Okay. This is my source. So I want to move all fights from here for thoughts. Source Directory. And let's say target Derek T. This is my target directly. Let me just empty my target directory. Target directory is empty. Target directory. This is my target. Direct received this. No. First step. Now, only this step will be executed. Safe this package and execute No. Five. Look, my source directly will be empty. All files have been moved to my target directly. Look, So no. If you want toe move any specific file, you will use this directory. Uh, here. Okay. If you want to move any specific for let's say I want to move. Let me just carton based here. Okay? Control, Lex. So if there now the target directory is empty. So I want to move specific PFI. Let's say this file. I want to move only this fight for what I'm going to do. I'm going to provide the part off this file copious part, So find name. So where I want to move this file, I want to move this fi in which directly here in my target directory. Where is my target? Directly District. I want to move this file head in my target directory. Target, Final target directly. I will choose target directly if I have to rename it Rename a file. I have to use the target file. Now. I want to move off this after execution. Off this step, this file will be moved toe here in my target. See if and execute implied our text will be in my target directory. Look implied or text is in my target directory after execution off this step. So next we're going to discuss me directly. Make directory in or the A zip. So where is made directly? Make directory will create a folder. So if you want to create a folder in your next all annex, you will use the command M k D I R. Okay, so here I am goingto have this utility or D A made directly. This is very simple. Made directly food. Connect this. Make this first step. Okay, so this will create a for let's say I want to create a four letter file or d a lesson or d a lesson. So this is very simple Executor 35. Packages in ODI Part-02: Hello, everyone. In this lecture, we're going to discuss another utilitarian package, which is known as or D a fight wit. So let me just drag and drop here. So Oday filed wage utility is going toe wait for files. So once files are arrived in a folder, then it will execute the next step. So let me just give you an example here. Let's suppose I have this mapping ET l ah. File Underscore mapping dis mapping. Okay, so what this mapping is going to do this is going toe extract two files, employees in department. Then it is going to have a giant. Then finally, it is going to Lord that time my final target table, which is employees. Okay, so these are two fights. Let me show you defiled department and implies, So these two files will be present in this folder. Okay, so it is going to extract these two files, and then it is going to have a joint based on department I d. Then it is going to load that time. My target table. So what I'm going to do, I'm going to include this mapping in a packet, file and disk or mapping I'm going toe connect this. Who gave filed late with this mapping, so od a file. Wait is going to wait for files. So let's suppose this OD filed weight is waiting for these two files. These two files. So let's look at different options in oday filed late. So look, actions. So once these two files will be loaded in source directory in this directory, what action should perform? So if I select the, uh, copy option, it will copy the files. If I select the move option, it can either rename a file or it can move files in in this folder where I'm going to specify the scissor target folder. So it is. So by selecting this option, it is going to move files in this one girl. Okay, Now what I want to do, I want to copy files. So which for that I'm going to select. I'm going to select here for yet name archive a lot of life. Just select copy and paste. So it is going to copy my files in this folder. So once I'm going to pieced my files from here to here, or the Evan copy files there in the archive for her. Okay, so this step is used for archiving files. OK, Okay, Next is directory. So where files will be present. So files will be present in my source directory, so Okay, fine. Great step is going to search files here in this directory. Okay, So files are search share in this directory. Next step is which files you want to search. Do you have to specify the file names? I want to search Darty X to find. So I will specify this former star dark txt. Okay, so if you want to rename a file by you think the move option you will give you will have to specify the party. Okay. Okay. Sensitive. No, Over. Write files. Yes. Okay. Number of files. So number of files should be too. So this step will read to files will wait for two files. Okay, maximum number of files to wait. So in this case, it should wait for 25 cents. I'm reading two files. Okay. Employing department dymo time. All this maximum period of time in mili seconds over which the direct creates search. Let's say I've owned give 100 1000 milliseconds, so after 1000 million seconds or the A find weight will stop searching. Okay? It will stop surgery. So if I specify this option, let's say, um yes, so often 1000 milliseconds. If file is not present in the folder, it will given Earth. It will give an error. Okay, if I specify yes If I specify No, it will not give another. Let's say yes if files are not present after 1000 milliseconds, it will give and it will give another. Okay. Yes. Keep first header. Yes. Okay. Oh, Headlines First in the file. The first line is Hager file. Look the first line. If Hagar fire No, Just Okay, look, Bookie, What is this? Pulling terrible the period off time in Mili seconds to wait Between direct research. First time Michael said. Then search a directory den. It will wait for 1000 seconds to search it against. This is polling interval time. Interval is after 1000 milliseconds. It will stop searching. Okay, if if this option is selected, it will give me narrower after 1000 milliseconds. If file is not present. Okay. Now, Uh okay. Save dis option said dis s first step. Okay, this is already selected As for step. Okay, so once filed their present in the in the source directory here in this directory. Then the next step is going to be executed. Then OD able execute this step. Otherwise it will wait for files to come. If I the North present enough in this folder for about 1000 milliseconds, it will give me another. Okay, so let me just execute it right now. This folder is empty. Archive folder is also empty. So what it will do. So what I'm going to do, I'm going toe based files here in the in this directory source directly. I will cop it these two files and based here in my source directory here. Okay, Right now, I'm not tasting it. First, I'm going to execute it. So execute this operator. Okay, with session now. Ah, refresh this refresh. Look, it is giving me an error. So what is that? I read It is giving me no file found. Okay, let me increase the Dymo instead of 1000 milliseconds. Let's say no ex secured this decision started. No, it is executive. It is waiting for file to come. No, I'm going toe paste file in my source directory. These two fires control C and based here in my source director here. Okay, now what it will do? Refresh this. Look, the step is completed. It will execute this step. So now if I look at my target table that ice populated and my target table Look. Ah, Walkie. One more thing, let me show you the archive. Okay, look, that is our co copy in my archive folder. So let's say Ah, action. Copy the sister directory. It will search door text file. So in this particular directory, it will search for files. Target directory disses the directory. Veritable copy files. OK, Next. Okay. Time out. It is going to time up after Ah, distant seconds. Millisecond. Okay, Polling interval. The period of time in milliseconds. Toe weight between the search directory. So April 1st time it will serve the directory. Genital. Wait for 1000 seconds. Ok, then after 1000 seconds, it will again search for files so it will search for about this milliseconds. It's if the file is not present. If no finalist president in the directory, it will give me another because I have selected this option And if Okay, so this is the concept off OD a file weight equal weight for file until files are arrived, it will execute the next step. Otherwise it will not execute the next step. So hope you industry the concept, Thank you so much and have a great learning. 36. Packages in ODI Part-03: Hello. Everyone in this lecture will discuss some other files. Related utility. So now we're going to discuss OD a file open. Just drag and drop here in the panel. Okay? Or d a file. A print. So let me just give you an example here, using this utility, you can append multiple files. Let's say I have these three files in implied or text file 1/2 this information I d want to three tell 10th imply to zero door textile. I have Ah, disinformation employee. I d 11 12 implies 03 door text to again 14. So using this utility, I'm going toe upend these fights. OK, so final target file is going to be my separate file where I'm going toe a paint these three files. So now, first of all, I'm going to provide the five, but so the file part will be this one. Troy defy part Andy filename. So since I want to upend these 35 so I'm going to specify e m P star. So it is going toe upend only these 35 which start furred e m p E m p. Book it next target fight. So where I want to create a file which will have ah, the debt off all three files. No, I want to place file here in my source directory. So I've on to create file in my source directory where I will have that off all 35 So name of file will be employees door text fun. Okay, so, total. I'm going tohave. How many rules? Look, file 11 10 records total 10 records. Who has 11. 12 and 12. 14 total. I'm going to have 14 records in my final fight, which will be created here in these source directory. So I'm going toe create implied or text trial here in the source directory. So OD a file. A pen will automatically cleared this file here in this source That Okay, next step is Heather Lines. Yes. The first grove will be Hagar had a row. Keep first header. Yes, In my fine the first road will be hagel file Had their line? Yes. Okay. Next. No, it is not mandatory. No, Save this. So this is my first step. Next, I'm going to include my mapping. So let's have want to include this mapping file. Underscore mapping this file. Underscore weapons. So let me just Andy name here, Get are loading file a bank. Okay. Gender name here. Data lording connect different death. Fine. Next utility I'm going to discuss is or d a SQL unload. So this is my target cable. So once to get eyes in circuit again my target table using SQL unload utility I'm going toe export my data from here. So I'm going toe connect this with this utility. So let me just select my utility SQL unload target. Fine. So I'm going toe export that time. My target direct here in this territory. So the name off file will be target file E m p. Dark CSE. So name off. I will be impede RCSC. It will be. It will be automatically you get in my dis directly Target directory. Do you d be thick? Drivers selected your BBC driver for Oracle. Dig BBC You are select this one. Okay, Now let me just change affecting here. A local host board number. Let me specify the port number, which is 15 to 1 service name is or D B 12 c. We're db Wells is my service name. No, you. The name is for def if Target Properties Port number is this service name is this user name ist ist bastard. I'm going to give my pastor there, which is admin wanted. Three file Formative Della Meter. Still separate. Terry's comma separated. Okay, here. I'm going to provide the SQL query. Select Start from this table. The leg star from employees apply. Okay, so diff if my select Grady fetch size select. So if you want to fetch 100 records so you will provide 100 size, I don't want to provide any size. It will fetch all the data which is present in my employ table. So first this step will be executed. Then this step will be executed than this step will be executed. So I'm going to make this s first step. It is already considered as the step saved this. Now I'm going to execute this. So here I'm going tohave source file here. I'm goingto have defied employed or text, so let me just execute my package. Executor this sessions started Look implied or text total 14 records Select me. Just quit. Emerged that abyss. Okay, look, total How many? 12 records. The total number of records are 12 not fucking 56 then nine. Okay. Number of records act well, not 40. So now let me just check the file here. Ah, Target? Yes, implied dot CS If I list created like me, just open it. No, Get a has been in certain. Let me just remove this. I don't want to include this. Uh, select this. Remove this. So let me make this first. Okay? Dilute this for safe side delivered this Vic. Okay, let me just tell you this fight. Now execute this again. So this step will be executed only. Okay, now let's check. Look, Data has been sexually executed. This step is successfully executed. Look, employee, I d want to treat tell 14 Number of rows. Total number off. Ruth, How many? 12. Number of records. 12. So this is how you used the od? A SQL Unload your ability so you can also use a variable if you want. So let's say instead off. Ah, let's to say instead off Select star from employees. I can also do like this where employee i d equals two where salary greater than equals two . We're salary greater than 2000. So you can also use variables, if you want, will discuss how to use variables in a a name package. No apply. So I'm going to make this as a step already to the first step. So for safe side, like me, just delete this, but maybe not. Okay, only three records. Okay, celery. Greater than 2000. So look, Espo Day s cure a large utility will only Lord that I can not A hugger, okay. Could only lorded that averages present Indeed. Database. Not a header, which is employer Give first name, last name, name, gender, etcetera. OK, hope you're destroyed. The concept. Thank you so much and have a great learning. 37. Packages in ODI Part-04: Hello. Everyone In this lecture of your going to discuss you more utilities in a package. But first, I'm going to discuss different options available in a package. Okay, so let me just drag and drop map ings here in my panel. So let's a transformation. Dragon drop here. Filing the score. Mapping dragon drop. Let's drag this mapping as okay. Mm. Let Okay. No, I'm going toe. Select this option. Next. Step on success. Gonna disturb with this step The Stepford the step in District A district. So let's suppose if this step is successfully executed, if this step is successfully executed, it will go to the next step. If this is successfully executive, it will go to the night step. If this step is executed, 60 sleep will go to the next step. Let's suppose if this step fills, if this step says it, it will go to the next step. Okay, let's suppose this steps feels this tempest successfully executed. Then it will go to the next step. Let's suppose this step sales when this step sales, it wouldn't go to the next step. Okay, so this package will stop here. It wouldn't go to the next step. So now let me just select another utility. Ah, Internet sank email saying immobile sang animal Select, select and select. Okay, now I'm going to discuss this next step on failure. Still air. Okay, let me just select this utility. Now, select this and this. So let's suppose if this step feels it will go here. Okay? It will send an email failure. Riemann, lift the steps. Feels dispatch it from stop. It will go here. Sending him a little sending email failure, email. If the steps fails, it will go here. Failure, Email. If this steps feels, it will go here. If all the steps started President in my package are successfully executed. It will go here. This is success email. So it will send the success image decked. All the steps are executed successfully. So Deaver emails related to failure. And this is success Email. If all the steps are executed successfully or the A send email, this option will send an email, get all steps are executed successfully. So in the send email option, you will have the male consideration mail server port number. Let me show you different options. User authentication, user name and password. from to CC. All email former subject Attachment board the all email form It board number. You will have to provide the port number here. Protocol Which particle pop? Three Bob three Protocol is the Internet Particle s empty B is the internal exchange server . If the message if the emails are going through internal Exchange Server, you will have to select SMTP. So if you are sending animal toe Gmail or Internet eso, you will select depart the protocol. So this is the fifth four failure. This is a success. Okay, Now go to the next step. If this is successfully executed, go to the next step. If the steps cells Okay, go here instead of going here. Go. Go ahead. Send an email failure, remember? So So I have discussed these two options. So this is now let's select this option. What is this option View high links between select this. Okay. Only read descent hide de success steps. Okay, now selected. Look, it could hide this. I want to show green I don't want to show red. So look now culture reorganized. Let's duplicate. So if you want this duplicate, you will select duplicate look duplicate. If you want d duplicate off this, You will select this and you will take this duplicate duplicate. Okay, Now, let me select the cursive Delete this. I don't want duplicate. Okay, what if this this is so If you want to delete any step, you will select this option reorganized. So if you want to reorganize your package so I'm going to select this option, this will reorganize your steps. Look, Leo will be changed. Look, layout has been changed. Now, what if this option? So if you want to edit any step, select this. I want to edit this our justice. Edit link. Look, this will be in a great mood, okay? No. What if this steps if you want, Execute any specifics? Step? I don't want to execute the steps. I I only want to execute the step. So what I'm going to do, I'm I'm going to click this, and I'm going to execute the space six step. I'm going to click this button, so if you want to debug, you will select this option. So if you want to execute the whole package, you can either choose this option or you can click this. Okay? Are you can go like this, right? Click and run. Here you will select Iran button. So what if they're soaking? So these are the different options available in a manual bar? No. Next I'm going to discuss de even detection. Different options that are present in even deduction. 38. Packages in ODI Part-05: Hello, everyone. In this lecture, we're going to discuss some more options that are present in the even detection section. So now first, I'm going to discuss the read mail utility. So this step is very simple. This is going to read mail, so you have to provide the mail server credentials like male juicer. Password started, location from where you want to read mails. Folder option. If there is any folder option present, you have to provide. Okay, Protocol. So which protocol you are using Part trees for Internet particle. There is another protocol. I am AP. So if you are using the internal exchange several you will use the I am AP Protocol. So this is very simple extract attachment. If you want to extract attachment, you will select Yes. Otherwise, you will select no extract body email body. If you want to extract while reading mills, you want to extract email body as well, so you will select yes, option. So these are different search options that you can opt for if you weren't so. This is very simple. You just have to provide the mail server configurations. Right now I don't have the configurations meal. Several configurations. So now next step, we're going to let remove this. So next step we're going to discuss is V it or d a week for table. Select me. Just drag and drop here. Or d a vic for table. So this step is very similar to the step or the a file weight or the a file. Wait. Step is going toe wait for files. Various. This step is going toe wait for table. So let me just select this option. Let's look at the properties off this step context. You have to provide the context. Say, let's say production global row count. I'm going to discuss this option later. Okay, logical schemer. So I'm going to select my target table or target schema. Okay, This so my target tables, I'm going to use this table. So first off, all I'm going to truncate this table dropped If table drop table drop table. Okay. Now I'm going to use the create Able command. Okay. So clear. Dissed able right now. I'm not creating it. So let's discuss these options. Table name. Table name is this. Employees underscored detail table. So you have to provide the table lemon capital imply underscore detail. This should be in capital imply. Underscored detail. Time up. Zero. Let's say housing 1000 seconds. Milliseconds time up without error. Yes. So in gifts, uh, it gets time out and table doesn't It doesn't find any table in the database. It will give me another. Okay, No global row counter zero. So I'm going to discuss this option Global count room. So received. If next I'm going to drag my mapping here, which mapping? I want to drag this mapping transformation. Let me just added this mapping. Okay, this mapping is going to Lord their time. Employees underscored the kale table, but imply underscore detail. Table is not present in my database. So first or give it table step is going toe wait for table. Toby created. Okay, so this step will not be executive successfully until this table is created. Okay? Imply underscore detail. Cable is created. No. Ah, select this option so or get select this option. If this step is successfully executed, then the step will be executed. No. Let me just move this. Save this Now let me just execute this. I will get another because table is not present in my it is going toe it for the stable Look. Feel it decided because this table is not present in my database. No next, let's say let me just increase their time. Our time. See if this no executor death production It is waiting. So it is going toe. Wait. This amount of time, this milliseconds then it is going to fail. So it is still the reason it is failing because table is not present. Time out. No results. Now let me just increase the time out time. No, What I'm going to do, I'm going to create this table. Okay, so I'm going toe first. Execute this. Okay, This is the first step. I have already defined it the first step now executed This Refresh this So it is executing now what I'm going to do, I'm going to create this table Imply underscored the tail table. So coma and execute this Could you take successfully? No. Look, Refresh Ok, it is executor sexually. This step is executed successfully because I have created this table. So this is this step is going toe Wait until table is created Until cable is there in my deck A base so no global row count. So this step is going to be executed. Now if I select star from implying disco detail select start from this table implying that scored the palatable that eyes loaded in my dis implying that Scott detectable the reason that is loaded because this step is executed after this step, that step is sick executed. This step has loaded mad attain my employer. This could be guilt able No. Now I'm going to explain to you the concept of global row count. Let's see if I specify 10. So it means that minimum records should be present in my in my employ underscored the kale table. Otherwise this step will not be executed. Okay, this implying this cortical table should also be present. But along with this table, minimum 10 records should be there, in my imply, underscored the kills people. Otherwise this step will not be executed. So now let's remove this. First of all, I'm going to take back up off this table. Okay? So create table as this. Let me just executor this Be Kippy Executive created 60 c no, b k p select star from this beekeeping table Look, 29 drools total 29 does. So now what I'm going to do, I'm going toe truncate this table imply and just caught the tail table. I'm going toe trunk it this table drunk it table, right? No. Nor digitized, present nor their taste spreading. But if I'm going to execute this, so let me just remove the step. So if I'm going to execute this step, I will get another. The reason I'm going to get another because no, that is present in my employ underscore decay table. So, look, minimum 10 records should be there in my implying this cortical table in order to execute this step successfully. So let me just decreased Dymo thing. So now this step is going toe feel okay? This step is going to fail. The reason it is going to fail because nor decades present, look nor their guys present in my di moat. So now what I'm going to do, I'm going to increase the time all time next time, going to executor distant it is wreaking. It is a thing mean life. I'm going to insert their time my employees and this could take a cable okay, in certain toe insert into this table select star from this 29 rose in circuit. Okay, now refresh this. Commit discovered this. I have to commit this. Look, it is successfully executed. Okay, look, this step is successfully executive because I have just committed the operation. So you have to commit it when you're going to insert the debtor. Okay, look, now the step is successfully executed. 39. Packages in ODI Part-06: Hello, everyone. In this lecture, we're going to discuss a few more steps in the even detection section. So the first step Detrol discuss this son or gave it for that, I let me just drag here in the panel. So I gave it for data is going to look for a particular sect off Tetteh. So if a particular set of that is present in a table, then it will execute the step. Otherwise it is going toe. Wait, wait for for time that you're going to specify here in the properties. So now select. Let's look at the properties their context. So I'm going to select production global row count, which is one as default. Logical schema. Okay, this one. Okay. Filter column. These two, these two steps are related to variable. So if you're going to use a variable, then these two steps will be used. Now, first example, in my first example, I'm going to use a variable. So now, first of all, I'm going to create a new variable here. Variable name is var 03 numeric value. Okay, deep save this. Okay, variable 03 values 30. So now filter column Select star from employees. So I'm using this employee table. Total number of records. 29. So I'm going to use where each less than turkey executor this. Okay, age left and Kirti. So how many records? 23 records. So I'm using dis condition in my gave it for data. OK, so filter column Is each field operated left then? Philip Variable. I'm using the variable three in my variable tree. I have value 30. So the condition is each less than 30. This is my condition. So since I'm getting a variable so I'm not going to specify the school filter Object type is stable. Object type is stable. Table name Is this implying? Discord dictated. So let me specify the table name here employees and of course, details. Cable Click. Ok, Dymo 1000 time out Without ever years. Okay, these steps are optional. So ignore these steps. Save this. So now let me just execute this executor death. Okay, now let's circuit It is successfully executive. It is successfully executed now instead off each two d Let me specify each greater than 50 So no dice present with this condition. So now I'm going to change my variable value variable value. Let's change it to 50. Save this in the condition it greater than 50. No, this is going to fail greater than now. This is going to feel the reason it is going to fail because I'm not getting any data from this further condition. So picked will feel. So if you're looking for a particular set of data, Look, this is going to fail. If you're looking for particular set of data, you will use this or give it for data step. So this poor gave it for data. Step is going toe. Wait for particular set of that is, if particular set of the day is not present, it is going toe feel like this particular set of data is not present in my and table. So this is going to feel it is going toe pick. So when I'm goingto insert, uh, this information in this table, it is going to be executed successfully. Otherwise it will feel now it is going to feel Look, it s really no no. Instead, off using a variable, I'm going to use a school figure. No, let's remove this. Remove this two steps age. Now I'm going to use ich less than 30 age less than 30. Okay, save this age less than 30 Executor this it should be executed successfully Look executed successfully because it's less than 30 disturbed ties. President and my table. So it is going toe. Wait this amount of time. So if there's a sound in the table, it is going toe Uh, it is going to be executed successfully, Otherwise it will fail. No, we have discussed this step. Now let's discuss some other steps or d a sleep. Save this or gay sleep. Now let me just include the mapping here. Let's say I'm using this mapping transmission so I've warned the first time I want to execute this step. Then I'm using the ordered A sleep or did a sleep step is going to stop the stop the package for about whatever the value going to specify. This is in milliseconds. So if you want to stop the package for about ah, one are you have to specify the value. Let's say want to specify it for 100,000 milliseconds, So this step is going to be executed. Then oday sleep is going toe posit off. Stop it for about 1000 milliseconds. Then it is going to execute this step. So let's say if there is a scenario in which you have to execute this next step after one are you can use the od a sleep step and define a value. Where for? About one. Not so you have to convert milliseconds toe one are so whatever the value it will be, you have to specify here. Okay, let's say you want Oh, after this step, you want to execute the next step after one are you have to use this body a sleep step Here you're going to specify the value which is equal to one. Not so the sister used off this step or D. Let's say yeah, execute make. This s a step. It is a first step successfully indicated Vic. How many seconds? One second equals a. It will stop for about one second. Then it is going to execute this step transformation. So let's say o D a No look. It is picking, not evicting. It isn't pause. It isn't sleep mode. It is going to sleep for about ah, 10,000 milliseconds. Then it is going to exhibit the step. Now it is completely so. This is all day sleep step 40. Packages in ODI Part-07: Hello, everyone. In this lecture, we're going to discuss a few more steps in the even detection section. So the first step Detrol discuss would be a way for log data. So this step is useful footing that are capturing this especially applicable when you're using C disease offers external cities is off first, like Golden Gate poverty exchange toe capture change. So the information that you're going to provide here in the property section are going toe before external saw. For if you're using Golden Gate, you will provide the information of Golden Gate logical schema. So you will provide. Let's say you're using the gold. Engage will provide the logical schema that you're using for Golden Gate. OK, subscriber table the terms subscriber is used, especially when you're using CDC table lame. C D C D. They're all CDC configurations. So this step is useful, especially when you're using the external software for capturing change. So true logs This step is going to figure out the change. Tecca Okay, through logs. So now, since we don't we're not using the external so fair. So now let's not discuss this more, so I'm going to delete this step now. Or do you wait for Lord plans? Or they worked for Lord plan? Step is going toe. Hateful Lord plans to be completed So we haven't discussed the load plan, Jack. So or David for child sessions of this step is important when you're running multiple steps in pattern. So when you're using a mapping, you weren't able toe execute multiple steps in federal. So let me just give you an example here. Let's say you're using multiple map ing's this transformation. Ah, let's herbal dis mapping expression. You won't be ableto execute thes steps and federal there is no option off executing them in parent. Okay, First, this will be executed that this will be executed. Then this will be executed. These steps will be executed in a sequence in a sequential manner, not in peril manner. OK, now, when you're using a scenario in a package, then you will have the option off executing them in better than so you can execute different scenarios in peril. So now let's let me just give you an example off a scenario. Let's say electric youth scenario transformation. Yes, I have created us an idea for dis transformation. Let me use this. So let me drag this. Okay, let's create a scenario for this generate scenario. Virgin zero. There one expression really want. Okay. It is generating a scenario for this mapping versions. Evil there one. So let me just add another. It'll mapping. Let me create us an idea for this mapping as well. Okay, dented it this scenario. So I'm going to explain the step or David for child Session Dragon. Drop it. No, Connect this with this. Gonna diss it. This Okay, select this option. So there is an option off. Select this. Let's look at the properties is in cornice slash synchronous asynchronous means that you can execute the step in federal synchronous mean that this stuff will be executed in a sequential manner. Okay, let's say asynchronous likewise. It's in clearness, Ihsan goodness more. Okay, this step. So I want todo execute this step in synchronously. Okay, this one singleness. Next. Let's see. Okay, Now let me just drag this or gave it for title session. So they wait for child session is going toe were for child sessions. Toby completed. So let's say I I'm executing these steps. The 1st 2 steps are going to be executed in Parral. Where s this step is going to be executed in in sequence. So let's suppose I want to execute 1st 2 steps, then I want to execute this step. What or the child session will do or digital session will wait for child session. Toby completed. These two are the store child sessions. So let me just instead of connecting this. Ah, no, Select this. Okay, This No. What will happen? These two steps are going to be executed in federal. Then once these two steps are completed, then this step is going to be executed or David for child. Step is going toe. Wait for these two steps to be completed. If these two steps will be completed, then it will execute district. Okay, let's look at the properties off the step Beer in session. Better in session will be this package. Name, package 11. Name of packages pay. Didn't desire Chai Children. The three Children. Okay, so number off failed child sessions by selecting all It means that if all child sessions are failed, ignored them. Okay, Now what if if I don't use this or gave it for trial session, so what will happen so long if I don't use this. So there is a possibility that this step is completed in this step is not this step is yet to be completed. What if if this step is completed and this step is not completed yet because these two steps are executing in peril. But when this step is completed, it will go to the next step. Okay, So what I want I've worn these two steps should be completed. Then it should goto distant. So I want these two steps to be completed first. Then it should go to the next step. So by adding this component what a week for child session company dis component is going toe wait for these two steps to be completed, then it will go toe this step. So this is the use off the step. So hope you understood the concept. Thank you so much and have a great learning 41. Variable In Package: Hello, everyone. In this lecture, we're going toe. Discuss variables in a package. So first of all, I'm going to create a package. New package, package 12. Okay, so in a package, I'm going to use a variable. So first I'm going to create a variable sort. Like I can use it in a package. So in order to create a valuable you will click variables. Right click new variable name A very blessed value. Zero to data type. Nomadic. So I'm not I'm not going to specify a value for this variable. Since I'm using this very Bellini package, I'm going to specify value in a package. I'm not specifying any very were. OK, so if you want to keep history yes, I want to keep history all You saved this variable so you can also create a variable from here. Global objects, global variables. Right leg, new variable. So the difference between creating a variable from here and from here within a project, if that when you're going to create a variable in a global object, it will be valid for all projects. But when you're going to create a variable within a project like I'm creating it right now , it will be valid for a specific project like this. Terrible will be valid only for this particular product Day wh underscored project. Ok, so I cannot use it anyway in any riddles, like in another project. Okay, Now this is value zero toe I have created. Right now, I haven't specified any value. So I'm going to use this variable here in the Beckett. So by default, when you're going to drag a variable in a packet, it will be a secondary. But there are different types of variable set variables, refresh variables, ValueAct variables. Last but not the least declared available. So this is secular, arable. So when you're going to let me just drag this again So when you're going to drag available in a package, it will be Secretary Bill. So, in a certain credible, you have to define a value here. So I'm hard according a value. So let's say value with 30 dis is set variable. Let's give a nice name. Sec, variable. This is a certain credible Ocasek variable. Look at the sign. Equal toe. This is the second arable. Now I'm going to include the mapping here in my package consummation. Look in my transformation. I'm going to use this variable sec. Credible. So click transformation. Fatal condition here. I'm going to use this variable less than dis. Very but value zero toe values, little toe dysentery will know apply. Okay, So I'm going to use this fine sec variable, then the transformation. So select star from employees underscore detail. So now I'm going to execute this. So each less than 30 I'm going toe have data present in my implying discarded collectible where age will be less than Kirky. So let me just execute this. Give. This is the first step. Okay? This is different Step no executor Death package. It's left in 30. Look, age left in the 30. So there is another variable called refresh variable. So the when you're going to use the refresh variable. So what you are going to do, you are going to take value from from database. So look, first name this. This is a secretarial. So when I'm going to select the refresh variable, Look at the sign off this variable. So look, sign off. This variable will be changed. Okay? Look, now design. So when you have to take value from a database, you will use this refresh variable in the refreshing. In this section, you will provide the SQL query. Now, let's say I want to take data from a configuration file rather providing value rather hard . According a value. I want to take value, form a database. So let's say select start from Gun Fig. I have a table convictable in my database. In this table, I have table aim and columns each. So I want there. Table name, cable underscore. Name equals two table. And the scorn Emmick was to imply underscored detail. Age aged 30. For now, I'm taking a value from a database, so I'm going to use the refresh variable here. I'm goingto provide the stool. Grady scheme. I've will provide the schema so you can also validate this school query. Test valued. Grady, save this. Now, this has become refresh variable. Make this refresh variable refresh. Very bad. So this has become a refresh. Very blue. Okay, so this variable is going to take value from a database, so it will take each 35. So here condition is values here to less than 35. Okay, this is the refresh variable value 02 is the refresh variable. This is the refresh variable. So now see if this execute Now execute this 32 okay to give on values are less than 35. So let's change it toe. Let's change the value to 30 instead of 35. Let's change it to let's a value greater than two different. I want value greater than 35 no greater than 25. So I can also change the value off this variable. Let's say ah, I want less than this value. Okay, so I won't pretend this variable value so update this table certain age equals two. Do nt it vit table longer school name. He goes to imply detailed table So let me just update this value Toe 28 Wonder updated now I'm going to perform the comet operation Commit now Select age from gone Fig. Oh, indeed. So, in my final target table in this particular table, I will have each less than 28. So now let me just check. Let me just take the filter condition Age less than okay, 28. Okay. This refresh variable is going toe pick up a value from a database so let me just Okay, this is going to be refreshing looking. Now, let me executor this executor death. So in my final target table, I've loved lesson 20. Okay, Is letter continued. So now desist. Refresh. Very well. Let me just click this available. Now look at the history. So it is going to maintain the history. Is there 30 35 35 28? Because the reason it is maintaining a history because we have selected dis option all value. So it is going to maintain history off all rallies, so this is refresh variable. So when you're going to take value for me from database, you will use the refresh variable. 42. Declare Variable: Hello, everyone. In this lecture, we're going to discuss some other types off variable. So first off, all I'm going to create a variable specially. So now let's create a very bad name. So wearable debtor type nomadic, will you All these fear want to maintain So variable this variable dragon drop here. So this time around, we're going to discuss Well evaluate Very Berlin declared variable. We have already discussed Secretary Berlant. Refresh variable. So now this is going to be the refresh variable. Select me. Just dragged one variable again. This variable. So the 1st 1 is going to be the sec. Credible. So, by default, it is going to be a sect. Variable insect variable. You will have this option as a incurring mint. This inclement option is going to incriminate the value off this variable. Every time this variable is executed, valley will be implemented. So whatever value you goingto provide there it will be implemented. So now if it is a text value definitely won't be implemented. So you won't have the option off this inclement. This is only for numeric value. So now let me just select a variable alerts a value with 30 This is my evaluate variable. So let me just change the name. Evaluate. So evaluate, This is set variable. So evaluate Variable is going toe Evaluate. Devalue. So let me just connect this with this. So here I am goingto provide a value for which value values 30 30 less than equal toe greater than less than equal to Let's say here I'm going to provide 50 or 50. Okay, So the condition is 30 less than equal to 50. This is true condition. So if the condition is true, what it is going to do, it is going to write Identify So would a out file. It is going to right here the fifth to condition So if the condition is false so what it will do, it will select me just it will execute this you deal mapping if the condition is false so here in a text while it is going to write a value. So let me just specify death Copious part Execute death. Okay, text It is going to provide the now I'm going to provide the variable here. So very believe this Apply okay file writing. This is transformation. So if if values true if turkey votive less than equal to 50. So this is satisfying the conditions. So it will come here. It will not execute this transformation right now. Test file is empty. Now, let me just execute this step. Okay? Look there. Store fixed qwerty. So I'm going to get a value here, which is the value of this variable. So let's suppose this is 50 30 left and equal to 50 now instead of turkey, I'm going toe give 60. No. 60 is not less than equal to 50. So what it will do, it will execute this mapping. Okay, so this condition is not satisfying equal. Go in egg liqueur. Dis it'll mapping. So in my final let me just open the this filter condition project variable. Save this. Save this and executed this so it will execute physical mapping. Look, value is 60 so 60 less then So age less than equal to 60. So now this is this. It's evaluate variable. So let me just give you one more example. So let me just drag and drop this variable again. Variable Let's suppose 50 60 This is 50 60 less than equal toe. No, let's posed 30 now Discontinuation iss variable True. So it will come in if this condition said this faith. Look Turkey less than equal to 50 years, it is less than equal to 50. Now this is evaluate Ready? But so here it is going toe of a fluid ready. But so I'm I'm ah giving multiple conditions. So first condition is first condition is first of all you it Very belief 30 less than equal to 50 Yes, it isf 50 So it will come here True section It will not execute this now where it will come here it will check this condition 30 equals to 30 If Death 30 equals to death 30 so it will execute this true condition Otherwise it will execute and other mapping Let me just add the mapping here which mapping, transformation, mapping Otherwise it will execute this mapping. This is false. So let me just repeat this again So sec variable value is 30 30 less than equal to 50 Yes, it is less than equal to 50 It will come here true Then it will check This 30 equals 2 30 yesterday Physical to 20 It will execute this So in a test or text equal right value here. Okay, so let me just remove this No value. So now let me just execute this mapping Look Turkey. Okay, it is going toe. Use this variable turkey. So it is going to right here because this here I have provided the dis variable certain variable. Okay, so that is why it is writing this value 30 in final test or txt file. Okay, now next we're going to discuss, declare variable. So declare Let me make this variable declare. Declare variable so declare variable is used when you have to provide devalue for variable at runtime. So if if if nothing are you like in test environment we used declare variable normally declare variable is used in a test involvement, not in production environment. So when you have to provide value a drunk time, you will use declare variable So now let's make this declare variable So it has become a dictator available now this is declared very but select meeting This declared irritable So the cystic level will save this side. No, When you are, you think it declare variable And when you're going toe execute this It will not ask to give valued runtime. It will only ask you to give very return time when you're going to make a scenario off this , Beckett. Okay, let's look at the example executed This It will not ask you to Ah, give value a crime time. Now what I'm going to do, I'm going to make a scenario off this, Beckett. So let's make us an addio generate scenario. So I'm generating a scenario for this package for that. When I'm going to execute this, I will provide very for very Bella crying time. So scenario has been created This Okay, now execute this scenario. Now I'm providing a value turkey. So right now, I don't have any value in my text file. Okay, So this is the value which employed in her drink time turkey, so you can change it as the so look 30. So instead, let me do some changes in my package. Now, this is my packaged 13. So instead, off here, I'm going to provide evaluate variable equals to 40 instead of 30. Let's I want to provide 40. Now what I'm going to do, I'm going to regionally this packet so that latest value is updated. We're regenerated now it is regenerating dysentery because I have done some changes in my and back it. So I have to region eri this now click death What I'm going to do So first I'm going to remove turkey Here, save this So now execute death Okay 30 So I'm goingto provide 40 here click Ok, Okay look 40 40. So if you're providing a value a grand time if the river scenario where you have to provide a value at run time, you can use declare variable normally declare variable iss used When you're using a staging environment staging or test environment in production environment, we don't choose declared variable hope you understood the concept. Thank you so much and have a great learning. 43. Packages in ODI Part-08: Hello, everyone In this lecture, we're going to discuss this section Internet. So in this section we have different commands Ftp sftp SCP So FTP stand for file transfer protocol. So when you have different servers when you're moving files from one server to another server, you will use this protocol FTP file transfer protocol. So when you're moving files the Dennis over, then you don't need FTP protocol. You will Jews d move command but you But if you have different servers and if you want to move files in different servers, then you will use the FTP command. Let me just drag and drop here. Look after become and command to execute which command you want to provide here. Well, while giving ftp vile doing f to be, um, get input m get input. Okay, Which command m delete. Okay, rename. So which command you want to provide here. So the more toast user name and pass Forgivable pride Local directory from where you want to get files. So the difference between like me just drag this STP get FTP put So put commanders you used to place files in another severed get commanders used to get files from another seven. Okay, so So let me just select this option. Put command. So when you're going to use the port command, it is going to place files in another server. When you're going to use the get command, it is going to get files from another several. Okay, so let's suppose let me just give you excellent. So let's suppose I have to seven. Ah, I have two servers. The first Severus. This is my landing server. And this is my OD. A server. Let's suppose I want to place files in or the A server. So here in the landing server, which command I'm going to use, I'm going to use the port Command and put combined. So let's suppose in this Lynn lending server I want to get files from this or day centres. I'm going to use em. Get command. So this is how you use disc amounts. So venue using ah or dft bu will provide the specific command. Either m get import. I am delete which operation you want to provide which operation you want to perform. You have to specify the commander. Then the whose name you the name password directly. Where you want to apply these commands? Okay. Today more directly So ftp when you're going toe select this option FTP get it is going to get files from another server. Like, if you're going to perform M get equal. Get files from this server so you will have to provide the user name Password host Name these details in orderto get files from in the December. Okay, this is m get so Okay, what if this would a sftp So ods ftp is same s ordered a ftp so Cody sftp is secure version off FTP This is going to ask some extra secure Take questions. Okay. Ah. Sftp stands a secure file transfer particle this tensor file Transit particle. This is secure version off FTP. So let me just select this. What options? I have command to execute. Okay, user name password. Look, it is going to look a sous such proxy host. It is going to ask some extra questions for security reasons. So this is more secure. Version Okay, s ftp get port get is getting fired from another server. Port is putting files to another server. Okay, Worked If this or day SCP so o d s C P. So when you have to UNIX servers you are going to use this command. So to UNIX servers, UNIX and UNIX Then you're going to use this command S f t s E P s E p stand for secure copy protocol. So SCP get is going to get fined for another UNIX ever SCP port is going to put files in another server. So this is when you are when you have to UNIX servers. So you will use the FTP are sftp command When you have windows too Ving goes to Lennix over , or bring those to bring those service, bring those two windows then you can use the Then you can either use, uh, ftp or sftp sftp our FDP. Okay, so when you have unit to UNIX servers when you have to transfer files between UNIX servers , you will use the SCP Command s IPI lockable provider credentials off recipe. Sorry, UNIX ever. So this is what I have in the Internet section. No metadata. So in the meta data, we have this utilities reversed. Matt set metadata reverse reset table. So what? This reverse reversing? What is the concept of reverse engineering reverse engineering is basically bringing the debt structuring or d A Or do you give you an option off doing it from here in the package so you can use this step OD reverse. It matter data. So instead of doing it from here now, you can do it from from here inside the package by using the step. So here you're going to provide the model global I d. So able. Aim Which table? Um, you want to reverse engineer? So let's I want to reverse engineer death. So if you're doing some changes, So, for example, if you have done some changes in your table and you want to reverse engineer your table again, you can use this step So instead of doing it from here in the moral section, you can do it from here. So you stable Aim for your update. Yes, you stabling for update. In case you don't want to use their table name, it is going to use the resource name, toe object. So this is reverse it metadata Reverse get matter. That is also Samos Reverse it matter data, but the difference is it is going to use deep J d. B C driver capabilities to reverse engineer your data. So these are reverse engineering steps Reverse research table. It is going to reset your table once it is reverse engineered. Okay, so you can use dis utilities but this is not recommended to use in a package. So whenever you have to reverse engineer any table you you are recommended to do it from here from mortals. So these are matter data next section I'm going to discuss the oracle that I integrator objects. So we have different objects. Like if you want to impose import export masterwork Repository, you can use this step Let's have one too Take a backup off work and master Repository I will use this step and distant It is Export Master Depository So after one week let's have one too Export Master deposited I want to take a back up off my master repository I will use this company Ah OD export Master Repository, This is going to take a back up off your master repository So five limit is going to zip Purify Stargate directly where you want to take a back up? Okay version export version Which were you want to export? Yes, export. What? You also want export versions. So if they inversion off Death Master repository, if you specify yesterday still also export versions. Okay. No export vert repository. Likewise, you will specify. Did a target directory zip file name and they say you can export them. Work repository. So we have other options or gay export log. This is going to export logs, which which are present here. So so for analysis Purpose. If you want to export your logs, you can You can export it from this utility here. Export type target directly there. You want to export your logs? The file name okay started, okay, started and ended. So from which period you want toe export. So you will provide the started and get Let's haven't export logs off. Uh, it didn't septal. Let's see 25th simp. So export. I want to export lords of these dates so I can use this utility to export logs. So this is very helpful when you are going to perform any analysis. In case you are having trouble, you can export laws and perform your analysis in either excel format or in a deferment. Okay. No. Next. Okay, there some other utilities that you can use to export. You can export scenarios. Okay? Exports and Aereo's export or degenerate all sin it is. If you want to generate all scenarios instead of one by one, you can use dis utilities. Oh degenerate. All scenarios project which project you want to use. Right now I have only one project. So I want to fold your name within a project. Which folder you want to you. So here I have to for the first folder and the transformation for ah so market group Progress Prayer Thais Miley's So in this way you can generate scenario. You will specify a d ah search condition like air I've specified I want to generate scenarios off this product. Okay, wh inside. Go inside this folder transformation in generic scenarios. So this is very helpful when you are going toe generate scenario for all objects. So instead off generating it one by one, you can use your this utility gin rate scenarios off all object, no important areas. So in this way you can imported any scenario, so export objects. So if you want to export any audio object, you can use this utility. Okay, Master and work repository Done Industry generate all scenario. Okay, this is going to generate scenario Import scenario If you want to import scenario so you will with this import object. Okay. Delete any scenario you will just destroy utility export All this an area. You will use this so you can also import from here. Go where? Important of work repository. Important apology. Logical topology. Okay. From here you can import or you can export. You can also exported from here. So these are the options available that you can export Exported a politics for the master report. It reeks Export multiple objects. So? So this is how you use the article that time to greater objects. 44. Packages in ODI Part-09: Hello, everyone. In this lecture, we're going to discuss the last part off this package. Tooth, this is your abilities. So in this section, this is important this ISTEA utility we normally use or gay os command. So when you have to execute a shell scraper or any other script, you will use this utility. Okay? US Command Command to execute which command you want to execute in case you're executing the dart such command. Let's save it. Is daughter such money? Dis command, You will execute the name off a command to execute Jewel. Provide the name past dark. Shh. It's a click. Task dark. Shh. You will provide their name. Ah, Put filed. In case you are, you're going to provide the output file. You will probably. Okay, let's I want to provide mile put file ear. Ah, output Dark text file. So I will provide the part of this file. Okay, enter if I in case I got another wild running defector mind Ah, I should get another in any enter finds. I have to pride in error file here to execute descript. So these are optional steps. These are not Monday. Three steps. Okay, so the 1st 1 is mandatory. Filed a pento or profile Desert of Working directory. This is also optional directory where the command will be executed. So I have to specify the directory where the command will be executed. In this case, this is the directory we come dis command will be executed so others steps are optional. So in this way, you can execute any script like you can execute Shell scraper bass script using this audio . Estermann. So there are some other utilities. So in case you want Oh, purge Lourdes. You can use this utility or the A perch lock. In case you want to remove temporary objects. You will use this utility or the remove temporary objects. Start Lord plant, start on job. So start scenario. So these are simple utilities, so normally we don't use dis utilities Very often. We only use this rodeo s command. So now I'm going to show you one more example. This is what I have from this package Toolbox. Next, we're going to discuss markers. So when I'm going to open this, I have disparities progress, smileys. So when you have to prayer ties, your prioritize any object you will use this, Marcus. So let's say I want to say I have this mapping. I have some mapping here, okay? I just have some map ings. I have one toe. Paradies my mapping. Let's said this. Mapping silent discord, mapping. I want to say that this is the most. This is the priority one. So I have answered this priority when this is the most important mapping. I have to look at this first, let's say my team Leeds, chest it. Whenever you have to log in this or d A, you have to look this mapping, which is the priority one mapping. You have to look at the priority one mapping. So I will set this party one. So whenever I'm going to log in or D I will first look at the Priority one map ings. Okay, Letter. This is priority one. Okay, lets it this if de Lise parity, this has the least priority, which is 35 different yellow. Uh, Borghi. Okay. So likewise, I can set progress. Let's say 60% off. This mapping is completed 60% for this mapping. Let's see and market progress, 10% is completed. So this is how you use different markers to certain the importance off different topics 45. SCD Type-01 Implementation in ODI: Hello, everyone In this lecture, I'm going to implement a city type 01 in o day. So But first, let me show you my source and target table. So this is my source table. And this is my target table. OK? My source table has customer I d. Customer name, city and country. So no, let me show you my source and target models. So source customer and target customer. So now, first of all, I'm going to create new mapping. So now we are implementing SCG type 01 a city type 01 Okay. No, I'm going to select customer Intar gettable. But first, I'm going to change some options. It customer sed type one. Okay, well, people, let's change it to slowly changing dimensions. See if this now I'm going to select my target target SED type one lap type changing toe, slowly changing dimensions. Select the attributes. Okay, so now let's change the behavior off. STD customer raggy tended to natural key. Ok. Ah, customer name, uh, over right on change. Change it to overwrite on change. Well, what I continent save this So I'm doing some changes in my target table, so I'm not changing in my source table. OK, over. I can't change over. I can change over. I can change natural cavities. Customer Raidi Now I'm going to select my source. Okay, let me just confirm one thing. Slowly changing dimensions. I'm not changing anything on my source. So select custom RSCG type 01 table in the target table, which is the, uh, this table. OK, right now I don't have any daytime. My target table. So now let me just connect these two. Okay, Customer. I d name city country. Now select the knowledge models. OK, but first selected Target table here. You're going to select the customer. Ready? So stuff tomorrow I d should be your key. Okay? Save target integration type should be incremental Update save this physical customer. I d select the knowledge models here. You goingto select the I came article incremental object Select the staging area. I came or do I will select l came l came SQL to Oracle, so I will select. L came This Okay, now here I have selected the incremental object. So flow control I would I will change it. Toe falls Okay, everything is okay. Save this Now. I want to execute this mapping. So in this particular example, I have implemented acidic. I've 01 So now let's look at the target table. Data has been inserted. Now I'm going to do some changes. So in a city type one, no history is maintained. Okay, Like this. Right Leg data. So this is my data present in my source cable. Uh, sixties Munich countries, Germany. Let's change it, toe, let's say selects elite. So let's say Jeff moves to Leeds Country UK. Okay. Ah, Comic Jeff Fleet UK. So no. In my target day. But, Jeff Munich, Germany. Now I'm going toe execute This mapping value will be changed. Okay, Jeff will be Ah, leads in country will be UK Now let me just execute this mapping. Now let's check if the values stand on. Look, Jeff Leeds UK. Okay, so now let me just insert a new rule. Plus, let's save +10 seven. Customer name is Brecht. He lived in alerts in New York. Uh, country U S c. Commit. Do you want to see if the changes? Yes. Okay, just this glows New York break New York us. Okay, now I'm going toe executor. This So after this, a new road will being circuit in my target table. Ok, look, Bread, New York U. S. C. So this is how you implement a city type 01 in a CD type 01? No historical information is maintained. That is objected. Okay, So hope you understood the concept. Thank you so much and have a great learning. 46. SCD Type-02 Implementation in ODI: Hello, everyone. In this lecture, we're going to implement a city type two in Oh, dear. So but first off, or let me show you my source and target tables. So this is my source table in my table. I have customer. I d name City Country. This is my target table. A city type toe customer. Underscore city toe in this table, I have some extra columns like started and eight current status And the Sorrow gate. Keep so basically sorrow. Getty is created so dick. Each row is uniquely identified. So Sarah beauty is will be generated via sequence number. It is automatically generated key. OK, it will be generated by a sequence number. So here, this is my source. Morgan. Okay, source. Morgan. And this is my target. Where is my target model? This is my target model. So this is Mr Target Mortal. Okay. You know, let me select my target table. So a lap type, I'm going to select slowly changing dimension. So this table is slowly changing dimensions. Okay, now, next attributes. So customer Raidi, I'm going to select the natural key customer name. I'm going to select over right on change city. I'm going to select air drawn agent country air growing tint started. I'm going to starting time stem and it ending timestamp current status Didn't record flag, so get key. Last one. Surrogate key. Ok, see if this no definition or lap type. Slowly changing dimension. So for safe site, I'm going to set my source stable. This is my source. Customer table. Let's change it to slowly changing dimension as well. Okay. At two boots. I'm not going to change any setting here, okay? No. Next What I'm going to do. These are their tributes. Okay, This is my source, Taibeh. I'm going to create new mapping a CD type zero to, so let me just drag my source stable. Customer target table. Let me just drag my target table in my target table. I have some extra columns like start date ended. Current status. Surrogate key. Okay, customer. I d. Customer name City country started in my started. I'm going to mention sister ended assistant. Good. And status one. Ah, one mean Jess Zero mean or one is new. Aaron, zero is old, so get key here. I'm going to provide the sequence Product sequence. Native sequence. Okay, so select this native sequence. Next. Where you I received this. Next I'm going to select the MMA target table here. I'm going to select attributes. OK, so started It is going to be executed or target Target? Yes, and it current status. Target target. No hint means that by the fourth it will be executed on on your own source. So since these columns are not present in my source So I want these calculations to be performed on my target table. OK? On my target Schemer? No. So target schema, Target schema. Target target. Okay, correct. Next, I'm going to set my customer idea ski. Okay, It is selected as key General setting. Find target integration type. I will select this slowly changing dimensions. Okay, Save dissecting physical A city type toe. I came. Or do you live all selectee? I came Oracle slowly changing dimensions. If this model is not present here, you have to import this model from here. Okay, you have to go here right leg important Alit model. You have to select the slowly changing for oracle for Look, I have already selected Lee. I came Oracle slowly changing. Okay, so I have already imported this model Next. Select the this. Okay, I'm going to select. L came astral to article. Okay, fine. Select this. I came here. I'm going Toe Falls Flo Control, I want to certain this fall's okay. Falls. Okay, Save this. Okay. Execute now. Ah, look, Data has been inserted. Next, What I'm going to do, I'm going to change my data. I'm going to change. Let's I'm going to change this Chicago and country, So select this data Chicago. Let's say I want to change it to suit me. Served me and country If Australia close. Yes, Safe changes. No executor. Dis mapping. You will see. And at the roof. So for this record, you will see in adoro. So this customer idea is not a primary key. So look 10 Which certain Me 100 100 is this? The first row is Let me just soared. This or good bye. One ascending Look. Destro. So a city type two maintains historical information. Look, it maintained the historical data. Look, look at this Is the current data one mean they're desisted current data zero me instead it is the old data. Look, The current data which is Australia so surrogate key will uniquely identify each record. Look. Customer ideas north. Unique for the record. Look, Look. Multiple. So this is Look, Bob. Sydney, Australia. Look, govern. Status is one. This is the old status, which is zero. No, I'm going to change some moral values. Data. Let's say, Shane, I want to say, Ah 60. Let's hit the Bay Country You A So let's insert one more room. 106 Johnson. Ah, You okay? I learned that. No executor. Def. Look. 105 Death. Dobey, The Bay Birmingham Wonder is added. Which is this Johnson London. Dukie started ended. Current status is one here. The current status is right now. Shane is living in Dubai. Yui. Okay, the current status. If this this is the current status, This is the old status, which is zero. Okay, what? Desist the order status. So this is how you implement a city type zero toe in ODI? A hope you're invested The concept. Thank you so much and have a great learning 47. Creating Master and Work Repository with RCU: Hello, everyone in this particular section, we're going to discuss O d e a agent and Lord plans. But before we discuss or day agent and Lord plans, we are going to create master and work repository via our see you. So we have already discussed how to create Master invert repository from this Do you interface from this OD a studio 12 c? So you will go in the final section knew the creator New master repositories. So this is how you create a master repository from this od a studio. But in this particulars lecture, we're going to learn how to create master and worked repository via are see you. So we'll also look at the difference off creating it. Why are see you So first of all, I'm going toe open. I'm going to go in the directory. They're my OD A well sees President Oracle Chelsea. Okay, so now article underscore Common. I will go here, So Okay, been I will go in the bin. I'm going to click this are See you on the internal dot Bad find seven kb file. So double click this file. So this is going to take some time to give me Deve Indo. Okay, give me the RC you visit. So what is the difference off creating repositories? What is the difference off creating master and work repository via this and through Geo interferes. So when you are going toe, create uh, master invert repository via our see you. It will create some other scheme must like STB repository creations Utility. So when you're going to use this utility to create Master invert repository, it will create some other schemers like STB. So STB will host agent related configuration details. It will also host fusion middleware component details figure middleware components are like veb logic server O d i e o b i e agent. So if you want these components toe talk to each other, you need a common configuration schema. Okay, you need common skimmer. So these components are going to talk to each other via agent. So that is the reason why you create master and work repository via our see you when you are going to create an agent. So, uh, this is how you create your master and Burke repository. So when you're going toe, create master and worked repository through this road, a studio, It will only create master and work repository. It will not create any other scheme us. But through this r c u equals create STB schema, which will host agent related configuration details. So this is the main difference. So creative partner, two years System Lord and product Lee Load. I have db a privilege, so I'm going to select this option. Click next. Soho's name Local host board number 15 to 1. Service name. User name, password using nemesis bastard Edmund Edmund is my postured sis db so Ok, now service name is Let me just execute this query we said with If so, I'm going to provide this service name O R C l p d b who are sealed B B B Marciel PDB local owes 15 to 1 says a green is my password click Next. Okay, check requirement requirement for specified database. So you will provide this. Ah, service name. Okay, click. OK. Next. Vab logic several and common. Okay, back. Okay, Oracle data integrated. Click Death Rabb. Logic Services. Where is STB Common Infrastructure Service. This is STB. This will host agent related configuration. This is important, so I'm using the production environment. I'm going to pride B R o d Perfect production. Perfect production Underscore OD. I underscored. Repository is my ah user name production Underscore OD. I underscore. Repository, click next click. Ok, okay. Provide the bastard Edmund Oneto trees My password segment Want to trace my password? So I'm providing Igman Goingto three s password. Click next supervisor Password. So I'm providing supervisor pastored a supervisor Supervisor Confirm supervisor Password So supervisor if the O'Dea user work repository Ah di de for development E for execution development work repository. So I want to create development work Repository Vote repository named by deformed work, Ari Piece de name. So if you want to provide any other name, you can provide it. So I'm not writing any passer for this work. Repository of work Rdp is my work A polity Work Rdp is member True quality Click Next. Okay, Desire might It is click next table space that do not do not already existence will be created. Okay, this is going to automatically create a table space click OK, to create table space. Click OK, check table space requirements are selected. Component Blake. Okay. Create production. Master in work deposit you look? Ah, not only it is creating master and work repository. It is also creating some other components. Some other details configuration or did service. Okay, it's a recipient. VEB logic services article platform security services. This is important Common infrastructure services. Okay, scheme owner is death. This is going to host agent related configuration details. So that is the reason why we are creating the master and work repository via this utility. So since we really create agent after this So this step is important Create. So this is going to take some time, maybe 5 to 10 minutes to complete this process So the repository creation step is completed . Next, I'm going to log in tow, my odious to do so now close this so connected repository it meant 123 So click plus working here to create a new logging. So bride, the log in name. So let's a log in. Name is OD A production underscore OD A repository production underscored or the A depository. So this is the log in name Supervise you The name is supervisor Supervisor is the user name Bastard A supervisor. So this is the od A user name This is the ODIHR using him. Logging name is production. So you can. This is the name which I have given so you can take your user name log in name. So this is the database connections. Use the name you're going to give the database you the name, Which is this which is different. This is different. This log in name is the one that you're going to give. So I have given production Underscored OD and of course, repository Brassard. I'm going toe give eggman. 123 Oracle J D B C driver You are l. I will provide this here. I'm going toe Pride the local host Local host Port number 15 to 1. So this is how you define port Number and service Name back slash Service name Witches Who are CLP db Who are CLP DB Okay, Flying eso If you want to log in only two Master depository, you will select that If you want to log in both master depository of the left worked repository, you will search your work repository So I want to log in on Lee to Master Repository Test Connection Success Connection Click Ok, so Okay. This is the log in name. So click So logging is successful. Look, log in. It's successful now if I want to Logging toe, Let's disconnected if I want to log in tow Book. Uh addict, I've want to logging Towboat Master repository as arrest Divert Repository I will select. Could not connect. Okay, this is different. Ah, select this product. Children Discord Repository This user name addict Disuse. The name. Okay, I want O logging towboat mustard repository as a leftie. Worked Apology click. Ok, okay. So this is going to log in towboat mustard repository as the leftie worked repositories. So I have locked in tow. Mustard repository has the left ever composite You look Ah, operator security topology, Look. 48. ODI Agent: Hello, everyone. In this lecture, we're going to discuss about agents in ODI A. So Egypt is a lightweight program. Get is responsible for execution. Of your components are programs. So let me just right here in excel. So what is the definition often, Agent Agent in or D A agent So he gently no d a Oh, dear is a light weight with a Java program. Debt is responsible for execution off you're component slash programs. So, for example, agent has different types. We will discuss different types off Agent. So let me just put the definition here. Okay? So for example, this I'm going to go into designer more. He had This is my DW project. Let me just open any mapping. So I have created some mapping for Let me just open this death mapping. Okay? So let me just execute this particular mapping. So if I'm going toe execute this mapping, let me just execute this. So if I'm going to execute this mapping, I will select context physical mapping design, which is physical. Look logical agent. So here I'm selecting the logical agent as local no agent. I'm not selecting any agent. So what? It is doing what Cody is doing, or D is executing this mapping in my local machine in my local computer. This machine. Okay, it is It is consuming the memory. It is consuming the memory off this machine. So it is not using aging to execute this mapping. It is using my machine to execute this mapping. Okay, so this is how you used the local. This is local mean new agent. So there are different types off Agent and Odiah. So the definition is, or the agent in O'Dea is a lie. Quid Java program that is responsible for execution off your components or programs. So let's say in this particular tablets A I'm going toe create air. Okay, this is Let's, er this is ordered a studio for days to dear 12 c. This is my deck. Stop in my deck. Stop. I have the hors d A studio Chelsea, This board a studio or gays to deal 12 c. This odious todo is connected to master and work repository. Select me. Create another box. This muster repository, Lex, it This is monster repository, and this is okay. Different mustard and work your brother to master in work herb oddity. This is this sword a studious, connected toe master and work report that reelect me selected this one This is mustard and work a polity. Let me select this shape Master in rock Apology This is master bother tree And this is the work repository. This is Master Repository. So my odious to do is connected to Master Invert Repository. So when I'm going to execute this mapping, it will give me an option on selecting the local knowledge. And it means Stacked OD is going to execute this mapping on my local system. It is going to consume my system resources to executor this. Okay, so it is not using any agent. So this odious studios connected toe master and work repository This is Master and this is work through poetry, so no dragon drop. So, Cody, a Grell c has three types off Agent three types off agent types of figure. So in Saudi it well see, we have three types of agent First a dentist Stand alone Agent 2nd 1 is dear to doubly Agent 3rd 1 is go Locator Gauging so stand alone isn't independent Agent independent program. It can be installed anywhere. It can be installed anywhere in in my local computer system or it can be installed in any server. So this is stand alone, agent. So, for example, right now, if I give you an example here, right now, my OD a Studio 12 is connected to master and work repository. I have a database Were I have created Master invert repository skimmer. Okay, this or gays to do is connected to this scheme us. So, in case if I'm using the or day studio in in any office in any work environment, I'm going toe have a server instead, off a database, I'm going to have a server. But so my database will be present inside this over right now, my data basis present in my local system. But when I'm going to use it in in industry, database is going to be located in my separate server. So let me just select the select the separate service. So here I'm going to have a separate server where I'm going tohave where I'm going tohave the database Here inside, I'm goingto have the database. So stand alone, agent. It can be installed anyway. It can be installed in the Indy server or it can be in stall in my local system. So let's suppose this stand alone agent is installed in my ah, in my server. So here, I'm going tohave Stand alone, agent. So this is my DWR database. Okay, so this database is also located Andy in the server. This standalone agent, which can be installed anywhere it can be installed in my local system or in it, can be installed anywhere in my servant right now. If I'm going to install this turned alone agent, I'm going to install in my local system because I don't have a sewer. So it it will be installed in in the local system. So s. But the definition of stand alone agent it is an independent Java program that can be installed anywhere. So this is first type. Second type is J to doubly agent subject to doubly. Agent will have VEB logic server logic server and and fusion middleware in start. If you go in Middlebury Inn story so Jake to double agent will have the capabilities off. Web logic Survival Lefty fusion middleware on top off Red logic sovereign figure middleware agent will being stoned agent will be install own stop off VEB Logic Infusion Middle This is Web logic or application server application. So let me just draw the diagram here so it will be like this. So I'm going to have a server. So here I'm going tohave the database D wh database. So here I'm going tohave vab logic service in a few years on top Perfect agent will being stormed agent So first do in turbulent on top Perfect agent will be in start here I'm going tohave the dwt get of Eros database. So this is Jay double Agent. So there blood X server prides additional capabilities like it provides operational console which is a do I based software. So this operational console operational console is going to use to monitor your logs So it is going to use to monitor your daily logs jobs which are scheduled It is going to give you information off completion time complete completion time off your map ings tasks, etcetera So it is going to be used for monitoring purpose. This operational can console it is going to be it is going to give you a complete summary off your components. Different components like completion time off different jobs. Except so this rib logic sever has the additional capability. So Agent is going to use these capabilities. So Agent is not independent off this web logic several and diffusion middleware. So we have another agent which is known a school located agent co located the agent When it comes to cool located aging. In this case VEB logic or application server and fusion middleware. These two components ardent story. But this co located agent is not going to use the capabilities off these two components. It will not used the capabilities off these two components. This school located component will be independent off these two components. So the diagram will be like this. This is my co located Aging. This is DW database. So this agent Cho, this is co locating agent. This agent is going to be independent off the logic cerar, angst Regan, Middle way. This agent will not use the capabilities off these two components. So here, this is This is co located agent. This is J doubly, Agent Jay Doubly agent like we just repeat the concept of J. W region. Has the have the web logics arrived? If Ethel is different middle were. So Web Logic server has the capabilities off operational control, which is a do I best offer which will give you the complete some re off your off your company's different components. Okay, so we have three types off, agent. So the job off agent and odious that it is responsible for execution off your components or programs. So then we are going to use the Lord plants when we're going to use the Lord plans in order . Here, this is This is the section off Lord plans. Let me show you the section off Lord plans. So we cannot execute Lord plants without an agent. You have to have an agent install created a device you were not able to execute lord plants . So, uh, one more important thing is that when you are going to execute this mapping like here, I'm executing this mapping here. I'm executing this mapping. I'm selecting the local law. Urgent. I'm not selecting any aging. So if I have to execute footing instance 100 mapping. Since I'm not selecting any agent, I'm selecting this local no agent option. This is going to put Lord on my system, so it is not a feasible solution to execute map ings on local No agent option this option. So you have to have an agent and start configured toe execute map ings or any other component. So that is the reason why we create agents so we can create anyone agent s so that we can use it in order. Ta! So there's no need of creating all three agents we will use only wanted agent to execute your component. So thank you so much and have a great learning. 49. Standalone Agent and J2EE Agent Configuration: Hello, everyone In this lecture, we're going to configure stand alone and do to doubly agent. So first of all, I'm going to go in the directory. Where my oracle, Odie eight. Well, season start. So forget currency. The sister directory. Where Or date? Well, season story. Oracle in the score. Common government been conflict dark CMG fight. So I'm going to execute this side so quick to this trial. So this is going to open up the configuration with it. So meanwhile, I'm going to connect to my repository. So egg men want to treat the buffered which repository This one. So this is the master and work repository which I have created from our see you utility create a new So this is the part there. Agent files will be located. Agent files will be created. Click next now. So if you only want to install standalone agent if you only want to configure, stand alone and then you will treat this option Standalone agent in this. But if you want the jato doubly agent as well, you will select some other templates along with these two template. So you will select Oracle Agent this Agent libraries I also want dis consoler o d a console Then I want article enterprise managers. So you have to select these options in order to configure stand alone a drink and do too doubly Agent click Next. So I have locking toe my master and work repository. Look, right now I haven't created any project. So the Sistine New master and work repository I have created from RC utility So Okay, so this is my database Select Star from V Dollar Services. For this, I'm going to select this O R c l p D b this service name. So if you are having trouble, why is using this service name? If this service name is shut down or closed, you will have toe execute this particular command toe. Open this service name alter plug Global database. O r C l p d. Be open. Then you have to execute this look or C L p D. Be read and write permission. This isn't open mood. Read and write, so effect is in mounting more. Then you will have toe a executor disc amount in order to make it and read or write more. So now application location. This is the application Location Agent location Click Next Vab logics. Okay, Web logic name you the name. Okay, eggman 123 Bastard eggman 123 Development more I c u deca oracle local Who states My whose name? 15 to 1 port number service name is You are Warsi l p d b is disservice Name or C l p d b is the service name Ah products NPR d underscored STB schema eggman 123 is Diskin Monem get R C U configuration. Ok, all ok, successfully done. So it is getting the RC you configuration to skin my name of this so agent will be created here in this particular schema which is this came our production underscored STB So STB is the schema where agent configurations will be created Click next Okay or C l P d b Bastard scheme owner, leave this click next Or did a monster scheme of work schema. Okay, test elected connections. Click next supervisor. Ah, User name is supervisor past 30 Supervisor click. Next administrator Sever Nord manager Topology system company. These two are options Edwin's ever ah, port numbers. This is deport number we're going to use for Jato Doubly aging. So save the sport. Number 7001 is deport number. Okay, use the name next. One more thing. So I will also create an agent here. A new agent. So you have to create the agent first in the ODI a studio in order to configure it. So the 1st 1 I will create it for stand alone agent O D i or D E a. Agent STD Stand alone. SD it. 01 Okay, port number. Okay. This save this. This is for, uh, stand alone. This is for directo. Double the agent. Agent dear to double e little one. Save this hair. You will provide deport number which is 70 a little one. Save this. So first you will have to create the agent in the ODI. A studio. So no click next using him in password. Okay, Nord Manager using them and positives. Let's say the logic is the eggman. 123 eggman 123 Next This is the OD. A server report number Is this Bleidt next? No, I don't want to or days ever. Template port number is death and SSL report number that this click next local or D A machine. Okay, select this option, and then you will have to select these two. And this to click next. Next local host. Ah, server port number is dead Too little. 910 Supervisor Supervisor password, Possibly supervisor Board number Is this od a oracle or day? Agent Oracle de Agent one system company. So here I will provide the name this name next Good. This name supervisor is the password click next. Next create. This is actually creating your aging. So it is going to create both standalone agent and J W. So in order to start stand alone, agent, you have to use the command prompt. And in order to start Jato doubly agent, you have to use the ah Web logic, Sarah. So this is going to take some time to ah, create an agent. 50. How to Start Standalone Agent: Hello, everyone. In this lecture, we're going to learn how to start Standalone agent. So this is the agent which I have created here in the old days studio. Now I'm going to test this. So this is going to fill the reason it is going to fail because of the fact that stand alone agent is North starter Jet. We have to start the standalone agent in order to start a start. Standalone urging. I'm going to open the command prompt. Here. Look, let me open the directory where the agent is install article. Well, see, uh, this user and the score projects do means based on discord. Domain Been. So this is the directory where the agencies and start Look, agent dark C m D. So now what I'm going to do, I'm going to use this directory. Copy this CD de directory. Okay, CD, then this. So, first of all, I'm going to go ahead in this particular directory where agent files are present. So now next, I'm going toe execute disc Ammand to start standalone Agent agent minus name equals to dis is the name of agent, which is this This one. Okay, this is the name off. Agent eso. I have created this agents best especially for standalone agent, then deport number. Port number is 20910 This is 20910 This is the local host. Okay? No. Ah, just copy this in paste here. Uh, no. This is going to take some time to start an agent. - So the agent has been started. Looked scheduler, starter for work repository on agent This. So next what I'm going to do, I'm going to test this particular agent which I have created, especially for stand alone in the test. Look, they're successfully agent. Was your number this? So this is how you start Standalone agent. So if you want to shut down decision, you will just close this. This is going to shut down. No, uh, if I test this, it will feel because the agent is sure Don't Agent is down. Okay. Now, in order to make this test successful, I have to start this agent again with this particular command. So this is the particular this safety directory where the agent is and star. Look, this is my new directory. Based on this Could domain then in this is directory. And this is the command to start Standalone agent. So hope you understood the concept. Thank you so much and have a great learning. 51. Load Plan: Hello, everyone. In this lecture, we're going to discuss OD. A load plan. So what is Lord plan? So Lord plan is an ordered a object which can sequentially or peril really execute or the A scenarios. So in their Lord plan, we can only egg scenarios. So Lord plan can be executed through agent so it can only be executed through an agent. So let me just, uh, show you the agent which I have created. So this is the agent which I have created. So to run an agent you have to execute this command. You will first go in the directory where Agent is installed using the command prompt. You are going to execute the agent. So this is the agent which have created no next time going to create the Lord plan. So this is the agent in the 50 logical scheme of which have created for this particular region. Let me show you. This is the logical schema. Production is the context, then the physical aging. In order to create new logical schema For this agent, you will have to click new logical agent. Then you have to provide the name. Okay, so I have already created Next. What I'm going to do, I'm going to create new Lord plan. Eso new Lord Plan loading is the name. So I will only provide the name here, then the steps. So this is the route step which you will get once you're going to create the New Lord plan . So this is the default step. So now what I'm going to do, I'm going to add scenarios there inside this route. Step. So this is dysentery A And this is dysentery, which I have created for these two map ings. So what I'm going to do, I'm going to just drag and drop here in place it inside the route Step. Okay, so now there is another way off adding this scenario plus sign runs scenario, step search. It'll mapping finish, dude, Step bless. Sign runs an area Step a mapping little to OK finish. So there is another way off adding Ah, the scenarios here inside the Lord plan. So this is the route step. So by Gifford, these two steps will be executed in a sequin schellman. Our first this step will be executed. Then this step will be executed. If this step sales then this step will not be executed. So let me say this. Now execute this. Uh this is the logical agent. You will select log level five No executor, This lord plans started. No, Check the logs here it is successfully executed. Look at these steps. Look. First step successfully executed. Second step is also executed successfully. Let's or both. If this steps feels so what will happen if this steps feels so open? Linked Object Diesel Take you too deep Mapping section filter condition Let me just specify greater now. This time will not execute because off this header Save this now. Next I will regenerate this. Where is the regional adoption? Regenerated this because changes have been made in the mapping. So in orderto make changes visible, I have two regionally. The scenario So now this step will fail. So this step next step the subsequent step will not be executed. This step will not be executed. Reese, Freshness, Look steps this step feels this step will not be executed. Because by default these two these two scenarios will be executed in it. Sequin shell manner Now. Next, What I'm going to do, I'm going to add I'm going to change this open link object. Okay. Greater than no regenerated this because changes have been made. Next, What I'm going to do, I'm going to wear this serial step. So this serial step okay, let me just make this stop. Now. This is the serial step inside this step. I have two scenarios. 1st 1 second one. So this is going to execute these two scenarios in a sequential manner. Next, I'm going to add another step which will be petrol step. So this is going to add these two steps, Imperator, let me just aired these two scenarios stayed inside the petrol step. So you can also add a scenario from here. Just drag and drop. This is going to Air Day scenario. So here you can see the scenario. Okay? No, just drag and drop from here. Dis mapping Derartu No. Desist, Sequential. No, I don't want to execute the serial step. I only want to execute this step. Okay, let's have gone to execute this, Okay? First, I will execute the serial step. I'm not executing the parable step. So this is going to execute the serial step first. Equal executed, disturbed. An equal execute distant. Now let's see if I am able to execute this on art. Okay, step it successfully executed. Look, these tooth Sen ideas I've been executive successfully. Next, what I'm going to do, I'm going toe execute these two steps Federal detail mapping and these two Okay, save this. Now these two steps will be executed in a barrel manner. Ok, now execute this Fife and executor this. No, let's look at the log. Their Lord plan steps look better. These two steps apparently executed. So I'm not executing the serial step. I'm only executing the petrol step. Now Let me just do some changes. Open linked object. No descent idea hasn't entered greater than greater than is not uh de Come on. OK, next This will not be executed. But this will be executed successfully because this is a federal step. So I have to regenerate this first. Okay, regenerate. OK, restart. Okay, look. This step is executed. Sexist labor, disturb piss filled. The reason it is failed because off this greater than greater than no generate regenerate. Now this is going to be executed successfully. Five. So this is going to be executed successfully. Look, Lord, plan steps both steps are executed successfully Now. Next, what I'm going to do, Serial. I'm going to execute this step and this step. So as they said under the route step under this route, step steps will be executed in sequential manner. So, for example, if I look at this example, this step will be executed first. Then this step will be executor. Good first. If this steps fails, this step will not be executed. So let's suppose open linked object. Okay, Greater than this. Saved this. No, let's do some changes. Regenerate instead. Off doing drift from here. Regenerate. Okay, No, this step will feel. Let me just to execute this safe Changes this step. Feel this step will feel because of this step. This step will not be executed. No, because both these steps will be executed in a sequential manner. Look, this step is not executed because this steps failed because of this step. This step is not executed because these two steps should be executed in sequence. Elmina No. Okay, now let me Just ain't this opening top project. See this lit 60 center and regenerated this you did mapping V generator death. So next I'm going toe egg in a district. The ruled step Okay, Barrel step. I have already discussed case step so to the variable. So for K step, I have to create a variable. Okay, so let's look at the variable here. So I have created a variable. This this is my variable. No miracle is okay. Gear step. Okay, Finish No next aired. Step when? Okay. Next he goes to QWERTY. When value goes to 30. When value equals to 30. Execute this particular mapping. Disinherit you. Okay, executive decision and you select this option plus sign. Otherwise l step. So first what you are going to do? You will click care plus sign else. So if you further want to add some ven step, you're a click death option. If you want to include the l step here, you will click this option So else I want to include this step mapping step. Okay, So first when value calls to Turkey executed this step otherwise execute district So let's say if I only want toe execute this Save this for this is the case step. You have to specify the value. Let's I want to specify Turkey So as I said it will execute this detail mapping. Open it there, Lord. Plane steps. It is going to execute this mapping. Okay, Next, let's sit. 20 execute steps. Okay. Else this will execute this else condition. This this still execute mapping zero to look, This has executed this, Okay, Mapping zero toe. So? So we have federal step, which will execute each scenario in peril manner. Gay step based on value. It will execute the scenario. And we have the serial step ritual which will execute the steps in in sequential manner, executed scenarios and sequential manner. So one more important thing restart from failure. So first, look at the root step. So we have two options. Restart from failure and restart all Children. So when I'm going to select this option, let's suppose if I have to restart this step, so what it will do, it will restart from first step. It will restart the Lord plan from the first step through the Children off this route Step Are this serial parallel case the other Children, so it will execute it from this step. Okay, So how to restart it? You will have to click this. Then you have to, uh with First it will sail. Click destined. You have to restart this. So let's suppose I want toe fill this step. Let's suppose Let me just put this open linked object. Let me just for this Save this. No, I have one toe. It'll mapping regenerate this. So now this step will not be executed. The other steps will also be not executed Because this steps feels so now and secure this saved this and then execute this turkey stoops steps. Look, dis step feels of the steps Uh, did not execute. Now what I'm going to do, I'm going to If I have to restart this, click this and restart. So the selected option is restart all Children or restart from failure. So since this step is filled, it will start from here. For example, if this steps feels so since I have selected dis option so it will execute from here. So since this steps feels this steps got failed so it will execute from here since I have selected restart from failure. So first I will have to change this Save this So this is restart from failure. Okay, He tell mapping regenerator death. Okay, this is executing V start from failure. Successfully executed. Okay, so it will start from here, then acquittal. Execute the other steps. It is successfully executed. Look. Okay, this is restart from failure. Restart all Children. It will start from the first step. It will restart all Children. Okay, Next, we have this step Serial step barrel step No first option is restart from failure. So for example, if this steps feels so the 50 serial step, for example, if this step if this step successfully executed Okay, if this step is successfully executed But let's suppose if this steps feels so since I have selected restart from failure, it will only executor district. It will not execute district. Likewise for federal, we have two options. Restart from field Children or restarts all Children. So if I select this option ical the equal execute all two steps No, Inside this step you have different options. Restart from failed steps. It will only restart the feel step. This is going to be fulfilled. Task. This will start a new session for every time. So every start will have. Ah, every step will have a new session. So it will execute this scenario from a new session. So this is the Lord plant. This is how you create a Lord plan. You can also include this lord plan in a package. So the difference between package and a Lord plan. So what is the difference between a package and a lower plan? So, in a package, we can use multiple variables like we can. You set variables. We can juice refreshed variables, evaluate variables. But in a Lord plan, we can only use SEC variable, other variables we can't use. Also, the drawback off using a load planets that in a Lord plan, we can also we can only use scenarios. No other objects can be used in a little Lord plan. But when it comes to packages, we can use some other objects like map ing's procedures like these objects mapping reusable mapping procedures, variable sequences, etcetera. So hope you're industry the concept, Thank you so much and have a great learning