Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Cockpit and in this video we will
talk about the SAP HANA Database Explorer and some of its new features.
This video has been recorded on release SAP HANA cockpit 2.0 SP 07 from August 2018.
For earlier or later releases, please check the playlist on the SAP HANA Academy.
Hi, I am Denys van Kempen.
Most of you will be familiar already with the Database Explorer, it is a separate XS
Advanced application, which we can access from both the SAP Web IDE for SAP HANA, the
development tool, and from the SAP HANA cockpit, our focus here.
On the SAP HANA Cockpit Landscape Home page, we find two tiles (or cards) that will open
the Database Explorer for us: Browse Database Objects and Execute SQL.
It is a different app, different port, and to access the SQL Console, you would select
a database and then select Open SQL Console (Ctrl+Alt+C).
There are a number of keyboard shortcuts.
OK —very briefly, we have our catalog browser where you can view the definitions of all
the different catalog objects (tables, views, stored procedures, functions, synonyms, and
many, many more), and you can also view the content (the data) of your tables and views,
or synonyms.
Let’s take the monitoring view M_DATABASES, for example.
It is a synonym in the PUBLIC schema for view SYS.M_DATABASES.
Details - will inform us about the different columns, data type, nulls and default values;
the CREATE statement, and Open Data, will query the table or view.
There is an MDX console, where you can create and run MDX queries.
SQL for multidimensional OLAP-type queries.
There is a SQL debugger for when you are working with procedures in HDI containers.
You can monitor background activities.
There is an extensive Search interface, to find the objects you are looking for.
And here we can set our workspace preferences: data preview, code completion, and for the
console, the theme, auto-save.
Stuff like that.
I should also mention features like Save to Library, so you can create a library of SQL
statements that you often use.
Code Formatting to prettify your code.
We can run statements in the background.
We can run statements on multiple databases, and most important, we have a built-in SQL
Analyzer (Ctrl+Shift+X), which we can call from the SQL Console.
This will give us statistics about compile time, execution time, operators, the plan
graph, table access, time line.
All very, very useful to analyze your SQL code.
OK —What’s New?
Well, for starters, the remote source editor has been updated to support remote sources
for SAP HANA smart data access.
Here we have the screen where we can edit existing data sources and search for objects.
We can create virtual objects, so the metadata of the object (the table, the view) will be
recreated in the current database.
This will speed up things.
We can edit our data source here.
Maybe change the DML mode from readonly to readwrite, change the technical user or password,
and we can also, from the context menu, a right-click for most of us, create a New Remote
Source.
You give it a name, select the Adapter name, the list is quite long: ASE, IQ, MaxDB, Vora,
all SAP; Hadoop, Spark SQL (BigData), RServe (Predictive Analytics), Oracle, Microsoft
SQL Server, DB2, the usual suspects, and any other non-specific ODBC data source.
The properties change, depending on the source you select.
OK- Remote Data Sources.
The Import Data wizard has been updated in the SP 07 release, as well.
This is to import data from the local file system into the database.
Let’s take a look.
First we need to point to the file to import (comma-separated or Microsoft Excel).
Here is my sample CSV file with some different data types.
The file will be uploaded to the server.
Next we can either create a new table for the data or add to an existing table.
So, unlike the IMPORT statement, which is create or replace, here we can also append.
Then, we need to do our table mapping: Step 2.
Well, column A are integers, B is VARCHAR of length 10 —let me resize this—, column
C is of type DATE, D is time, and E is DECIMAL.
OK —In Step 3, then, we can define the error handling.
In case of an error, how do you want the system to handle it?
I will stick with save successful.
Then, here we have our import summary with the import properties, the table mapping,
error handling, and a data preview.
Looks good.
Import into Database.
Well, its only 4 rows.
No errors.
Let’s double check.
Open the catalog, expand the schema, select Tables, TEST, Open Data, and here we have
the result set.
That’s the new Import Data wizard.
Another new feature is the capability to save SQL plans to a file, a PLV file.
You can do this from different performance monitoring tools: expensive statements, plan
stability, SQL Analyzer and Statement Hints, so, not only the Database Explorer.
We cover the performance tools in another series, so I will keep it brief here.
I have entered some SQL into the SQL Console.
If we click the green arrow button for execution and hold it for a second, we get a context
menu, and here I can choose, Analyze SQL or Analyze SQL and Save Plan.
Let’s do a Save Plan.
We are prompted to provide the file name, the prefix, the suffix, the extension, is
PLV, and will be saved to a location on the HANA server.
Analyze & Save.
OK-we have seen this before, the plan analyzer, with the operators and statistics, time line,
tables in use, and so on.
The Plan Graph.
For a select * query, we are not getting anything fancy, of course, this is just to show you
that you can save the plan in SP 07, which is new.
And then, if go the SAP HANA cockpit System Overview page, —so, we are no longer in
the Database Explorer, we are back in the cockpit —, and to Saved Plans, in the Performance
section,
We can load the PLV from the HANA server and then view the plan in the SQL Analyzer from
this location.
This way, the developer and the DBA can easily share expertise and insight on how to tackle
any SQL performance issues on queries.
That’s the idea of Saving Plans.
OK — Then we also have Data Provisioning Agent for Smart Data Integration, another
SAP HANA option.
We can monitor the SDI Agent from the cockpit overview page but new in SP 07 is that we
can now also do this from the database explorer.
When you expand the Catalog, the Data Provisioning Agents are listed, and you can select Show
Data Provisioning Agents from the context menu, for example, to get the last connected
timestamp, to get used memory statistics, and so on.
No need to switch to another tool, it is integrated in the Database Explorer.
OK —the SAP HANA Database Explorer is documented in a separate guide, here in the Administration
section on the SAP Help Portal on help.sap.com.
Here you will find what’s new, a chapter on Getting Started, with specific information
about Security, error analysis and diagnosis, querying, performance analysis, and data import/export.
That’s it —thanks for watching.
You can find more video tutorials on our YouTube channel.
If you would like to be informed about new video tutorials, please subscribe to our channel.
You can connect with us on LinkedIn or follow us on Twitter, as well for updates, and if
you are watching this video on YouTube, do not hesitate to leave your comments to the
video page and, if you like, give us your vote on this video.
Thank you for watching.