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.
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
.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.
.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
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:
- Check next parts about the SQL Analyzer published by Tae Suk Son,
- Make The First Step Towards SAP HANA Query Optimization with openSAP,
- 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.
db/.gitignore file and add the following line.
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
-Vitaliy, aka @Sygyzmundovych
Hi Witalij, thank you for your great job explaining the latest functionality for us!
I have 2 questions:
I have rather unfortunate experience editing code in early web apps from SAP (there was no other way), so I am a bit careful about web IDE's since then. I do also suspect the web needs something better than JS frontends, given how it was evolving, but it's a whole different story.
Cześć, Andrei Dorfman 🙂