Category: Tech Tutorial
Tags: DataIntegrationMappingOracleTutorial
Entities: Knowledge ModulesOracleOracle Data Integrator 12cSSHTerraform
00:00
this is based off the live lab introduction to oracle data integrator 12c let's get started so first one thing you want to do is go to developer services and then go to
00:17
stacks under resource manager i've already penned it and what you're going to do is you're going to want to download this terraform zip file
00:33
that's already made available and then you're going to want to create the stack upload this change it to zip file upload the zip file you can see i already have it here
00:51
then you can give it a name of your choosing choose the compartment i just put it in the root compartment we see terraform 0.11 is not
01:07
supported anymore so we're going to go with 0.13.x hit next
01:28
we you give it the instance configuration select the availability domain uh we're going to need the remote access so we're going to do a keygen um so
01:44
if you need to figure out how to do that there's the documentation how to generate ssh keys um i've already done that
01:59
so then we're gonna get gonna go to uh two hit next and then click run apply to
02:16
automatically approve it i've already done that you can see i have my stack up and running here next we're going to go to
02:32
our graphical user interface for odi
03:15
let's go to application information we see the remote desktop is here show it copy it paste it let's go uh
03:33
we'll do a sign out so you can get the full breadth of experience of initiating the odi studio
04:04
so we want to do connect to repository enter the wallet password which you should have configured while doing the terraform stack oh this is already changed i changed my
04:22
wallet password hit okay it's initializing the the environment
04:37
click import we're going to do smart import change it to basics
05:01
slash odi underscore mappings audio underscore beginner
05:19
underscore labs dot xml hit next
05:35
wait for the wizard to process enter export key so
05:51
be the same thing it's our wallet password hit next no issues we would click finish but i don't want to replicate the same files
06:06
because it might have issues later so you can see i've already done that you can check the status in the operator pane so there's four panes designer security operator and topology pane
06:22
uh windows here on the left you can see it i've already seen successfully initialized the environments
06:38
so let's get started in odi studio let's so the first thing we're going to do is create our first mapping we're we're gonna load data from src product table and the order application model into our trg product family target
06:55
table so this is really just um learning how to map data from sources to targets and odi
07:12
so the first thing we're going to do is in our designer navigator expand the demo project node in the projects accordion under sales administration
07:29
uh right click mapping new mapping give it a name demo
07:44
map one make sure the create empty dataset option isn't selected and then now an empty mapping window
08:02
will appear on the screen here in the middle so the first thing we're going to do is define the target um so the target is the element that will be loaded by the mapping to insert the target and the load trg customer mapping
08:20
we're going to want first verify that we are in the logical tab here so you see there's three tabs the overview with the logical and the physical and the mapping editor pane um so the designer navigator and we're gonna expand the models accordion here
08:37
and under sales administration we're going to select trg product family and we're going to drag it and drop it to the mapping editor
08:53
so we've defined the target now that step two is defining the source so the source data stores contain data used to load the target data store two types of data stores can be used as a mapping source data storage from the
09:09
models and reusable mappings so this example here will use data stores from the order applications and parameters models to add source data source to the load src product mapping we're going to go to under models drag the
09:27
drag the following data stores into the source diagram so we're going to do src product drag it and drop
09:42
target let's go ahead and you can see the f stands for source t
09:58
stands for target go to components our components here on the right which uh are etl tools and uh visualization tools so we're
10:14
gonna grab distinct the distinct component over here and we're going to position it in the middle between the source and the target
10:43
we're going to want to drag the family name into the distinct target having the distinct component and we're going to map it over to the target table
11:04
we're going to hit click click save you can see it saved in there and our expression is correct so i want to select the family id column
11:21
and the target table and we're going to want to type in our expression upper substring dot
11:37
family name one three
11:59
so the target table integration type is going to be defined in this section so we're going to target our integration type
12:16
we have control append which appends data to target tables and all the records are considered as inserts or we we're going to change to incremental update which will update the existing data and just insert the new data
12:31
so we're going to use incremental update for this mapping and this is all defining the target integration type so now we've upgraded we've defined our target integration type for this mapping
12:48
next we're going to define the data loading strategies also known as lkm learning which stands for the loading knowledge modules
13:03
so the data loading strategies are defined in the physical tab of the mapping editor oracle data integrator automatically computes the flow depending on the configuration in the mappings diagram
13:19
it proposes default knowledge modules for the data flow so the physical tab enables you to view the data flow and select the knowledge modules used to load and integrate the data loading knowledge modules are used for loading strategies and integration
13:35
knowledge modules are used for integration strategies so we have both loading knowledge modules and integration knowledge modules so you need to define the way to retrieve the data from the src product table in your source environment
13:50
to define the loading strategies in our physical tab of the mapping editor we're going to select the access point that corresponds to the loading of the source product in this example this is the distinct
14:07
so the property x inspector should display the properties of that of the access point and the property inspector we're going to want to verify that the load loading knowledge module sql to sql built in option is selected in the
14:23
loading knowledge module selector list and our target database loading knowledge module we see we want loading knowledge module sql to sql
14:38
which is built in natively and globally so now that we've done that just click save we're going to define the data integration strategies
14:56
also known as the integration knowledge modules so we need so we've we've already defined our loading knowledge module now we need to find define our integration knowledge module to tell the data how it needs to be integrated
15:13
so we're gonna we're gonna go to the the physical tab which we're already in and we're gonna select trg pro uh target product family and the target group object the property inspector will display the properties of
15:29
the target and the property inspectors set the ican integration knowledge module to integration knowledge module oracle and incremental update
15:48
if you don't see that option make sure you have correctly set the target integration type to incremental update in the logical panel so we see it so we know we've done that correctly and the knowledge module options we're just going to leave the default values
16:04
as is and you can see the property inspector appears as shown and i'm going to save it and we're going to define the data control strategy as our next step
16:22
so in the preceding steps we define the data flow from the source to the target but now we must define how to check um our control knowledge module and the constraints and rules that must be satisfied before we can integrate the data
16:38
so we're gonna we're in the mapping physical tab of our mapping editor and we have target product family selected and under properties we're going to select check knowledge module
16:54
and we're going to verify that it's ckm oracle and we've done that so we're going to go back to our logical uh tab we're going to select the target sword trg customer
17:13
and we're going to verify the constraints panel um and we're gonna click
17:30
it's already saved and now finally we're going to run this mapping that we have everything primed and ready to go so
17:46
we're going to click on data in our target table and we see the data that's in there and now it's right we can see it's ready
18:02
to run so we're gonna click run to run the mapping just gonna hit okay see hit okay for session start
18:20
we're gonna refresh the designer tab navigator and now we're going to go to
18:39
uh we're going to verify the data in the table after the run just like we did before and we see that the data is in the target table and next we go to the
18:56
operator navigator go to status to see the status of the job and we see that it successfully
19:13
uh did the mapping
19:55
it takes a second to just exit the session went back in because it takes a second to show under the status tab we see the status as successful okay so we've completed our first mapping
20:12
um which maps to we map data from our source into our target table now we're going to do the second mapping
20:33
so we're going to want to go back to our designer navigator and we are going to want to go to uh under demo
20:48
expand the cells administration node mappings new mapping yet again uh demo map to make sure create empty dataset is not selected
21:04
we see the empty window appears in our mapping editor yet again for and we're going to just go through the steps so we're going to define the define the target first so first we're going to define the top find the target then define the source
21:22
and then we're going to do it instead of this time instead of doing a distinct component we're going to do a join component um because the different types of etl tools and we're going to do then we're going to define the target integration type then define the data loading strategies
21:39
the data integration strategies define the data control strategy and then finally run the mapping so first let's define the source so we're going to go to under the models accordion drag
21:55
we're going to drag src product under order application to the mapping editor and then drag target product family from the cells
22:10
administration folder
22:31
um and trg product so we're just gonna put it exactly how we want it to be
22:47
visualized grab just a distinct component put it here grab the join component put it here
23:20
uh so we're going to create the mapping drag family name from src prod from source product into the join
24:01
just click okay we're gonna uh drag distinct to target now we have our mapping click ok and we can see what our mapping editor
24:18
looks like so now we're going to click on our join component and we are going to uh we see
24:33
the expressions already done for us because the odi is becoming extremely intelligent so we defined our source now let's define our we define our source and target now
24:49
let's define our target integration type so this is going to be familiar to what we did in the first mapping so we're going to select our target table
25:04
and we're going to go to target and we're going to change um we're going to keep control append as our integration type in this case
25:20
uh let's click save as we go next we've so we've defined our source to find our target defined our target integration type and now let's define our data loading strategies
25:38
so the data loading so we're going to go to our physical tab in our mapping editor yet again click our source product
25:54
because it's the access point we're so we're always going to start when we define our data loading strategies we're always going to start with the access point of our target uh target group so we go to
26:10
attributes our loading knowledge module we're going to go lkm sql to sql built in again like we did in the first mapping click save and now we're going to find we define our loading strategy now let's define our data integration strategy
26:27
so now we're going to select trg product just like we did in the first mapping and the property inspector we're going to change ikm to icam global insert global we see
26:43
it's done that uh if if it's not in the list make make sure you've correctly set the target integration type to control append in the logical panel because that is the area associated with that issue
27:00
in the knowledge module options we're just going to leave leave them default yet again and now we're going to define our data control strategy so we're going to do our check knowledge module again all this should be very
27:15
familiar to you at this point so verify that it's a check knowledge module oracle for our ckm and it is so we're going to go back to our logical view
27:31
you want to select trg product and verify that we're going to verify the constraints panel so go to constraints and we see all the constraints we want are set to true
27:47
and it's already saved so finally we're ready to run the mapping so go back um and click run
28:05
click ok session started and then we're