Technical Articles
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:
- 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.
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
Hi Witalij, thank you for your great job explaining the latest functionality for us!
I have 2 questions:
Best Wishes!
Andrei
P.S.
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 🙂
Best regards,
-Witalij