Skip to Content

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,

  • World
    • Continent
      • Country
        • Region
          • City

It can be as granular as you like depending on the dataset.

For the example shown in this blog, the hierarchy will contain,

  • Continent
    • Country
      • Region
        • City

In HANA, we have created five tables,

  • Continent
  • Country
  • State
  • City
  • Customer_Location

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.

/wp-content/uploads/2012/08/desc_mapp_128950.png

Based on these tables, the Attribute View looks as follows.

Attribute View

/wp-content/uploads/2012/08/1_128505.png

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:

Connection

/wp-content/uploads/2012/08/1a_128516.png

Data Foundation

Next, we will create the Data Foundation.  Browse to the _SYS_BIC schema, find your Attribute View, and add it to the Data Foundation.

/wp-content/uploads/2012/08/2_128506.png

Business Layer

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”,

/wp-content/uploads/2012/08/3_128517.png

Then click on “List of values based on custom hierarchy” and hit “Add Dimension”,

/wp-content/uploads/2012/08/4_128518.png

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.

/wp-content/uploads/2012/08/6_128522.png

Next, for each of the 4 dimensions of the hierarchy we need to map the List of Values to each object:

/wp-content/uploads/2012/08/7_128523.png

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,

/wp-content/uploads/2012/08/8_128525.png

The result is:

/wp-content/uploads/2012/08/9_128536.png

Index Awareness

In the Business Layer currently we don’t currently don’t have Index Awareness set, thus the query looks like,

/wp-content/uploads/2012/08/9a_128987.png

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:

/wp-content/uploads/2012/08/10_128988.png

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. 

/wp-content/uploads/2012/08/11_128989.png

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.

/wp-content/uploads/2012/08/12_128990.png

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.

/wp-content/uploads/2012/08/13_128991.png

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply