8 SQL and SQLScript

Techniques You’ll Master


In many large SAP HANA projects, you’ll reach a point at which you need to do something more than what is offered by the graphical information views. Information modeling using SQL and SQLScript offer more powerful options. You lose the productivity and simplicity of working graphically with information views, but you gain flexibility and control.

As SAP HANA has matured, the graphical modeling tools have grown such that the requirements for SQL and SQLScript have been reduced. In the past, once you started working with SQL and SQLScript, you had to continue in that environment (or choose to create any subsequent layers in your information models there as well). This issue has been addressed in the latest versions of SAP HANA.

You can now work graphically with your SAP HANA information views, address the occasional constraint with SQL and SQLScript, and bring this development back into the graphical SAP HANA modeling environment. You can then continue building the remaining layers of your information models using the graphical information views. The focus has therefore dramatically shifted towards the graphical modeling environment.

[»] Note

There is definitely less focus now on SQL and SQLScript in the average modeling workflow than there was in the past.

To enable this shift, many of the SAP HANA modeling constructs and objects have changed. If you haven’t looked at SQL and SQLScript in SAP HANA in the last two years, you will probably find that everything you knew has changed!

In this chapter, we will get you up to speed quickly on the new SAP HANA information views, how to convert the old information views, and how to integrate all SQL/SQLScript back into the graphical modeling environment.

Real-World Scenario

You have been working on a large SAP HANA project for a few months when you get a request from business users: They love working with the decision tables and updating the values via a Microsoft Excel spreadsheet, but many of their decision tables use exactly the same rules and are based on similar tables. They want to know if there is a way to reuse the same decision table rules and dynamically change the tables used. A few months ago, you would probably have said this was impossible, but you now know that you can base the decision tables on table types, and so you can help these business users.

Soon after, another group of business users asks you if you can help to make security access more powerful and sync it to automatically use their hierarchal organizational structures. You base the analytic privileges on a procedure that easily exploits these structures.

The company is quickly moving into the area of mobile business services. They plan to deliver some new, real-time business dashboards to executives and managers. SAP HANA is an obvious choice, and your modeling and development skills are required to build the link to the mobile screens.

Objectives of This Portion of the Test

The purpose of this portion of the SAP HANA certification exam is to test your knowledge of modeling using SQL and SQLScript.

The certification exam expects you to have a good understanding of the following topics:

[»] Note

This portion contributes up to 10% of the total certification exam score.