Automatic testing of BW queries, anyone? Here you go!

Dear readers

This time I’m not presenting you any of my own work but instead want to advertise what my CSA colleague Tobias Kaufmann just published here on SCN:

Automatic Test of Queries with RS Trace Tool. (really well done Tobias!)

Given how important the correctness and well working (concerning results and performance) of SAP BW queries are to many users, in my eyes this is one of the most important and most underused features around.

With the trace tool environment (transaction RSTT) you are able to set up test scenarios for the key queries of your BW solution and re-execute and evaluate them whenever this is required.

Just installed a new revision of SAP HANA?

Run the tests and see if everything still works as it should.

Upgraded application server hardware and want to know if the performance improved for any of your queries?

After running the tests you’ll know.

Imported a new SAP note correction or SP stack?

After executing the tests you’ll be sure whether or not this broke any of your business reports.

Although this does not eliminate the need for end-user acceptance tests or the final approval of the report users after a critical change in the system, it does make testing your BW solution a lot easier, cheaper and more reliable.

It may not be completely effortless to setup a whole test battery that covers all of the important queries, but it surely pays out to do so.

In fact, SAP BW development uses this exact functionality for testing changes in the coding.

Personally, I totally like this thing and wish we would have something similar for queries and models that are developed right in SAP HANA.

So, if you’re running SAP BW powered by HANA don’t miss out on this feature – it’s already present in your system and only waits to be employed.

Cheers, Lars

HANA quick note – checking my connections and using them securely …

based on revision 48 (SPS 5) of SAP HANA Studio and Database

 

I just browsed throught the updated SAP HANA Developer Guide, SPS 05, Ver. 1.1, 21.12.2012 (really that date? not even 12.12.12? 🙂 ) and found something pretty nice in there for ODBC and JDBC users.

By the way: meanwhile (as of SPS 5) three of the files from the HANA documentation website have been included into the local eclipse help.

This means:no more back and forth between the PDF and the HANA studio as well as ‘F1’ help in the studio.

As you can see, the SAP HANA Administration Guide, the SAP HANA Developer Guide and the SAP HANA SQLScript Reference are now ‘on board‘.

While I’ve no idea on when the SQL reference will follow, I still use the web browser integration until then.

Let’s see the nuggets in there!

A common task is to check the database connectivity via ODBC after a user workstation has been set up.

This is easy to do with the SAP HANA client program odbcreg.exe (to be found in the installation folder of the SAP HANA client software).

By using the command odbcreg.exe -t hdbodbc the driver is loaded and a logon dialog window is opened.

A nice giveaway is that the exact driver software version information can be found in the title bar of this dialog window.

You can now either type in the usual SAP HANA logon credentials (hostname, port number, username and password) or, and this is really nice,  you can use an entry from the secure HDBUSERSTORE.

I’ve mentioned the HDBUSERSTORE earlier and really like how it makes getting access to the SAP HANA server seamless.

So, instead of filling out the logon form you can just enter @<KEY> to have ODBC use the HDBUSERSTORE-entry named <KEY>.

In my example I used the entry HANLARS to connect to my test instance HAN.

Once odbcreg.exe got the logon data it tries to connect to the SAP HANA instance and runs a command like

   SELECT now() FROM dummy;

(for which you see the output as well in the command line window).

As no special authorizations are required for this command, running it means:

if you get a result, that’s good. You’re in 🙂 !

or

if you don’t get a result, well, don’t go looking for some “missing” authorizations on SAP HANA level.

In that case, the client didn’t even get through to the SAP HANA server.

By now you may wonder, why the heck I’m so happy about this HDBUSERSTORE feature in ODBC.

After all, most often people don’t make connection tests, but need to logon to the database to actually do something with it.

Something you would do with ODBC… like connecting Excel or R or PYTHON or…

Well, guess what, this way of providing a link to the HDBUSERSTORE also works with any ODBC connection string!

As an example I use the same HDBUSERSTORE entry in R Studio, the IDE for R development, to re-run one of Blags test cases:

Isn’t that nice?

No need to type in the server hostname and port any more.

No insecure coding due to hard coded username/password information any longer.

PLUS: you can use the very same code without change for multiple users and workstations.

All you have to do is to setup the HDBUSERSTORE entry you refer to for the user that should run your code.

As promised above, there’s also something in here for JDBC users.

Although unfortunately JDBC cannot make use of the HDBUSERSTORE by its very design, the JDBC driver .jar-file actually can do more than one might expect at first.

Besides printing out its own version number, the JDBC driver also has a little connection test on board, very similar to the ODBC driver.

By running

C:\Program Files\sap\hdbclient>java -jar ngdbc.jar –version

package com.sap.db.jdbc, SAP In-Memory Database JDBC Driver, SAP AG, 1.00.48.Build 0372847-1510 (Commit-Hash: not set)

we get the exact version information.

And by running

C:\Program Files\sap\hdbclient>java -jar ngdbc.jar -n <hostname>:30015 -u <username>,<password>

Now I’m successfully connected

|            |
————–
| 42         |

we not only get a connection test, but also the Answer to the Ultimate Question of Life, The Universe, and Everything

AWESOME, I say 🙂

Running up that hill – a quick note on improving SAP HANA

based on revision 48 (SPS 5) of SAP HANA Studio and Database

Maybe I’m a grouch about our software design from time to time.

Maybe I’ve been working in support for too long and got the ‘problem glasses’ still on.

I start using programs and features and rather often it doesn’t take too long that I think ‘oh, now that is awkward‘ or ‘hope that this won’t make it into the final release‘ (as I’m no developer/alpha/beta-tester, usually it DID make it into the release though…).

Important, to me at least, is to provide feedback to our development.

So, I do this, come up with alternatives, that I think are better or that I like better and then – I wait.

Sometimes the ideas get implemented, very often a more general (better) solution is found and implemented, sometimes (rather seldom) an idea is refused for some reason.

Key here is to not get frustrated over time.

For sure there are tons of ways to deal with that – one thing that works for me is to actually see how things are improved and made better.

Two examples for that:

In early revisions of HANA Studio (I just crammed out rev. 28 to make the screenshots) there was an option to drop users.

In the navigation tree you looked up the user, right-clicked on it to get the context menu – ‘DELETE’ and then you got this:

You might ask ‘so, what, what’s the problem with this?’.

Well, if you just click [OK] in this screen,  the user and all objects the user owns are gone and can’t be brought back without recovery of the whole database.

Due to the way HANA deals with SCHEMA and OWNERSHIP (regardless of the schema you create an object in, the owner is always the user that creates the object).

So, if you want to, say, get rid of one of the development user accounts that created a lot of code and tables and views in your product schema and you press the [OK] button above – these tables and views and procedures would be gone as well.

That’s the problem. Bad pitfall.

Luckily this behavior had been improved in current revisions (SPS 5).

Now, the default selection is not to automatically drop all owned objects.

If you accidentally just click on [OK] now, the worst thing that could happen is that you loose a user account and the priviliges you set up for it. Should be easy to repair. No real harm done.

However, when you really think you want to get rid of this user account including all the owned stuff, you now also get an overview about what exactly you’re going to drop.

Much better user interaction, much more ‘safety’ for the user.

I like that a lot.

Second example

Have you ever wanted to know where the heck SAP HANA Studio takes its information from?

I mean, you get all sorts of data displayed and sometimes this could be something you might want to use as well in your coding.

I had this rather often.

Sadly, the only option for me to figure out the SQL that was triggered by HANA Studio was the JDBC trace.

While I’m now pretty well accustomed with this trace, it simply was annoying to have to do this.

Nowadays, SAP HANA Studio is so nice to tell you.

By clicking on this fancy ℹ View log… – Button you are provided with a general SQL log that allows you to inspect the SQL, the runtime and the number of rows returned.

Small, available and useful – love it!

 

If there are any real advantages of an agile release model like the one we currently have with HANA, then it’s exaclty this:

the very short time to improvements.

What makes me even happier is the fact, that the above really are just examples.

In fact there are many smaller and bigger improvements that had been build into HANA software and documentation.

Not sure about you, but I like seeing things getting better.