Universal Data Adapter (UDA), direct DB integration - Part 2

If you just landed into this post you may want to visit Part 1 first in order to get the basics of the new Universal Data Adapter (UDA).

Configuration steps in a nutshell:

1. Configuration of ODI components (topology, projects and models)
2. Configuration of FDMEE components (source system, source adapter...)

In this second part of the series we will cover the configuration of the UDA in FDMEE.

Source System

In the same way we do with the OIA, we need to configure a source system in FDMEE.

When we register a new source system we need to select the source system type. One of the changes in 11.1.2.4.100 is that we don't have type "Others" any more. This was the source system type for Open Interface Adapter before PSU100. We now have one type for each source database supported (besides the new one for Open Interface):

As we have two source databases (MSSQL and Oracle) we need to register 2 source systems:

After saving our source systems we will see the two of them in the source system summary panel:

ODI Context Code

As you can see, the ODI context code is a mandatory field when registering a source system. If having one source system of each type you would probably use GLOBAL context which is the default one for FDMEE.

The context code links our source system with the physical architecture defined in ODI.

Think then about the following scenario: what would happen if we have 2 different source systems for Oracle databases? Which context would you use? GLOBAL? we can't use one context as ODI wouldn't know which database to point when extracting data. Therefore we would need two contexts. 

Technically, at run-time, ODI points to the data server/physical schema mapped to specific logical schema (based on our source type) through the context set in FDMEE.

For example, if our source system type is "MSSQLServer Data Source" and context code is "UDA_MSSQL", ODI will extract data from the database (physical schema) mapped to logical schema "UDA_MSSQL" through that context: SRC_ERP.

In order to provide a flexible configuration I always like to setup different contexts when using other source systems than File.

Source Adapters

In the same way the source system is the link between FDMEE and the configuration of our physical connections in ODI, the source adapter is the link between FDMEE and the ODI objects which will perform the data extract.

When adding a new source adapter we can't select neither ODI Package Name nor ODI Project Code. This is because the UDA only uses one project. This project has a package template which is used to generate new packages for each table we extract data from. 

This approach is different than the one used for Open Interface Adapter but similar to the one used for SAP BW adapter.

Don't worry if you did not understand what I just said. I will explain it graphically below.

1. Creating the source adapters

MSSQL

Oracle

How does the source adapter for UDA work?

Once we have created the new source adapter we need to

1. Bring table definition into FDMEE so we can configure filters and import format

2. Generate the ODI package which will extract data from the table and import into TDATASEG_T

Do we have to perform these operations manually? noooooo! I can't imagine we would have to do that :-) So then how we do it? Luckily, FDMEE will do for you by clicking 2 buttons :-)

• Import Table Definition: will import source table/view columns into the source adapter in FDMEE
• Generate Template Package: it will generate a package in ODI which extracts data from the table. This package will contain an ODI interface using the filters we define in the source adapter

2. Importing the table definition

Going back to the configuration, we have already added the two source adapters. Now we will import the table (MSSQL) and view (Oracle) definitions into FDMEE:

When importing the columns we need to select the source system name so ODI can go to the physical table and reverse it.

Hopefully you get a success message like this:

Cube definition? I said that UDA source adapter was similar to SAP BW, too similar I would say :-) Obviously this message will have to be changed by Oracle so we see Table definition has been imported successfully (Update 22/4/16: already fixed in 11.1.2.4.200)

You may not be familiar with reverse concept if you don't know ODI. Let's try to give some clarification of what happens internally when you click that button.

If we navigate to ODI operator we will see that an ODI process was executed. This process reverses the table which basically means: it will create a data store in ODI for that table. 

Then this datastore will be used in the ODI interface as a source datastore. If we navigate to the ODI model folder that we imported when configuring ODI, we can see how a new datastore has been created below MSSQL Source model:

I'm not going to test this today but I can anticipate a potential issue here. What would happen if we had two source databases of same type having tables of same name?

I guess the import table definition process would fail. If you have this issue you may have to create Views on top of the tables and name each one differently.

Going back to the table columns, once they are imported, we have to classify some columns and optionally type a user-friendly name for each column. This is the name that we will see in the import format configuration:

3. Classifying amount and period columns

We need to classify the following columns:

- Amount (mandatory): so FDMEE knows which is the column imported as amount

- Period (optional): used to filter data if DLR is configured with period type "explicit"

- Period Number (optional): same as period

- Year (optional): same as period

If we assign only Amount, then we can only use period type "None" in the data load rules. In other words, FDMEE assumes that the content of the table/view has data only for the period being processed. If we classify the period columns then we can use period type "Explicit" which will be passing as extract filters the values that we configure in the source period mappings.

Update (26/Feb/2016): We can find source tables/views where we only have one period column. This column could have values like JAN-16, FEB-16, etc. In this case we just have to classify the period column as Period class. Then it is quite important that we set correct values in the source period mapping table for the adapter as this will be the value passed as filter when explicit period type is selected in the data load rule.

Data types must match with the following table:

Time to change my source table and view because I forgot to include period columns :-)

Once they are added I can re-import the table definition:

We perform the same steps for the Oracle View:

If you have new columns in table, don't add them manually in the source columns panel as they table needs to be reversed in ODI. Otherwise that column cannot be used in the interface and your configuration will fail when generating the template.

4. Defining the source parameters (extract filters)

In a typical data extract from database we usually use filters. If you think in a SQL query like this:

SELECT X, Y

FROM TABLE

WHERE X = 'red'

We are using a filter on column X so only records having X with value "red" will be extracted.

The same applies to UDA. We can define filters on any column of the source adapter. These parameters will be available as text box in the data load rule.

• Parameter name: in order to be aligned with other source adapters I like using the following naming convention for parameter names 
• p_param_name (lower case and words delimited by underscore)
• Data types: char (text), number or date
• Conditions: condition used to filter the column with the value set in the data load rule
• Explicit (WHERE COLUMN='x')
• Between (WHERE COLUMN BETWEEN 'x' AND 'y')
• In (WHERE COLUMN IN ('x','y'))
• Like (WHERE COLUMN LIKE 'x%')
• Default value: value used as a filter when you don't pass a value
• Parameter prompt: parameter name you see in the data load rule (user-friendly)

We have created one parameter for each source adapter:

MSSQL

Oracle

I have created one of them with LIKE condition as I want to stress test it :-)

5. Generate template package

As I explained before, once ODI has the datastore for our table/view (imported after clicking "Import Table Definition"), it needs to generate the package with an interface to pull data from our source into FDMEE staging table TDATASEG_T.

Before clicking any button I'd suggest you visit ODI Studio and see ODI project structures. You will see something like this:

I will go back to that structure once we generate the new package.

Let's click on "Generate Template Package":

Whoops! An error occurred message. We didn't expect this one :-(

Let's do some troubleshooting!

The 1st place where I will look is the aif-WebApp.log:

There is one error:

Error encountered while computing Source Sets :ODI-15562: Source sets cannot be computed because one execution area doesn't have a physical schema. Check the errors on the interface

It says something about physical schema so I will review my topology.

I don't see anything wrong here as my context UDA_MSSQL is correctly configured.

Then I remembered when I was struggling with the SAP BW adapter where I had a similar issue. I found out that even if you are using other contexts than GLOBAL (we have configured UDA_MSSQL and UDA_ORACLE), context GLOBAL cannot have the logical schema for the UDA mapped to <Undefined> physical schema.

Why? I guess this is needed because the ODI interface generated by FDMEE is using GLOBAL as the optimization context (it has no impact as we will be using our own contexts):

Nevertheless, if we go to ODI Studio and configure GLOBAL so the logical schemas are mapped to ANY physical schema (it's not important the physical schema but the fact that it isn't <Undefined>):

Then the package template will be successfully generated:

Finally, if you are curious and you want to fully understand what happened, then you must visit ODI again.

You will notice couple of new items:

• Package UDAMSSQL (this is the name of my source adapter)
• Interface UDAMSSQL

ODI Interface generated

The interface extracts data from SAMPLE_UDA datastore and loads into TDATASEG_T.

You can also see how the parameter we defined has been added as a filter on the source datastore.

Don't be surprised if you don't see all mappings in target datastore as they will be dynamically created when we define our import format and generate the ODI scenario:

For Oracle:

Note: you can apply your ODI knowledge and adjust the interface as required. The only thing you need to take into consideration is that changes on generated interfaces/packaged need to be recreated manually in each environment as you may have potential issues when exporting/importing the ODI objects between environments having the same repository ID (FDMEE repositories have same ids across environments by default)

ODI Package generated

The package has been generated based on the existing one and it contains the interface that pulls data.

The ODI scenario that we will generate from the import format, will be generated from this package:

For Oracle:

After the ODI objects have been generated we can see that the ODI package has been updated in the source adapter accordingly:

OMG! I just noticed that I used the Oracle table rather than the View :-)

I don't want to take all screenshots again so I will copy the existing adapter and create a new one for the view:

Then I change the Table name (which actually should say Table/View):

Don't think we are done! copying the adapter doesn't mean the ODI objects are copied so we need to import the view definition and generate the package:

6. Drill-through URL

I'm not covering this topic today as I'm not going to use any drill to source.

Some considerations

• You need one source adapter for each source database table
• Don't change the project code in ODI for any UDA
• Don't change the ODI Package name in ODI for any UDA
• Documentation says that we should enclosed table name in double quotes to preserve capitalization but when I type the table name as "SAMPLE_UDA" and move to another field, double quotes are automatically wiped out. Also, if I enter any text in lowercase in any of the source adapter fields, that text is capitalized automatically. I won't take this into account for the moment :-)
• You could have issues if having same table name for different source systems of same type
• You can copy existing adapters to create new ones with similar configuration
• Click Save after every step :-)
• When changes are done in the source adapter setup, you need to import the table definition and regenerate the template package again.

Enough for today!