Universal Data Adapter - Making it Simple for Multiple Databases

Hi folks!

Long time since I don't post but several events happened lately. Anyway, sorry about that. 

Before I dive into today's topic I'd like to summarize my life in the last weeks:

• Kscope17 was a great event as always. It's a very good opportunity for us living on the other side of the pond. Meeting lot of people, partners, customers is always great. San Antonio was impressive. I spent one day visiting the city with my colleague Henri (The Finnish Hyperion Guy). We had sun and rain. And what do you when it's raining? Shopping! In addition, I got the "EPM Data Integration Top Speaker" award! That was awesome. I didn't expect it so I can only say thanks to all the community. 

• Heading to large family. This is an easy one: if all goes fine, next year we will be 2 more in the family :-)
• New apartment. I've been very busy assembling IKEA furniture. For those would would like visiting Malaga (Spain), we bought a new apartment for rental. Feel free to visit it!

OK, now that you know what I have been doing...time for some FDMEE content!

Universal Data Adapter (UDA)

If you are not familiar with the UDA yet, it may be a good a idea that you visit my previous entries about it:

• Configuration of UDA (Part 1, Part 2, Part 3)
• SAP HANA On-premises Integration (Part 1, Part 2, Part 3)
• SAP HANA Cloud Integration

The Requirement - Multiple Source Databases with same Source View Layout

One of the drawbacks of the UDA is the configuration and maintenance. That's something we cannot change. It has been designed like that. 

Why configuration? 

UDA requires configuration in FDMEE and Oracle Data Integrator (ODI).

In FDMEE, UDA requires typical artifacts plus some specific ones

• Source System with an ODI context assigned
• Source Adapter
• Source Period Mappings
• Import Formats
• Location and Data Load Rule

In ODI, UDA requires new artifacts to be created in addition to the ones imported during initial configuration (Projects and Model Folders)

• Manually in ODI
• Data Server
• Physical Schema
• Context
• Generated in ODI from FDMEE
• Datastore for Table/View definition
• Package and Interface
• Scenario for the Import Format

Why maintenance? There are many events that require re-generating the ODI objects created from ODI. I'm not going to list all of them but will explain the main ones.

• Migrating across environments. LCM doesn't migrate the ODI objects. Besides, you can't perform a native ODI export/import. FDMEE has been designed to have the same ODI repositories' ID across environments so the export/import objects will fail.
• Apply patches. Some patches may require to re-import some default objects for ODI. This will probably delete the objects you generated from FDMEE.
• Changes in Tables/Views. Think about adding a new column to a Table/View. You have to re-import table definition again, regenerate the package and interface, adjust the Import Format and regenerate the ODI scenario

What about multiple databases? All configuration mentioned above multiplies as well. Why? All is chained. Each Source System has assigned an ODI Context. If you have multiple sources of the same database type, you can't use the Global context as you can only assign once to the Logical Schema UDA_MSSQL. Then, as you need multiple Source Systems and Source Adapters, you will need multiple Import Formats as they are assigned to adapters, and so on...

I know what you are thinking...lot of manual work to do!

Today, I will show you the solution we implemented for an integration with Microsoft Navision. (SQL Server). Customer had 30 different source databases in the same server (including different collations)

The Solution - Moving complexity to the DB makes UDA simpler!

As part of the Analysis & Design workshop, we explained the drawbacks of having 30 source databases. They understood that we had to find another solution immediately.

For 2 databases, solution architecture would be as:

Then, I told them that the design could be significantly simplified. However, that required some additional work on the SQL side. They wanted to keep things simple in FDMEE so they were happy to go for it. Actually, they did :-)

Basically, my advice was to move the complexity to SQL. That would made the UDA configuration and maintenance simpler.

In a nutshell:

1. Create a DB Link (Oracle)/Linked Server (SQL Server) from FDMEE database server to the source database server
2. Create a view in FDMEE database. This view has an additional column for the source database. It queries the source views using remote queries (Ex: OPENQUERY in SQL Server) which perform quite well as they leverage the source DB engine
3. Configure UDA in ODI
4. Configure UDA FDMEE
5. In addition to the required parameters, define an additional one for the column having the source database

Note that if the source databases are in different servers, the solution would be slightly different but still doable.

Also, a similar approach could be taken if you have multiple views with different layouts. You could merge them into one with a common layout.

I know you may have concerns about performance but if the views are correctly designed and network delay is not a bottleneck, everything should be fine. Indeed, ODI usually executes the SQL extract queries in the source database. Don't forget to play a bit with Data Server tuning settings to get the best performance.

As usually, I'm not telling you this is your solution but definitely a good practice. I hope you found it interesting as it may help you to simplify your design.

Have a good summer!