Monday, February 15, 2016

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:
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?

17 comments:


  1. I’m so grateful for this post and thanks a lot for sharing it with us
    Adobe Photoshop CC 2016 Keygen

    ReplyDelete
  2. Excellent post on this new feature. Do you have an example for a SQL server drill path URL for the FDMEE Source System?

    ReplyDelete
    Replies
    1. Hi, thanks for feedback.
      There is drill path for SQL Server or any other db system. The url is for the ERP system. FOr example if you have a view for E-Business Suite AR sub-ledger you can setup the drill url for E-Biz

      Delete
  3. Hi Francisco, if I want to retrieve data from a table in SQL into FDMEE, how do I make sure the table is updated with the latest values? In this particular case, there is a table in SQL that retrieves data from Great Plains. This table is refreshed using a SQL procedure. Can FDMEE UDA call a SQL procedure instead of a table? I do not want the user to interact with SQL at all, as I just want them to click Import in FDMEE and see the latest values. Thanks a lot

    ReplyDelete
    Replies
    1. Hi,
      If you want to refresh the table you may want to execute the SP in the befimport so the table is populated with latest data

      Delete
  4. Hi Francisco, This was an excellent post. I was just wondering with UDA being introduced now, whether we can now do metadata loads too using UDA. As per documentation Oracle has mentioned it can do metadata load only from Oracle EBS, Oracle Financials and Peoplesoft. So does UDA give the opportunity to do the metadata loads too.
    Thanks a lot

    ReplyDelete
  5. Hi, I would lie to know how to retrieve data from EBS Fixed Assets and Inventory using FDMEE for ARM. Can you please share some guidance. Thanks

    ReplyDelete
    Replies
    1. Hi,
      you first need to create views with source EBS FA and INV data.
      Then configure UDA for the views.
      FInally you need to create the interface between your UDA (Source) and ARM (Target)
      If you are asking about View definition, I would suggest this is built by someone knowing the EBS model.

      Cheers

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. great post can you also provide information on how to use Open interface to get data from SQL

    ReplyDelete
    Replies
    1. Admin guide shows how to use it. Why not Universal Data Adapter instead?

      Delete
  8. great post can you also provide information on how to use Open interface to get data from SQL

    ReplyDelete
  9. Hi Francisco,

    Thank You for your help with this post :)

    I have a question, you always need to use the FDMEE DB? I have a view in my oracle database with 1 milion lines per months, when I try to load this data into Planning FDMEE will replicate this view. So I have 1 milion lines/month in my oracle database plus 1 milion lines/month in FDMEE db.

    There is a different way to do this?

    Thank You
    Cátia

    ReplyDelete
    Replies
    1. Hi,
      the universal data adapter can access to the oracle database ti get data from your view so the view does not have to be in the FDMEE db.

      Delete
  10. Hi Francisco,

    Thanks for the write up on the new feature, very insightful. My goal is to load data to an Essbase app from a Sybase DB. At first I thought UDA would be the solution, but it sounds like UDA will only work with the 5 types you mentioned above correct? Seems like I might have to use OIA instead - thoughts?

    Thanks!

    ReplyDelete
    Replies
    1. Hi,
      thanks for feedback.
      Yes, your option would be to use Open Interface Adapter. There are some Sybase technologies in ODI that you can use to pull data or you can connect to Sybase through jdbc (like this one https://sourceforge.net/projects/jtds/)

      Delete

Thanks for feedback!