The aim of this blog is to provide details on how to expose SAP HANA modeled views through SAP HANA Extended Application Services (SAP HANA XS). The information shared within the blog does require some level of familiarity with SAP HANA - specifically around modeling but also assumes that the reader has reviewed Thomas Jung’s introductory blog on SAP HANA XS. Please note the important information regarding the initial controller release of SAP HANA XS; details can be found toward the end of his blog.
One of the core components of SAP HANA XS is to provide http(s) access to the underlying data within SAP HANA by leveraging open technologies such as OData, REST, JavaScript and HTML5. The example outlined in this blog implements a simple version of the classical ERP CO-PA probability analysis scenario whereby a Calculation view encapsulates the business logic to provide insights into a company’s product profitability. My team within SAP, Customer Solution Adoption (CSA), commonly runs into customer implementations where CO-PA data is of significant interest to the customer and runtimes on large scale implementations make a strong business case for the incorporation of HANA as a platform for CO-PA operations. We will discuss several examples on how to efficiently expose the Calculation view using open standards such as REST, OData, JSON, JavaScript, and to render the result within a HTML user interface using SAPUI5.
SAP HANA modeled views
As a general rule modeled views (i.e. Calculation, Analytical and Attribute views) should be considered as a first choice when developing content for SAP HANA. They are design time objects and once deployed as runtime objects they become highly optimized in-memory columnar database views that will exploit the various optimizers within SAP HANA with the sole intent to maximize performance. Since the business logic is embedded directly within the models this automatically enables calculations and filters to be pushed down and parallelized deep within the database. What distinguishes modeled views from SQL views is that they generate a dynamic execution path that supports query stripping and join omission and will, for example, only perform the calculations against those columns requested making them good candidates for large data sets where optimal performance is required.
It is important to consider the following basic principles when developing content for SAP HANA: first, ensure that business logic and calculations are pushed down into the database either via modeled views or procedures. Second, minimize the data movement between different layers. To fully capture the performance benefits of SAP HANA adequate attention needs to be given when designing applications in order to ensure exploitation of the underlying engines based on the application requirements. The CO-PA scenario in our examples utilizes several engines that are concealed from end-users. First the heavy-lifting number crunching sales-data is retrieved and processed using several Analytical views executed by the OLAP engine. The aggregated reduced results-sets from these views are encapsulated and combined within a Calculation view executed by the Calculation engine. In turn the results of the Calculation view are handed over to the XS engine, then the data-set is then sent to the user’s browser via the built-in SAP HANA XS Web server.
Calculation view
The baseline Calculation view that is to be exposed through http(s) implements the classical union with constants pattern that combines different input sources in the most optimal fashion for in-memory columnar processing. The data is grouped by Sales Organization and Material and consist of several calculations that will eventually be used by the business to provide insight into product profitability.
Figure 1 – Graphical Calculation view
In our example, the following SQL statement and result-set satisfy the business scenario handed to us as a requirement for our development project within SAP HANA XS.
Figure 2 – SQL statement
As the result-set confirms, the data-set that will be exposed through SAP HANA XS is not only grouped by Sales Organization and Material but also filtered by materials starting with ‘DPC’.
Figure 3 – SQL results
XS Project
In order to develop and deploy SAP HANA XS content the different artifacts (i.e. text files) must exist first within specific local projects. Throughout this blog we will be using the standard SAP HANA Development XS Project. This section assumes that the reader has some familiarity using the SAP HANA Development Perspective and is able to create a local project and link the local project to the repository within SAP HANA for source code and version management.
XS Projects contains several different development artifacts depending on the requirements of the application. Required files that enforce authorization have extensions (.xsaccess), (.xsapp), and (.xsprivileges), optional files include server-side Java Script (.xsjs), and client-side UIs using SAPUI5 (.html). OData Service Definition Language files have extensions (.xsodata). Unlike the SAP HANA Studio modeler perspective that have sophisticated UIs for creating models, content for the SAP HANA XS can be created using simple text editors which come standard within SAP HANA Studio. It is important to note that XS Projects also contain specific libraries known as the SAP HANA XS JavaScript APIs that should be used when creating server-side JavaScript content.
Figure 4 below displays a typical XS Project structure; the CO-PA example artifacts are stored in a local project called (copaproj) and linked to a repository package called (copaxs) within SAP HANA.
Figure 4 – XS Project example
Depending on the http(s) port of SAP HANA, any html pages, server-side JavaScript pages or OData services can be served by using URL syntax such as:
>> http://hanaserver:port/copaxs/ui/PlannedvsActualSales.html
Similarly OData Service Definition Language files can be referenced the same way.
>> http://hanaserver:port/copaxs/odata/PlannedvsActualCalculationView.xsodata
OData
The first steps when exposing content within SAP HANA through OData is to create an OData Service Definition Language file referencing the particular database object. Although Calculation views are used in the example the same principles applies to Analytical views.
Figure 5 – OData Service Definition Language file
Line 2: The <::> notation specifies the Calculation view as a runtime object that is located within the <copa> package. It is also possible to reference design time objects. For details refer to the SAP HANA Developer guide.
Line 3: Objects that do not have a unique key in their results (i.e. Calculation views & Analytical views) requires the generated <local> key entry. Be aware that this key value numbers the result starting with 1 and is not meant for dereferencing any item in the result set. The key is valid only for the duration of the current session and is used only to satisfy OData’s requirement for a unique id in the results.
Line 4: <Aggregates always> is required for Calculation views and Analytical views where the information regarding attributes and measures are stored within specific metadata tables. That means that the aggregate always statement will ensure that this information is derived from metadata; hence the notion of derived aggregation. This however means that whenever the underlying Calculation view changes this OData Service Definition Language file needs to be re-activated. This can be overcome in line 2 by referencing a design time object instead of a runtime object.
Hint: You can verify the location of the existing modeled view referenced in the OData Service Definition Language file by expanding the Content folder, and the <copa> package from within the Navigator view.
Figure 6 – Package structure using Navigator view
Once activated the OData Service Definition Language file can be called using a web browser as follows.
>> http://hanaserver:port/copaxs/odata/PlannedvsActualCalculationView.xsodata
Figure 7 – OData Service Definition Language file
The ODate Service Definition Language result indicates that a collection can be obtained by appending <PlannedVsActualCalcView> to the URL.
Hint: Be aware of the following nuance that applies to multi-dimensional reporting (i.e. Calculation views and Analytical views) and specifically when aggregating data. In order to aggregate, the <$select> parameter should be used as it will inform the OData service to return an aggregated subset.
Figure 8 – OData service response
At this point we have good understanding how to construct the URL query in order to call the Calculation view via http(s) and we can proceed to display the results in a more readable HTML tabular fashion.
SAPUI5
SAP UI development toolkit for HTML5 (SAPUI5) is a client-side cross-browser JavaScript library for building modern rich internet applications. It is JavaScript OpenAjax-compliant and it fully supports SAP's product standards. It is extensible, lightweight and easy to consume and can be combined with 3rd-party JS libraries. As is standard with SAPUI5 applications the UI5 JavaScript libraries need to be loaded before they can be used, using the bootstrap notation pointing to the correct location within the script tag.
Figure 9 – SAPUI5 Library
The data-sap-ui-libs declaration is used to import other SAPUI5 properties, functions and objects. The Navigator view can be used to verify the installation and location of SAPUI5.
Figure 10 – SAPUI5 Library in Navigator view package structure
Hint: A good source of information regarding SAPUI5 can be found locally on the SAP HANA server.
>> http://hanaserver:port/sap/ui5/1/sdk
Figure 11 – SAPUI5 API
The next step requires attention to detail- it is important to understand the handshake options available between the client-side JavaScript code and the server-side service. Since much of the heavy-lifting coding has already been done behind the scenes within SAPUI5, you can simply tap into the framework which will significantly speed up your development effort. There are several options available depending on the data format and protocol used.
Figure 12 – Client-side Java Script using SAPUI5
Line 23: The first step in rendering any data using SAPUI5 is to create an instance of a particular model. SAPUI5 implements strict MVC patterns whereby the model retrieves the data and allows for data binding to UI elements. Several predefined models are available in SAPUI5; this blog has examples for the OData, JSON and XML models.
The OData model is a server-side model, so only the data that is requested by the UI is loaded from the server; any change of binding or list operations requires a new request to the server. The OData model’s constructor consists of one required parameter which is the URI pointing to the root of the OData Service Definition Language file location.
Line 24: Exposes the OData Model to the SAPUI5 libraries and makes it known to the framework.
Line 30: This line is not important; it is a quick and easy way to minimize the coding by using a custom helper function to loop through the required columns.
Line 34 - 39. The OData model is bound to the table using the collection property found with the OData Service Definition Language file. Notice the select parameter used earlier.
Line 40: Places the table object within the <div> tags on line 47 (Figure 13) and renders the table result.
Figure 13 – HTML area to render table resuts
After successful activation and deployment the CO-PA Planned vs Actual Sales report is displayed showing the products similar to what was displayed earlier using the SAP HANA Studio SQL Editor. However, the example is not complete; the products need to be filtered.
Figure 14 – Data result rendered using SAPUI5
JSON
Typical, business requirements demand greater flexibility via runtime parameter selection usually to restrict the data. It is always easier to first establish the particular URL query parameter before embarking on the front end coding. This also provides a glimpse of how the JSON data is structured by appending the format=json parameter.
Hint: Filtering is achieved by introducing the <$filter> and <startswidth> parameters as follows.
>> /copaxs/odata/PlannedvsActualCalculationView.xsodata/PlannedvsActualCalcView?$select=MATERIAL,ANETREVN&$filter=startswith(MATERIAL,''DPC')&$inlinecount=allpages&$format=json
Hint: The <inlinecount> parameter can be used to verify the correct number of expected results.
Figure 15 – OData with filtering in JSON format
Hint: Take note of the document root element <d:> including the child element <results:>. Those elements will be referenced later when binding the data using SAPUI5.
In contrast to the OData model the JSON model is a client-side model, the data of the model is loaded completely and is available on the client. It is therefore meant for small datasets, which are completely available on the client, it does not contain any mechanism for server based paging. Notice the <$select> clause that contains all the required column names including the <filter=startswith> parameter.
Figure 16 – JavaScript using JSONModel in SAPUI5
Figure 17 – Binding results using JSON Model
Line 17: Sets and binds the JSON model to the table UI element.
Line 18: Binds the result set rows to the table using the </d/results> elements seen earlier within within the JSON document.
Figure 18 – Final results aggregated and filtered
The data is aggregated and filtered according to the requirements.
Server-side JavaScript
The final example will be familiar to classical web developers. It uses the well-established server-side development approach whereby sever side coding is used to interact with the SAP HANA database. It is good practice to become familiar with the SAP HANA XS JavaScript API. Currently the API consists of a Request Processing API to handle http(s) request and responses and secondly a Database API to communicate with the underlying SAP HANA Database.
Figure 19 - SAP HANA XS JavaScript API location
The following is a classic example using server-side JavaScript very similar other web development technologies. Server-side JavaScript coding can be done within any text editor but requires an extension (.xsjs).
Figure 20 – Server-side JavaScript
Line 7: The <$> sign is used to reference classes within the API.
The majority of the instructions shown below simply loop through the result set and packages up the data in XML format. Similarly HTML can be imbedded directly within the JavaScript code. XML is used in this example with the sole intent to utilize the SAPUI5 XML model and to separate out the UI processing. Refer to the SAP HANA XS JavaScript API for additional parser information as they become available in future releases.
Figure 21 – Server-side JavaScript
XML
Figure 22 – Server-side JavaScript producing XML
The server-side JavaScript file produces the following results. Notice the <product> element; this will be used within SAPUI5 to bind the data to the UI table.
Figure 23 – Client-side JavaScript using HTML5 XMLModel
Notice that the XML models constructor takes a URL parameter string pointing to the unqualified location of the server-side JavaScript end-point. Finally notice when binding the rows to the tables the XML <product> element is used as seen in the previous step.
Figure 24 – Final results
Conclusion
The above information will help you get started. Since HANA is new, exciting and evolving, as revisions come out there may be additional ways to meet your goals. As you are coding if you find more elegant solutions please don’t hesitate to comment on your experiences and share your findings with everyone.
Appendix
Calculation View with a Single Input Paramater:
service {
"copa::PLANNED_ACTUAL_SALES " as "PlannedvsActualCalcView"
keys generate local "ID"
aggregates always
parameters via entity;
}
http://server:port/copaxs/odata/PlannedvsActualCalcView.xsodata/
PlannedvsActualCalcViewWithInputParamsParameters(‘.20’)/ Results?$select=salesorg,material,anetrevn&$filter=startswith(material,’DPC')
Calculation View with multiple Input Paramaters:
service {
"copa::PLANNED_ACTUAL_SALES " as "PlannedvsActualCalcView"
keys generate local "ID"
aggregates always
parameters via entity "InputParams";
}
http://server:port/copaxs/odata/PlannedvsActualCalcView.xsodata/
InputParams(ip_discount=’.20’,ip_region=’NW’)/Results?$select=salesorg,material,anetrevn&$filter=startswith(material,'DPC')&$format=json
- SAP Managed Tags:
23 Comments
