This blog will detail how to create a prompt with hierarchical display with HANA using the Information Design Tool (IDT). First, let’s take a look at hierarchy. For example, a geographical hierarchy might consist of something like,
It can be as granular as you like depending on the dataset.
For the example shown in this blog, the hierarchy will contain,
In HANA, we have created five tables,
Each of the geographic tables has an ID and a Key (a key/value pair), this is what we will map later in IDT. In HANA Studio we have setup description mapping. For example for the CONTINENT_ID we have mapped it to CONTINENT_NAME, as seen below.
Based on these tables, the Attribute View looks as follows.
Next, we need to create the universe. The first thing we will do is to create a new project and a new connection. To create a new project use the file menu and then right click on the folder to create a new connection, you will end up with:
Next, we will create the Data Foundation. Browse to the _SYS_BIC schema, find your Attribute View, and add it to the Data Foundation.
Next we need to create the Business Layer. In the Business Layer click on “Parameters and Lists of Values” and then click on the add sign for “List of values based on business layer objects”,
Then click on “List of values based on custom hierarchy” and hit “Add Dimension”,
In the “Select a Dimension” window, we want to setup our hierarchy. Since, ours is a geographic one, it will look like as below. Thelowest level being City and the top level being continent.
Next, for each of the 4 dimensions of the hierarchy we need to map the List of Values to each object:
Test the Hierarchy
We will now create a query to verify the hierarchy is working correctly. For example, if we want to find all the customers in Europe we would select,
The result is:
In the Business Layer currently we don’t currently don’t have Index Awareness set, thus the query looks like,
You can see that concatenation is being done, so the query is not as efficient as it can be as it may be slow depending on data volumes. We can make the query more efficient, by setting up Index Awareness. Index Awareness will need to be setup for every dimension that is in the hierarchy.
Now here is the same query with Index Awareness set:
The concatenation has been removed with the indexed values from our LOV objects. Also, you may notice that your SQL has “MAX” in the select, the reason for that is stated in this SAP note: 1632683 – BI 4.0: “MAX” function added to the primary key’s SQL.
Prompt: Equal To vs In List
When a query has a query filter you can select from a variety of operators. When select the “Equal To” operator you are only allowed to select one value and it has to be from the lowest level. In the example below, we are forced to select a city from the LOV.
Changing the “Equal To” to “In List”, will let us select multiple values and from any level. As seen below, we can select a continent and a city making “In List” a lot more versatile.