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. From the Quick Launch pane of the Modeler perspective of the Studio, select Analytic View, then click Create. Select Copy From, then choose the analytic view, which we created in the previous section:
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 field. SAP HANA will present us with a filter dialog, as shown in the following screenshot:
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 suggests, 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 do is 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.
As expected, 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.
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.
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
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 field.
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 earlier, 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_PERCENT$$) / 100) + “ORDER_AMT”.
Once that’s done, save the calculated measure, and reactivate the analytic view. Your view should at this point look like the one displayed in the following screenshot:
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:
Graphical Calculation View
In order to tie all our views 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 the two input nodes (our analytic views) and an Output node, as shown in the following screenshot:
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 is 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.
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 will 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:
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 is 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 been done, you should end up with a union mapping that looks like the one displayed in the following screenshot:
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.