Skip to Content

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:

1ST_PIC_PREFERENCES.jpg

Expand Application Design:

2ND_PIC_EXPAND.jpg

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

3RD_INFORMATION.jpg

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:

4RD_ERROR_LOG.jpg

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

4RD_ERROR_LOG_IN_FILE.jpg

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:

5TH_BICS_HANA_INFO.jpg

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:

6TH_BICS_HANA_DEBUGGING.jpg

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

7TH_STUDIO_SQL.jpg

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:

8TH_TEMPORARY_TABLES.jpg

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?

9TH_OVERVIEW.jpg

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:

10TH_SUBTOTAL.jpg

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:

11TH_GRAND_TOTAL.jpg

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.

To report this post you need to login first.

11 Comments

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

  1. Sree Kamatgi

    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”

    GROUP BY GROUPING SETS

    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”

    GROUP BY GROUPING SETS

    –WITH SUBTOTAL STRUCTURED RESULT WITH OVERVIEW PREFIX ‘#BICS_11234_’  (this line is commented)

    (((“col1″,”col2”, …) ORDER BY “_col1” ASC , …

    WITH PARAMETERS (‘PLACEHOLDER’ = (‘$$IP_Param$$’,’0012345′)) ;

    Any clues?

    (0) 
    1. Dwayne Winters

      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?

      (0) 
  2. Sujith PN

    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.

    Thanks,

    Sujith.

    (0) 
        1. Dwayne Winters

          Interesting – I just tried and no longer see the SQL statements in the log. You can try to get the statement from the SQL plan cache on Hana (SYS.M_SQL_PLAN_CACHE) instead…

          (0) 
      1. Praveen Kishen Pandian R

        Hi Alex , I am following the same to generate SQL statement from Design Studio tool , but I end up not generating SQL statements. Do we need JDBC connection to see SQL Statement ?

        Currently I am using ODBC connection to connect to HANA.

         

        Thanks,

        Praveen

        (0) 

Leave a Reply