Universal Data Adapter for SAP HANA Data Source - Part 1

This blog series will cover the Universal Data Adapter (UDA) configuration to use SAP HANA as data source for FDMEE (On-premise). This is now possible with new PSU210 which I already introduced in this entry.

If you are not familiar with the UDA, I would suggest you visit the three entries I posted about it. Then it will be easier to understand the process.

SAP HANA (High-Performance Analytic Appliance)
First of all, I'm not SAP HANA expert, just in case you want to know. HANA is simply one of many systems I have to work with as an Integration Architect. It's always a challenge when you have to understand a new system in order to design and implement a solution to pull data from it.

It's true that I have done many integrations with SAP systems, either direct using the FDMEE-SAP adapters or file-based ones. However, SAP HANA was new to me (still it is but after pulling data from it I can address HANA as "tú")

I will keep it simple when I speak about HANA. It's the SAP in-memory database. But this is just the database layer. Then, similar to Oracle, SAP has several products and platforms which reside on HANA (S/4HANA, BW/4HANA, etc.)

Let's put an example you can better understand (SAP guys, feel free to correct me if I'm saying something wrong):

You may be familiar with SAP ECC or R/3 (the ones used as a source in FDMEE-SAP Adapters). These ERPs use a relational database so if we think about General Ledger (GL), both journal entries and total balances are physically stored in the database.
SAP ECC has evolved to SAP S/4HANA (formerly Simple Finance), the new in-memory ERP. Now journal entries are stored in disk (although it can be loaded into memory) but there is no need to store total balances in the same way. Why? with in-memory database, they can be calculated in real time. And yes, we can extract that data with FDMEE now.

What can we do in FDMEE when having HANA as a source? A picture is worth a thousand words:

Some people were asking me about HANA integration with Data Management in the cloud. The UDA is not available in the cloud so if you want to load data directly from SAP HANA into any Oracle EPM Cloud Service (the supported ones in FDMEE so far... PBCS and FCCS), you will need to configure hybrid integration in FDMEE on-premise (11.1.2.4.200+)

I would also like to highlight that this post is not about integration with S/4HANA or BW/4HANA, it's about integration with SAP HANA. What do I mean? I'm not saying you can't use UDA to pull data from new ERP/BW SAP systems. Indeed you can because data is stored in HANA. I'm just saying that you won't read anything specific to ERP/BW integration in this series (not yet).

BTW, I found this podcast comparing Exadata Vs. HANA very interesting.

HANA on-premise or cloud?

Same as Oracle, SAP is pushing for the Cloud. Therefore, we also have SAP HANA Cloud Platform, which means you can have S/4HANA and BW/4HANA either as on-premise or cloud deployment. 

Although it seems that we can use the JDBC driver for the cloud as well, these first posts will cover on-premise HANA data source only.

HANA 1.0 or 2.0?

Basically, there are two releases of HANA: 1.0 and 2.0. The second one was released just one month ago which is not good for us because there were some important changes in the new JDBC driver.

I have configured the UDA against the two versions. However, the JDBC driver coming with 2.0 does not work with FDMEE unless somebody shows it does. Therefore, we can't really use it. Details below :-)

How does it work? The JDBC Driver for SAP HANA
The Universal Data Adapter provides direct integration with source Tables/Views in HANA. It uses Oracle Data Integration (ODI) which connects to the source through the JDBC driver for HANA. Therefore, data is extracted through the database layer.
I haven't gone through any other method to extract data from HANA so using the database layer makes sense to me. It's secure and provides the best performance to extract big volumes of data.

I must also say that I have tested with a column-based table so far. I have to dive into HANA a bit more before I perform further tests.

HANA JDBC Driver (ngdbc.jar)
I must say that I started to test with the new JDBC driver 2.0 connecting to HANA 2.0. That was the reason I found the new driver couldn't be used. The following table gives you some clues:

Having a look to the table above, why do you think it was not working properly with FDMEE?
As you may know, FDMEE service runs on Java 1.6 (same as all EPM). That's the main reason.

Let me show you some screenshots with the issues in case you face them.

HANA JDBC connection issue (Driver 2.0)
The following command should open the driver configuration window but when opening the jar file with JRE bundled in EPM 11.1.2.4:

As you can see above, connection failed when testing it with the local agent (I will cover how to configure the JDBC driver in the next post)

As a unit test, I executed the jar with same JRE but I added the Java 1.7 library to the class path: 

Then you can see the configuration window as expected.

Error during Import Table Definition (Driver 2.0)
As you will see in next posts, importing the table/view definition is one of the configuration steps. When performing this action, FDMEE executes an ODI scenario to reverse the table/view into an ODI data store. This scenario fails:

Error above was giving no clue to me so I decided to reverse the model manually from ODI. The good thing was that my test throws a different error which put me on the right track:

Class java.sql.SQLType was introduced in Java 1.8 :-(

HANA JDBC Driver 1.0, working now!
Luckily, I got the previous driver so I tried to reproduce all issues I had before. As you can imagine, both connection and reverse were working fine and I did not get any other error.

Connection to both SAP HANA 1.0 and 2.0 worked:

 Reverse from both SAP HANA 1.0 and 2.0 worked:

First Step, Simple Java Program for Unit Testing
This is something I usually do. I like testing the JDBC drivers from Eclipse to better understand which options I can use in the JDBC url, get some dummy data, etc. or simply check that connection and driver are working as expected.
Configuring Eclipse for this is out of the scope but I can show you how this is quite useful to anticipate issues before you get into FDMEE.
This one shows issues with loading the driver when JDBC driver 2.0 was used with Java 1.6:

Once I switched to driver 1.0, I could get my first row of data from SAP HANA using the JDBC connection:

Important Note
Before I continue blogging about this, I would like you to understand that any information I provide here is related to my testing. Therefore, I cannot guarantee that JDBC driver 1.0 is 100% working with SAP HANA 2.0. That's not my job neither. I guess it works but I suggest you give a try with your particular context and let us know if you face any issue.
Oracle may state something in the documentation regarding JDBC driver so we will see.

I'm going to leave it here for today. I think I covered all I wanted as an introduction. In my next posts I will go through end-to-end configuration:

• Configure the JDBC Driver for FDMEE
• Configure the ODI Topology for SAP HANA and Universal Data Adapter
• Configure the Universal Data Adapter for HANA source in FDMEE
• Configure FDMEE artifacts (period mappings, location, data load rule, etc.)
• Pull data from SAP HANA Column based table

Enjoy!