SAP HANA is an in-memory database that allows you to create informational views that you can then use to create data models with more than one dimension. In order to perform these tasks, you need to know how to model in the system. Our SAP HANA modeling tutorial will give you a basic overview that explains what modeling is, the current best practices for creating those models, and techniques regarding performance optimization within SAP HANA. Moreover, note that performance optimization relates closely to the modeling process. Although some knowledge of the SAP system may be useful to you as you read through our SAP HANA modeling tutorial, it can also be a great resource for beginners who have an interest in learning about this specific aspect of the database platform.
SAP HANA Modeling Tutorial: Overview
Modeling within SAP HANA refers to an activity you can perform that allows you to either refine or compartmentalize certain data points by creating a personalized “Information View” for various scenarios. You can then use the data within Information View to report statistics to the client or help them make a decision about the direction of their project.
Finally, there are two main types of content in an Information View:
Attribute data includes data that you cannot measure or that is descriptive in some way, such as vendor names and IDs or city locations.
This category of content includes data that is quantifiable or that you can use for calculations, such as total revenues, or product quantities the client sold within a given period. Note that you can derive these measures from the Calculation View. However, you cannot create them in the Attribute View.
SAP HANA supports three primary types of attributes:
- Simple – You can derive these attributes from the data foundation
- Calculated – You will find these attributes based on one or more constants that already exist within the data. A mathematical calculation is a good example of this type of attribute.
- Local – You can use local attributes within various modeling views to customize how that attribute behaves. Finally, this means it is specific to Modeling View, and you cannot use it outside of that particular view.
The in-memory database supports four measurements:
- Simple – Like the simple attribute, you can derive this measure from the foundation of the data.
- Calculated – This measure functions in the same way as the calculated attribute we outlined in the previous section.
- Restricted – You’ll need this measure if you want to filter for values based on rules you set up for the values of a particular attribute.
- Counter – This is a specialized column that can display unique attribute numbers in Analytic or Calculation Views.
The three main view types you’ll use within the system are:
- Attribute – You will use this for any master data.
- Analytic – This one will help you create fact tables when necessary.
- Calculation – You can use this option to create more complex viewpoints within the system.
More Resources for the SAP HANA Enthusiasts:
SAP HANA Modeling Tutorial: Privileges
The SAP HANA modeling process requires each user to have privileges within the system before they can work on modeling tasks. Finally, we will use this section of the tutorial to outline the privileges you may need as a user before you can begin modeling within SAP.
These are SQL-based privileges that can provide you with both read and write access to objects within a particular database. Note that your privileges may only grant you access to specific databases while restricting your access to others. Moreover, you will need the following object privileges for modeling purposes:
- SELECT on the _SYS_BI and SYS_BIC schemas
- SELECT on the Table schema
- EXECUTE on the REPOSITORY_REST SYS schema
You will need these privileges to authorize particular actions on individuals packets of data. These package privileges include the following options:
- REPO.MAINTAIN_NATIVE_PACKAGES on the Root Package
- REPO.READ, REPO.EDIT_NATIVE_OBJECTS, and REPO.ACTIVATE_NATIVE_OBJECTS on the “Content Objects” packages
If you want to have full access to data in all available Information Views within the system, you will need the _SYS_BI_CP_ALL privilege. Create and assign these privileges to specific users to grant them restricted access.
Other privileges you might need for modeling include the following options:
- GRANT OPTION to provide permissions on a user’s own schema using the schema name and _SYS_REPO
- Create delivery units with REPO.MAINTAIN_DELIVERY_UNITS
- Import or export those units with REPO.IMPORT or REPO.EXPORT
- Create permissions in foreign workspaces with REPO>WORK_IN_FOREIGN_WORKSPACES
SAP HANA Modeling Tutorial: Performance Optimization Techniques
Understanding and applying the following rules during modeling within SAP HANA may allow you to optimize performance across the board.
- Use a projection node in conjunction with all Information or Table Views.
- Nodes should narrow column sets to improve performance levels
- Apply filters at your projection nodes
- Don’t use JOIN nodes while in Calculation View. We recommend you switch to UNION in these cases
- Restrict your datasets within Analytic or Calculation Views using Variable or Input parameters.
- Perform the Calculation View prior to aggregating the data.
- Redefine the hierarchies in Calculation View to make them visible in that window.
- Note that hierarchies are invisible in Analytic View.
- Attribute labels and descriptions will not display in Calculation View.
- While in the Information Model, don’t mix CE Function with SQL script.
Finally, our SAP HANA modeling tutorial gives you an overview of what modeling is, the various attribute, measure, and view types you will encounter when using it, what privileges you need to access these functions, and some of the best practices to optimize performance levels in the system. Please share your SAP HANA modeling thoughts with us in the section below.