Universal Data Adapter for SAP HANA Data Source - Part 2

In my last post about SAP HANA integration I went through an introduction of what we can do with the new data source in the UDA. Basically, we saw that we can now extract data from HANA tables and views (pure views so do not confuse with Core Data Services for HANA) through the database layer using JDBC.

In order to use the UDA we need to perform the following tasks:

1. Import ODI Models Folder and Project into the FDMEE repository
2. Configure the JDBC driver
3. Configure ODI Topology
4. Configure Source System and Source Adapter in FDMEE
5. Configure FDMEE artifacts (period mappings, import format, location, etc.)

1st task was already covered in the review of PSU210. However, there is a bug related to this which you can see at the end of this post.

In this second part I will cover the following topics:

• Configuration of the JDBC driver for SAP HANA (2nd task)
• Configuration of ODI Topology for SAP HANA (3rd task)

JDBC driver for SAP HANA (ngdbc.jar)

As an important remark, I already showed that we can't use the latest version of the JDBC driver (2.0). Once you have downloaded the driver (you need access to SAP market or get it from your SAP guys), you need to place it in a location so FDMEE (agent) can use it.

As usually, I perform several tests with the local agent in ODI so I need to place it in the driver's location for ODI Studio as well:

I just need to add a reference to the driver in additional_path.txt file located in WINDOWS_USER_HOME\AppData\Roaming\odi\oracledi\userlib (user "Administrator" in my case).

In the same way, we need to place it in the FDMEE agent's drivers location:

Please note that driver must be copied into every FDMEE server in case you have high availability architecture. For the local agent, just in the machines where ODI Studio is installed.

Both ODI Studio and FDMEE service(s) need to be restarted before using the driver.

JDBC Url (connection string)

The JDBC connection string is used by the agent to connect to the source HANA db. The generic url is jdbc:sap//<server>:<port>[/?<options>] and it is configured in the Data Server created in ODI Topology for the HANA DB server.

In my case, I tried to keep it simple and I used the following url to connect to the SystemDB database:

jdbc:sap//<server>:<port>?databaseName=SystemDB

I used the following ports but value depends on your SAP environment:

• 30013 for SAP HANA 1.0 (HANA instance number = 00)
• 39013 for SAP HANA 2.0 (HANA instance number = 90)

You can also configure tracing in the JDBC driver but this is something I won't cover as it is not essential. These two sites are good to see how the JDBC Url is configured and how to enable tracing:

• http://gerardnico.com/wiki/hana/jdbc for a good overview
• SAP HANA Studio guide contains all details about the driver

Configuring the ODI Topology
Once SAP HANA technology has been imported into ODI, we have to configure our physical and logical architecture:

1. Create new Context(s)
2. Create new Data Server(s) 
3. Create new Physical Schema(s)
4. Link Physical Schema(s) with the Logical Schema UDA_HANA through context(s) created in step 1

I guess you notice about plurals for each artifact. This is just to highlight that may have to create multiple objects in case you have multiple HANA DB servers. In my example it's simpler, just one source.

Context
I always recommend to create new contexts rather than using GLOBAL one (it gives you more flexibility). My new context is UDA_HANA:

Data Server
Then I create a new data server under SAP_HANA technology:

As shown above, ODI will connect to HANA with specific user FDMEEHANA created for this purpose. This user owns the schema were my source table is located. You will have to ask your HANA admin for which user will connect to HANA via JDBC.
Don't get confuse about seeing two data servers, it's just because I tested the two versions of HANA.

Time for the JDBC Url:

I'm using the SystemDB database but again, just to keep it simple.

Physical Schema
Once the data server is created, I have to create a physical schema where I can set the HANA schema storing my source table/view:

Configure Context (link Physical-Logical schema)
You may have noticed that I skipped creation of Logical schema. This is because schema UDA_HANA is created when importing the technology into ODI. Therefore, the last step is just to link our physical schema with the logical schema through the context created:

As an additional step, we also have to link Global context as FDMEE uses it when generating the ODI project template for the source adapter. That's a common root cause when generation fails.

Testing the ODI connection to SAP HANA Database Server
Now that our topology is configured, we can test the connection to the SAP HANA DB server so we can confirm that everything is good so far:

Common errors you may get:

• Invalid user/password: check with your HANA admin
• ODI cannot reach the host: check your FDMEE server can ping the HANA DB server
• ODI cannot load driver: check driver is in the right location as shown above and that the FDMEE service was restarted

Bug: SAP_HANA technology is missing the Data Types
I haven't covered this one before as I wanted you to have all steps clear. Now it's time to talk about it.
The technology delivered in PSU210 is not complete. Data Types are missing. Human error. It can happen to everyone :-)

Without data types, nothing will work.

At this point you have two options:

1. Raise a SR with Oracle although this will be hopefully fixed in a PSE soon. Please confirm that the logical schema is present after importing the new technology delivered in the PSE. In case it doesn't exist, you have to create it manually (name it as UDA_HANA)
2. Reverse data types yourself. This option requires also creating maps between HANA and Generic SQL technology for each data type

I would suggest option 1 so you are safe. In case you prefer option 2, I let you proceed on your own risk:

Once data types are reversed, you need to map 1 by 1 in the Converted To tab:

This map needs to be replicated in the Logical Schema as well:

FYI, I verified that data types reversed from HANA 1.0 match the ones reversed from HANA 2.0.

That's all for today. With these steps, UDA for SAP HANA is now configured in ODI.
What are the next steps? configure it in FDMEE so we can get our data extracted.

Enjoy!