17- ODI Mapping Concepts-2

In the second part of this Mappings module, we’re going to have a look at actually creating the mappings.

Remember that the mapping is the ODI object that you are going to define to move the data from one or more source datastores into one or more target datastores. And the attributes in the source datastores will be linked to the attributes in the target datastores through your business rules.

Within the datastores, you are going to have on the target an expression. And this is the business role that is going to be used to decide how the target datastore attribute is going to be populated. They will be implemented as an SQL clause. And the transformation rule is going to usually map the attribute in the source datastore onto the target datastore attribute. And at runtime, that will be executed by your database server.

We’re now going to do a demo where we’re going to have a look at creating the mapping. To create the mapping, then, we go into the Designer Navigator. You go to your project. You expand the folder option. There is a Mapping subfolder.

You right-click on the Mappings, you choose New Mapping, and then you enter a name for the mapping.

You can give a description for the mapping. Now, you can create an empty data set. Data sets were very commonly used in earlier versions of ODI. However, they are not so commonly used within 12C. So here, I’m not going to create an empty dataset.

I now click OK.

That will put me into the Mapping Designer. At the top, we have where we’re going to create our mapping. Then, underneath that, we have where we are going to define our properties.

There is the Overview tab,

which gives you an overview of the mapping. Then we have the Logical tab. This is where we are going to say what we want to do. There is also a Physical tab, and that says how we are going to do that.

To start with, we’re going to create a simple mapping.

Now, to do this, we are going to expand our Models folder. We are going to choose what we want to use within our Models folder. And here we have my source. Here we have the target. And what we’re going to do is, you just drag the datastore that’s going to be your target and drop it into the Designer.

Within 12C, you can have more than one datastore as the target. To start with, though, we’re going to be very simple. We’re going to have one target datastore. And now, what we’re going to do is, we’re going to drag the source.

So we go in and we drag the source and we drop it. And now we have our source.

And if you make a mistake, you can highlight and then delete, and that will remove it from the mapping.

So now I’m going to go back and take my country.

So it doesn’t really matter which order you do it in. Now what I’m going to do is — if I have a look at this datastore.

if I have a look at the region datastore and look at the data, you’ll see that what we have is, we have a region — for example, South Provence, and one country has many regions.

Now, what I want to do is populate a city table, but I only want to have one entry for each city. Now if I took these rows across, I would have multiple entries for my country. Therefore, what I’m going to do is, I’m going to use a component, which is known as a distinct component. I drag and drop that across. You can see at the moment it is now empty.

What I can now do is, on my source, we have this little nodule. I take that, and I drag and drop it across to the distinct. And it’s now going to say, well, how are we going to match the attributes on the source to the attributes on your component? And it says that if the attributes do not exist on the target, we can create them. If they do, we’re going to match them by name. And it doesn’t matter if the case is different.

Well, on my distinct, the components’ attributes do not exist. So I click OK. Basically, it copies across the components of the attributes from my source to here. Well, I’m only going to take across COUNTRY_ID and COUNTRY.

So I’m going to go into REGION_ID and delete that. I’m going to delete REGION. So now, all I’m going to do is take from the source COUNTRY_ID and COUNTRY. I’m going to move it to the distinct component.

And then from the distinct component, if I take the module and drag and drop it from the component to my target, it says how it’s going to map. Well, we’re going to match on the name. And we’re going to ignore the case.

So now, if I click OK, we’ve matched the COUNTRY_ID on distinct, the COUNTRY_ID on COUNTRY.

We mapped the country name on the distinct to the country name on COUNTRY. If I click on the COUNTRY_ID, you’ll see here is the expression. And we’re saying the COUNTRY_ID in the COUNTRY — we’re sourcing that from the COUNTRY_ID on distinct. If I look at the distinct COUNTRY_ID, we’re sourcing that from COUNTRY_ID on our source region.

Now, here, we are just doing a very simple match. You can go in, and you can have an expression. And notice, if I highlight over the expression, there is a little cog.

You can click on the cog, and you can build an expression here. If you are going to use columns, Then we recommend that you choose the column from here — you do not manually type them in.

So there I’ve said what I want to do. Well, now what I need to do is, I’m going to say how I’m going to do this. Now, if I have my white space here, I could give a hint of where I want the staging location to be. And we’ll come back to that later on in another lesson. But now, I need to say what Knowledge Modules I’m going to use.

So to do this, I’m going to click on the Physical tab.

And here, we have the Physical, and you can see that we’re taking the data from the source to the target. Now, to do this. I’m going to need to use a Load Knowledge Module, to date the data for my source table, and to load it into my temporary table.

I go into the access point. And on here, if I click on the object, you’ll see here is the loading module. And we can choose the loading module that we wish to use. So we can choose the loading module that we are going to use. So here, I’m going to use SQL to Oracle. I could use the loading module SQL to Oracle. As we will talk about later on, each Knowledge Module has various options that we can set. We’ll talk about setting those options later on. We can also specify, if I click on the target country, the integration Knowledge Module that I wish to use.

Now, when you choose the integration Knowledge Module, if I click on Logical and go to the target table and I click on the target attribute, it says how I want to integrate. So I want to control append. So do I want to append, or do I want to do an incremental update? And what you choose here — that will then dictate the Knowledge Modules that are available to you in the physical.

So if I choose the I want to control append, if I go into the physical, then I go into my target table.

And I click on Integration Knowledge Module, that will show me Knowledge Modules that are suitable for control appending.

Again, I can set that. And if I was going to do a check.

I could go to the Check Knowledge Module and specify a suitable check Knowledge Module.

So now I’ve gone in and I’ve chosen the load module I wish to use, the integration module I wish to use, and the check Knowledge Module I wish to use. We haven’t finished with our mappings. We’re just creating a very simple mapping. Once I’ve created the mapping, I can then save the mapping. And I can now test my mapping by running the mapping.

To test the mapping, there are a number of ways I can test it. I can go in, and I can highlight the mapping. And now what I can do is, there’s a little green arrow on there to run. Or I can right-click on the mapping, and I can choose Run. When you run the mapping, you choose the context. By default, it will show the context you set as your default. I could change that. We’ll talk about the physical mapping design in more detail tomorrow, so I’ll leave that as it is.

And here, we would choose the agent. Now, if I’m in development and everything is on the same machine, I do not have to use an agent. I can just use a little local agent. And that’s what I’m going to do here. I can set the log level. I now click OK. Choose local, no agent. Click OK. And now it started my session.

I can now go to the Operator tab, choose how I want to look at the session logs. Here, I’m looking at date. And you can see that my one failed. Now, tomorrow, we’ll have a look at how it failed, but it’s something to do with the integration Knowledge Module.

So what I’m going to do is, I’m going to go back into my mapping, go to Physical, change my Knowledge Module, my implementation.

So I’m going to go into Logical. Go to the target, change that to control append to incremental update. Now, I go into the physical, go to my target table, choose my integration Knowledge Module here, which is going to be the Knowledge Module I’m going to be using, which is going to be Incremental SQL Oracle Incremental Update. Now, save and run.

Click OK.

Go to my operator, refresh, and now you can see that it’s worked OK.

I’ve got the green tick. In a later module, we’ll talk about looking at the session logs.

So I’ve now chosen the correct integration Knowledge Module.

I can now go to the Designer. And to test, I can go to my target, which is target country, right-hand click, and look at the data.

And you can see there, I’ve populated my target country. And that ends the demo for creating a very simple mapping.

To create your mapping, then, you create and give the mapping a name. Optionally, you can give it a description.

You can then go in, and you choose New Mapping, choose the name, and optionally, a description. You then expand your Models view, and you drag and drop your target datastore.

You can have multiple targets to start with, though we’re just going to have a very simple mapping, one source, one target.

You can then take your source data from the Models pane and drag that in as well.

You connect the ports to do the attribute mapping. You can specify how you want to do your attribute mapping.

You can go in and define your expressions. You click the Logical tab. You then go to the attribute. You click on the attribute, and in the Properties panel, you can then specify how you are going to populate the target attribute by specifying your expression.

When we have our expression, we can put values in. If you are using any string values, the string value should be enclosed in single quotation marks. Please do not enclose numeric values in single quotation marks. You can use source columns. Please do not type in the source column value, but instead, choose it from the Expression Builder.

You can use any DBMS function that is supported by your underlying information system. That includes aggregate functions such as max and min. And ODI will automatically generate the GROUPBY clause. And you can combine your clauses as well. So here, we’re taking the first name of the salesperson, we’re piping it with a space to the last name of the salesperson, and we’re transforming the last name of the salesperson into uppercase.

Once you have created the mapping, you can save the mapping. The mapping is saved in the work repository. And then you can test your mapping by running the mapping. And by going to the Operator tab, you can look at your session logs. Those session logs are also stored in the work repository.

To finish off, then, we have some quiz statements.

So which of the following is not true? First of all, ODI is based on your business rules. Secondly, ODI requires a proprietary engine. Thirdly, ODI can generate native or standard SQL. Fourthly, ODI business rules are defined in a mapping. And lastly, the ODI processing instructions are implemented in Knowledge Modules Well, the statement that is not true is B. ODI does not require a proprietary engine it uses the underlying information system function suite and will be processed by the underlying information system.

Next question. How would you populate several targets simultaneously in ODI 12C from one source? First, you need to insert several datastores in the target area. Secondly, you need to insert several datastores in the target area and you must use a Split component. Lastly, it cannot be done in one shot. You must make multiple mappings and put them together in a procedure.

Well, C is not true. It can be done in one shot. You could insert several datastores in the target, and you could use a Split component. However, you did not need to use a Splint component. So B is false where it say, you must use a Split component. You can use a Split component, but you don’t need to. So the statement that is true there is A, you need to insert several datastores into the target area.

There, then. We’ve had a look at the concept of ODI mappings. We described the concept of expression, joins, and filters. We described the process of implanting our business rules. We described the concepts of staging area and execution location, how we can use Knowledge Modules with ODI mappings, how we can create and execute basic ODI mapping, and we’ve introduced you to lookups. In subsequent modules, we are going to go into mappings in more detail.

Now, you are going to do some labs. And in the labs, you are going to create some ODI mappings. In the first lab, you are going to create a very simple mapping that takes the data from one source and loads it into the target. In the second mapping, you are then going to enable constraint checking. And in the third mapping, you are going to have an expression, a calculation for your target attribute. So you’re going to create a calculation expression. That ends the second part of this module, looking at designing simple mappings.