Of all the new modeling features of SAP HANA Support Package 9 (SP9), the one I think is the most important is the new ranking node part of the graphical calculation view. Why is it great? Well, the answer goes to the core of a well-designed business intelligence (BI) system. The main purpose of a well-designed BI system is to give analysts the data they need to make decisions as fast and accurately as possible, while limiting noise. In this context, noise means extraneous data that does not help analysts make good decisions. The ranking node feature goes a long way towards meeting this goal by eliminating the records that are not important from the resulting analysis.

A very sophisticated, yet more complex way to meet this goal (e.g., giving analysts the needed data) is ABC Analysis, where an algorithm pre-processes the data and then physically assigns each value a code. Let’s use the following parameters to illustrate this scenario. In this example, there are 50 million sales records summarized by customer, and sorted top down by revenue. ABC analysis would assign customers that make up the top 20 percent of total revenue a value A. The next group is the value Bs, and the remaining customers make up the value C group.

With this ABC technique, you’re typically analyzing the data maybe once a week or once a month, then determining the rank as the ABC code, and persisting it as a field in the master data. For example, you would store the customer’s ABC code in the Business Partner Master tables along with the customer’s state, city, and ZIP Code. Filters in the initial query limit the data to normally just the A rank. Contrasting this method of eliminating the noise with the ranking option discussed in this article, the rank as a number (e.g., #1,#2, #3, and so on) is not persisted at all but rather is calculated in the run-time view. It is then used to limit the dataset to the top N or bottom N value, or, as in my example, the top three.

You used to gain a huge advantage with the ability to persist the data, because it gave you the ability to have to perform this resource-intense calculation just once. It also had the advantage of keeping the As, Bs, and Cs fairly stable (e.g., keeping all eyes on the same list of customers), as these rankings would only change weekly or monthly when the algorithm was re-run. Using the rank-node functionality I describe, the cost of calculation is applied each time a view is executed, and as such it provides the current As in real time—not the As calculated earlier in the week.

With a non-SAP HANA system, this cost of calculating the ABC rating is very high, but with SAP HANA, the increased cost is incremental. What you still have if either the ABC or rank options are not deployed, even with SAP HANA, is that lots of records (a bunch of noise to the client tools) will be returned, and they have to be filtered as such later by the analyst. Using the rank node option, with SAP HANA, the noise problem (i.e., extraneous data) is no longer an issue. With SAP HANA, both of these ranking methods—ABC and rank node—allow you to filter the data before it leaves the SAP HANA database.

Configuring the Rank Node in Calculation Views

Now that I’ve given you some background on why you would want to use this functionality, I’ll discuss the how of using the rank node functionality. By how, I mean how to configure and how to use this new feature—the graphical calculation view in SAP HANA SP9.

The rank node feature is available as an option on the graphical Calculation View design pallet as shown in Figure 1.

Figure 1

Rank node in graphical Calculation Views

Like all the existing nodes (e.g., join, union, project, and aggregate), the rank node is used by dragging and dropping data into it, and then choosing fields to output to subsequent nodes (the  aggregation node in this case) to which you connect higher on in the Scenario pane.

Figure 2 Illustrates how to do this. The rank node (on the left) is dragged to the active scenario area (on the right, in the middle). The CEA1_00 table (of customer sales information) is dragged to the rank node Details section, and then add the fields from the table as desired to the Output on the right. Then select the rank node and its settings from the bottom of the middle section of the screen (shown in Figure 3).

Figure 2

Add the rank node and then select the fields to output from it

Figure 3

Select the rank_1 node configuration settings

Let’s focus for a moment on the settings—the guts of this article. In the portion of the screen in the center of Figure 3, you can choose the sort direction—ascending or descending—by using the drop-down options in the Sort Direction field. Choose Descending (Top_N) and click the OK button, and the pop-up window in Figure 4 opens. This screen is by default populated with Top_N and Top_N? in the Name and Label fields, respectively. In this case, N stands for how many resulting records are exported from the rank node. But rather than hardcoding the N value, as is the default, I want the user to be able to enter this value at run time. To accomplish this you need to create an input parameter via the New… option of the context menu as shown in Figure 3. In the pop-up window that opens (Figure 4) you can complete this task by entering the values as shown in the figure.

Figure 4

Create an input parameter to ask users how many records they want to see

These steps are the same steps you would use to build any input parameter. What is slightly different is how you use this input parameter later.

The next field is Threshold. Click the dotted square icon to the right of the field (boxed in red on Figure 3), and the pop-up window in Figure 5 opens.

Figure 5

Assign a fixed value as the threshold

In this example, you want to use the newly created input parameter, so change the Type field from the Fixed default value to Input Parameters as shown in Figure 6. Note that the Threshold field is left blank as you are creating a threshold with a fixed value. Once you choose the Input Parameters drop-down, the pop-up screen on the right of Figure 6 opens where you can choose the previously created TOP_N input parameter. Then click the OK button.

Figure 6

Choose an input parameter to prompt users for the top N value

The next step for configuring the rank node is to set the Order By option. This field is critical because it is the field that the TOP N is based on.  For example, do you want top N revenue or Profit?  In this case (Figure 3) I want the best (TOP_N) GrossRevenue producing records to be ranked in descending order. Make the selections as shown in Figure 7, and select the gross revenue measure (GROSSREVENUE) from the drop-down options.

Figure 7

Select the Order By option to rank the records

The final settings, which are optional, are shown in Figure 8. If I wanted the top three revenue-producing records from table CEA1 and that was it, I would just leave the settings as they are at this point. However, in this case I want a more complex scenario—I want the top three records for each country. Therefore, in the Partition By section, select the LAND1 (the country field) drop-down option and click the Add button. Do this for any additional fields you wish to add. In this case, I also want to choose the Dynamic Partition Elements option button (the reason for this will be discussed in more detail later in this article).

Figure 8

Add the partitioning by column and dynamic columns options

These last few tasks are not changed in SP9 and are covered in the normal modeling classes mentioned earlier, or you can view a demo here for more help: modeling demo. However, just for closure, these final steps are: connect the rank node to the aggregation node that was defaulted during the creation of the calculation view, add the aggregation node fields to the output, set the semantics of measure versus attributes, and, finally, activate the model. The steps are shown are shown in Figures 9 and 10.

Figure 9

Complete the aggregation node (add fields to output as aggregated and normal columns)

Figure 10

Choose the semantic type (e.g., blue for attributes and orange for measures)

The steps taken in Figures 9 and 10 show a quick overview of the rest of the normal steps for using calculation views. This includes choosing the proper semantic from the Type field drop-down options in the semantics node, and using the activation icon  to activate the model and generate the code that the system really uses to define the database view. I now have a completed model as shown in Figure 10—the completed and activated calculation view.

Review the Output from the Rank-Based Calculation View

This final section requires a graphic to fully understand it. When the view is deployed, in my example using Microsoft Excel, the output of the top N value changes based on three factors:

1. The attributes used in the output.
2. The attributes chosen for the Partition elements.
3. Whether or not the Dynamic Partition Elements check box is ticked (Figure 8).

In this scenario, if I had not chosen any partition elements at all, then the output would yield just three records—the three with the highest gross revenues. It would not matter if you displayed the country, the customer, or the city; the results would be the same three records. Since this option is so simple, I am not going to show screenprints for outputting this. However, for the more complex scenario that I discuss here, I’ll show the exact steps as follows.

In my output example I left the settings as they were enabled above—I partitioned by country (LAND1) and I selected the Dynamic Partition Elements check box (Figure 8).

In Figure 11, choose the Calculation View as the data source to output to BusinessObjects Analysis.

Figure 11

Insert the rank calculation view data source into SAP BusinessObjects Analysis for Office

In my scenario, there is an input parameter for my calculation view, so the system offers a prompt for its value at run time (Figure 12). So, when the pop-up prompt window opens at run time, I change the current input parameter default value from 3 to 2 and then click the OK button (Figure 12).

Figure 12

Enter the input parameter to determine the N value in Top_N

Next, back in the Excel spreadsheet, drag and drop the Country and Customer Number into the rows. Since the LAND1 partition in the design (in the earlier steps) limited the data to the best two revenue records for each country (note that some only had one record) and thus a total of 13 records in the data set, the output in Figure 13 is consistent with this setting.

Figure 13

The top two records for each country (note some countries have just one entry in the source data)

The Dynamic Partition Elements

Now, for the nuanced discussion of the options. Figure 14 shows the same source data as in Figure 13, but with Country removed from the displayed rows (using the drag-and-drop functionality). In this scenario, the Country (LAND1 field) is a Dynamic Partition Element (Figure 8). When set this way, removing this element from the output has the effect of dropping the partition element completely. This makes the model behave as if the country was not there at all. As you can see in Figure 14, without an effective partition in place, the model shows just the top two records overall.

Figure 14

The end result of removing the dynamically partitioned country field from the displayed rows

Now let’s review this same scenario, but this time, in Figure 8, untick the Dynamic Partition Elements check box. After reactivating the model and refreshing the data in Excel (again with the value of N = 2), the output appears shown in Figure 15.

Figure 15

The output for ranking without the Dynamic Partition Elements

As you can see in Figure 15, in this case the system returns 13 records for the data by customer, as opposed to the two records returned when the Dynamic Partition Elements check box is ticked (Figure 13). This is because when the Dynamic Partition Elements check box is not selected, the dynamic removal of Country from the partition does not happen. The system always returns the data with the 13 records (the top two for each country), and this does not change based on which fields are displayed, as was the case previously when this check box was ticked.

There is no one right answer for how to use these settings, but understanding how they work is critical for you to make decisions to enable your users to have a clear understanding of the output.

In summary, if  the Dynamic Partition Elements check box is unchecked, the output is the top_N for each combination of fields used in the portioning area, no matter what attribute is used to display the data (e.g., in this case, 13 rows of data is used to form the output). On the other hand, if the Dynamic Partition Elements check box is checked, the fields that are displayed do affect the number of records (e.g., showing 13 records when Country is displayed, but only two records show when it’s not).

Ned Falk

Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.

You may contact the author at ned.falk@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.