Once companies have made the switch from an SAP BW standard relational database to an SAP BW powered by SAP HANA database, their focus moves to trying to speed up processes in the SAP BW environment. The big advantage of using SAP HANA is not just increased speed and the move to a newer database; it also offers a reduction in redundant SAP BW data and more streamlined functionality (by moving functionality from the application server to the SAP HANA database layer) with its modern tools and architecture. I currently make use of SAP HANA functionality such as calculation views as much as possible to feed CompositeProviders, the successor to MultiCubes in SAP BW. This, in turn, leads to new challenges due to the absence of online analytical processing (OLAP) variables (e.g., current year). Before, when using standard SAP BW based on a relational database, it was easy to use OLAP variables to filter data in data transfer packages (DTPs) or InfoPackages when loading data to persistent InfoProviders. The modern concept of keeping as much data as possible in SAP HANA and using views instead of persisting data results in a lack of OLAP variable options. OLAP variables that were used in the old SAP BW world are executed in the ABAP application-server environment, but selecting native SAP HANA database tables makes those variables inaccessible. This new design architecture, provided with SAP BW systems driven by an SAP HANA database (I currently use SAP BW version 7.5, Support Package 4), leads to new challenges, including the need for filtering data at a very early stage. The old ABAP or OLAP variables (BEx Query variables) are not suitable in the filters of calculation views or projections because they are intended to work on the application layer instead of the database layer. In the new design, you can specify a hard-coded filter, but more often you need dynamic filters, such as a specific date range. A good example of this is a filter for reading SAP variant tables or equivalent sources. This can be achieved by using SAP HANA procedures (stored procedures) that can be called by defining input and output parameters, such as function modules. SAP HANA functionality offers multiple ways to retrieve values from internal SAP tables. In my opinion, the dynamic way is the best method for doing this. Using three examples, I explain how to overcome the issues caused by SAP HANA schema names that are based on the prefixes of table names. For example, the schema prefix for tables such as TVARVC is SAPDEV, but in production it could be SAPPROD. When this is the case, issues arise since these prefixes are based on your system schema—for example, YOUR_SYSTEM in program code. This can lead to problems after calculation views are transported, since the prefixes in the table schema names don’t match the system schema prefixes. In the coding examples used in this article, I first explain the static approach and some of its issues, then the synonym approach, and, finally, the dynamic solution that I recommend using for the prior two approaches. In addition, I explain how to use procedures to replace the former variable approach when using calculation views underneath CompositeProviders. This introduction to procedures and synonyms, tightly integrated with SAP BW version 7.5, helps you enhance your calculation views. Using the methods shown here, you can then build your procedures in the way you previously built variables, and you can reuse them everywhere in SAP HANA to speed up your system.

The Static Approach for Retrieving Values from Internal SAP Tables

The code in Figure 1, which uses static table names and schema, shows how to write a procedure that reads data from a given table (e.g., table TVARVC) by the default schema in a development (DEV) system. If you have a one-tier landscape you can use this approach. If your procedures use tables in specific schemas, these procedures might stall after transporting from development (DEV) to quality assurance (QA) and to production (PROD) systems in a three-tier SAP landscape. In my example, I show how to read a specific entry from table SAP DDIC TVARV (which stores all types of variants—for example, date ranges) and receive the requested data as a return value. The sample code for doing this is shown in Figure 1.

Using Synonyms as Alias Names for Any Table

My second example uses the synonym functionality of SAP HANA, which works the same way as table alias names. By using this functionality, you avoid the problems that can be caused by using static schema names such as MY_SYSTEM_TABLE_NAME and static table names (shown in my first example). However, you must create synonyms in all the tiers of your SAP BW landscape because each system has its own prefix, such as MY_DEV_SYSTEM or MY_PROD_SYSTEM for table TVARVC (e.g., in a three-tier landscape, you must create the synonyms in all three systems). In this example (using synonyms), you must refer to the specific table via the individual schema in your DEV system as I showed in the first code example (e.g., the schema SAPD04). However, this schema is called SAPD04 in DEV but in PROD it is called SAPP04. This change of name causes problems when transporting a procedure through your existing SAP BW landscape because these exact schema names do not exist across all three systems in the three-tier environment after transport. To avoid these kinds transport problems in code sequences for procedures, you can use SAP HANA synonyms that can be addressed in your code like table alias names. These allow you to use a plain synonym table (like my example table TVARVC) instead of referring to schemas and table names (e.g., schema SAPD04 and table TVARVC). The first step is to create a public synonym that can be used by all users, not just your personal user and schemas. To create a synonym, open a SQL console in Eclipse and enter the code shown in Figure 2. Execute the statement to have the synonym TVARVC created in the SAP HANA database so that you can reuse it within your procedures. After you have successfully executed the SQL statement to create the new public synonym, you can see the new synonym in the table schema under DF4 Development, in the Public Synonyms folder (Figure 3). You can test the newly created synonym by executing the statement shown in Figure 4. Displaying table contents ensures that your synonym has been created and is working correctly. As you can see in Figure 4, there is no need to use the schema name anymore, as was done in the previous static approach. Now, after the creation of the synonym, you can use this synonym and modify the procedure created previously. Just change the code to refer to the synonym instead of the schema and table name. This code for doing this is shown in Figure 5. The SAP HANA lookup strategy is first to search within the current schema and then, in the next step, search within the public namespace to find your just-generated synonym. From that point on, all your coding can reference that table via a synonym without adding the schema prefix, as shown in the code example in Figure 5. One advantage of using synonyms is that code is more related to the original SAP table names—for example, first addressing table TVARVC instead of MY_SYSTEM and TVARVC—than addressing it when using the static approach. Second, by creating synonyms in all the systems of your landscape, you alleviate any problems that could occur during transport caused by the naming convention of your schema. There are some potential drawbacks to using this method, however. The transport of synonyms is not currently supported by SAP, and I don’t know if this will change with later patches (in the SAP BW 7.5 version powered by SAP HANA Support Package 6). One workaround is to export your procedures and synonyms and import them to the next tier via the Eclipse/SAP HANA studio import/export functionality. Or, my recommendation is that you can build your own library of SQL scripts and execute them in each individual system to generate the synonyms, much like the delivered SQL statements for SAP HANA administrative tasks. SAP Note 1969700 – SQL Statement Collection for SAP HANA shows how you can define your scripts for individual purposes (e.g., to create synonyms and table types, for example) and share them with colleagues.

How to Build Dynamic Table Strings via Procedures [head 2]

In this section, I show you how to keep your procedures dynamic and avoid the problems with synonyms or schema names that I described before. In this last example, my preferred approach, I show how to avoid all manual interactions such as having to manually change code or add synonyms in each system. Why not use a procedure to get the information directly from SAP HANA mapping tables? Using the following approach, you can do this by simply assigning the translational mapping (SAP HANA schema mapping) once in all the systems (i.e., a three-tier landscape), as shown in Table 1.

System	Authoring schema	Physical schema
DEV	D04	D04
QA	D04	Q04
PROD	D04	P04

Figure 6Table 1 The result when calling the procedure (e.g., in the PROD system) is shown in Figure 7. Here the schema and table names are concatenated, as demonstrated in the static approach, but without any hard-coded program code. You can reuse this procedure (Figure 6) as a template that can be modified, as shown in the next code example in Figure 8. The last task is to change the original routine and replace the hard-coded sequence with the result of the procedure call. Figure 9 contains the complete code for doing this. Calling the sub-procedure in Figure 9 to retrieve a specific entry from table TVARVC (storing variants) returns the exact entry found in table TVARVC (Figure 10). Think of the need to reduce data within any calculation views by using variable settings. For example, you store a specific time frame in a variable (e.g., MY_WIS_FILTER_VALUE.) The procedure retrieves exactly that time frame’s values (e.g., 20170101 and 20170103). This might be used to restrict your data, even on the database level, to have your views execute faster and avoid obsolete data being passed from the database to the calculation view. My recommendation, even for SAP HANA, is to restrict your data at the first possible level of your SAP HANA data models to avoid memory and execution time problems. Call your procedure and pass the parameters using the code snippet in Figure 11. When calling procedures, the syntax passes the input parameters separated by commas and passes the question mark for all the available output parameters of the procedure (you have a four-output parameter in the dynamic procedure Figure 9). After executing your SQL statement, the result displays the exact data stored in table TVARV (Figure 12). In calculation views you can use this just-created procedure to filter time frames stored in table TVARVC.