Skip to Content

Design Studio, BICS HANA and Group By Grouping Sets

Hi everyone,

For my first blog, I thought I will show you how Design Studio uses BICS to connect to SAP HANA.

I’ll let you open your Design Studio application 😉 , then just go to Tools > Preferences as per the screenshot below:


Expand Application Design:


Then select Support Settings and set your Log Level to Information:


Click Apply and OK to close the panel.

The logs will be displayed in the Error Log, you better make sure the panel is available or just follow the steps below to make it visible:


You can also view the logs from your analysis-workspace folder and then going to .metadata. My one below:


Now, it’s time for you to add a Data Source from your HANA system and see our logs..

So what do we see? we will use the filter “BICS HANA” and you should get something like this:


We can see information about versions (BICS & HANA) and also the JDBC connection.

We need more! Go back to your preferences and put the log level to Debugging Information.

Create a cross tab  and link it to your data source. This should start to be interesting… Here is what I see with a table containing MONTH, DAY_OF_MONTH and an AMOUNT:


Indeed this is cool information. Let’s take our 1st SQL statement and put into our HANA studio, here is mine:


What can we notice?

  • Group By Grouping Sets
  • With Structured Result
  • With Overview
  • With Subtotal

BICS HANA uses GROUP BY GROUPING SETS. So let’s investigate this one more. We go to our SAP HANA SQL reference guide and what we can read is this:

Generates results with specified multiple groupings of data in a single statement. If no additional options such as BEST and limit, are set the result produced will be the same as a UNION ALL of the aggregation of each specified group.

You can refer to the guide and go at the bottom of the page 233 to see more details about the group by clause.

Let’s run our statement from the studio and because we have a WITH STRUCTURED RESULT, it is creating temporary tables. We can have a look with:


I have 4 temporary tables indented from 0 to 3. The one finishing with 0 is the overview because WITH OVERVIEW was used in the SQL statement.

What do I have in my overview?


Interesting… you can see the meaning behind each table and we can understand from where the limits come from in the other SQL statements from our logs.

Because the statement was using WITH SUBTOTAL, we should get a total for each grouping sets too:


And yes we do!

You can see that some of the sqls generated by bics use LIMIT (only returns a specific number of tuples) and OFFSET (skip a specific number of tuples), you may want to check the result by yourself but if I take the last one from our logs, we can see that we are looking for the total sum:


Hope you have a better understanding now of what comes from the HANA BICS and how to find it.

Do not hesitate to leave comments/questions and I’ll be happy to answer them (if I know!).

And if you didn’t understand anything I will stop writing blogs 😆 if not I might have some other interesting stuff..

Cheers, Alex.

PS: I’m using DS 1.1 SP2 and HANA rev 62.

You must be Logged on to comment or reply to a post.
  • I have a code generated by Design Studio which runs very slow.... looks something like below.

    SELECT col1, col2,  SUM(measure)

    FROM "_SYS_BIC"."xxx/Calcview"


    WITH SUBTOTAL STRUCTURED RESULT WITH OVERVIEW PREFIX '#BICS_11234_' ((("col1","col2", ...) ORDER BY "_col1" ASC , ....

    WITH PARAMETERS ('PLACEHOLDER' = ('$$IP_Param$$','0012345')) ;

    When I run the above code on HANA SQL Console it takes about 4m 50s, but when the same code is run with the line 'WITH SUBTOTAL STRUCTURED RESULT WITH OVERVIEW PREFIX '#BICS_11234_'  commented out (see below), it runs in 6 seconds....

    SELECT col1, col2,  SUM(measure)

    FROM "_SYS_BIC"."xxx/Calcview"



    ((("col1","col2", ...) ORDER BY "_col1" ASC , ...

    WITH PARAMETERS ('PLACEHOLDER' = ('$$IP_Param$$','0012345')) ;

    Any clues?

    • I too am experiencing some "poor" performance situations with design studio/HANA as mentioned above. Does anyone have any insight into how we can eliminate "WITH SUBTOTAL SRUCTURED RESULT" through design studio?

  • Hi Alex,

    Thanks for the valuable info. It has helped me understand the background processing of BICS HANA.

    However coming to my situation. i connect to HANA using Business Objects Platform where HANA connection is defined. When i follow the above steps, in the error log i do not see any SQL generated.

    Could you please help me with that.