One of the most common dashboard feature requests from clients is for the Top N/Bottom N functionality. As we know, this is pretty straightforward to implement. But what if the client wants the Top N or Bottom N ranking based on a KPI that is selected dynamically at runtime? In this blog, we list down the steps for Top N Ranking on dynamically selected KPI in HANA, using a single Measure. The KPI on which the ranking is to be based is passed as Input Parameter.

1. Create a HANA View, drag and drop a Projection, map it to a source with the required KPIs and Dimensions.

Note: There needs to be at least one Dimension with a single member.

2. Create an Input Parameter to receive a numeric value (here 1,2,3 etc.) corresponding to the Measure (KPI), based on which Top N Ranking needs to be done. Setting a default value is optional. Here, we set it to 1.

top-n-ranking-on-dynamically-selected-kpi-hana

 

3. Create a Calculated Column in Projection (here, ‘KPI_VALUE’) and write a Case Statement to check the Input Parameter value and return the corresponding Measure for Top N Ranking.

top-n-ranking-on-dynamically-selected-kpi-hana

Parameter ValueMeasure returned
1State Bottle Retail
2Volume_Sold_(Litres)
Any other valueBottles Sold

 

4. Create an Aggregation on top of the Projection, so that data gets aggregated and has unique values for Top N Ranking. Also, ensure you have converted all the KPIs/Measures to Aggregated Columns.

top-n-ranking-on-dynamically-selected-kpi-hana top-n-ranking-on-dynamically-selected-kpi-hana

 

5. Create a Rank Node on top of the Aggregation Node with the settings below :

  • Choose Top N or Bottom N for the Sort Direction.
  • Set a fixed Threshold value (here, 5) or use an Input parameter for N.
  • In the Order By field, choose the Calculated Column that was created (‘KPI_VALUE’).
  • For Partition By, choose the Dimension with the single member. In this example, we restrict Year to static value ‘2018’.
  • Enable ‘Dynamic Partition Elements’ and ‘Generate Rank Column’.

top-n-ranking-on-dynamically-selected-kpi-hana

 

6. Map this Rank Node to the available Aggregation Node as Input and ensure all the columns including ‘KPI_Value’ & ‘Rank_Column’ are propagated to semantics.

7. Change ‘Rank_Column’ to type Dimension, ‘KPI_VALUE’  to Measure and activate the HANA View. Execute the View and you will be prompted for the Input Parameter.

8. After entering a value for the Input Parameter, drag and drop the Dimension with the single member into Rows, since it was used for partitioning, as also any other required Dimensions (here, ‘Store_Location’). Place ‘KPI_VALUE’ in Columns. The output as seen below displays Top 5 Store Locations by ‘State_Bottle_Retail’. Note that ‘KPI_VALUE’ column displays the same values as ‘State_Bottle_Retail’.

top-n-ranking-on-dynamically-selected-kpi-hana

 

9. Now if you change the Input Parameter value to ‘2’, it displays Top 5 Store Locations by ‘Volume_Sold_(Liters)’. Note that ‘KPI_VALUE’ displays the same values as ‘Volume_Sold_(Liters)’.

top-n-ranking-on-dynamically-selected-kpi-hana

top-n-ranking-on-dynamically-selected-kpi-hana

 

The steps explained above clearly show how we only have to use one single Measure in the Reporting layer (here, ‘KPI_VALUE’) to display Top N data based on selected KPI.

 

Learn more about Visual BI’s SAP HANA Offerings here.

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com