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.
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.
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
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.
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
Similarly OData Service Definition Language files can be referenced the same way.
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.
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.
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.
Figure 11 – SAPUI5 API
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
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.
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 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.
Line 7: The <$> sign is used to reference classes within the API.
Figure 24 – Final results
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.
Calculation View with a Single Input Paramater:
“copa::PLANNED_ACTUAL_SALES ” as “PlannedvsActualCalcView”
keys generate local “ID”
parameters via entity;
Calculation View with multiple Input Paramaters:
“copa::PLANNED_ACTUAL_SALES ” as “PlannedvsActualCalcView”
keys generate local “ID”
parameters via entity “InputParams“;