Sap Hana Application
A SAP HANA application is where all the SAP HANA magic comes together.
Where in a “normal” reporting application (for example, SAP BW) we copy, transform, and aggregate data using transformations and intermediate storage objects (ending up with several copies of the same data), SAP HANA gives us access to transformed, aggregated views on the original data in the base SAP HANA tables, without going through the initial preparation steps—the necessary calculations and transformations are done on-the-fly, when the data isrequested by the user.
Whenever new data becomes available in the system, it is immediately visible in reporting, since no transformation or copy is needed. Whenever a new application is necessary, with new metrics on existing data, this application will take up practically no space on the server, since it will consist of views, and not more copies of preformatted and precalculated data.
Step 1 – The example data
In this section, we’ll create a simple SAP HANA application, using the following two tables:
CUSTOMERS: A list of customers, with their numerical ID and name
ORDERS: A list of purchase orders, with a numerical, purchase order ID, a country code, calendar year, customer code, and order amount
The following code creates a BOOK schema in the database, and inside that schema creates our two example tables, CUSTOMERS and ORDERS. Some simple example data is inserted into both the tables. We’ll be using this sample data throughout the book to show the various features of SAP HANA:
create schema “BOOK”;
grant select on schema “BOOK” to _SYS_REPO with grant option;
Step 2 – Creating a package
Open the SAP HANA Studio now, and choose the Modeler perspective (go to Window | Choose Perspective | Modeler). You’ll be presented with the main SAP HANA Modeler screen, which we saw in the previous section and which we’ll be using from now on.
In the middle of the screen, in the Quick Launch panel, we have the possibility to create the most used objects in SAP HANA. In this chapter, we’ll be creating the first three (package,attribute view, and analytical view). The means of creation and the usage of each of these objects will be explained in their corresponding section. In the next section, we’ll look at more advanced usage of these, as well as seeing other key concepts in SAP HANA.
The first object to create is a package. A package is a logical grouping of objects you want to keep together. It’s the same terminology you will find in SAP ERP or BW development.
Objects in different packages can be used together in the same development, so placing objects in different packages will not have any effect on the way you use them, but keeping objects that have a logical link between them will later make them easier to find. For example, you might like to keep all your development objects for the Sales Reporting project together in one package, and all your HR Reporting objects in another.
For the purpose of this book, we’ll create a simple package called (rather unimaginatively) book, and we’ll put our development objects in it.
From the main Modeler screen, in the Quick Launch panel, select Package, then press the Create button. A new window will open, as shown in the following screenshot:
You’ll be asked for a name and a description for the package—the package for this book is called book, with a description of book.
There is a field called Delivery Unit. A delivery unit is analogous to SAP’s transport order concept, and allows you to package development objects for transporting them to other systems (such as, testing and then production). Delivery Units are not covered in this book, and we don’t have one for this development, so leave this field blank.
Once the package has been created, the modeling objects needed for the development can be created, and stored inside it.
Step 3 – Creating an attribute view
In SAP HANA, an attribute view is used, as its name suggests, to present a view of master data to the user.
We can picture a master customer data record, where every imaginable piece of information concerning the customer is available, from their name, their date of birth, right down to the nearest post office, or the name of the railway station closest to the customer (yes, these fields really are available in SAP ERP!). Most of this information is unnecessary to the development being made and should be excluded from the view so as not to pollute the report, and so as to limit the amount of data read from the database in order to speed up the application.
Of course, we could only copy the data we’re interested in to a new table and use that, but it’s time-consuming, error-prone, and a maintenance nightmare to duplicate the data, when we can just have the database present only the fields we’re interested in and ignore the rest.
An attribute view performs this role in SAP HANA—it can join one or more tables of master data (attributes), and present only certain fields to the user (view). Note that an attribute view can only use non-numeric information—you can’t add numeric data to an attribute view.
From the main Modeler perspective of the Studio, in the Quick Launch panel, select Attribute View, then click on Create. You can also right-click on the package in the Content node of the Navigator pane, and select New | Attribute View—whichever method you prefer, they both lead to the same screen, shown in the following screenshot:
Give a name and a description for your attribute view. The Name field should consist of only uppercase letters, numbers, and the underscore character. The Description field can be anything you prefer—it’s for your reference, not for SAP HANA’s.
There are four different types of attribute view, explained as follows:
Standard: This view is based on database tables.
Derived: This view is derived from another attribute view (somewhat like the “create by reference” option for InfoObjects in SAP BW, for those who are familiar with the software)—if you create View B, derived from View A, then View B will be identical to View A. If, at a later time, you change View A, then View B will change as well.
Copy From: Like Derived, this will incorporate the particularities of the original View, but is not linked to that View—if you create View B as a copy of View A, then View B will be identical to View A. If, at a later time, you change View A, then view B will not be impacted.
Time: Finally, an attribute view can hold time information. Typically, this includes date, week, month, and year details such as which month of the year contains which week number.
Here we’ll create a Standard View in our book package so just click on Next; where we can add table(s) to our View, a dialog window will appear, as shown in the following screenshot:
This is the SAP HANA Select Tables dialog window. We’ll see it often when we create Views. We can either select the table to add by exploring the tree structure until we find the table, after which we select the table and then click the Add button (as can be seen in the screenshot), or we can do a search by table name, as shown here:
Just type the first few characters of the table name, and click on the arrow—all matching tables will be listed, and here again, select the table and then click on the Add button.
Several tables can be added to a view at once, just add all the tables required. Here, we’ll just add our CUSTOMERS table to the view—once that’s done, click on Finish to see the results.
The next step in our attribute view creation is to add the fields we want to use. The CUSTOMERS table contains three fields; we’ll only be using two of them in the view.
An attribute view contains, like a table itself, key fields and data fields. Right-click on the CUST_ID field in the left-hand pane, and select Add as key attribute. Then, add the CUST_NAME field as a regular attribute, using the Add as attribute option.
Once that’s done, we have an attribute view that looks similar to the screenshot we’ve just seen. There’s a little green star next to CUST_ID, to show it’s a key attribute.
Activation is simple—as you can see in the screenshot, at the top-right of the development pane is a round green button with a right-facing arrow. This button saves and then activates the current development object. Click this button now so that we can use our attribute view.
The results of the activation request are in the Job Log pane at the bottom of the screen (by default, unless you have moved or hidden it):
In order to check that our attribute view is working correctly, SAP HANA provides a Data Preview function. This is roughly the equivalent of the SE16 transaction in a SAP ERP system, and shows a tabular listing of the view’s contents. It’s a very useful debugging tool.
In the Content node of the Navigator pane, right-click the attribute view (you might need to expand the tree to see your newly created view), and select Data Preview. SAP HANA will present you with the contents of the view, as shown:
As expected, the contents of the attribute view correspond to the contents of the underlying CUSTOMERS table; however, only the two fields CUST_ID and CUST_NAME are visible in the view.
Step 4 – Creating an analytical view
In the previous section, we created an attribute view—a projection of non-numeric data, allowing us in a simple way to extract only the necessary information from the database.
As we saw, attribute views can only work with characteristic attributes—that is, meta-information, or information describing other information, such as a name, an address, and so on. Attribute views cannot work with any numerical data, such as an order amount, or a salary.
To extract figures from the database, an analytic view must be used. This view will provide a projection of numeric data, combining it (if necessary) with one or more attribute views. In this way, the analytic view will extract the figures from the underlying table, and combine them with the meta-information in the attribute view, to describe the numeric data. For readers familiar with SAP’s BW platform, this is analogous to a BW Cube (analytic view) and its dimensions (attribute views).
Here, we’ll create an analytic view that will use the attribute view to provide the customer’s name, which can then be presented alongside the order amount value. This is much more useful to users than simply showing the order amount alongside the customer’s ID.
Creation of an analytic view is done in the same way as an attribute view. From the Quick Launch pane of the Modeler perspective, click on Analytic View and then click on Create to be presented with the New Analytic View creation dialog:
An analytic view needs to have a name and its description filled in the Name and Description field respectively. Additionally, we need to indicate to SAP HANA in which Package we want to store our view
In much the same way as an attribute view, we can create our analytic view either from scratch, or by copying an existing analytic view.
In this example, we’ll create a view from scratch, so just click Next to select the tables we wish to include in our view. You’ll be presented with the Select table dialog window we saw in the previous section. For our analytic view, we want to add the ORDERS table from the BOOK schema. Once that’s done, click Next, to add attribute views.
By including an attribute view in an analytic view, we can describe the numeric data. Of course, if all the information necessary was already present in our ORDERS table, we could just use the fields from the table. In our example, in order to be able to show the customer name alongside their order information, we’ll be adding the attribute view that we created in the previous section. This is done using SAP HANA’s Select Attribute Views dialog window, as shown in the following screenshot:
This dialog is almost identical to the Select table dialog window, but it allows selection of attribute views. Just add our view, then click Next, where we’ll be able to design our analytic view with the graphical designer.
First, notice that the graphical designer tab (called *<view name>(HNA.book)) has two tabs at the bottom of the screen—Data Foundation and Logical View. The Data Foundation tab is used to see what tables (note, not attribute views) and fields are available in the view. Here, for example, we can see that the ORDERS table has been added to the view—and it is the only table we selected. The Logical View, which we will see in a while, will show us how the fields we have selected are joined to each other.
Next, we can see that the fields from our attribute view are available for use in the analytic view, even though we can’t see the attribute view on the screen. All the fields in every attribute view selected in the analytical view are automatically visible—it is enough to add the attribute view to the analytic view to have access to all its fields.
In much the same way as when we created our attribute view, an analytic view requires that we define first which table fields we wish to use. The definition is done in exactly the same way as for the attribute view, by right-clicking on the required field, and selecting Add as attribute.
Please add the ORDER_YEAR, ORDER_ID, and CUST_ID fields as attributes to our analytic view. You’ll notice that in the Output pane, the CUST_ID field, which you have just added, has been renamed to CUST_ID_1, because an attribute named CUST_ID is already present in the analytic view—it was provided by the attribute view.
Adding numeric data is done in a similar way, but these fields are marked as Measures, not attributes; so please add the ORDER_AMT field as a measure. Adding a field as a measure indicates to SAP HANA (and to reporting software) that you expect to be able to do calculations on this field—for example, summing, calculating the average, finding the largest value, and so on.
You will end up with an analytic view resembling the one seen in the following screenshot
Next, we’ll join our analytic view with our attribute view so that we can see the customer name (from the attribute view) alongside their order information (from the analytic view). To do this, we need to switch to the Logical View tab.
To join the attribute view to the Data Foundation tab of the analytic view, simply click on the junction field in the attribute view, and drag it to the junction field in the Data Foundation tab.
We’ll join using the CUST_ID field in the attribute view, and the CUST_ID_1 field in the Data Foundation tab of the analytic view, as shown in the following screenshot:
That’s all that needs to be done; our analytic view has been created, and it joins the ORDERS table to the attribute view (which we created on the CUSTOMERS table). We’re almost done—once the analytic view has been activated, we’ll be able to see our data, so do that now. Activation of an analytic view is done in the same way as an attribute view; just click on the green save and activate button at the top right-hand corner of the analytic view development pane.
Step 5 – Previewing the data
The Data Preview function that we saw earlier is a more useful function when used to visualize the output of an analytic view.
Open the Data Preview tab for our analytic view; you will see the order information, from the ORDERS table, alongside the customer name, from the CUSTOMERS table:
If you switch to the Analysis tab of Data Preview, however, you can see your data in a much more visual way, similar to an Excel pivot table, as shown in the following screenshot:
Here are some of the features we can see on the screenshot (feel free to play with the Data Preview tab until you are comfortable with it—you cannot break anything, and nothing that you do will be saved):
We can add fields from our view to the Labels and Values axis as we require, and here we are visualizing the ORDER_AMT field by the CUST_NAME and ORDER_YEAR fields.
We’re using a Column Chart chart option—there are a number of other chart options available. We’ve added ORDER_YEAR to the Filters box; this has given us an automatic filter possibility, either using the slider, or the drop-down box, or by entering the value on which we wish to filter. Here, we’ve filtered on the year 2010.
In this section, we’ve taken a look at the two basic building blocks of a SAP HANA application, the attribute view and the analytic view, using some simple data. First, we created our sample tables and data using standard SQL statements, and then we created a package to contain our two views.
We created an attribute view, which allows us to use only the fields we require from master data tables, and an analytic view, which, when combined with the attribute view, shows the numeric data we need for our reporting application
These two types of views are the basic development objects necessary to build a SAP HANA application, and in many cases will provide the functionality you need to build your reporting application, allowing you to create complex reports while keeping only one copy of the data, and needing no special processing when new data is added to the base tables.