SAP HANA Data Modeling
Utilizing Input Parameters and Filter Expressions for Fewer, more Efficient, Views
As any data modeler knows, there are many ways to achieve a desired outcome when it comes to modeling. Therefore, one of the goals of modeling should be to find the most efficient way to meet the requirements in regards to both performance and future maintenance. SAP HANA has many features to aid the modeler in achieving these efficiencies, one of which is the input parameter. In this discussion I go through an example inspired by a recent project I worked on to demonstrate how we used input parameters and filter expressions to enable efficiency in the design. The design enabled a fewer number of information views to meet project requirements and ultimately cutting down on future maintenance while improving overall dashboard performance.
One of the requirements was to create datasets of top ten performing markets for different time dimensions depending on the user selection (current month, year to date, current month vs prior month, and year to date vs prior year to date); another feature that was later requested was to have the ability to switch between top 10 and bottom 10. The image below shows a visualization of the dataset to fulfill these requirements.
When modeling the data in HANA there were a number of methods we could have used to satisfy the requirements. We knew we were going to utilize the HANA calculation view Rank node to perform the top and bottom rankings, because it’s functionality is built into HANA and performs well with large data sets, although we wanted to avoid having to create separate views for the top CM, bottom CM, top YTD, bottom YTD, top vs YTD, etc. The way we achieved that was using input parameters.
By creating one input parameter for the sort direction and one for the time dimension measure, we were able to create one simple view for the chart above. Below are the steps we took to create this calculation view.
First, create the input parameter for the time measure, making sure that the Name in the list of values matches the name of the different measures that the Rank node will sort by (CM, YTD, vsYTD, and vsPM):
Then, in the projection node of the data source, create a calculated measure using the input parameter as in the image below. This calculated measure will now contain the measure CM, YTD, vsYTD, or vsPM depending on the user choice in the input parameter.
Next, create the second input parameter which differentiates between top or bottom rankings:
Next, set up the calculation view with the projection node of the data source being split into two different Rank Nodes based on the IP_SORT_IND input parameter. This is achieved using two more projection nodes and their filter expressions, the image below demonstrates this split along with the filter expression used for the bottom rank path:
Be sure, when setting up the two Rank nodes, that the RANK_MEASURE calculated measure is used in the “Order By” selection so the node ranks the measure based on the input parameter. Also, select the Generate Rank Column box to create a rank dimension to be used by the chart. The “Sort Direction” selection depends on whether it is the top or bottom ranking node.
Complete the calculation view using a Union node to bring the two Rank nodes together, the finished calculation view looks like this:
When querying the model simply use the RANK_COLUMN dimension and RANK_MEASURE calculated measure. Depending on the two input parameter selections, you will see different results:
Utilizing the different HANA tools together (calculated measure, input parameters, filter expressions, and Rank nodes), we produced one clean view that performs well and is much easier to maintain than some earlier versions of our view (or 8 different views which is one way we thought of handling this).
Below is a screenshot of an earlier version of the calculation view that we created prior to us using the IP_MEASURE_IND input parameter in a calculated measure. As you can see, that prior to refactoring, we had to create eight different Rank nodes. This view fulfilled the same requirements, but performed slower and would require much more work in any future maintenance than our final view.