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 http://help.sap.com/hana/SAP_HANA_SQL_Reference_en.pdf 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.