Tag Archives: sap hana studio

The 7th Niceness of new toys’n’tools

If you’re not living behind some red moon and you’re reading this blog, you will likely be aware that SAP HANA SPS 10 had just been released.

With it also the SAP HANA Studio had been improved and extended.

Since I personally like eye-candy and enjoy exploring new features when they come with a button to click, I have installed the new version on my laptop and played with it; against a SPS 9 SAP HANA database!

That’s right – everything I describe here works just well on a Rev. 96, which means you can use these features even without upgrading your server.

(This is one of the benefits of the de-coupling of SAP HANA Studio releases and SAP HANA Server releases – indicated by the different version numbers.)

So here we go with the nice things I found in SAP HANA Studio 2.1.4.

Drop and drop of design time objects

Since basically ever I wished I could just drag and drop information models into the SQL editor so that I could execute SELECT statements against them.

No idea what took the dev-colleagues so long, but now that’s actually possible.

Simply dragging the design time object (you know, the object as it appears under the Content folder) over into the SQL editor and the corresponding runtime object name (yes, the _SYS_BIC.”<package_name>… <object_name>”) is placed right at your cursor.

Alternatively, there is a context menu entry “Copy Column View Name” that puts the name into the clipboard.

Nice!

SELECT statement generation incl. input parameters

Similar to the above, you might have stumbled over models with input parameters.

The usual workaround to use the SQL generated by the data preview is alright, but a bit clumsy.

With SAP HANA Studio 2.1.4 you can now simply right-click and select “Generate Select SQL“.

After a few moments of checking the metadata, you get a new SQL editor with the basic SELECT SQL:

 

As you can tell, someone automatically pressed the “Format” button as well.

Nice!

Compare two plans in PlanViz

More than once I got the question on how to perform comparisons between two PlanViz traces and the usual answer had to be: you put the two PlanViz editors next to each other and compare the Plan OPerator boxes one by one. This is of course tedious.

Now, there is the (once again – anyone remember Show me the timelines, baby! ? 😉 ) well hidden feature to compare two plans.

I found it in the context menu of an executed PlanViz by clicking on “Compare With Another Plan…” – not sure if there is another way to this feature.

Next you’re greeted by a little selection dialogue window, that I found rather self-explanatory:

Seems to be straight forward – so I selected one of the other open PlanViz displays by clicking “Other visualized plan“.

DISCLA(I)MER: for the next step, you should have a wide monitor. Or two monitors. Wide ones. Really! (It’s better for you anyhow, if you use it right.)

See? I told you so!

As I haven’t really worked with the feature there’s little I can report beyond that it’s there.

Go play around yourself!

In my books, this feature really is:

Nice!  🙂

Immediate execution for PlanViz

Alright, you have your SQL statement. You want the PlanViz for it.

Up to now you were always confronted with some “Prepare” step and a graphical version of the Explain Plan.

How useful…not!” you might have thought (so did I most of the times 😉 ).

No more of that!

Modern days SAP HANA Studio comes with this shortcut in the context menu:

Nice!

Identify plan operations in PlanViz by name

Ok, the following is not actually a new feature, but I have just found this and decided to put it in this anyhow.

A major difficulty in analysing PlanViz outputs often is to map the model design to the actual execution.

This is inherent to the calculation view instantiation process but you can make it a bit easier for you by naming the nodes in your model with your own names.

To understand what I mean, just look at those two models, that do pretty much the same thing:

Default node names Custom node names

At this point there is already a benefit as the intended function for the nodes is much clearer.

Now, when the model gets executed and a PlanViz is created, we can find those node names in several places:

1. In the POP node details:

2. In the “Tables Used” list:

3. In the “Operator List”:

You will still find a lot of things that you cannot easily match back to the model, but with the custom node names, you should be able to find some anchors in the PlanViz.

Not sure about you, but I think that’s Nice!.

Comments in information models

One of the things you wish you had when working with complex models, that you or someone else created a while ago typically is: a documentation.

  • Why, just why is this condition there?
  • What exactly is the idea behind this calculated column?
  • Where should this data go?

Wish no more!

With the SPS 10 SAP HANA Studio you can now add comments/notes to virtually anything in the model.

Click the little “Maintain Comment” button in the model, when hovering over a node…

… then enter some amazingly useful comments!

Once you close the comments editor you’ll see a little indicator at the node-icon to show that there’s a note for you:

Let’s say it together:

Nice!

Data Lineage in Modeler

When working with the modeler it can get quite difficult to understand where the data of a certain output column actually comes from.

To help with that, there is now the Data Lineage feature available.

The red borders around the model nodes indicate that the data is “touched in there” and the little annotation boxes show you the column name per node.

Nice!

Alright, given that I only had a few hours to look into it, I’m personally quite happy about the result here.

Seven “NICE” for the current major release of SAP HANA Studio (and there are more, like the on-demand loading of the Contents sub-folders, but I couldn’t really capture this with a screen shot 😀 ).

 

There you go, now you know!

  • Lars

Community criss cross puzzle posting in multiple locales

SAP loves to make a point of eating its own dog food and so lowly SAP clerks like yours-truly are offered the experience to participate in multiple user communities be it SCN or one of the many SAP JAM groups.

In one of the SAP internal groups, I recently answered a question, that I believe will be interesting to SCN readers, too.

So this is it:

“… a user wants to have the client display in English format.

Decimal values are displayed in German format “123,5”, but they want to have it the English format “123.5” – ‘decimal point is a point’.

[…]

I set the properties for the system in HANA Studio (or Eclipse) with Properties –> Additional Properties –> Locale “English”.

I also tried to set the User Parameter ‘LOCALE’ for the user to “EN” or “EN-EN”, also without effect.

Has anybody an idea, which client setting has to be chosen to get decimal values displayed in English format? …

To answer this question there are a couple of things to be aware of.

Pieces of the puzzle

Piece 1: SAP HANA Studio has a preference setting to switch on or off result data formatting.

Piece 2: SAP HANA Studio is an Eclipse-based product.

Piece 3: Eclipse is written in JAVA.

Piece 4: JAVA provides a rich set of API functions to format data, exposed via Formatter objects.

Piece 5: The Java Formatter objects are using so-called Locales, which are objects that bundle localization specific settings.

Putting the pieces together

Whether or not the SAP HANA studio formats the data in the SQL result set grid depends on the setting SAP HANA -> Runtime -> Result -> Format values.

Now, having activated the formatting, we can see that e.g. numeric data with decimal places gets formatted.

When I am logged on with a German Windows language setting, I will see that the number 1234.56 will be printed out as ‘1.234,56’ – as the thousands delimiter for Germany is the dot ‘.’ and the decimal separator is the comma ‘,’.

With English language settings, this would have been the other way round.

If you ask yourself “Why would I ever switch this off?“, then it’s probably good to know that formatting lots of data is not a trivial task and can take up a considerable time when printing your results. That might slow down your development cycle of re-running queries considerably…

“Stop talking, show us how it looks like!”

… I hear you say, so here you go:

Formatting Enabled, German locale settings

Behold and compare to the output with formatting switched off:

Formatting disabled, output as raw as possible in SAP HANA Studio

SAP HANA Studio provides a place where one can specify the session language for a user connection and it is even called ‘Locale‘:

Irritatingly this setting only affects the SAP HANA behaviour within a SAP HANA DB session. That is, text joins and language filters use that setting to return language-dependent data.

An important part of understanding why this setting doesn’t help here is to understand that the whole business of printing data out to the screen is done by the client application and not by the database server. This includes formatting the output.

Technically speaking, numbers don’t have a format to the database.

In the database, numbers have a scale and a number of significant fractional digits.

If and how those are printed out is a different matter – just like your good old TI-30 would calculate with 11 significant digits internally, while displaying 8 of them at max.

Having said that, I would agree with the notion that when we have a setting called LOCALE this should either change the ‘whole experience’ or there should be more specific setting options. Something like ‘output locale’ or so… (like here API: Internationalization).

Anyhow, the point is: the LOCALE setting with the connection doesn’t fix our formatting requirement.

Fiddling with the invisible pieces

So, we know that the data gets formatted via a JAVA Formatter, but apparently, the LOCALE setting doesn’t set this thing up for us.

Checking the JAVA SDK documentation (LOCALE and FORMATTER again) the avid reader figures out:

If the application doesn’t specify the LOCALE for the FORMATTER it uses the LOCALE that is currently active in the JAVA VM.

As it turns out, the default JVM behaviour is to try to figure out the locale setting of the operating system user that is running the JVM.

But there are options to overwrite this, as we find here.

Working the puzzle from the edges

So, we can specify command line arguments when starting a JVM to set locale parameters:

-Duser.language=EN , -Duser.country=UK and -Duser.variant=EN

This is all great, but how to start the SAP HANA Eclipse with such parameters?

Putting in the final pieces

This last piece is actually easy.

Eclipse uses a parameter file called eclipse.ini located in the folder where you find the Eclipse executable.

SAP HANA Studio simply uses a renamed version of those files, so the parameter file we’re looking for is called hdbstudio.ini just as the executable is called hdbstudio.exe.


-startup
plugins/org.eclipse.equinox.launcher_1.3.0.v20140415-2008.jar
--launcher.library
plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.200.v20150204-1316
-vm
plugins/com.sap.ide.sapjvm.jre.win32.x86_64_81.0.0/jre/bin
--launcher.XXMaxPermSize
512m
--launcher.defaultAction
openFile
--launcher.appendVmargs
-vmargs
-Xmx1024m
-Dfile.encoding=UTF-8
-Xms256m
-XX:MaxPermSize=512m
-XX:+HeapDumpOnOutOfMemoryError

Looking into this file we find that it contains a part that starts with -vmargs.

This section allows specifying parameters for the JVM used by Eclipse respectively SAP HANA Studio.

Putting in the desired locale parameters like this


[...]
-vmargs
-Xmx1024m
-Dfile.encoding=UTF-8
-Duser.language=en
-Duser.country=UK
-Duser.variant=EN
-Xms256m
-XX:MaxPermSize=512m
-XX:+HeapDumpOnOutOfMemoryError

will make provide the setting we are looking for.

Be aware that in order to be able to save this file on MS Windows you will need to have elevated privileges.

The final picture

To set the new settings active it is required to restart SAP HANA Studio.

We can see that the formatting option now uses the specified locale.

Formatting Enabled, English locale settings

Unfortunately, the setting is active for all connections used by this SAP HANA Studio.

So we don’t have a proper user specific setting for the data formatting – a workaround at best.

This puts SAP HANA Studio clearly into not being a data consumer/end-user tool.

It’s an administration and development tool, just like it had been positioned since day one.

Having the picture completed, we can take a last look at our puzzle and pack it away again.

There you go; now you know.

Cheers,

Lars

p.s.

An alternative approach would have been to set the JVM arguments via environment variables. This approach would allow to easily create several different startup scripts that first set the parameter and then start SAP HANA Studio.
If you got the gist of this post, you shouldn’t have issue plugging this together.