Modeling in SAP HANA Cloud (part 4): SQL Analyzer

Recently Tae Suk Son announced that SQL Analyzer is now available in Business Application Studio.

Let have a look at it using the SAP Business Technology Platform trial. As an example, I will use the cube with the Inventory demo data from my previous post.

Activate SAP HANA Performance Tools

SAP HANA Performance Tools are now available among additional extensions for SAP HANA Native Application Dev Space in the Business Application Studio (I’ll call it BAS for short). It can be activated for the existing dev spaces too, like TPCDS in my example.

Once activated, you should see an icon of the SQL Analyzer tool in the studio…

…and among built-in extensins.

The version of the extension is checked every time your dev space is started and updated extensions are installed if available.

Collect plan graph data

The SQL Analyzer extension is still being actively developed. One current limitation of it in the BAS is the lack of a live connection to analyze query plans and performance. We need to collect this data into a .plv file and import it into the Business Application Studio for further analysis.

Here is an example of doing that.

Generate the .plv file

Let’s go to the Database Explorer and open data for the Inventory.

But instead of executing this data preview let’s click on an icon opening SQL Console.

We can modify the generated SELECT statement to whatever suites our needs. An example is below.

SELECT TOP 100
	"I_CATEGORY",
	SUM("INV_QUANTITY_ON_HAND") AS "INV_QUANTITY_ON_HAND"
FROM "TPCDS_HDI_DB_1"."vital.tpcds::Inventory"
WHERE (("D_DATE" = '1998-01-01'))
GROUP BY "I_CATEGORY"

And next pick Analyze -> Generate SQL Analyzer Plan File.

Provide the file name prefix (like mytest here), and note where the file is saved on the server, i.e. in the subfolder other.

Download the .plv file from the database server

To download the file you need to switch the Cockpit database with the DBADMIN user authentication.

Find the subfolder other in Database Diagnostic Files, and then the earlier generated .plv file in it.

Right-click and download the file to your local computer.

Upload the .plv file to SAP Business Application Studio

Let’s go back to the BAS and create a subfolder db/plv to store plan files.

Next upload the file downloaded using Database Explorer…

…which is an XML file, if you preview its content in the editor.

But we do not need to read this XML file, as we got the SQL Analyzer for that, remember? 😉

Open the file in SQL Analyzer

Switch to the SQL Analyzer extension and open the .plv file.

An overview of the query plan will be opened…

…and you can keep analyzing further all the details of the execution plan.

But digging into these details is going outside of the scope of this post.

Further learning content

Should you want to learn more:

1. Check next parts about the SQL Analyzer published by Tae Suk Son,
2. Make The First Step Towards SAP HANA Query Optimization with openSAP,
3. Review SAP HANA Database Performance Guide for Developers.

Before we are done…

…one more thing to do is to exclude the plv directory from our Git repository.

The folder and a file inside have been marked as unstaged by BAS editor now. But we do not need to store these files in the repository.

Open the db/.gitignore file and add the following line.

plv/**

Now db/plv is not marked as unstaged, but db/.gitignore is marked as modified.

Let’s commit these changes.

git add --all
git commit -am "Exclude plv in gitignore"
git hist

Happy analyzing,
-Vitaliy, aka @Sygyzmundovych