Interview questions for SAP-HANA Data Modelling

General Questions:

1.What is SAP-HANA?.
   SAP HANA is an in-memory database:

•    It is a combination of hardware and software made to process massive real time data using In-Memory computing.
• It combines row-based, column-based database technology.
• Data now resides in main-memory (RAM) and no longer on a hard disk.
• It’s best suited for performing real-time analytics, and developing and deploying real-time applications.

2.Why to choose SAP-HANA?

SAP HANA is one of the fastest growing products in SAP’s history and is viewed by the industry as a break through solution for in-memory databases. SAP HANA claims that it accelerates analytics and applications on a single, in-memory platform as well as combining databases, data processing, and application platform capabilities. 

SAP HANA is a next-generation business platform which brings together,

• Business transactions.
• Advanced analytics
• Social media
• Mobile experience
• Collaborative business
• Design connections

3.What is SAP HANA Studio?

• The SAP HANA studio is an Eclipse-based development and administration tool for working with HANA.
• It enables technical users to manage the SAP HANA database, to create and manage user authorizations, to create new or modify existing models of data etc.
• It is a client tool, which can be used to access local or remote HANA system.

4.What are the supported platforms for HANA studio?

The SAP HANA studio runs on the Eclipse platform 3.6. We can use the SAP HANA studio on the following platforms:

• Microsoft Windows x32 and x64 versions of: Windows XP, Windows Vista, Windows 7
• SUSE Linux Enterprise Server SLES 11: x86 64-bit version

Note: For Mac OS, HANA studio is available but there is no HANA client for that. 

5.What is SAP HANA 1.5, 1.2 or 1.0 SP03?

They are all the same thing, and 1.0 SP03 is touted to be the final name which should go into Ramp Up (beta) in Q4 2011. This allows any SAP Net Weaver BW 7.3 Data Warehouse to be migrated into a HANA appliance. HANA 1.0 SP03 specifically also accelerates BW calculations and planning, which means even more performance gains can be attained.

6.What is a restricted user in SAP HANA system?

Restricted users are those users who access HANA system with some applications and they don’t have SQL privileges on HANA system. When these users are created they don’t have any access initially.

7.What is schema in SAP-HANA?

There are 3 types of schemas.

1. User Defined Schema

2. System Defined Schema

3. SLT Derived Schema

1. User Defined Schema:

• These are created by user (DBA or System Administrator).

2. SLT Derived Schema:

• When SLT is configured, it creates schema in HANA system. All the tables replicated into HANA system are contained in this schema.

3. System Defined Schema:

• These schemas are delivered with the SAP HANA database and contain HANA system information. There are system schemas like _SYS_BIC, _SYS_BI, _SYS_REPO,_SYS_STATISTICS etc.
• A database schema is a way to logically group objects such as tables, views, stored procedures etc.

8.Maximum number of tables in a schema? In SAP HANA ?

• Maximum number of tables in a schema are  131072.

9.What can be the maximum number of columns in a table in SAP HANA ?

• Maximum number of columns in a HANA Table is 1000.

10.Explain what is schema mapping in SAP HANA ?

• Schema mapping is done when the physical schema in the target system is not the same as the physical schema in the source system.
• As mentioned earlier, suppose we are moving components from Development System (DEV) to Production System (PROD).The tables in DEV reside in DEV_SCHEMA and the same tables reside in PROD system in PROD_SCHEMA schema. If an attribute view is transported from DEV to PROD, it will not work because the schema name is referenced in the definition of attribute view. In order for the promoted objects to work in PROD, schema mapping needs to be set up in the target system.

11.What are System Requirements for SAP-HANA?

Java JRE 1.6 or 1.7 must be installed to run the SAP HANA studio. The Java runtime must be specified in the PATH variable. Make sure to choose the correct Java variant for installation of SAP HANA studio:

• 32-bit installation, choose a 32-bit Java variant.
• For a 64-bit installation, choose a 64-bit Java variant.

12.What is Meta data, Master data and Transaction data?

Meta Data:

• Meta Data is data about Data. It tells you about the structure of data or Meta Objects.

Master Data:

• This Data is key business information like Customer information, Employee, Materials etc. This is more like a reference data. For Ex. If a customer orders 10 units of your product instead of asking customer for his shipping address 10 times the same can be referenced from the Customer Master Data.

Transaction Data: 

• This is data related to day to day transactions.

13.What is a Delivery Unit in SAP HANA?

• Delivery unit (DU) is a container used by the Life Cycle Manager (LCM) to transport repository objects between the SAP HANA systems. The name of Delivery Unit must contain only capital letters (A-Z), digits (0-9) and underscores (_).

14.Why to configure Import server in SAP HANA ?

• In order to load data from external sources to SAP HANA we need to establish a connection with the server. To connect, we need to provide details of Business Objects Data Services repository and ODBC drivers. Once the connection is established, we can import the tables definition and then load data into table definition.

15.What is SAP HANA Appliance 1.0?

• AP HANA 1.0 is an analytics appliance which consists of certified hardware, and In Memory Database (IMDB), an Analytics Engine and some tooling for getting data in and out of HANA. The logic and structures are built by the user and a tool is used e.g. SAP Business Objects, to visualize or analyze data.

16.What is the prerequisite for Unit of Measure in SAP HANA ?

• We need to import the tables T006 & T006A for Unit of Measures.

17.Explain the column and Row store in HANA?

• HANA supports both type of data store in database. Row store is used when you need to use Select statement and no aggregations are performed.
• Column store is used to perform aggregations and HANA Modeling is supported only on Column based tables.

18.Which operating system is supported by HANA system?

• Only operating system that is supported by HANA is Suse Linux Enterprise Server SP1/SP2 (SLES SP1/2).

19.What are the different license keys types in HANA system? What is their validity?

1. Temporary License key :Temporary License keys are automatically installed when you install the HANA database. These keys are valid only for 90 days and you should request permanent license keys from SAP market place before expiry of this 90 days period after installation.
2. Permanent License Key  :Permanent License keys are valid till the predefine expiration date. License keys specify amount of memory licensed to target HANA installation.

20.What is the difference between Catalog and Content tab?

1. Catalog −This contains RDBMS objects like schemas, tables, views, procedures, etc.     You can open SQL editor and design database objects.
2. Content −This is used to maintain design time repository. You can create new packages and design Information views in HANA system. Various views can be created under content tab to meet business requirement and to perform analytical reports on the top of the Modeling views.

21.What are the different perspectives available in HANA?.

1. Modeler: used for creating various types of views and analytical privileges.
2. SAP HANA Development: Used for programming applications for creating development objects to access or update data models such as Server-side Java script or HTML files.
3. Administration: Used to monitor the system and change settings.
4. Debug: Used to debug code such as SQLScript (.procedure files) or Server-side Java script (.xsjs files).

22.What are the supported object types in modeler perspective?

• Attribute views
• Analytical views
• Calculation views
• Analytical privileges
• Procedures
• Decision tables
• Process Visibility Scenario

23.What is the main SAP HANA database component?

• Index Server consists of actual data engines for data processing including input SQL and MDX statements and performs authentic transactions.

24.Explain Parallel Processing in SAP HANA?

• Using the columnar data storage approach, the workload in SAP HANA is divided vertically.
• The columnar approach allows linear searching and aggregation of data rather than two-dimensional data structure. 
• If more than one column is to be processed, each task is assigned to diverse processor. Operations on one column are then collimated by column divisions processed by different processors.

25.Will the table size in SAP HANA database and in the source system the same?

• No as HANA database supports compression.

26.What is SQL Script?

• It is a set of SQL extensions for SAP HANA database which allow developers to push data intensive logic into the database.

27.When should be SQL Script used?

• It should be used in cases where other modeling constructs of HANA such as Attribute views or Analytic views are not sufficient.

28.What is Label Mapping?

• We can choose to associate an attribute with another attribute description. Label mapping is also called as Description mapping.
• For example if A1 has a label column B1, then you can rename B1 to A1.description. The related columns appear side by side during data preview.

29.What is Schema mapping?

• Schema mapping is done when the physical schema in the target system is not the same as the physical schema in the source system.

Note: Schema mapping only applies to references from repository objects to catalog objects. It is not intended to be used for repository to repository references.

30.What are different types of joins?

• A Join clause combines records from two or more tables /view in a database. 
• SAP HANA supports following types of join.

1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join
5. Referential Join
6. Text Join

INNER JOIN

• The INNER JOIN selects the set of records that match in both the Tables.
• Where to use:

1. Inner join should be used if referential integrity is ensured.
2. Inner Join is much faster that Outer Join thus is the preferred solution if possible from semantically perspective .
3. Attribute View: Inner Joins can be used to join different master data tables to a joint dimension
4. Analytical Views: Inner Joins can be used if referential integrity cannot be ensured.

Left Outer

• The Left Outer Join selects the complete set of records from first table (CUSTOMER), with the matching records (where available) in second table (SALES_ORDER).If there is no match, the right side will contain null.

Right Outer

• The Right Outer Join selects the complete set of records from second table (SALES_ORDER), with the matching records (where available) in first table (CUSTOMER). If there is no match, the left side will contain null. 

FULL OUTER JOIN 

• The FULL OUTER JOIN keyword selects all rows from the left table (table1) and from the right table (table2).
• The FULL OUTER JOIN keyword combines the result of both LEFT OUTER and RIGHT OUTER joins.

Referential Join

• Referential Join is semantically an inner join that assume that referential integrity is given. 
• Note: Referential integrity is the property of database which ensures that each foreign key value in a table exists as a primary key in the referenced table.
• Referential join is performance wise better than inner join, but only be used when you are sure that referential integrity is maintained.

Text Join

• Text Join is used in order to get language-specific data.

31.What is  SAP-HANA analytic privilege?

• Analytic Privileges restrict the user to view data for which they authorize.
• SAP HANA Analytic Privileges is used for Security purpose.
• SQL Privileges provide authorization on object level not at a record level, so provide a record or row-level authorization "Analytic Privileges" will be used.
• SAP HANA Analytic Privileges are used to provide authorization on below information view – Attribute View, Analytic View, Calculation View.

ATTRIBUTE VIEW QUESTIONS:

1.What is an Attribute View?.

• Attribute views are used to model entity based on the relationships between attribute data contained in multiple source tables. You can model Columns, Calculated columns and Hierarchies.

2.What is an Attribute?

• Attribute represents the descriptive data used in modeling. Example: City, Country, etc.

3.What are Attributes?

• Attribute view acts like a dimension. 
• It join multiple tables and act as Master. 
• Attribute view is reusable objects.

4.What are the advantages of attribute view?

• Attribute View act as Master data context, which provides Text or Description for Key/Non-key field.
• Attribute View can be reuse in Analytic View and Calculation View.
• Attributes View is used to select a subset of columns and rows from a database table.
• Attributes (fields) can be calculated from multiple table fields.
• There is no measure and aggregation option.

5.What are the types of attribute view?

1. Standard attribute view: It is a standard attribute which is created by table fields.
2. Time attribute view: It is Time attribute view, which is based on default time table
3. Derived attribute view :It is an attribute view which is derived from another existing attribute view.Derived attribute view will be opened in read-only mode. The only editable field is its description.

6. Detail of each part in Information Editor is as below -

1. Scenario Pane: In this pane the following node exists-

• Semantics
• Data foundation

    2. Detail Pane: In this pane following tab exists –

• Column
• View Properties
• Hierchery

    3.Semantics (Scenario Pane): This node represents output structure of the view. Here it is Dimension.

   4.Data Foundation (Scenario Pane):

• This node represents the table that we use for defining attribute view.
• Here we drop table for creating attribute view.

     5.Tab (columns, view Properties, Hierarchies) for details pane will be displayed.

     6.Local: Here all Local attribute detail will be displayed.

     7.Show: Filter for Local Attribute.

6.Which engine is used to create attribute view?

• Join engine is used to create attribute view.
• This engine is used when we execute any Attribute View in HANA or run native SQL on more than one table with join condition.
• If there are any calculations involved either in Attribute View or in native SQL then Join Engine will use Calculation Engine for calculations or fetch the result.

7.What is a Calculated Attribute?

• Calculated attributes are derived from one or more existing attributes or constants.
•  For example deriving the full name of a customer (first name and last name), assigning a constant value to an attribute that can be used for arithmetic calculations.

CALCULATION VIEW QUESTIONS:

1.What is Calculation View?

• SAP HANA Calculation view is a powerful information view.
• SAP HANA Analytic view measure can be selected from only one fact table. When there is need of More Fact table in information view then calculation view come in the picture.
• Calculation view supports complex calculation.
• The data foundation of the calculation view can include tables, column views, analytic views and calculation views.
• We can create Joins, Unions, Aggregation, and Projections on data sources.

2.What are the characteristic of SAP HANA Calculation View?

• Support Complex Calculation.
• Support OLTP and OLAP models.
• Support Client handling, language, currency conversion.
• Support Union, Projection, Aggregation, Rank, etc.

3.What are the types of calculation view?

• SAP HANA Graphical Calculation View (Created by SAP HANA Studio Graphical editor).
• SAP HANA Script-based calculations Views (Created by SQL Scripts by SAP HANA Studio).

4.What are the different types of nodes in calculation view?

• Different types of nodes:

1. Join: This node is used to join two source objects and pass the result to the next node. The join types can be inner, left outer, right outer and text join.Note: We can only add two source objects to a join node.
2. Union: This is used to perform union all operation between multiple sources. The source can be n number of objects.
3. Projection: This is used to select columns, filter the data and create additional columns before we use it in next nodes like a union, aggregation and rank.
4. Aggregation: This is used to perform aggregation on specific columns based on the selected attributes.
5. Rank: This is the exact replacement for RANK function in SQL. We can define the partition and order by clause based on the requirement.

5.What is calculation view with star join?

• Calculation View with Star Join does not allow base column tables, Attribute Views or Analytic views to add at data foundation.
• All Dimension tables must be changed to Dimension Calculation views to use in Star Join.
• All Fact tables can be added and can use default nodes in Calculation View.

6.What are the benefits of using Calculation view with Star join?

• It simplifies the design process as allows you to select multiple measures from multiple fact tables. Can implement 3NF using Star Join.
• If we compare restricted users with Standard users −

1. Restricted users can’t create objects in HANA database or their own Schemas.
2. They don’t have access to view any data in database as they don’t have generic Public role added to profile like standard users.
3. They can connect to HANA database only using HTTP/HTTPS.

7.Can we call an analytic view or calculation view inside another calculation view in SAP HANA ?

• Yes. We can call all the views (attribute, analytic and calculation view) inside a calculation view.

Note: Calculation views are composite views and can be used to combine other views. It can consume other Analytical, Attribute, other Calculation Views & tables. It can perform complex calculations not possible with other views.

8.Which engine is used to create calculation view?

• SQL Engine: SQL Engine which is also known as SQL Parser/interface is used for all sorts of SQL statements generated by frontend application via different different clients and also for native sql run at database level.
• From SAP HANA SP7, we have an option for Calculation Views in ‘Properties’ section, where we have an option to choose calculation view to run in ‘SQL Engine’. Advantage of this option is that, instead of moving data between multiple engines HANA executes the entire script in SQL Engine to get the final result.

ANALYTIC VIEW QUESTIONS:

1.What is analytic view?

• Analytic View is in the form of Star schema, wherein we join one Fact table to multiple Dimension tables. Analytic views use real power of SAP HANA to perform complex calculations and aggregate functions by joining tables in form of star schema and by executing Star schema queries.

2.What are the properties of analytic view?.

• Following are the properties of SAP HANA Analytic View −

1. Analytic Views are used to perform complex calculations and Aggregate functions like Sum, Count, Min, Max, Etc.
2. Analytic Views are designed to run Start schema queries.
3. Each Analytic View has one Fact table surrounded by multiple dimension tables. Fact table contains primary key for each Dim table and measures.
4. Analytic Views are similar to Info Objects and Info sets of SAP BW.

3.What is star join in analytic view?

• Create join in order to join the attributes view with the fact table.

4.Which engine is used to create analytic view?

• OLAP Engine:

1. This engine will be called in the back-end whenever we run any queries on Analytic Views in SAP HANA.
2. If there are no additional calculations performed like calculated columns, restricted measures and counters, then everything will be processed in OLAP Engine.
3. OLAP Engine acts as join engine for those Attribute Views used in Analytic Views, without any calculated columns
4. All the join engine work will be converted into ‘BwPopJoin’ which is part of OLAP Engine.
5. If there are any calculations present, to be performed then Calculation engine will be used along with OLAP Engine.