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.
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.
|Parameter Value||Measure returned|
|1||State Bottle Retail|
|Any other value||Bottles 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.
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’.
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’.
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)’.
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.