Why SAP HANA modeling with SQL and SQLScript? Because implementing in-memory models with ABAP is not possible.
So SAP itself develops business processes and their logic in-memory with SAP HANA (see Rapid Deployment Solutions). But let’s get started from the scratch.
There are two different ways of modeling in-memory on SAP HANA. First of all you can user standard functionalities which I will describe in the first part. But more interesting is the advanced data modeling with SQL and SQLScript. Missing funtionalities for complex business transformations or logic can be added by implementing them with SQL or/and SQLScript. Of course, it is more difficult as for example other SAP systems provide consistent data checks and you have to do all on your own (the whole programming stuff). I like to see it as challenge and possibility to have the greatest possibilities for new „features“ which weren’t possible before 😉
Standard data models in SAP HANA
You have the possibility to model with the SAP-given standard features on SAP HANA. Mainly any kind of information models or views as e.g. the attribute, analytical and graphical calculation view or decision tables (at the moment). But these standard models are limited to their functionalities which might be feasible for some data models. The other data models need more logic than offered by e.g. calculated columns or joins.
Here starts the advanced implementation by coding the business process with SQL or SQLScript.
SQL and SQLScript
- (HANA) SQL can be seen as the SQL you maybe already know. SQL contains data manipulation statements (DML, Update, Insert, Delete) as well as SQL-Query (SELECT) statements. You can implement SQL with CASE expression and even calculate or manipulate the data (more as with the standard information models). So you already habe some options and possibilites to transform, join or manipulate data but most of the time on sinlge data set level (or for the entire database table).
- SQLScript is on the other hand a further collection of extensions for the SQL. So whenever SQL is not enough due to more complex logic (e.g. calculations while referring to other data, if-statements, loops, etc.) you need the extension functions of SQLScript. Every database system has its own SQL scripting syntax. Therefore you find e.g. a lot of Oracle SQL scripts when you search for SQL scripting on Google.
A closer view about SQL, SQLScript and procedure for data modeling
It is difficult but essential to describe the relations of SQL, SQLScript and procedures for data modeling on SAP HANA. So try to explain it step by step. This is necessary due to explaining the following coding for data generation in detail and shows the difference between SQL /SQLScript for database purpose and SQL / SQLScript for data modeling as well as the usage of SQL and SQLScript.
- You can use SQL and SQLScript in a procedure.
- A procedure can take place at different layers, as part of the content (information model) or as part of the catalog (database procedure).
- As information model you have the choice between a procedure (see Figure 1, in a shell with the option of input and output parameters) or a SQL calculation view (see Figure 2, also in a shell but mainly based on a fixed output and input parameters as kind of variables). SQL calculation views can be reused as information model in other models or you can even do multidimensional reporting with input parameter / variables you a highly sophisticated freedom in data modeling. Whereas procedures in the content model can be seen as function modules or standardized methods which you can use in your SQLScript.
- A database procedure (see Figure 3) must be coded completely and is executed in the SQL console (see Figure 4). After executin the creation of the database procedure you can call the procedure. You are on your own by programming a shell for the database procedure with input and out parameters. I use the for test data generation but such procedures can also be used to copy a user with template (input parameter new user and template user for copying) or creating a bulk of test users (input parameter x users).
- So if you are not in the SQL console you are most of the time in a SQLScript shell by SAP. You are free to decide on your own whether you use SQL only or SQLScript functionalities in the procedure. You can even mix them up (WHILE loop is SQLScript functionality which might execute SQL only statements within the loop).
- In the SQL console you can do single SELECT statements, INSERTs or by implementing a short SQLScript shell (always by creating a procedure) with SQL and SQLScript functions.
- Because I want to do data modeling with SQL and SQLScript I will stay most of the time in the SQL calculcation views trying to use information model procedures for making things easier or more performance oriented.