ODXL - An open source Data Export Layer for SAP/HANA based on OData

[roland_bouman]

I'm very pleased to be able to announce the immediate availability of the Open Data Export Layer (ODXL) for SAP/HANA!

Executive summary

ODXL is a framework that provides generic data export capabilities for the SAP/HANA platform. ODXL is implemented as a xsjs Web service that understands OData web requests, and delivers a response by means of a pluggable data output handler. Developers can use ODXL as a back-end component, or even as a global instance-wide service to provide clean, performant and extensible data export capabilities for their SAP/HANA applications.

Currently, ODXL provides output handlers for comma-separated values (csv) as well as Microsoft Excel output. However, ODXL is designed so that developers can write their own response handlers and extend ODXL to export data to other output formats according to their requirements.

ODXL is provided to the SAP/HANA developer community as open source software under the terms of the Apache 2.0 License. This means you are free to use, modify and distribute ODXL. For the exact terms and conditions, please refer to the license text.

The source code is available on github. Developers are encouraged to check out the source code and to contribute to the project. You can contribute in many ways: we value any feedback, suggestions for new features, filing bug reports, or code enhancements.

What exactly is ODXL?

ODXL was borne from the observation that the SAP/HANA web applications that we develop for our customers often require some form of data export, typically to Microsoft Excel. Rather than creating this type of functionality again for each project, we decided to invest some time and effort to design and develop this solution in such a way that it can easily be deployed as a reusable component. And preferably, in a way that feels natural to SAP/HANA xs platform application developers.

What we came up with, is a xsjs web service that understands requests that look and feel like standard OData GET requests, but which returns the data in some custom output format. ODXL was designed to make it easily extensible so that developers can build their own modules that create and deliver the data in whatever output format suits their requirements.

This is illustrated in the high-level overview below:



For many people, there is an immediate requirement to get Microsoft Excel output. So, we went ahead and implemented output handlers for .xlsx and .csv formats, and we included those in the project. This means that ODXL supports data export to the .xlsx and .csv formats right out of the box.

However, support for any particular output format is entirely optional and can be controlled by configuration and/or extension:

• Developers can develop their own output handlers to supply data export to whatever output format they like.
• SAP/HANA Admins and/or application developers can choose to install only those output handlers they require, and configure how Content-Type headers and OData $format values map to output handlers.

So ODXL is OData? Doesn't SAP/HANA suppport OData already?

The SAP/HANA platform provides data access via the OData standard. This facility is very convenient for object-level read- and write access to database data for typical modern web applications. In this scenario, the web application would typically use asynchronous XML Http requests, and data would be exchanged in either Atom (a XML dialect) or JSON format.

ODXL's primary goal is to provide web applications with a way to export datasets in the form of documents. Data export tasks typically deal with data sets that are quite a bit larger than the ones accessed from within a web application. In addition, an data export document might very well compromise multiple parts - in other words, it may contain multiple datasets. The typical example is exporting multiple lists of different items from a web application to a workbook containaing multiple spreadsheets with data. In fact, the concrete use case from whence ODXL originated was the requirement to export multiple datasets to Microsoft Excel .xlsx workbooks.

So, ODXL is not OData. Rather, ODXL is complementary to SAP/HANA OData services. That said, the design of ODXL does borrow elements from standard OData.

OData Features, Extensions and omissions

ODXL GET requests follow the syntax and features of OData standard GET requests. Here's a simple example to illustrate the ODXL GETrequest:

GET "RBOUMAN"/"PRODUCTS"?$select=PRODUCTCODE, PRODUCTNAME& $filter=PRODUCTVENDOR eq 'Classic Metal Creations' and QUANTITYINSTOCK gt 1&$orderby=BUYPRICE desc&$skip=0&$top=5

This request is build up like so:

• "RBOUMAN"/"PRODUCTS": get data from the "PRODUCTS" table in the database schema called "RBOUMAN".
• $select=PRODUCTCODE, PRODUCTNAME: Only get values for the columns PRODUCTCODE and PRODUCTNAME.
• $filter=PRODUCTVENDOR eq 'Classic Metal Creations' and QUANTITYINSTOCK ge 1: Only get in-stock products from the vendor 'Classic Metal Creations'.
• $orderby=BUYPRICE desc: Order the data from highest price to lowest.
• $skip=0&$top=5: Only get the first five results.

For more detailed information about invoking the odxl service, check out the section about the sample application. The sample application offers a very easy way to use ODXL for any table, view, or calculation view you can access and allows you to familiarize yourself in detail with the URL format.

In addition, ODXL supports the OData $batch POST request to support export of multiple datasets into a single response document.

The reasons to follow OData in these respects are quite simple:

• OData is simple and powerful. It is easy to use, and it gets the job done. There is no need to reinvent the wheel here.
• ODXL's target audience, that is to say, SAP/HANA application developers, are already familiar with OData. They can integrate and use ODXL into their applications with minimal effort, and maybe even reuse the code they use to build their OData queries to target ODXL.

ODXL does not follow the OData standard with respect to the format of the response. This is a feature: OData only specifies Atom (an XML dialect) and JSON output, whereas ODXL can supply any output format. ODXL can support any output format because it allows developers to plug-in their own modules called output handlers that create and deliver the output.

  Currently ODXL provides two output handlers: one for comma-separated values (.csv), and one for Microsoft Excel (.xlsx). If that is all you need, you're set. And if you need some special output format, you can use the code of these output handlers to see how it is done and then write your own output handler.

  ODXL does respect the OData standard with regard to how the client can specify what type of response they would like to receive. Clients can specify the MIME-type of the desired output format in a standard HTTP Accept:request header:

• Accept: text/csv specifies that the response should be returned in comma separated values format.
• Accept: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet specifies that the response should be returned in open office xml workbook format (Excel .xlsx format).

Alternatively, they can specify a $format=<format> query option, where <format>identifies the output format:

• $format=csv for csv format
• $format=xlsx for .xlsx format

Note that a format specified by the $format query option will override any format specified in an Accept:-header, as per OData specification.

ODXL admins can configure which MIME-types will be supported by a particular ODXL service instance, and how these map to pluggable output handlers. In addition, they can configure how values for passed for the $format query option map to MIME-types. ODXL comes with a standard configuration with mappings for the predefined output handlers for .csv and .xlsx output.

On the request side of things, most of OData's features are implemented by ODXL:

• The $select query option to specify which fields are to be returned
• The $filter query option allows complex conditions restricting the returned data. OData standard functions are implemented too.
• The $skip and $top query options to export only a portion of the data
• The $orderby query option to specify how the data should be sorted

ODXL currently does not offer support for the following OData features:

• $expand
• $metadata

The features that are currently not supported may be implemented in the future. For now, we feel the effort the implement them and adequately map their semantics to ODXL may not be worth the trouble. However, an implementation can surely be provided should there be sufficient interest from the community.

Installation

Use ODXL presumes you already have a SAP/HANA installation with a properly working xs engine. You also need HANA Studio, or Eclipse with the SAP HANA Tools plugin installed.

  Here are the steps if you just want to use ODXL, and have no need to actively develop the project:

1. In HANA Studio/Eclipse, create a new HANA xs project. Alternatively, find an existing HANA xs project.
2. Find the ODXL repository on github, and download the project as a zipped folder. (Select a particular branch if you desire so; typically you'll want to get the master branch)
3. Extract the project from the zip. This will yield a folder. Copy its contents, and place them into your xs project directory (or one of its sub directories)
4. Activate the new content.

After taking these steps, you should now have a working ODXL service, as well as a sample application. The service itself is in the service subdirectory, and you'll find the sample application inside the app subdirectory.

  The service and the application are both self-contained xs applications, and should be completely independent in terms of resources. The service does not require the application to be present, but obviously, the application does rely on being able to call upon the service.

  If you only need the service, for example, because you want to call it directly from your own sample application, then you don't need the sample application. You can safely copy only the contents of the service directory and put those right inside your project directory (or one of its subdirectories) in that case. But even then, you might still want to hang on to the sample application, because you can use that to generate the web service calls that you might want to do from within your application.

If you want to actively develop ODXL, and possibly, contribute your work back to the community, then you should clone or fork the github repository and work from there.

Getting started with the sample application

To get up and running quickly, we included a sample web application in the ODXL project. The purpose of this sample application is to provide an easy way to evaluate and test ODXL.

The sampleapplication lets you browse the available database schemas and queryable objects: tables and views, including calculation views (or at least, their SQL queryable runtime representation). After making the selection, it will build up a form showing the available columns. You can then use the form to select or deselect columns, apply filter conditions, and/or specify any sorting order. If the selected object is a calculation view that defines input parameters, then a form will be shown where you can enter values for those too.

In the mean while, as you're entering options into the form, a textarea will show the URL that should be used to invoke the ODXL service. If you like, you can manually tweak this URL as well. Finally, you can use one of the download links to immediately download the result corresponding to the current URL in either .csv or .xlsx format.

Alternatively, you can hit a button to add the URL to a batch request. When you're done adding items to the batch, you can hit the download workbook button to download as single .xlsx workbook, containing one worksheet for each dataset in the batch.

What versions of SAP/HANA are supported?

We initially built and tested ODXL on SPS9. The initial implementation used the $.hdb database interface, as well as the $.util.Zip builtin.

  We then built abstraction layers for both database access and zip support to allow automtic fallback to the $.db database interface, and to use a pure javascript implementation of the zip algorithm based on Stuart Knightley's JSZip library. We tested this on SPS8, and everyting seems to work fine there.

  We have not actively tested earlier SAP/HANA versions, but as far as we know, ODXL should work on any earlier version. If you find that it doesn't, then please let us know - we will gladly look into the issue and see if we can provide a solution.

How to Contribute

If you want to, there are many different ways to contribute to ODXL.

1. If you want to suggest a new feature, or report a defect, then please use the github issue tracker.
2. If you want to contribute code for a bugfix, or for a new feature, then please send a pull request. If you are considering to contribute code then we do urge you to first create an issue to open up discussion with fellow ODXL developers on how to best scratch your itch
3. If you are using ODXL and if you like it, then consider to spread the word - tell your co-workers about it, write a blog, or a tweet, or a facebook post.

Thank you in advance for your contributions!

Finally

I hope you enjoyed this post! I hope ODXL will be useful to you. If so, I look forward to getting your feedback on how it works for you and how we might improve it. Thanks for your Time!

[shervin_soleymanpoor]

Good explanation of what ODXL is. I Hope that more of the SAP community could look at this hidden gem. :wink:

[roland_bouman]

Thanks Shervin!

[seVladimirs]

Thanks! This is exactly what I was looking for! It's working as charm!

[roland_bouman]

TThanks man! Please use github in case you run into trouble or if you need more features.

[seVladimirs]

sure! Recently new slack launched for #SAP #HCP #HanaCloudPlatform specific questions and chat. If you want you can join via http://hcpslack.herokuapp.com

[joel_john1]

We use this in our application, but for some reason the generated xls file has the same data in a different order when compared to what the application shows in the browser. I am not able to figure out why this happens.

Any pointers to what might be causing this?

[roland_bouman]

Hi Joel John!

Thanks for your interest in ODXL!
> Any pointers to what might be causing this?

Today, ODXL supports the $format=sql query option. You can check if your version of ODXL supports that by checking out the config.xsjslib file. See below:

https://github.com/just-bi/odxl/blob/master/service/config.xsjslib#L41

If your version does not have it, get the latest from github.

Passing the value sql to the $format query option will generate a response containing the SQL statement that ODXL would generate to satisfy the request. I would start there and see if something is wrong.

If you find there is an issue with the way the SQL is generated, then please post an issue on the github issu tracker.

[joel_john1]

Thank you. I will check it out and post the results here.

[joel_john1]

HI Roland,

I checked the SQL query according to the suggestions you provided. We had an older version of ODXL. I updated that. But it didn't solve my issue.

The problem we had was with a hierarchical table. When we export the data, the query generated didn't have any "order by" clause. So the DB was ordering it based on the first column in ascending order, which ended up giving rows in different order than what was shown in the application.

We needed something that would order the rows based on their relevant nodes in the hierarchy and would replicate the table structure. We're trying to create an SQL query that does that. Hopefully, we will be able to do it soon.

 

[roland_bouman]

Hi Joel!
I'm not entirely sure what you mean by "hierarchical table". Can you paste DDL of your table, and some sample data, the ODXL request, the SQL generated by ODXL, the expected result and the actual result? Then I might be able to help?

Please use the github issue tracker  https://github.com/just-bi/odxl/issues

[joel_john1]

Hi Roland,

I meant the Tree Table in UI5. Anyway, I found out that creating a SQL query to replicate the tree’s node structure is either very complicated or not possible at all.

Thank you for the help anyway.

Joel.

[joel_john1]

Hi Roland,

I am working on another app, that shows the content of tables in HANA. But currently, the date data fields seem to have some sort of problem.

I already saw the open issue created in the GitHub repo. Is there some way to know when a fix might be available?

Thank you,

Joel

[roland_bouman]

Joel John,

> "But currently, the date data fields seem to have some sort of problem."

Please, be more specific. At a minimum:

- what does your table look like (DDL)
- what does your ODXL request look like?
- what response did you expect?
- what response did you get?

> I already saw the open issue created in the GitHub repo. Is there some way to know when a fix might be available?

Many open issues - which one? Github has notifications somewhere, I had to google very hard once to find it https://help.github.com/articles/watching-repositories/

[roland_bouman]

> I found out that creating a SQL query to replicate the tree’s node structure is either very complicated or not possible at all.

I think the graph engine could help here.

https://help.sap.com/viewer/f381aa9c4b99457fb3c6b53a2fd29c02/2.0.01/en-US/30d1d8cfd5d0470dbaac2ebe20...

If you then create the query you want to run and wrap it into a calculation view (or in a stored procedure so you can project it into a scripted calculation view), you should then be able to access that via ODXL request as usual.

 

Alternatively you could try and generate Excel document at the client side. THen of course you'd have to traverse the treetable's model and do the excel outputting yourself (without ODXL)

[roland_bouman]

If you really need this, we at Just-BI.nl can build it for you:

roland.bouman@just-bi.nl

roland.bouman@gmail.com

[Former Member]

Good! explanation of  XSJS implementation in ODXL. I was looking for it.

Thanks Roland!

Shambhu Nath

[roland_bouman]

Thanks! Glad that was useful to you.

[joel_john1]

Hi Roland,

 

Is there a way we can specify the input parameters to the calculation view on HANA?

I've been trying some things but cannot get the syntax right.

 

Thank you,

Joel

[roland_bouman]

Hi joel_john ! Thanks for your interest in ODXML.

Actually, I don't think ODXL currently supports parameters. I suppose we could add such a feature. We'd have to think a bit about how to map this to the odata uri conventions - in xsodata, parameters are passed to a "virtual" parameter receiving entity using the " key" part of the URI (the parenthesized list of name/value pairs after the entity name), and the actual result is returned as a property of that virtual entity - see for example:

https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.03/en-US/73a801f25cbf425994f22b06d3...

So that

"MYCALCVIEW" as "MyEntity"

  parameters via entity "MyEntityParameters"

  results property "MyEntityResults";

}

would let you call /MyEntityParameters(param1=value1,param2=value2,...)/MyEntityResults

The problem with this approach is ODXL does not rely on any service definitions - it has to derive all info from the url.

If we decide not to use the Xsodata approach, then things can be very simple to implement if we invent a new custom query option, let's say: $parameters.

Perhaps you can add a issue to the ODXL issuelist for this?

https://github.com/just-bi/odxl/issues

If you email me at roland.bouman@just-bi.nl then we can discuss some options.

[joel_john1]

I have created an issue over at Github.

Hopefully we can get this sometime down the line.

Would be a great value add.

[roland_bouman]

Thanks! I think it's a great idea, willing to do it. Have to find some time. If I implement it, will you test it?

[joel_john1]

Sure. Glad to help.

[roland_bouman]

Hi joel_john ! Did you see my comment on github? I can get input parameters to work - it seems the feature was there already using the key part of the uri, it slipped my mind that I had implemented that. This is not ideal for reasons I explained above but since I probably should not change the existing behavior (save for bugs in the implementation) we might as well roll with it. So, I'd appreciate your feedback - please checkout my comment on the github issue and provide your feedback.

https://github.com/just-bi/odxl/issues/40#issuecomment-384766453

Cheers, roland.

[klaudry]

Hi Roland, I have question

 

I'm been trying to use ODXL to export CSV file in my VUEJS App from Hana Database, I built the odxl endpoint in odxl.xsjs, the final endpoint looks like

/odxl/"SCHEMA"/"hdb.view"?$format=csv&download=file.csv

it works, I get the response from request and can download the CSV file in my VUE App, but as I tried to add filter into the URL &$filter=color eq 'blue' , it's not working, I got 400 Error response, as far as I know the filter is correct

/odxl/"SCHEMA"/"hdb.view"?$format=csv&download=file.csv&$filter=color eq 'blue'

know why?

 

https://answers.sap.com/questions/14033205/odxl-filter-query.html

[roland_bouman]

Hi Khasan,

 

thanks for your interest!

 

If you pass $format=sql, like so:

 

/odxl/"SCHEMA"/"hdb.view"?$format=sql&download=file.csv&$filter=color eq 'blue'

 

It should return a SQL query. Can you spot any syntax errors in there? If not, please run the SQL statement and see if you receive any errors from HANA. That should get you started to troubleshoot.

 

[klaudry]

Hai Roland thanks for your asnwer, when I try as as $format=sql,  as run SQL, it didn't work, it turns out the filter should be

&$filter="color" eq 'blue' 

because all I know all this time in Odata V2 we can just use filter as

&$filter=color eq 'blue' 

thanks

[roland_bouman]

Ok thanks Khasan. So I guess, problem solved then?