Blog
SAP HANA Modelling Interview Questions
- September 21, 2015
- Posted by: admin
- Category: Uncategorized
There are 3 types of modeling in HANA:
- Attribute View
- Analytic View
- Calculation View
Delivery unit (DU) is a container used by the Life Cycle Manager (LCM) to transport repository objects.
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.
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
If an analytic view has a calculated column then internally it is treated as calculation view and executed in calculation engine.
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.
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.
There are 2 types of calculation view possible.
- Graphical calculation view
- 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.
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 |
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.
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.
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.
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.
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
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
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.
For example:
(‘PLACEHOLDER’ = (‘$$Param1$$’, ‘value’))
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.
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_BICschema with name “MyPackage/CV_VIEW1”. This column view is used when we do the data preview of calculation view.
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.
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.
If a user has the _SYS_BI_CP_ALL Analytic Privilege assigned all other restrictions are ignored. This is generally used for developer roles.
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
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. If tables of a schema are used to build modeling views then it’s necessary to grant SELECT privilege to user _SYS_REPO. Why?
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.
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
Distinct Values: It displays all attributes along with data in graphical format.
Analysis: It displays all attributes and measures in graphical format.
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.