Universal Data Adapter for SAP HANA Data Source - Part 3

Time to finish this blog series for using the UDA with SAP HANA data source. In the previous parts I covered both introduction and configuration in ODI:

• Universal Data Adapter for SAP HANA- Part 1
• Universal Data Adapter for SAP HANA - Part 2

In this last part we will go through the configuration in FDMEE web interface, and of course, we will extract our data from HANA.

My Source Table: FDMEE_DATA_T
Just to show you how my source data looks like:

I will be extracting data from a column-based table. I haven't tested with Virtual Tables yet so my test just shows how to extract data from standard tables.

FDMEE Artifacts
A basic configuration to make the UDA working includes:

• Source System
• Source Adapter
• Source Period Mappings
• Import Format
• Location and Data Load Rule
• Dummy Mappings (just to make it working)

Source System
I have registered the source system with the new context I created for HANA:

Source Adapter
Once the source adapter is created for my HANA table (FDMEE_DATA_T), we need to import the table definition into FDMEE. This is actually performing a reverse in ODI so we get the data store with its columns:

We are now able to see the columns in the source adapter. Next step is to classify Amount and Year/Period (in case we have them) columns. In my case, we do have columns for Year and Period so we can filter out data when running the extract:

I will keep display names as the column names but remember that these are the column names you will see in your import format. Therefore, I'd change them if columns have technical names.
I haven't defined any source parameter for my test but this would be done in the same as any other UDA configuration.

Once the table definition is imported and parameters are created, we have to generate the template package. This step will create the ODI interface and package:

Now we are done with the source adapter setup.

Source Period Mappings
You need to configure calendars if you are going to filter by source period and year:

If your table or view has current period data, you will be probably fine with setting period mapping type in the data load rule to None. In my case, I just created one source period map for January-2016.

Import Format
Easy one-to-one map:

After configuring the import format, we have to generate the ODI scenario that will be executed when we run the data load rule. To generate the scenario, the source adapter needs to be configured first so the table definition is imported and the ODI interface/package are successfully generated:

Location and Data Load Rule
I have created one location with one data load rule which uses source period mappings previously defined:

Running the Data Load Rule
Voilà! Here is my HANA data extracted:

Giving a try with standard View: FDMEE_DATA_V
I have also tested the data extract from a standard HANA View. Configuration steps are the same so I'm not going to replicate them. Just to show you that it works. My view is filtering only accounts starting with 1:

In case you get errors...
It may happen that you get errors when generating the ODI Scenario. In that case, I would suggest to raise a SR with Oracle as we identified some issues when working with case sensitiveness enabled.

You may get a different error but the one below shows in the FDMEE server log that the generation failed due to fatal errors in the ODI interface:

And this completes my trip around SAP HANA world. I hope you enjoyed and helped you to avoid some headaches.

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!

Executing Custom Scripts with REST

This is a quick one...

As you may know, GJG (aka Great John Goodwin) has introduced FDMEE and REST in his blog:

• FDMEE and REST is up to you - part 1
• FDMEE and REST is up to you - part 2

So I'm going to steal a bit of his hard work :-)

In my review of PSU210 I went through executing a custom script from command line.

The formula is then easy, My post + GJG's post = Executing Custom Script with REST

We already saw that a custom script is actually treated as a report. Therefore syntax to execute the custom script with REST does not differ so much from the one John showed. The only difference is that reportFormatType is SCRIPT rather than REPORT.

BTW, I'm using RESTClient in Firefox. I'm not going to cover basics to add header with authentication and json content, etc. You can visit John's blog for that.

Also be aware this is only possible in FDMEE on-premise as Data Management in the Cloud does not support scripting yet.

Executing Custom Script without Parameters
We have three mandatory keys in the request body:

• jobType = REPORT
• jobName = this is the script name when you registered (not the py file)
• reportFormatType = SCRIPT

After calling the REST resource to run jobs, we get both status and job id. We can then easily get status to see if it's completed or error was raised:

As you can see, processType is now COMM_EXECUTE_CUSTOM_SCRIPT.

If you navigate to FDMEE, process details page will show the script being successfully executed:

Executing Custom Script with Parameters

If your script uses parameters, then you need to add them to the json body:

Status can be checked in the same way as before:

And finally, process details will show execution:

And that's all I wanted to show. With the new PSU you can now execute custom scripts from external systems in a very easy way.

Thanks again to John for introducing FDMEE and REST.

Enjoy!

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!

FDMEE 11.1.2.4.210 (PSU210), diving into the patch! - Part 2

In my last two posts I went through new PSU210:

• First glance
• Diving into the patch - Part 1

In this new post, I will continue with the new features, especially executing custom scripts from command line.

Running Custom Scripts from Command Line (executescript batch utility)

I'm going to keep it simple so I have created two custom scripts having application COMMA4DIM assigned as target application:

• Custom script with no parameters
• Custom script with one static parameter

The two scripts simply write a debug entry in the FDMEE process log:

I have registered the two scripts as follows:

When having parameters, it is important to know that Display Name value will be passed in the command line. I will show more details below.

If you are familiar with custom scripts, you may have noticed that they are actually treated and stored as reports. Indeed you can run a SQL query on TREPORT table to see them:

Script parameters are also stored in TREPORTPARAMETER:

Batch Utility executescript

This new command line utility allows us to execute custom scripts either from Windows or Unix systems. We already copied the bat/sh utility when applying the patch. Now we will see how to use it.

Bear in mind that the new admin guide has not been released yet (maybe it is already when you read this post), so I may have to update some details later.

Update 13/1/2017: admin guide is now released. However, this topic is not well documented, probably a documentation bug.

Syntax is quite similar to the one used for runreport utility:

executescript <user> <password> "<script name>" <parameters> <execution mode>

where:

• user is the FDMEE user running the script
• password can be either the hard-coded password or a file with encoded password (-f:filename)
• script name is the name registered in Script Registration page (not the python script filename)
• parameters are the script parameters in the form "Display Name=Parameter Value"
• execution mode is either SYNC (synchronous) or ASYNC (asynchronous)

If you are going to use an encoded password, you will find the configuration steps in the admin guide.

Executing a custom script with no parameters

If the execution request is successfully submitted, we will see the new process id:

Getting a success message in the command line window doesn't mean that the script has been successfully executed.

To verify it, we can navigate to Process Details page to see more details:

 

Executing a custom script with parameters

The new process id:

If the script has additional parameters, they will be passed as additional enclosed strings. For example:

executescript user password "script name" "display1=value1" "display2=value2" ...

FDMEE Process Log

I found something unexpected when I executed the script from the command line. FDMEE created two process logs, one in the global application folder and another one in the target application application folder.

For example, when executing the script with parameters:

• Log in the target application log folder

• Log in the global log folder

What I'm missing

If you give a try, you will notice that noting prevents you to create two custom scripts for two different target applications but having the same name. In the following example, I have a new custom script assigned to target application GRA and registered as "Script from Command Line (no parameters)". In the syntax we don't have an option for target application. Therefore, I guess FDMEE just queries the TREPORT table and gets the last one created. In my example, the script for GRA was executed instead of the first one created for COMMA4DIM:

I was also expecting a new button in the UI to show the syntax for executing the custom script from batch. We already have one for executing reports from batch utility in the Report Execution page.

Allow Unlock by Location

When enabling this new option, user will be allowed to unlock a specific POV (Period and Category) by location.

Users can unlock the POV when selecting it in the Data Load Workbench:

IMHO, POV lock feature really needs more enhancements. For example, I would like to have an easy way to have all related to this feature in one place: see all locked/unlocked POVs, lock/unlock a set of POVs in one click, etc.

Max Number of Parallel Batch Jobs

We can now specify the maximum number of parallel processed submitted by a batch in one execution. This can be configured when defining the batch:

Just a funny experiment I tried to set it as a character:

Message says value must be a signed decimal,,,but we just need a positive integer.

It doesn't raise any error when you save a negative number which doesn't make sense at all.

Export Delimiter for Mappings

This a new Global/Application/User setting to define the delimiter for mapping export files:

REST API for FDMEE  (Data Management)

As happened with PBCS and Planning, REST API for Data Management (Cloud) is now available in FDMEE. I'm not going to cover it as my colleague John is already blogging and reading his posts about REST API is highly recommended.

However, I already covered how to use the On-prem Planning REST API to load metadata from FDMEE in this post. In the same way, you can think in may integration/automation requirements which can be now easily met with REST API for On-prem Data Management (aka FDMEE).

I'm going to leave it here for today. I hope you now have a better idea about new PSU210 and what new features are provided. In any case, don't forget that this new PSU fixes several bugs in addition to providing new features.

I consider the SAP HANA integration one of the coolest new features so in my next posts I will cover this integration in more detail.

Enjoy!