SAP HANA Features and Building Blocks

SAP HANA Features

As you start to use SAP HANA, you will realize that there are a wide variety of things that you can do with it. This section will teach you the most commonly performed tasks and most commonly used SAP HANA features.

We’ll be expanding on the example that we built in the last section to use the more advanced features of SAP HANA in creating a sales prevision system, using only the sample data, which we’ve already seen. Our prevision application (or simulator) will allow the user to enter an expected increase in sales (in percent), and then show, alongside the actual data, what that percentage increase will translate to in projected sales.

We’ll be using the same tables created earlier, and just expand our reporting application-no new tables or data will be needed to create our sales simulator. The simulator will work on the original data, and present results created on-the-fly. The advantage to this is that when new data arrives in the underlying table (say we load data in from a new department), our simulator will “just work”-no rework, fiddling, or tweaking necessary.

This is a fairly common scenario in business reporting and simulation applications. We’ll see that using only the standard tools available in SAP HANA, we can build this simulator relatively quickly and with little fuss.

In order to build this application, we will be using the following building blocks:

1. A second analytic view

2. A filter

3. A calculated attribute

4. An input parameter

5. A calculated measure

6. A graphical calculation view with projections and a union

Finally, we will call up our application with Microsoft Excel, displaying simple integration with SAP HANA.

Analytic View:

 The first thing we need to create is a second analytic view. This will enable us to compare our projected sales with the actual sales.

As we saw in the previous section, we can create an analytic view by copying an existing view, so we’ll do that now.

From the Quick Launch pane of the Modeler perspective of the Studio, select Analytic View, and then click Create. Select Copy From, then choose the analytic view, which we created in the previous section:

As we saw earlier, when we create an analytic view by copying another analytic view, our new view will be an identical copy of the old view. We can, however, make changes to the old view without impacting our copy.

You will be presented with a new analytic view, identical to the one we created previously.

This analytic view will be used to show the projected data, and we’ll modify it heavily over the next few sections.

Filters:

In SAP HANA, we can filter information at the lowest level-right at the database. By filtering data as close to the disk as possible, we will greatly speed up our reporting application.

In our simulator application, we’re going to filter the data in our PROJECTED view. By default, the view will give us all data from the underlying ORDERS table, but we only want to keep data from the year 2011.

To create a filter, in the graphical analytic view definition builder, right click on the field name on which to filter, and select Apply Filter. We’ll be filtering on the ORDER_YEAR filed. SAP HANA will present us with a filter dialog, as shown in the following screenshot:                                 

We can choose the type of filter to apply – for example, Select all lines where the filter field has a value equal to, or Select all lines where the filter field has a value less than, then we just need to give the value for comparison, and SAP HANA will do the rest.

Here, we’ll select Equal. Next to the Value filed is the input help button; click on that to see the Value Help Dialog window, which you can see in the previous screenshot. If you click on the Find button, SAP HANA will present a list of unique values in the view, for the field selected. Here, for our ORDER_YEAR filed, we find our two values of 2010 and 2011. Select the value 2011 and click OK to create the filter. You’ll see a funnel icon in the view builder, indicating a filter is in place.

F we activate the view and then preview the data in it, we’ll see that only data from 2011 is present.

Calculated Attributes:

Now that we have our view filtering only on data from 2011, we’re going to create a new column from scratch, which will show us the year 2012. This is the base of our simulator application – we’ll simulate data from 2012, based upon the data from 2011.

To do this, we’re going to create a calculated attribute. As its name suggest, this function allows us to do calculations on attribute (non-numeric) values. For readers familiar with SAP’s BW system, this feature is unique to HANA – you can’t create calculated attributes (at query level) in BW, but can only create calculated measures (which are referred to as ratios or key figures).

In our example, we’ll be taking the ORDER_YEAR value, adding one year, and storing the result in a new column that we’ll call NEXT_YEAR.

In the Output pane of the analytic view, you’ll see a node named Calculated Attributes, as we can see here:                                         

Right-click on the Calculated Attributes node, then select New from the menu. You’ll see the Calculated Attribute dialog window, which asks for a name and a description for the attribute (we’ll use NEXT_YEAR as both name and description). The data type for the calculated attribute is VARCHAR with a length of 4-this is the same type as the original ORDER_YEAR field.

Next, we need to enter the formula used to calculate the attribute. If you’ve used formulas in Excel, then this will seem familiar; it works the same way.

Basically, what we need to s to take the value of the ORDER_YEAR column, and add 1. However, we can’t work on the ORDER_YEAR column directly; as we’ve just seen; its data type is VARCHAR, which is a string of characters.

In order to do mathematical calculations on the value in the ORDER_YEAR column, we must first obtain a numeric representation of the value. Next, we’ll add 1 to this value, and then retransform the result back into a string.

This can be summarized in the following points:

• Obtain numeric representation of the contents of the ORDER_YEAR column
• Add 1
• Transform the result of the calculation back into a string representation.

Fortunately, this is a lot easier than t sounds; SAP HANA provides a large number of functions to do different tasks – you’ll see them in the Functions pane. Have a look at these functions; most of the function names are fairly self-explanatory, and they have been grouped together into categories (just like Excel really). The two we’ll be using are int () and string ().

In the Elements pane, you’ll see the different components (attributes, measures, calculated attributes, and so on) of the analytic view – any or all of these can be used when you create your calculation. You can even mix and match your attributes and measures. For example, you could create a PREGRESSION column (calculated attribute) when the sales for a year for a particular customer (ORDER_AMT, measure) are higher this year than their sales last year (ORDER_YEAR, attribute). We’ll be using the private attribute, ORDER_YEAR.

In the Expression Editor window, enter string (int(“ORDER_YEAR”)+1).

We can now add our calculated attribute, then save, activate, and do a data preview on our view to see the results.

As excepted, we have a new column, called NEXT_YEAR, which contains the value 2012, as shown in the following screenshot:                             

Of course, if we change the filter on the view, to filter on the year 2010 instead of 2011, then the NEXT_YEAR column will take the value 2011 instead of 2012. If you decide to check this, then please make sure you restore the filter to the default value of 2011, or else the rest of our simulator won’t work right.

Input Parameters:

One of the more useful features of SAP HANA is the input parameter. This allows an application to be developed where the results depend on the input of the user at runtime. This input can be used to filter results, to perform calculations, and so on.

In our simulation application, we’re going to ask the user to tell us by how much the company sales are expected to increase next year. When we run the application, we’ll be asked to fill in the value, and then we can use this value to calculate our projected sales.

Creating an input parameter is a simple affair – as usual, in the Output pane of our analytic view, click on the Input Parameters node, then New to see the Input parameters dialog, as shown in the following screenshot:                           

Here, we’ll create a parameter called SALES_INCREASE_PERCENT, which is mandatory (the user will have to enter a value to be able to see the results of the view).

Because we’ll be asking the user for an increase in percentage, we’ll not define a type for the variable. The type field indicates to SAP HANA the kind of data we’ll be asking for – either an attribute value, a currency, a date, or a list of accepted values from which the user can choose. We’ll be asking for none of these – we just want the user to enter a number, so leave the Type field empty. The Data Type filed, however, will contain an INTEGER value; SAP HANA will use this information to validate that the user enters a valid value, and because it’s number, we’ll be able to use it later in calculations.

Note

If you save the variable, activate the view, and request a data preview, you will obtain the same results as we saw earlier. Even though we defined our variable as Is Mandatory, we were not asked to input any value. The reason for this is quite simple – the variables exist, but it is not yet used anywhere. As soon as we integrate the variable in our view, we’ll be asked to input a value.

Calculated Measures:

Now that we have a variable, which the user can fill in, we can use this to create our projected sales total. To store this amount, we need a new column in our view, and this column will be a measure, just like ORDER_AMOUNT. However, because this information is not present in any underlying table, it s called a calculated measure. Every time we ask SAP HANA for the contents of our view, this column will be recalculated.

Checkout SAP HANA Interview Questions

Creation of a calculated measure is done in exactly the same way as a calculated attribute – right click on Calculated Measures in the Output pane of the view, and select New. We are presented with exactly the same dialog as when we created our calculated attribute earlier; the only difference being that this time, instead of creating text-type columns, we are presented with a list of numeric types in the Data Type filed.

Give the calculated measure a name and a description (PROJECTED_SALES) and a data type (INTEGER).

In the Formula Editor window, we want to calculate a value based on the ORDER_AMT field, and increase this value by the percentage amount the user enters in our SALES_INCREASE_PERCENT variable.

You’ll see, in the same way as for the calculated attribute we created earler, that in the Elements pane our ORDER_AMT field and SALES_INCREASE_PERCENT variable are available for use:                                                            

If you double-click on the field and then the variable, they’ll be added to the Formula Editor window. ORDER_AMT will appear as “ORDER_AMT” and SALES_INCREASE_PERCENT will be surrounded with $$. These dollar signs indicate that we’re talking about a variable, and that the value will be supplied at runtime.

The formula to calculate the projected sales as a percentage increase of ORDER_AMT is ((“ORDER_AMT” * $$SALES_INCREASE_PERCENTA$$) / 100) + “ORDER_AMT”.

Please enter this formula as it appears here into the Formula Editor window. Be careful with the brackets – they’re important. SAP HANA will perform the calculation we enter respecting mathematical rules of precedence; the brackets make sure calculations are done in the order we specify (notably that the multiplication is done before the division) – if not, then the values will be divided by 100 before being multiplied by the percentage increase, and since the fields are of type INTEGER, the result will be 0.

Once that’s done, save the calculated measure, and reactive the analytic view.

You view should at this point look like the one displayed in the following screenshot:                                                      

 Starting to look fairly full, isn’t it?

If we now request a data preview on our view, we obtain an input prompt, asking us for a value for the SALES_INCREASE_PERCENT variable, as shown here:                                  

If we enter the value 10 in the From field, and click OK, we can see the results of our calculations, as shown in the following screenshot:                                 

As we can see in the previous screenshot, we have a new column, called PROJECTED_SALES, and t contains the value of the ORDER_AMT column, increased by the value of the SALES_INCREASE_PERCENT variable – in this case 10 percent. If you refresh the data preview, you will once again be asked for the input variable – try playing with different values to check if the calculation really is done each time the data is refreshed, and that it calculates the results correctly.

Note the results for the sales value of the customer Moreti. In 2011 their sales were 135. We asked for an increase of 10 percent, which is 13.5. The result of the projected sales column, however, is only 148. SAP HANA rounds the value down, not up as we might expect. The reason is simple – like we explained earlier, the field is of the type INTEGER – so the best we can get when we calculate 10 percent of 135 is 13. If you require mathematically correct results, use a different data type for your columns, such as DECIMAL, NUMERIC, REAL, or FLOAT, and specify the number of decimal places you require.

Let’s take a quick look at what we’ve built so far. In the previous section, we created two tables containing customer and order information, an attribute view allowing us to recover the customer name, and an analytic view that joins the customer name to their order information. In this section, we’ve added a second analytic view that will show projected sales for the year 2012, based upon the actual sales of 2011, increased by a user-entered percentage. To do this we created a filter, calculated attribute, input parameter, and a calculated measure.

We are now in a position to join all this information together and create our sales simulator, showing the real data from 2010 and 2011, and projected sales for 2012.

Graphical Calculation View:

In order to tie all our view together, we’re going to use a graphical calculation view. This tool lets us pick and choose fields from one or more analytic views, perform joins and unions on these views, and present the results to the user.

The easiest way to understand how a graphical calculation view works is by building one. From the Quick Launch pane, select Calculation View, then Create.

Calculation views can be either Graphical, or can use SQL SCRIPT, an SQL-based programming language proposed by SAP for the HANA database. SQL Script calculation views are more powerful, since they provide more features than graphical views, but they are also more complex. SQL Script-based views are not covered in this book.

The Calculation View dialog will appear as follows:                                

Our view will be called SIMULATOR, is of type Graphical, and is in the book package.

Click on Next, to see the now-familiar view-selection dialog. In our calculation view, we’ll be combining the output of the first analytic view we created, plus our PROJECTED view, so please add both of those to the calculation view.

In order to define our calculation view, we are presented with a graphical designer, which initially will show only two input nodes (our analytic views) and an Output node, as shown in the following screenshot:                                                                      

Note that by default, the input has no link to the output – we have to define the junctions ourselves.

In Tools Palette at the top of the graphical designer, we can see the types of building blocks available for calculation views; in our example, we’ll be using two Projection blocks and a Union block.

The Projection blocks allow us to  select only certain fields from the analytic view to use in our calculation view – once more following the principle of “filter the data as soon as possible and work with as little of it as it necessary” in order to speed up calculations. The Projection block also allows us to add calculated columns – these are the same as calculated attributes or measures in the analytic view; however, they are visible only in this calculation view, not everywhere the analytic view is present.

In order to combine the results of the two analytic views, we need to either add a Join or a Union block to the calculation view. Both of these can do the job of combining the data; however, since we need no special handling of the data (for readers who are familiar with SQL, we do not need a special join type, we’re just doing a regular inner join), a Union block is recommended by SAP as it’s faster.

The first thing we need to do is add a Projection block to the view, then link the upper junction of our “real data” analytic view to the lower junction point of the Projection block – simply by dragging a line between them. The junction points are the small circles at the top and bottom of the blocks. Click inside the circle of the source block, and drag to the circle of the destination block,  as shown in the following screenshot:                                                                              

Note the direction of the arrow! Also be careful to work on the first analytic view we created, not the projected sales view – we’ll get to that one in a minute.

Once this is done, click on the Projection block, and we are given the list of fields in the analytic view; here we can choose with which fields we would like to work.

As usual in the SAP HANA Studio, right click on a field name, and choose Add to Output to select a field. To remove a field added in error, right-click on it in the Output pane, and select Remove.

In our simulator, we’ll be working with the ORDER_YEAR, CUST_NAME, and ORDER_AMT fields, so please add these to the Output pane.

Next, we’re going to create a calculated column, which will indicate that the data in this projection is REAL data.

Right-click on Calculated Columns in the Output pane, and select New. The Calculated Column dialog (identical to the Calculated Attribute dialog and the Calculated Measure dialog) will appear, as shown in the following screenshot:                                  

Our calculated column should be created as specified in this screenshot. It’s a text-type field, which contains the value REAL.

Note:

The expression used to indicate the value REAL just single quotes, as shown in the screenshot:

Click Add to complete creation of the calculated column.

Now we need to add a second projection; join it to the PROJECTED view, and define our output columns. For the REAL data in the projection we just created, we used the ORDER_YEAR field from the underlying analytic view. For our PROJECTED data, we will use the NEXT_YEAR field, instead of ORDER_YEAR, since NEXT_YEAR contains the projected year value. Likewise, ORDER_AMT will be replaced with PROJECTED_SALES. Please add these fields to the Output pane.

In this projection too, we will create a calculated column, just like in the first projection, only this time it will take the value PROJ. Please do this now, using the instructions given to create the REAL calculated column in the first projection.

Our projections are now ready – one containing the current real data, the other containing the projected data for the value 2012 – we can now merge the data from them into one unified view, ready for consumption.

In order to do this, add a Union block to the view, above the two Projection blocks, and join them. This is done, again, by drawing a line from the upper junction of the projection nodes to the lower junction point of the output node, as shown in the following screenshot:                                                                              

Now that we have a Union block, we need to make the different fields from the underlying analytic views point to the correct output fields.

Click on the Union block, and you’ll see a list of all the available fields, from both projections. Add the following fields to the target from the projection that contains the Real data (usually called Projection_1) by right-clicking on each field in turn and selecting Add to target:

• DATA_TYPE
• ORDER_YEAR
• ORDER_AMT
• CUST_NAME

In order to join the data from the “projected data” view, we need to map its fields to those of the “real data” view – this s done by right-clicking on the field and selecting Map to Target, then by selecting the appropriate output column. We need to make the following column mappings in our view:

• DATA_TYPE maps to DATA_TYPE
• CUST_NAME maps to CUST_NAME
• PROJECTED_SALES maps to ORDER_AMT
• NEXT_YEAR maps to ORDER_YEAR

Once this has done, you should end up with a union mapping that looks like the one displayed in the following screenshot:                                  

Who said spaghetti?

The final step that needs to be done is to add the union’s Target fields to the Output node. Join the Union block and the Output block, then click on the Output block to define the fields used.

Add DATA_TYPE, CUST_NAME, and ORDER_YEAR as attributes, and ORDER_AMT as a measure.

Once this is done, check if your final calculation view looks like the one displayed in the following screenshot:                                  

And now the moment of truth! Activate the calculation view, open a data preview, and you should be able, using the Analysis function, to obtain the following output:                                  

And our simulator is done! With only two tables, and using the standard building blocks available in SAP HANA, we have built a sales simulator, allowing our user to enter a projected sales increase percentage, and showing the projected data (which is created on-the-fly by SAP HANA) alongside the real data stored in the database.