HANA Tips & Tricks: issue #1 – Hacking information views

At Just-BI we just launched a knowledge-sharing initiative where our consultants and developers discuss any issues and share tips & tricks concerning SAP HANA development. Our monthly meetings are company internal, but we decided to share any items that might be interesting to other SAP HANA professionals in public. Since SCN is already the go-to hub for all things HANA, we felt that this is the most appropriate place to do this.

So, here it is – our first post!

We plan to publish one immediately following our monthly meetings, and we will tag it using the hanatipsandtricks tag. We hope that our tips, tricks and discussions are useful to you. Feel free to chime in, or to share your insights. Thanks in advance. – we welcome your interest and participation!

Editing XML source of Information Views

Just-Glenn kicked off the meeting with a very useful and powerful tip: editing the XML source code of SAP HANA information views.

Information views (Analytical-, Attribute- and Calculation Views) are typically created and edited using the SAP HANA View Editor (also known as the Modeler). This is essentially a query builder that allows you to use drag and drop to graphically build a query out of nodes representing things like database schema objects (tables or views), other information views, and query operators (such as join, union, aggregation, and so on). The models you build this way are stored as XML files in the repository. Activation of these models generates runtime objects, which are basically stored procedures that implement the query according to the model.

While the SAP HANA View Editor is the tool of choice when developing new information views, it can get in the way when performing certain tasks. For example, sometimes it may be convenient to build an information view against a personal database schema where you keep only a few objects just for development purposes. Once you’re happy with how your information view works, you’ll want it to work against the objects from the actual application database schema. (There are many similar scenarios like this, such as updating the package name if you’re referencing CDS objects).

While the view editor does offer a “Replace With Datasource” option (available in the right-click menu on the item), this quickly becomes a rather tedious and time-consuming tasks, especially if your model contains many nodes, or if you have many information views that you want to point to the other schema. You can save yourself quite a bit of time by opening the view in a text editor and using search/replace to change the schema name. You can even do this without leaving SAP HANA Studio: simply right-click the information view in the project explorer, and choose “Open With” > “Text Editor”. For real bulk operations, you need not even open the file in an editor, you can use a command like tool like sed to perform a regular-expression based text substitution.

Of course, you should always be very cautious when editing the XML sources directly. Unlike the SAP HANA View Editor, your text editor or command line tools do not validate the changes you make to the model. Always make a backup of your source files or make sure you have some other way of restoring them should your raw edits render the models invalid.

Cross Join in Information Views

Another tip from Glenn is how to create Cross Joins in information views. A Cross Join is a type of join operation that returns the Cartesian product of the joined tables (that is, the combination of all rows). While there is rarely need for a true Cartesian product in analytical queries, a use case sometimes does pop up when developing custom database applications.

While the SQL standard has a separate keyword for it (like it has keywords for INNER, LEFT OUTER, RIGHT OUTER etc), SAP HANA Studio does not offer a special Join type for it. Note that in SAP HANA Studio you must set the join type in the properties page that becomes active when you select the edge that connects the joined column. The property page does not have an option for Cross Join, nor can it have one, since a cross join does not have any joined columns. The solution is however very straightforward – when you add your data sources to your join node in the View Editor, simply don’t connect any columns. The resulting join will still be valid and SAP HANA will generate a Cartesian product as result.

Note however that this behaviour can bite you as well. I recently encountered a situation where I needed to clean up a Calculation View. As part of the clean-up, I was removing columns “downstream” of a join node. While SAP HANA studio will warn that the column is used by any upstream nodes, it is very easy to miss the fact that you might be removing a column which is used to define a join. If that is the case, then it’s very easy to end up with an unintentional cross join.

Adding Nodes mid-stream

Just-Scott provided a very useful tip on adding nodes to existing Calculation views. Let’s consider an example: It’s not uncommon to work on a Calculation View that contains a number of joins. In many cases, the number of joins grows organically as the development process progresses and user requirements evolve – the requirement to “look up” a few extra columns is a very common one.

Once you opened your existing view in the SAP HANA View Editor and identified between which two nodes you want the new join node, you might proceed by deleting the edge that connects those two existing nodes, add the new join node, and then re-create the edges between the nodes. This would be fine except for the fact that when you break the edge between two nodes, any columns upstream of the broken edge that originate downstream of the broken edge are simply removed. You would have to recreate all those columns after re-establishing the edges from and to the new join node.

While that is of course possible, there is a much better way: if you first click the edge that connects the two nodes where you want the new join to appear in between, it will be selected. If you then drag the new join node onto the selected edge, a messagebox pops up, asking you if you want to insert the new node inbetween the existing nodes. If you confirm, the new join node will automatically be inserted there, splitting up the existing edge and connecting the existing nodes with the new join node, without removing any columns. This avoids doing a lot of tedious and error prone work!

Generating Scripted Calculation Views

The following tip is by yours truly Past week, my co-worker Just-Ivo was creating a few Scripted Calculation Views. (A Scripted Calculation view is a Calculation View that is defined by user-entered SQL script.) One rather tedious aspect of creating scripted Calculation Views is that you have to manually define the output columns of the view, and enter the names of the output columns as well as specify their data types. Again, this is totally doable, but it is not a lot of fun. Apart from the fact that can be time-consuming, it can be error prone too – if you accidentally enter a data type or data type parameters (like length, precision, or scale) that do not correspond to the runtime type of the column, then you might encounter run-time errors when executing the view.

I decided to spend a little time to see if I could make this easier. What would be ideal is if SAP HANA Studio would offer some kind of wizard or integrated generator that you could invoke from the SQL editor, and which would open the SAP HANA View Editor with a newly generated Scripted Calculation View, based on the code that was inside the SQL editor, and having all its output columns generated based on the runtime types of the query. While I appreciate that such a generated view might still require editing, however it would give a considerable head start. I looked into it a bit and I quickly realized that actually modifying SAP HANA Studio to add such a feature would cost me considerable more time than I currently am willing to spend.

So, as a really quick and, admittedly, dirty alternative, I came up with a XSJS web application that can at least generate the Calculation View code, and offer the user a download link, which can be used to download the .calculationview file and save it in an existing SAP HANA project.

Here’s a screenshot of the application frontend:

The way it works is, you enter your SQL query (or at least, the query that will produce the output for your scripted calculation view) in the SQL textarea. You can enter the name for your view in the Object Name field, and enter a version number as well. If the SQL code contains parameter or variable references, the tool will generate inputs for those so that you can enter values. Finally, you can also choose the database schema against which any database object identifiers are resolved.
After entering or changing data in the form, the application will send the query to a XSJS service, which will take the query, append a LIMIT 0 clause to it (so as to prevent doing any actual work as much as possible) and then execute it in order to obtain resultset metadata. This resultset metadata is then used to fill in a calculation view template with both column definitions as well as variable definitions. The result of the filled in template is then exposed via a download link at the bottom of the page. Clicking the link will prompt the user to download a .calcullationview file which you should be able to save to your HANA project and then activate.

If you want to try this yourself, feel free to download or fork the code from the just-bi/scvg repository on github. It’s free and open and I hope it will be useful to you. If you’re interested in these kinds of productivity tools, then stay tuned – The Just-BI development team is currently looking into possibilities to create tools like these and integrate them into HANA Studio. I can’t really say when we’ll have time to make this happen since this is not really core business but I can promise that once we have some of these tools we will publish them and contribute them back to the SAP HANA Developer community just like we are doing now.

Update on Generating Scripted Calculation Views

After our knowledge exchange session, Just-Scott remembered a trick that makes it somewhat easier to define columns for your calculation view. I would recommend everybody to check out his write-up on that topic on SCN: HANA Tips & Tricks: issue #1a – Addendum: Importing Columns for Scripted Calculation Views.

Thanks Scott! Much obliged 🙂

Finally, I hope you enjoyed our tips and tricks! We’ll be back a month from now – just track the hanatipsandtricks to stay tuned