HANA Tips & Tricks: issue #1 – Hacking information views
About this post
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
Glenn Cheung 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
Scott Wery 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 Ivo Moor 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, Scott Wery 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: 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 π
Good initiative and a nice approach to introduce several contributors in a single blog post.
I'd recommend (also in regard of the upcoming tag oriented 1DX SCN) to assign tags a bit more liberally 'sap_hana', 'modelling', 'information model', 'calculation view'... would all work.
Concerning the direct generation and modification of the .calculationview XML texts the caveat could be a bit bolder. Fact is that SAP won't provide support for such manipulated views.
And as much as I see the point of enabling bulk changes on these artifacts, I can tell that there are way to many pitfalls associated with it (calculation views still don't behave like SQL in many places and many users still get confused by that). Ending up with wrong results, bad performance or both has just been made faster by that.
The cross joins section is quite interesting: what are the typical use cases you have seen so far? When do you want to generate all combinations of records? What are the advantages and limitations for this approach that you've seen so far?
Looking forward to the next tips and tricks.
Hi Lars! Thank you so much for chiming in! Much appreciated π
I'll look into the tags - thanks for the tip!
Thanks for mentioning the status of hacked calculation view sources! Actually, I noticed a number of issues with calculation views even when you do create them with the view editor, but these will be the subject of another post π Stay tuned!
Regarding the cross join - I'll poke Glenn and ask if he can elaborate a bit on the use cases he has seen so far.
Thanks again for posting - we look forward to posting more tips and we welcome discussion like this.
Best regards,
Roland Bouman
Thanks Roland and Team.
With respect to the "adding nodes" section, you don't need to click the edge before dragging and dropping the new join. you can drag the join to the approximate area between the 2 existing nodes, and drop it once the edge is highlighted in orange. The same message box then appears.
Thanks! Really appreciate the additional insights π
Best regards,
Roland
Good post Roland π
Cheers, much appreciated π
Good blog.
Looking forward to the next one π
Wow, great post. Β The tip about inserting a node without breaking the existing connection is priceless. Β Thank you!
Nice tips. Thanks
Thanks a lot, you saved me from several hours of work adding simply a new table to a complex CV. BTW: I found the maintenance of attribute views more appealing and easier.
Hi!
cool, glad that helped you π
Yeah I rather liked the attribute/analytic view combi too - much more structured approach and more close to standard DWH concepts. However, SAP wants to phase those out and their bet is on the calculation engine. So our current practice for new projects is to always use Calculation views.
Thanks, Ronald and Team, Quite Interesting and much appreciated.
Thanks,
KD Jain
Thanks for the post.