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!

FDMEE & Java APIs, more than friends

Hi folks!

Finally the day arrived.

Some years ago, FDMEE was introduced into our lives with lot of nice and new functionality. Jython is probably one the most important ones. Why? That's an easy one. With Jython, FDMEE opened itself to the Java world. 

If you haven't read it yet, there is a must-read chapter about Jython and Java Integration at Jython's site. I'd like to highlight the following phrase:

Java Integration is the heart of Jython application development...The fact is that the most Jython developers are using it so that they can take advantage of the vast libraries available to the Java world, and in order to so there are needs to be a certain amount of Java integration in the application.

Most of the key Oracle EPM and non-EPM products have their own Java API (JAPI). During this blog series, I'm going to focus on the EPM ones. In a nutshell, the integration of FDMEE with the Java APIs for products like Essbase or HFM, gives us freedom to implement additional functionality to enhance our EPM integration solutions.

Using Java from within Jython

One of the goals of Jython is to use existing Java libraries straight forward. It's as simple as using external Jython modules (PY files) within a Jython script:

1. Import the required Java classes and use them directly in your code
2. Call the Java methods or functions you need

What about Types? well, here the good thing comes. Usually, you don't need to worry at all about them. There is some automatic type coercion (conversion of one type of object to a new object of a different type with similar content) either for parameters passed and for the value returned by the Java method.

Basically, when it gets a Java numeric type, or a Java string, Jython automatically converts it into one of its primitive types.

Let's have a look at the following example:

As you can see, the ArrayList object (which is an object from the Java Collection Framework) has been coerced into a Jython list. We can use methods from ArrayList Class (like add) and iterate the object as it would be a proper Jython list.

We will see more examples for coercion when using the Essbase and HFM JAPIs.

BTW, what is Foo?

Using Java from within FDMEE Scripts (Jython 2.5.1 and Java 1.6)
When writing your Jython scripts, don't forget that:

• FDMEE latest version (11.1.2.4) uses Jython 2.5.1
• FDMEE uses Java 1.6 (as EPM system)

In other words, you are restricted to use classes available in Java 1.6. Also, if you use 3rd party Java libraries, they must be compatible with 1.6.

Regarding the different approaches to implement the Jython script:

• Build the custom functionality in a Java library that you can later import into your scripts
• Cast Java code as Jython within your script

Option 1 requires a deeper knowledge on Java programming. I'd recommend this option only if you know Java programming and your customization is a good candidate for being reused in other implementations. On the other hand, option 2 is quicker and probably a better option for one-time customization.

Essbase Java API

FDMEE comes with functionality that is commonly use:

• Extract data 
• Run calculation scripts before/after loading data
• Pass parameters to the scripts
• Create drill regions
• Among others...

But, what about?

• Run calculation scripts before extracting data
• Validate target data before is loaded
• Load new metadata before loading data
• Execute MaxL scripts
• Using substitution variables in FDMEE artifacts like period mappings 
• Among others...

I wish the product would provide this functionality but unfortunately it doesn't. However, it provides a powerful scripting engine which enables us to extend its functionality.

Going back to the list above, you have probably met some these requirements in one of your projects. What did you do? Create a MaxL script and run it from script using subprocess module? Or, did you leverage the Essbase JAPI?

That probably depends on many other factors...do we have time for implementation? do we know how to do it? do they have existing batches doing the work?...

To me, using the Essbase JAPI is not only about having seamless integration but capturing errors in an elegant way. Something that you can hardly get by running batches from external scripts.

Spoiler!!! see how simple would be to execute a MaxL script or statement:

I will cover more details about using the Essbase JAPI and some examples in upcoming posts.

HFM Java API

What about HFM?

• How can we extract Cell Texts?
• Extract and Load Metadata?
• Translate data before extracting it?
• Run custom consolidation/calculation/translation logic?
• Among others

HFM also has a JAPI! Actually, in the same way that happens with Essbase integration, FDMEE uses these APIs behind the scenes.

Spoiler again!!! extracting cell texts:

Other Java APIs

Besides the HFM and Essbase JAPIs, there are other products and components having their own API. Some of them such as LCM's one are documented, some others are not. In example, OLU's API (Outline Load Utility).

In the next posts, I will show some examples for customization implemented with the Essbase and HFM APIs. If you can't wait, my colleague John already published a very cool one.

I haven't forgotten about Planning. It does not have any published Java API but you should have a look at REST API.

Take care!