SAP HANA Interview Questions and Answers | Rise above the Rest candidates!

After completion of the first SAP HANA interview questions guide, it’s the turn of another top SAP HANA interview questions and answers article for excelling in the interview. This article includes very important questions which will surely help you a lot in your interview preparation.

These questions are mostly asked in the SAP HANA interview, so prepare them well.

SAP HANA Interview Questions and Answers

Let’s quickly start your interview preparation by exploring the best SAP HANA interview questions and answers.

Q.1 What are the ways in which you can connect to reporting tools from SAP HANA?

Ans. SAP HANA users or the users at the reporting end of the setup can have a connection established to the other end in two ways:

a. First is the direct way where reporting tools can connect directly to the SAP HANA database. This direct connection is an OLAP type connection. The reporting tools which connect directly to the SAP HANA system are Design Studio, SAP Lumira, Business Explorer, Analysis of MS Office, Crystal Reports, etc.

b. The second way is an indirect way of connection. It is a relational connection established by creating a universe using the Information Design Tool (IDT). The reporting tools using this method of connection to SAP HANA database are Web intelligence tools, SAP Dashboards, Crystal reports for enterprise.

Q.2 What are delivery units?

Ans. A delivery unit is a single entity or unit that maps multiple packages to it. Packages are mapped in specific delivery units. Delivery units are sent to other systems as a single unit.

The delivery unit import/export packages contain relevant database objects that are used for content delivery.

Q.3 What do you understand by Business Objects Universes in SAP HANA?

Ans. There are two ways in which the reporting tools can connect to SAP HANA. One way is a direct OLAP connection and another is an indirect relational connection.

In the relational connection type, reporting tools fetch data from a universe layer instead of connecting directly to SAP HANA. Thus, SAP Business Objects Universe acts like a middle layer between reporting tools and SAP HANA.

A universe contains data foundation layer which holds data tables with joins and conditions are applied to them (much like information views).

Reporting tools such as WebI, Crystal Reports for Enterprise and Dashboard designer use Universe as an intermediate layer to connect to SAP HANA. Although, the option of connecting directly to SAP HANA is available in the current versions of Web Intelligence.

Q.4 What are set operators? How many of them are supported in SAP HANA?

Ans. You can use set operators to combine multiple queries and return a single result set in output.

a. Operator: UNION
Description: This operator is used to combine the outputs of two or more SQL select statements or query expressions.

b. Operator: UNION ALL
Description: This operator is used to combine all the outputs of two or more SQL select statements or query expressions. It also includes all duplicate rows.

c. Operator: INTERSECT
Description: This operator is used to combine the outputs of two or more SQL select statements or query expressions and return all the common rows.

d. Operator: EXCEPT
Description: This operator takes the result from the first query, removes the rows selected by the second query and gives the output. It is also referred to as MINUS.

Q.5 Which data types are supported in SAP HANA SQL scripting?

Ans. There are eight categories of data types in SQL. Mostly, all of the data types can be used in SAP HANA.

Binary data type: We can assign a binary data type to the data values that store binary data bytes.

Boolean data type: The Boolean data type is for the values that store Boolean values. The Boolean values are TRUE, FALSE and UNKNOWN (also known as NULL). This data type can also return 1 for TRUE and 0 for FALSE if the Boolean type is not supported.

Numeric data type: The numeric data types are for the values to store numeric information in different ways. Each data type has a minimum and maximum value and any value less than the minimum or greater than the maximum is not supported.

Characteristic string data type: The character string data type is for such values that store values containing character strings. There are two main types of character string data types, VARCHAR and NVARCHAR.

Datetime data type: The values of the datetime data type stores date and time information.

Large Object (LOB) data type: The values of LOB i.e. large object data type contains a large amount of data like images or text documents.

Multi-valued data type: The multi-valued data type is for the value that stores a collection of values sharing the same data type. The multi-valued data type values are also known as nested data types.

SAP HANA supports all basic data types in multi-valued data type values such as FLOAT, DECIMAL, VARCHAR, INTEGER. It does not support the complex ones like TEXT, LOB and spatial data types.

Spatial data type: The spatial data type is used for the values which store spatial data regarding spatial objects like points, lines or polygons, etc.

Note: Data types questions are very popular in SAP HANA interview. Prepare it well.

Q.6 What is the difference between VARCHAR and NVARCHAR in SAP HANA SQL?

Ans. Both VARCHAR and NVARCHAR are numeric data types in SQL used in SAP HANA.

a. VARCHAR

The VARCHAR(<n>) data type assigns a character string of variable length. It contains only the 7-bit ASCII character strings.

The length of the character string is <n> and has a limit of a value between 1 and 5000.

If you use it in a DDL query, then n is 1 by default.

Whereas, if you use it in a DML query then n is considered as 5000 (if you specify no length). In a DML query, n is the length of the string in characters.

b. NVARCHAR

The NVARCHAR(<n>) defines a character set string of a variable length of Unicode characters. <n> is an integer defining the length of the string in characters. The value of <n> can be anywhere between 1 and 5000.

If the value of <n> is not specified in a DDL query, then it is 1 by default.

If the value of <n> i.e. the length of the string in characters is not specified in a DML query, then it is 5000 by default.

Q.7 Explain the relation between OPERATOR_ID and PARENT_OPERATOR_ID columns in an explain plan table.

Ans. Explain plans tells you how SQL statements get executed. They are useful for troubleshooting and performance optimization of SQL statements.

• OPERATOR_ID shows on which rank in the order of statement execution does a particular operator lies.
• PARENT_OPERATOR_ID is the number/rank of the statement through which a particular statement is succeeding. For instance, if parent_operator_id of statement 2 is 1 that means statement 2 executes after statement 1. This ID is useful to understand the where or join clauses where certain statements follow one statement.

Q.8 How can you perform data profiling in SAP HANA?

Ans. SQL data profiling is a process of refining the data and analyzing it to get a better understanding of it. The main purpose behind data profiling is to check data coming from different sources, identify problems in it and fix the issues to maintain data quality.

In SAP HANA Studio, we can conduct profiling for data in tables or data in views.

1. Open SAP HANA Studio and login to your HANA system.

2. Expand a schema in the Catalog tab and select a table or view.

3. Right-click on the name of the view or table and select the option Open Data Preview.

In the data preview window, you get three tabs; Raw Data, Distinct values, and Analysis tab.

Using the Distinct Values tab, you can perform basic data profiling. On this window, you will get a list of available objects from your selected data source. You can select one object from the list, and it will show all the details of it.

For instance, you can see the list of items in the object COUNTRYNAME, in the form of a bar chart representation, a statistics box at the bottom, the count and distribution percentage for the occurrence of each value. This helps in a detailed analysis of the data from the source.

Also, you can select an item and view its data type, native data type, column precision, column scale and row count in the statistics box.

You can get each data column available in the list profiled by clicking on its name.

Q.9 What are JSON object expressions in SAP HANA?

Ans. The JSON Object expressions used in SAP HANA generate a JSON object. A typical JSON object has two elements; Key and Value.

A “<key>” is a string literal which must be in double quotes “” always. And a <value> is an expression specific to a key and can be a simple value (string, integer), JSON object enclosed in {} braces or an array [].

One can only use JSON object expressions while working with the JSON collection tables. The reference statements of JSON Object expressions are SELECT, INSERT INTO, UPDATE or use with operators like +,-,/,*.

Example:

{“firstname”:’Aditya’, “lastname”:’Kapoor’, “age”:45, “address”: { ‘street’: ‘Flat no.512, wing B, Leela Residency’, ‘city’: ‘Mumbai’ } }

Q.10 What are SQL sequences in SAP HANA? What is the difference between CURRVAL and NEXTVAL?

Ans. SQL sequences are integer values generated automatically on executing a SQL statement. A sequence can be any trail of numbers like 100, 101, 102, and so on.

You can use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database.

The sequence statement can return two values, current value or next value. When you use the keyword CURRVAL, then you will get the current value of a sequence as a result. But, when you use keyword NEXTVAL, you will get the succeeding value (next value) of the current value.

Q.11 What are SQL synonyms in SAP HANA? Explain its types.

Ans. SQL synonyms are alternate names given to a database object residing in a particular schema in SAP HANA. That is, synonyms are an alias of database objects.

The SQL synonyms are not database objects in themselves but are just used as pointers to the real database objects. In SAP HANA, we can create synonyms for several database objects like tables, views, procedures, table functions, sequences, scalar functions, etc.

There are two types of SQL synonyms:

Public Synonyms: The PUBLIC schema owns public synonyms in an SAP HANA database. All the SAP HANA users can access objects by referring to public synonyms.

The owner of the application creates a synonym for database objects like tables, views, packages, etc. Those synonyms are available for use by other users to access the database objects.

Syntax:

CREATE PUBLIC SYNONYM <synonymname> for <sourceobjectname>;

Private Synonyms: As opposed to public synonyms, we can use private synonyms only within a schema. Thus, only the owner of the database schema can use private synonyms for referencing and hiding the original object names.

Syntax:

CREATE SYNONYM <synonymname> for <sourceobjectname>;

Q.12 Why are triggers used in SAP HANA?

Ans. We can use SQL triggers in SAP HANA for several purposes such as:

• Auditing operations
• Synchronous replication of data tables
• To store information on accessing the table
• Security authorization processes
• To prevent invalid transactions
• Event logging processes
• To enforce referential integrity
• To automatically generate derived column values

Q.13 Can you explain the three types of SQL statements used in SAP HANA?

Ans. SAP HANA support three types of SQL statements; DML, DDL, and DCL.

1. Data Manipulation Language (DML)

We use data manipulation language (DML) within schema objects to manipulate and manage data in them. Generally used DML keywords are SELECT (to retrieve data from database), INSERT (to add data into a data table) and UPDATE (to update data that already exists in a table).

2. Data Definition Language (DDL)

DDL statements and keywords define the structure of data in databases and schemas. Commonly used DDL keywords are CREATE (creates objects in the database), ALTER (alters the database structure), DROP (deletes or drops objects from the database).

3. Data Control Language (DCL)

The DCL statements control user access and privileges to the database. Commonly used DCL keywords are GRANT (to give access privilege to database to the users), and REVOKE (to take back the access privileges given using GRANT command).

Q.14 What is the different between Synchronous, Synchronous in-memory, and Synchronous full-sync replication modes?

Ans. Here is the different working of three replication modes:

Synchronous mode: In the synchronous mode of system replication, the primary node waits and halts its transactions till the time the secondary node sends an acknowledgment indicating it has received the data log and persisted the log volumes on its disk.

The main advantage of using sync mode is that it ensures and maintains consistency between the two nodes. The primary node ensures that it will not commit any transaction before the replication on the secondary node is completed.

Synchronous in-memory mode: Synchronous in-memory mode is the default replication mode. The primary node waits for a confirmation message sent by the secondary node upon receiving the log successfully. Until then, the primary node/system commits no transactions.

In other words, the secondary system sends an acknowledgment back to the primary system upon receiving the data successfully in its in-memory.

Synchronous full sync mode: The full sync mode ensures complete data protection i.e. zero data loss because it blocks the transactions on the primary node until replication is successfully completed on the secondary node.

It blocks all the operations on the primary node until the secondary node sends an acknowledgment indicating that it has received the data logs and persisted the log volumes on the disk.

Q.15 Explain run-length encoding compression used in SAP HANA?

Ans. We use data compression techniques to compress data stored in column stores in HANA database. It is a necessary step before storing data in HANA database to optimize SAP HANA’s performance.

Run-length compression: In run-length encoding compression method, it stores only one of the repeating value IDs along with its start position. Suppose a data value with value ID 5 appears 3 times consecutively starting from position 0.

Then the compression will store only one instance off value ID 5 and the start position as 0. Thus, we call it run length encoding because it stores the start positions as run-length values.

• Method type: RLE
• Applied to: Main storage
• Applied on: Several frequent column values

Q.16 What is SAP HANA sidecar?

Ans. SAP HANA sidecar is SAP HANA platform serving as a secondary database and computing platform to a full-service, already existing system with its own traditional database.

SAP HANA sidecar approach uses SAP HANA system for storage and computational capabilities to carry out operations related to the data and database objects of other systems.

Q.17 How is data imported/exported using Developer Mode in SAP HANA?

Ans. The Developer Mode method is essentially used to import/export individual database objects from a target system to a location in the local client system. It is different from the delivery unit method as in it, you have to export multiple packages enclosed in a single unit and cannot export individual files.

Q.18 What is SAP Support Mode?

Ans. We use the SAP Support Mode to export database objects and data for SAP support purposes. Users use this export method in case errors to seek help in troubleshooting and debugging from the SAP Support team.

In the SAP Support Mode option, you simply have to select the data object facing issues and add it into the Selected section. Then choose the export location, i.e. to server or local client. Add required details and click on Finish. It will export database object to the support team’s system successfully.

Q.19 What are the potential risks to SAP HANA system?

Ans. There are some key risks to SAP HANA system against which we implement security functionalities.

1. Web Applications

In a lot of SAP HANA scenarios, users use web browsers to access applications deployed on SAP HANA system. Thus, a lot of SAP HANA systems are easily available on the internet which makes them prone to hacking.

Also, unauthorized access is possible through the web applications, where hackers can interfere with HANA services. In addition to this, risks on SAP HANA includes several web weaknesses such as XSS, SQL injection, ABAP code injection, etc which makes intruding into the system easy for the hackers.

2. RAM Scraping

In RAM scraping security threat, the malware or virus runs on the in-memory along with the normal processes of SAP HANA. This makes detecting the malware very difficult as the malware also disappears as soon as the in-memory processes terminate and leaves no footprint.

RAM scraping poses the most dangerous threat to SAP HANA as it uses in-memory virus vectors because SAP HANA is also an in-memory technology. We cannot encrypt the HANA in-memory processes on RAM level as it would degrade the performance of the system.

3. Basis Security

In most scenarios, SAP HANA runs parallelly to other systems in a system landscape which increases the overall complexity.

The entire system landscape becomes more prone to security failures and glitches as multiple security processes and functions related to SAP HANA are also involved in the system environment.

Note: The above SAP HANA interview question is very important for your interview preparation.

Q.20 Explain the Kerberos method of authentication in SAP HANA.

Ans. The Kerberos method enables the direct authentication of a HANA user using external authentication methods. The external login is mapped to SAP HANA user profile and the user is authenticated in SAP HANA directly from external platforms.

For example – Users can authenticate themselves via JDBC/ODBC drivers through a network connection or using SAP Business Object which is a front-end application.

In addition to this, user authentication in SAP HANA XS is also possible for HTTP access. This process uses HANA XS engine and SPENGO mechanism to carry out Kerberos authentication.

Q.21 What is X.509 and its relation to SAP HANA?

Ans. It is an indirect method of security, authenticating users to let them access SAP HANA. It gives User-specific x.509 certificates to users requesting access into the SAP HANA database from SAP HANA XS system (via an HTTP request).

Trusted certification authority creates and signs these certificates.

Thus, it is a legitimate mode of authentication. The user must have a profile stored in SAP HANA for verification when they assign a certificate for authentication to him. This method does not map user data from external sources into SAP HANA.

Q.22 How are SAP HANA users classified?

Ans. SAP HANA users are broadly classified into two categories; Technical (DBA) user and Database user.

1. Technical (DBA) User

The technical users are the ones that perform database administration tasks in SAP HANA system. They are not the usual users using HANA tools for accessing and transforming data from the HANA database rather, they manage all the other users.

Some typical tasks performed by technical users are to create database objects, assigning privileges to database users, assigning privileges on packages and applications. SYSTEM, SYS, and _SYS_REPO are the names of technical users that are present in the SAP HANA system by default.

2. Database User

The database users are the actual users that interact with the SAP HANA database to access and transform the data. Thus, it is compulsory for every HANA user to have a database user profile. We can create Database users either by SAP HANA Studio GUI method or by SQL commands.

The SQL statement for creating a database user is CREATE USER (for standard user) and CREATE RESTRICTED USER (for restricted user). Further, there are two types of database users.

2.1. Standard Database User

The standard users are those who can create database objects in the schemas owned by them and have read access for system views.

2.2. Restricted Database User

The restricted database user type are the users who provision other users using SAP HANA through client applications. Initially, restricted users have no privileges and have limited SQL access via SQL console. Usually one can assign application-specific roles to a restricted user.

Note: The above question is most asked SAP HANA interview question.

Q.23 What do you understand by roles in SAP HANA?

Ans. A ‘Role’ in SAP HANA is a collection of different privileges granted as a single unit to a database user or another role in runtime. The standard mechanism of granting privileges in SAP HANA is through Roles.
By using roles instead of granting individual privileges, one can pass on complex authorization concepts to users and implement it in HANA system working.

A role essentially contains such collection of privileges using which the database users can perform certain tasks like creating models and reports, reading reports using client tools such as Microsoft Excel, maintaining and operating databases and users, etc.

Q.24 Enlist the scenarios in which object privileges are used in SAP HANA.

Ans. Object privileges used in different scenarios are given below:

• We can use object privileges to authorize run time database objects.
• Object privileges are used as source privileges to authorize the access of remote data sources connected to SAP HANA through SAP HANA smart data access.
• We can use object privileges to authorize activated repository objects such as calculation views.
• We can use object privileges to authorize schema containing an activated repository object.

Q.25 Which privileges do you need to work on a schema, table or views in SAP HANA?

Ans. An authenticated HANA system user requires the object privileges listed below to have the permission to work on schemas, tables or views.

• ALL PRIVILEGES
• CREATE ANY
• CREATE VIRTUAL FUNCTION PACKAGE
• ALTER
• CREATE TEMPORARY TABLE
• DEBUG
• DELETE
• DROP
• EXECUTE
• INDEX
• INSERT
• REFERENCES
• SELECT
• TRIGGER
• UPDATE

Q.26 What are the most probable reasons for SAP HANA system lockdown?

Ans. The most probable reasons for a system to go into a lockdown mode are explained below:

• Expiration of a temporary license which stays valid only for 90 days from SAP HANA database installation.
• When you have not renewed a permanent license key and the temporary key has also expired after 28 days.
• The hardware key changes while using a temporary license key.
• When you have an enforced license key type and you exceed the permitted memory usage on it.
• When you delete all the existing license keys on your system.

Q.27 What is the difference between unenforced and enforced licence keys?

Ans. There are two types of permanent license keys; unenforced license key and enforced license key.

a. Unenforced License Key: In the case of having an unenforced license key, if you exceed memory usage of SAP HANA system than what your license allows, it will not affect your HANA system. The system will not get locked down, instead, the user will receive a memory over usage message warning. That is if you have a license for using 1 TB memory, but you use 2 TB memory, then you will only get a warning message for the exceeding usage of HANA memory.

b. Enforced License Key: As opposed to the unenforced license key, the enforced license key locks a HANA system if the user exceeds the licensed memory usage. In this case, the user needs to restart the system and request for a new permanent license for installation with the same or extended memory usage limit.

Q.28 Which SQL statements will you use to check your current HANA license details, install a new license and delete a license?

Ans. To check the license properties of your HANA system, enter:

SELECT*FROM “SYS”.”M_LICENSE”;

To install a new license key, enter:

SET SYSTEM LICENSE <location of your .txt file>;

The location can be c:\license.txt

To delete existing licenses:

UNSET SYSTEM LICENSE ALL

Q.29 What are auditing policies in SAP HANA auditing?

Ans. The audit policies define and decide which activities or events in SAP HANA need auditing. The administrators need to enable a newly created audit policy as it will not get triggered on an assigned action unless enabled manually and explicitly.

The administrator is free to enable or disable an audit policy as per requirement. To enable or disable an audit policy, the administrator must have AUDIT ADMIN privilege.

In addition to this, any user who has system privileges like AUDIT ADMIN, CATALOG READ or DATA ADMIN can view and check the existing audit policies in public view named AUDIT_POLICIES.

Q.30 What is the maximum possible number of tables and columns within each table in SAP HANA?

Ans. The maximum number of tables which we can create under a schema is 131072 in SAP HANA system.

The maximum number of columns which we can create in a table is 1000 under a HANA system schema.

Summary

With this, we wrap up the DataFlair SAP HANA Interview Questions and Answers. We tried to cover all the concepts and features of SAP HANA database technology through both the parts of SAP HANA interview questions.

Still, if we missed any topic, feel free to share your thoughts in the comment section below.