Steps to Create 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 is requested 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 step, 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 SQL statements necessary to create the tables used in this example are printed here. Readers familiar with the SQL langauge will see that these are simple SQL staments; just copy and paste them into SQL editor window in the SAP HANA Studio, and the tables and data will be created for you in your local SAP HANA server. You’ll then be able to follow the examples locally and recreate the view of yourself. If you don’t feel like entering in the data as it’s printed here, then feel free to use it as inspiration to enable you to play with the data you have in your own system.

Note

In a typical SAP HANA system, normally tables would not be manually created as we are doing here. Tables would usually be provided by the ERP system and loaded using suitable ETL software (extract, transform, and load software, such as SAP’s BusinessObjects Data Services, or their SLT replication server). Here, though, we’ll create the tables manually, in order to more easily see the table structure and contents.
To open an SQL editor window in the HANA Studio, we click on the system name in the Navigator pane, then click on the SQL button, which is at the top of the Navigator pane. A text editor will open in the middle of the screen, alongside the Quick Launch pane.

Note

If you are not currently connected to a SAP HANA system, then the SQL editor window will open, but with no connection established to the database. If this should happen to you, just close and reopen the Studio and try again.

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;

This instruction creates a schema, which is just a logical grouping of tables. If your SAP HANA system already contains a schema called BOOK, then this statement will fail. If this happens, just rename the schema to something else, may be HANABOOK.

The second instruction, grant, is necessary to create views. Since we created the BOOK schema manually, the SAP HANA metadata doesn’t know about it, so we have to do this bit of housekeeping ourselves. In this way, the background process of the SAP HANA studio will be able to correctly read the tables in our schema. If we don’t execute this grant instruction, we won’t be able to create views later, because the Studio won’t have access to our tables.

CREATE COLUMN TABLE “BOOK”. “CUSTOMERS”

(
“CUST_ID” INTEGER CS_INT NOT NULL ,
“CUST_NAME” VARCHAR (25) NOT NULL ,
“CUST_COUNTRY” VARCHAR (20)
) ;

This previous SQL statement creates a simple table. Note that we’re creating a COLUMN table (using the column-based storage). Of course, we could create a row-based table using CREATE TABLE CUSTOMERS, but that would defeat the purpose of using SAP HANA really. If you changed the name of the schema from BOOK to something else, then you’ll need to change it here too.

Now. we’ll put some sample data into our CUSTOMERS table, using the following SQL statements:

insert into “BOOK”.”CUSTOMERS” values (1, ‘Smith’, ‘GB’);
insert into “BOOK”.”CUSTOMERS” values (2, ‘Jones’, ‘GB’);
insert into “BOOK”.”CUSTOMERS” values (3, ‘Martin’, ‘FR’);
insert into “BOOK”.”CUSTOMERS” values (4, ‘Machine’, ‘FR’);
insert into “BOOK”.”CUSTOMERS” values (5, ‘Schmidt’, ‘DE’);
insert into “BOOK”.”CUSTOMERS” values (6, ‘Muller’, ‘DE’);
insert into “BOOK”.”CUSTOMERS” values (7, ‘Garcia’, ‘ES’);
insert into “BOOK”.”CUSTOMERS” values (8, ‘Martinez’, ‘ES’);
insert into “BOOK”.”CUSTOMERS” values (9, ‘Rossi’, ‘IT’);
insert into “BOOK”.”CUSTOMERS” values (10, ‘Moretti’, ‘IT’);

Next, we’ll create our ORDERS table, again column-based, and insert its sample data using the following SQL statements:

CREATE COLUMN TABLE “BOOK”.”ORDERS”
(
“ORDER_ID” INTEGER CS_INT NOT NULL ,
“ORDER_COUNTRY” VARCHAR (20) ,
“ORDER_YEAR” VARCHAR (4) NOT NULL ,
“CUST_ID” INTEGER CS_INT NOT NULL ,
“ORDER_AMT” INTEGER CS_INT NOT NULL
) ;

insert into “BOOK”.”ORDERS” values (1, ‘GB’, ‘2010’, 1, 100);
insert into “BOOK”.”ORDERS” values (2, ‘GB’, ‘2010’, 2, 117);
insert into “BOOK”.”ORDERS” values (3, ‘GB’, ‘2011’, 1, 120);
insert into “BOOK”.”ORDERS” values (4, ‘GB’, ‘2011’, 2, 135);
insert into “BOOK”.”ORDERS” values (11, ‘FR’, ‘2010’, 3, 100);
insert into “BOOK”.”ORDERS” values (12, ‘FR’, ‘2010’, 4, 117);
insert into “BOOK”.”ORDERS” values (13, ‘FR’, ‘2011’, 3, 120);
insert into “BOOK”.”ORDERS” values (14, ‘FR’, ‘2011’, 4, 135);
insert into “BOOK”.”ORDERS” values (21, ‘DE’, ‘2010’, 5, 100);
insert into “BOOK”.”ORDERS” values (22, ‘DE’, ‘2010’, 6, 117);
insert into “BOOK”.”ORDERS” values (23, ‘DE’, ‘2011’, 5, 120);
insert into “BOOK”.”ORDERS” values (24, ‘DE’, ‘2011’, 6, 135);
insert into “BOOK”.”ORDERS” values (31, ‘ES’, ‘2010’, 7, 100);
insert into “BOOK”.”ORDERS” values (32, ‘ES’, ‘2010’, 8, 117);
insert into “BOOK”.”ORDERS” values (33, ‘ES’, ‘2011’, 7, 120);
insert into “BOOK”.”ORDERS” values (34, ‘ES’, ‘2011’, 8, 135);
insert into “BOOK”.”ORDERS” values (41, ‘IT’, ‘2010’, 9, 100);
insert into “BOOK”.”ORDERS” values (42, ‘IT’, ‘2010’, 10, 117);
insert into “BOOK”.”ORDERS” values (43, ‘IT’, ‘2011’, 9, 125);
insert into “BOOK”.”ORDERS” values (44, ‘IT’, ‘2011’, 10, 135);

When you have entered the SQL statements, click on the Execute button at the top of the SQL editor. The Execute button is the round green button with the right-facing arrow, which you can see in the following screenshot. The results of the instructions will be shown in the Job log pane (and if you’ve just created a table, it’ll appear in the Navigator pane on the left):

Once the data is in the SAP HANA system, we have a schema called BOOK with two tables, CUSTOMERS and ORDERS. If we refresh the Navigator pane by clicking on the Catalog node and pressing the F5 key, our new schema will be displayed there.

Step 2 – Creating A Package

open the SAP HANA Studio now, and choose the Modeler perspective (go to Window|Choose Perspective|Modeler). You’ll presented with the main SAP HANA Modeler screen, which we saw in the previous section and which we’ll be using from now on.

Note

SAP HANA is a rapidly evolving piece of software – both server side and client side. It is possible that, depending on the version of the SAP HANA Studio you use, the interface might not look exactly like the screenshots in this book. Don’t be alarmed at this – the concepts are the same, using the same objects, no matter which version of the Studio you use.

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 thier 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 modelling 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 copy the data we’re interested into 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.

In this example, we’ll create a simple attribute view, selecting only certain fields for use.

Checkout SAP HANA Interview Questions

Note

The attribute view created here uses the CUSTOMERS table from the previous section. In this table, the fileds CUST_ID, CUST_NAME and CUST_COUNTRY are available, and in the attributeview created, only CUST_ID  and CUST_NAME will be visible.

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 Navigotor 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 filed 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.

Note:

We can see here that the SAP HANA Studio shows in which schema a table can be found – if we have many tables containing the letters cust, they would all be listed, and by seeing the schema we can select the correct table.

Several tables can be added to a view at once, jsut 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 scrrenshot we’ve just seen. There’s a little green star next to CUST_ID, to show it’s a key attribute.

 Note:

Before any object in SAP HANA can be used, it needs to be activated. Readers familiar with the SAP ERP development cycle will have come across this notion often.

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 (be 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 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.

Congratulations! We’ve created an attribute view, one of the most important building blocks of a SAP HANA application.

Next, we’ll create an analytic view, which allows us to work with numeric information from the database.

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 Analytical 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 (our package is called book).

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.

There’s quite a lot of information on this screen, so let’s take a look at it in detail.

First, notice that the graphical designer tab (called *(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.

In the next section, we’ll be taking the reporting application that we’ve just built together, and expanding it, using more of the development objects provided by SAP HANA to allow our users to do simulations on sales for next year, based upon the sales reported this year. To do this, we will see advanced topics, such as calculation views, user input prompts, calculated measures, as well as visualizing our reporting simulator in Microsoft Excel.