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

What does "Universal" mean?
If you visit dictionary reference site or just take your dictionary from school, you will see the word "ALL" in most of its entries. For this reason you may find the term "Universal" a bit confusing in our context: FDMEE. Indeed I would have preferred "Universal Database Adapter" as a name... but of course, this is just a personal opinion.

Nevertheless, let's dive into this new feature added in 11.1.2.4.100.

What is UDA for? UDA Vs. OIA

• I say: "UDA can be used to load data from 'any' database into FDMEE"
• You say: but we had Open Interface Adapter (OIA) for this purpose
• I say: that's true but look at picture below

Before moving forward, I would like to highlight that we can use the OIA for other sources rather than Database. For example, one of my customers recently built a custom user interface based on Workspace jobs which import multi-period Excel files into Open Interface Table. Or we could also use it to consume external web services which generated data load files from ERP systems...

I would also like to briefly remind you what TDATASEG_T is: it is the temporary table for FDMEE data. Basically source data is imported into this staging table, then logic accounts and mappings are applied. Once data is ready, FDMEE moves it from TDATASEG_T to TDATASEG (which stores the data you see in the fishes' grid)

Being said that, I will focus only on the main difference when using a DB table or view as a source:

• Open Interface Adapter: we use FDMEE table AIF_OPEN_INTERFACE (aka the Open Interface Table) as a staging between our source DB and the FDMEE staging table (TDATASEG_T)
• Universal Data Adapter: we bypass the Open Interface table so we have direct integration between our source DB and TDATASEG_T (so FDMEE)

Summarizing, you can think of UDA as a way of extracting data from any data source which we (actually the FDMEE agent) can access to its underlying database. For example, we do have a pre-packaged integration with E-Business Suite GL balances, but we DO NOT have with Sub-ledgers such as AP and AR.

It comes to mind something I must insist as I had so many problems with Admins in the past: this integration is done through the DATABASE layer so we do not have any source application security applied (like SAP or JDE adapters), only DATABASE security (like E-Business Suite or Peoplesoft integration)

UDA is a Source Adapter
So now that we know what UDA is for, we can add more "technical" details about it.
You all should know that FDMEE uses Oracle Data Integrator (aka ODI) as the underlying ELT technology. UDA is not an exception. Actually the UDA is a source adapter in FDMEE composed of:

• ODI Project (ODI scenario generated from Import Format)
• Source columns (available in the import format)
• Source filters (used to filter source date. They are customizable)
• Drill-through URL (we can navigate to source system generating the data)

It's true that when using the OIA we did not have to play too much with ODI unless we wanted to include an interface(s) to populate the Open Interface table in order to complete the end-to-end data load process (I click import and data gets automatically from my source into AIF_OPEN_INTERFACE and then into TDATASEG_T)

In the case of UDA, we will have to perform small configuration in ODI topology in order to configure the physical connection to our DB server/database. Don't be scared, it will take only 2 minutes of your time.

BTW, Source adapters can be configured from menu Setup > Register > Source Adapter.

Supported database technologies
If you read carefully you saw that I said "you can load data from 'any' source". The reason I quoted any is that not all database technologies are supported at the moment.

Currently FDMEE 11.1.2.4.100 supports:

• Oracle
• SQL Server
• MySQL
• Teradata
• DB2 and DB2 400

In this blog series I will cover Oracle 12C and SQL Server 2012 as data sources for the UDA.

My data sources
As always, I like to keep things simple so I will have two databases configured as follows:

The view and the table will have same structure.

SQL Server 2012 (Table) 

Oracle 12c (View)

Bootstrapping ODI Topology
People usually ask me whether they use ODI Console or ODI Studio. I prefer Studio as I'm used to work with it. ODI Console is web-based and is automatically deployed when FDMEE is installed. On the other hand, ODI Studio has to be installed manually.
Do you need full ODI license? No, you don't. You can use ODI RUL (Restricted Use of License) for FDMEE purposes, including customization on source adapters.

Going back to ODI configuration, at a high level, we need to perform the following steps in Topology:

1. Create Data Server for our source technology
2. Create a Physical Schema for the new data server
3. Create a new Logical Schema for our source technology
4. User Global context or create a new one to map the physical schema to the logical schema

Some basics about ODI...

The idea is to represent our physical architecture in ODI. I always like to say that we need to represent our physical world in ODI's world. For now you only need to know how we map ODI and Database technology objects:

Couple of concepts we will use:

• Logical Schema: ODI object which groups similar physical schemas
• Context: resolves logical schemas into physical schemas at run-time

Logical Schemas

This a key component of the UDA and it MUST have the following names:

Contexts

By default, FDMEE uses only one context called GLOBAL. However, we will define two new contexts in our example so you can better understand how ODI works. This can be done by copying the existing GLOBAL one (right click > duplicate selection + change name and code)

1. Create Data Servers

MSSQL

1) Expand Physical Architecture > Right click on Microsoft SQL Server Technology > New Data Server
    You can type here any name although is good to follow some naming convention.
2) Enter credentials and adjust array fetch size (see FDMEE tuning guide)

3) Configure JDBC driver and URL based on your technology and DB version

4) Test the connection with the FDMEE agent (it must work with it!!!)

Oracle

1) Expand Physical Architecture > Right click on Oracle Technology > New Data Server
2) Enter credentials and adjust array fetch size

3) Configure JDBC driver and URL based on your technology and DB version

I'm using the default JDBC driver to connect to my 12c (even if you don't see that version in the description box...)
In case you need to use another JDBC driver I suggest you visit:

• JDBC FAQ
• JDBC 12c Downloads

You would like to contact your infra guy to make the new JDBC drivers accessible by the FDMEE agent :-)

4) Test the connection with the FDMEE agent

2. Create Physical Schemas

MSSQL

1) Right click on Data Server FDMEE_UDA_MSSQL > New Physical Schema
2) Select Database from drop down list (both Catalog and Work Catalog the same if you keep thing simple...)
    Set work table prefix for Loading to C$<?=SESS_NO?>. In this way ODI can process parallel data extracts.

3) Map context UDA_MSSQL to Logical Schema UDA_MSSQL
    Tip: we can enter the name of the logical schema and it will be automatically created by ODI :-)

Oracle

1) Right click on Data Server FDMEE_UDA_ORACLE > New Physical Schema
2) Select Database from drop down list

3) Map context UDA_ORACLE to Logical Schema UDA_ORCL

Importing ODI Model Folder and Projects for UDA
As described above, the UDA is an instance of Source Adapter. in FDMEE Therefore we need to import its ODI components into the ODI repository:

• ODI Models Folder
• ODI Projects

Objects are imported using ODI Studio (Designer tab) and have to be imported in INSERT_UPDATE mode. They can be found in path (or similar):

E:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi\11.1.2.4.00\workrep

Model Folder

Projects

Note: we will see in next posts how other ODI objects like template package and scenarios are generated from FDMEE when configuring the UDA.

We are now ready to start configuring UDA in FDMEE.

Which is your source? are you ready? any errors testing connection?