SAP HANA Modeling

Qs 1. What are the types of modeling views in HANA?
There are 3 types of modeling in HANA:

1. Attribute View
2. Analytic View
3. Calculation View

Qs 2. What is a Delivery Unit?
A delivery unit could be regarded as a collection of several packages, used for transporting content from one HANA system to another.
Delivery unit (DU) is a container used by the Life Cycle Manager (LCM) to transport repository objects.

Qs 3. There are 2 types of packages in content. What are they?
In HANA, 2 types of packages can be created.

• Structural: Package only contains sub-packages. It cannot contain repository objects.
• Non-Structural: Package contains both repository objects and sub-packages.

By default it creates Non-structural.

Qs 4. What are the different engines available in HANA?
In HANA there are mainly 3 types of engines.

• Join Engine: used for attribute views
• OLAP engine: used for analytic views
• Calculation Engine: used for calculation views

To know more about HANA engine, read Understanding SAP HANA Engine

Qs 5. There is an analytic view which has calculated column defined. Which engine will be used when calling this view?
By default analytic view is executed in OLAP engine. But this changes when analytic view has a calculated column.
If an analytic view has a calculated column then internally it is treated as calculation view and executed in calculation engine.

Qs 6. If my Analytic View foundation is joined to attribute views, is both the OLAP and JOIN Engine used?
Nope - during activation of the analytic views, the joins in the attribute views get 'flattened' and included in the analytic view run time object. Only the OLAP engine will be used then.

Qs 7. What is text table in SAP? What is the purpose of creating text table?
Table A is a text table of table B if the key of A comprises the key of B and an additional language key field (field of data type LANG). Table A may therefore contain explanatory text in several languages for each key entry of B.

Purpose: Text Join is used to fetch the description based on user's session language. Once we implement the text join in SAP HANA, it automatically finds out user's language and give description in that language.

To know more about Text table, read SAP HANA Text Join

Qs 8. We need to take UNION of 2 tables and then create a calculated column. Can we implement this in analytic view?

Analytic view doesn’t support UNION. We need calculation view to implement this logic.

Qs 9. Can we call an analytic view in another analytic view?

NO. We cannot call an analytic view inside other analytic view. 

Qs 10. Can we call an analytic view or calculation view inside another calculation view?

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. 

Qs 11. What are the different types of calculation view? Which one would you prefer?

There are 2 types of calculation view possible.

1. Graphical calculation view
2. Scripted calculation view 

Graphical calculation views are created using the graphical editor. Scripted calculation views are created using SQL Editor.

Graphical based calculation views offer better performance compared to SQL based calculation views.

But in scenarios where graphical calculation views cannot implement business logic, we need to use scripted calculation view. Complex calculations which are not possible thru graphical approach, can be created using SQLScript.

Qs 12. Explain 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.

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.

In this scenario, the schema mapping to be created is

Authoring Schema	Physical Schema
DEV_SCHEMA	PROD_SCHEMA

To know more about schema mapping, read SAP HANA Schema Mapping

Qs 13. What is the system table used to save metadata and other information of Schema Mappings?

The mapping between authoring and physical schemas is stored in the configuration table “_SYS_BI”.”M_SCHEMA_MAPPING”. 

Qs 14. What’s the purpose of Generating Time Data?

Generate Time Data option under Quick Launch helps the user to generate the Time data so that it can be used for the creation of Time based Attribute Views.

When you click Generate Time Data, you will be provided with two options

Gregorian: Mainly the data will be generated based on From and To years along with the granularity (Hour, Minute, Second, Day, Month) mentioned.

Fiscal: In this case, the time data will be generated based on the variant defined (some companies may use their own time period).

Standard tables T005T, T005U, T009 and T009B in SAP HANA are required if you go for FISCAL type.

Qs 15. In which configuration tables the generated time data information will be stored in HANA?

For Gregorian calendar type 

• M_TIME_DIMENSION_YEAR
• M_TIME_DIMENSION_MONTH
• M_TIME_DIMENSION_WEEK
• M_TIME_DIMENSION

For Fiscal

• M_FISCAL_CALENDAR

All these tables are under schema _SYS_BI.

Qs 16. What is the difference between attribute and measure?

Columns of modeling view can be classified as Attribute or Measure.

• Attribute: Non-measurable, Descriptive data, such as customer ID, city, and country
• Measure: Quantifiable data, such as revenue, quantity sold and counters.

Let us take an example of an organization’s sales tables. The table contains columns like SALES, PROFIT, PRODUCT, CUSTOMER NAME, YEAR, COUNTRY etc.

The important business use cases will be like:

• What is the sales value for country India for year 2013?
• What is the profit for product ‘XYZ’ for country USA?

The SALES and PROFIT columns are numeric values. These columns are measurable and can be aggregated. While the columns PRODUCT, CUSTOMER NAME, YEAR and COUNTRY are descriptive and non-measurable.

Here PRODUCT, CUSTOMER NAME, YEAR and COUNTRY are attributes, while SALES and PROFIT are measures.

Qs 17. What is a Private Attribute in HANA?

Private attributes are the attributes used inside a modeling views and cannot be used outside the view. These are used in a modeling view to customize the behavior of an attribute for only that view. 

Qs 18. What are the different types of measures in modeling view?

There are 3 types of measures in HANA:

Simple Measure:

Simple Measure is a measurable analytical element that is derived from the data foundation.

Calculated Measure:

Calculated Measures are created using some calculation on top of an existing measures or attributes.

Private Measures:

Private Measures are measures used inside a modeling views and cannot be used outside the view.

Qs 19. What is the difference between Variable and Input parameter in HANA?

Variables are bound to columns and are used for filtering using WHERE clauses. As such, they can only contain the values available in the Columns they relate to. Variables do not impact the execution

HANA Input parameters used to manipulate the execution based on user input. Sometimes you might not want a variable to just restrict the data of a view. But you also want to take input from the user and process it, returning dynamic data based on the user selection.  Input Parameters makes this possible.

To know more about Variables and Input Parameter, read Variables and Input Parameters in HANA

Qs 20. In which configuration table you can find the variables information?

There are 4 system tables under the schema _SYS_BI which contains information about Variables.

• BIMC_VARIABLE
• BIMC_VARIABLE_ASSIGNMENT
• BIMC_VARIABLE_VIEW
• BIMC_VARIABLE_VALUE

Qs 21. What are the different types of Input parameters supported?

The following types of Input parameters are supported.

Currency: 

Use this during currency conversion where the end user should specify a source or target currency.

Date: 

Use this to retrieve a date from the end user using a calendar type input box.

Static List: 

Use this when the end user should have a set list of values to choose from.

Attribute Value: 

When an Input Variable has this type, it serves the same purpose as a normal Variable.

None: 

If none of the above applies you do not have to specify an Input Variable type. The Type can be left blank.

Qs 22. How do we pass input parameters from SQL query?

The value of Input Parameter is passed using PLACEHOLDER clause.

For example:

    ('PLACEHOLDER' = ('$$Param1$$', 'value'))

Qs 23. Suppose you have the sales data in a database table in a different currency. How can you generate a Sales report for a region in a particular currency?

Create an Analytic view by selecting the table column containing the sales data and currency and perform currency conversion. Once the view is activated, we can use it to generate reports.

Qs 24. What are the factors that affect currency conversion?

Currency conversion is performed based on source currency, target currency, exchange rate, and date of conversion. You can select currency from the attribute data used in the view. Currency conversion is enabled for Analytic view and Calculation views. 

Qs 25. What is the prerequisite for doing the currency conversion?

You need to import tables TCURC, TCURF, TCURN, TCURR, TCURT, TCURV, TCURW and TCURX. 

Qs 26. What is the prerequisite for Unit of Measure?

You need to import the tables T006 & T006A. 

Qs 27. What happens when you activate an object in HANA?

When we activate an object, it becomes available for reporting and analysis.

After successful activation of a view, a run time object is created in _SYS_BIC schema.

For example, suppose there is a calculation view CV_VIEW1 in package “MyPackage”. After activating this view, a run time object (column view) will be created in _SYS_BIC schema with name “MyPackage/CV_VIEW1”. This column view is used when we do the data preview of calculation view.

Qs 28. What is the difference between Activate and Redeploy?

Activate - Deploys the inactive objects.

Redeploy - Deploys the active objects in one of the following scenarios:

• If your runtime object gets corrupted or deleted, and you want to create it again.
• In case of runtime problems during object activation, and the object status is still active. 

Qs 29. How do we control row-level access in HANA?

Analytic privilege can be used to maintain row-level access. It is used to grant different users access to different portions of data in the same view depending on their business role. 

Qs 30. What is the importance of _SYS_BI_CP_ALL analytic privilege?

_SYS_BI_CP_ALL analytical privilege gives access to read from all information model views. We can control this by creating our own analytical privileges and assigning it to only the views that a user should be reporting against and at the level they want to view data.

If a user has the _SYS_BI_CP_ALL Analytic Privilege assigned all other restrictions are ignored. This is generally used for developer roles.

Qs 31. Suppose we want to give minimum authorization to end users so that they can only see the output of modeling views. The users should not be able to perform any other activities. What are the privileges that should be assigned to user?

We need to assign following privileges:

• Read access to the package containing modeling view
• Execute & Select access on _SYS_BI
• Execute & Select access on _SYS_BIC
• Execute on REPOSITORY_REST

Qs 32. What are the types of schema in HANA?

In HANA, there are 3 types of schemas.

• User Defined Schema: These are created by user (DBA or System Administrator)
• 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
• 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.

Qs 33. If tables of a schema are used to build modeling views then it’s necessary to grant SELECT privilege to user _SYS_REPO. Why?

If tables of a schema (say SCHEMA_ABC) are used to build modeling views, then following SQL statement must be executed before activating any such modeling views.

GRANT SELECT ON SCHEMA SCHEMA_ABC TO _SYS_REPO WITH GRANT OPTION

Think of _SYS_REPO as "the activation guy". It takes your models and creates the necessary runtime objects from them. Therefore user _SYS_REPO needs the allowance to select YOUR tables/views. (If _SYS_REPO user cannot select on the tables specified in the from-clause of the view-definition, it cannot define that view)

If other users need to select this view (obviously this is always the case, otherwise the views would not make sense), then _SYS_REPO needs to have the additional allowance to grant the select further (WITH GRANT OPTION).

Therefor after having activated all your models that access data in your schemas, _SYS_REPO wants to give you (and probably other users) read access to the activated models.

Qs 34. What is Auto Documentation feature in HANA?

When the user creates Views in HANA Studio under “Contents”, he can automatically generate the documentation about the views. This generated document will have the details about all the view belongs to a package which he selected for Auto Documentation.

The user can invoke the Auto Documentation from three places.

• Right click Context menu of the Package or the Views
• On the top right corner of the opened view
• Quick Launch->Content->Auto Documentation

Qs 35. What is the difference among Raw Data, Distinct values and Analysis while doing the Data Preview?

Raw Data: It displays all attributes along with data in tabular format.

Distinct Values: It displays all attributes along with data in graphical format.

Analysis: It displays all attributes and measures in graphical format.

Qs 36. What is Hierarchy? What are the types of hierarchy supported in HANA?

Hierarchies are used to structure and define the relationships among attributes in a modeling view.

Organizations define hierarchies for information classification, allowing roll-up and drill-down analysis. For example, a sales organization might allocate a sales person to a country and a country to a region. Sales data can then be aggregated and analyzed by region, country, or sales person.

There are two types of hierarchies:

Level Hierarchies are hierarchies that are rigid in nature, where the root and the child nodes can be accessed only in the defined order. For example, organizational structures, and so on.

Parent/Child Hierarchies are value hierarchies, that is, hierarchies derived from the value of a node. For example, a Bill of Materials (BOM) contains Assembly and Part hierarchies, and an Employee Master record contains Employee and Manager data. The hierarchy can be explored based on a selected parent; there are also cases where the child can be a parent.

Click here to go to Next Chapter. 1.3  SAP HANA SLT Replication