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!

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?

The Definitive Guide to Oracle FDMEE (By Tony Scalese)

Hey folks!

I would like to use today's entry to promote the very first FDMEE book. I didn't have the opportunity to read it yet (I'm still waiting for my copy) but I'm sure it's going to be a top reference in the same way his author is.

I must say that when I started to learn FDM (some time ago...) I used Tony's presentations as a reference, the perfect starting point to start exploring what that product could provide in order to design multiple solutions which met all kind of requirements I had. I could feel that he was a pure Hyperion ISA (Integration Solution Architect), and that was what I wanted to be. Last year I was co-presenting with him at KScope15 (Miami) and this year he will be co-presenting with me at Chicago :-)

Regarding books, some time ago I was asked to write one but I didn't have a clear idea about how to start and how to approach that challenge (maybe I can have better idea now hehehe)

I can image that writing a book is not an easy task as you never know where to stop when communicating what you want people to read. For this reason, I would like to personally thank Tony for being the first :-)

You can see all details at p8tech site.

Enjoy!

FDMEE 11.1.2.4.100 released (PSU100) - Part 2

Happy New Year!

No, this is not a blog post yet but just to let you know what I'm working on...

• Universal Adapter
• Purging Scripts
• DRM Integration (update 18/2/2016: this will not be available until 11.1.2.4.200)
• Unpublished new functionality
• Scheduling Custom Scripts
• New artifacts supported in LCM like scripts :-)
• New Java API functions
• ...

I'm also open to some suggestions so feel free to reply to this post.

Show must go on!