GRC
HR
SCM
CRM
BI
Expand +


Article

 

How to Use Synonyms and Dynamic Parameters in Procedures with SAP BW Powered by SAP HANA 7.5 Support Package 4 to Compensate for Missing OLAP Variables

by Joerg Boeke

August 16, 2017

Get a detailed guide (including SAP HANA SQL code snippets) for how to speed up your SAP HANA calculation views by avoiding obsolete data at the database level. (The code in this article can be used as a reference for building your own procedure library for later use.) Learn about a dynamic method for reading data from any existing SAP HANA or SAP BW table. Finally, get new ideas about how to use SAP HANA data staging to speed up data loading and reporting.

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.


Figure 1
The code used for static table declarations in procedures

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 of 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.


Figure 2
The SQL console in Eclipse that shows the code for creating the synonym TVARVC

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).


Figure 3
The SAP HANA synonyms in the SAP HANA modelling view in Eclipse

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.


Figure 4
The SQL code used to check if the synonym TVARVC (table alias) is created successfully

(Tip: Always run this check [see the SQL statement in Figure 4] to make sure the synonym has been created in a public schema and not accidentally created in a specific user schema. As shown in Figure 4, this synonym has been successfully created in schema table SAPD04 (in column OBJECT_SCHEMA) and not in a user schema table, such as JOEBOE.)

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.


Figure 5
The procedure uses a synonym for table instead of for the schema and table names

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

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

Table 1
The SAP HANA custom schema mapping

The code in Figure 6 (run in your SQL console) accesses the schema mapping table (Table 1) with the help of a procedure. In this example, the column of the actual schema from the SAP HANA mapping tables (e.g., in DEV) returns D04, but in PROD it returns P04.


Figure 6
The code for fetching actual system schema names from internal SAP HANA tables

The result when calling the procedure (e.g., in the PROD system) is shown in Figure 7.


Figure 7
The SQL code result for fetching actual schema names and concatenating with static table names (e.g., TVARVC)
 

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 next code example in Figure 8.

CREATE PROCEDURE GET_TAB_NAME_P(IN p_tabname varchar(256), OUT p_sys nvarchar(256))

--procedure for entering table name and retrieving table name with actual schema prefix

LANGUAGE SQLSCRIPT

reads SQL DATA AS

 

BEGIN

      DECLARE LV_PHYS nvarchar(256);

  t = SELECT * FROM "_SYS_BI"."M_SCHEMA_MAPPING" where AUTHORING_SCHEMA = 'SAPD04';

    select PHYSICAL_SCHEMA into LV_PHYS  from :t;

   p_sys = concat('"',concat(:LV_PHYS,'"."'));    --eg "SAPP01"."

   p_sys = concat(p_sys,concat(:p_tabname,'"'));  --eg "SAPP01"."MyTable"

 

END;
Figure 8
The SQL code for fetching actual schema names and concatenating them with dynamic table names

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.


Figure 9
The main procedure, including the sub-procedure code, to reuse in other areas including calculation view restrictions using variable table names

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.


Figure 10
The display of the original values in SAP table TVARVC

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).


Figure 11
The SQL code to call the procedure to retrieve results

After you execute 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.


Figure 12
The result of the procedure reading SAP table TVARV

An email has been sent to:





 

Joerg Boeke

Joerg Boeke (Joerg.boeke@bianalyst.de) is an SAP NetWeaver BW solution architect and senior consultant working at BIAnalyst GmbH & Co.KG. He has 19 years of experience in SAP NetWeaver BW, having worked on it since SAP BW 1.2A.



More from SAPinsider



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ