SAP HANA Database Concepts: Tables, Models and View ProcessingSAP HANA database is a very capable database system but it requires some understanding, and to be
used correctly, to obtain good performance. Let's see the key concepts of the SAP HANA database.
We will explain how approach modeling in order to to achieve best results.
The SAP HANA database allows you to model your data as tables and views.
Tables are tabular data structures, each row identifying a particular entity, and each column having a unique name.
The data fields of one row are called the attributes of the entity. The word “attribute” is used with different
meanings. It may refers to a table column, a particular data field of a table row, or the contents of such a data field. The respective meaning will be clear from the context.
Views are combinations and selections of data from tables modeled to serve a particular purpose.
Views always appear like readable tables, i. e. database operations which read from tables can also
be used to read data from views.
SAP HANA can use several types of views.
In analytics applications star schemas are a general pattern: Fact tables are lists of business transactions
while the linked-in data are typically master data. These linked-in tables are often called dimension tables.
A fact table surrounded by its linked-in dimension tables is often called a star schema because of the geometry of its graphical model.
In SAP HANA a star schema can be created using tables or views by surrounding an analytic or calculation view with attribute views.
SAP HANA Modeling Views
Before we continue to discuss how to model in SAP HANA, first it is important to understand each of
the differing SAP HANA modeling view types and their capabilities.
Attribute views are used to give master data tables context. This context is provided by text tables
which give meaning to the master data. For example, if our fact table or analytic view only contains
some numeric ID for each dealer then we can link in information about each dealer using an attribute view. We could then display the dealers’ names and addresses instead of their IDs thus providing the context for the master data table.
Attribute views are used to select a subset of columns and rows from a data table. As it is of little use
to sum up attributes from master data tables there is no need to define measures or aggregates for attribute views.
You can also use attribute views to join master data tables to each other, e. g. joining “Plant” to “Material”.
Analytic views are used to build a data foundation based on transactional tables. You can create a
selection of measures (also knew as key figures), add attributes and join attribute views.
Analytic views leverage the computing power of SAP HANA to calculate aggregate data, e. g. the
number of sold cars per country, or the maximum power consumption per day.
They are defined on at least one fact table, i. e. a table which contains e. g. one row per sold car or one row per power meter reading, or more generally speaking, some form of business transaction records.
Fact tables can be joined to allow access to more detailed data using a single analytic view.
Analytic views can be defined on a single table, or joined tables.
Analytic views can contain two types of attributes (or columns), so-called measures and normal
attributes. Measures are attributes for which an aggregation must be defined. If analytic views are used in SQL statements then the measures have to be aggregated e. g. using the SQL
can be handled as regular columns. For them there is no need to be aggregated.
Calculation views are used to provide composites of other views. They are essentially a view which is
based on the result of an SQLScript. These scripts can join or union two or more data flows or invoke
built-in or generic SQL functions.
Calculation views are defined as either graphical views or scripted views depending on how they are
created. They can be used in the same way as analytic views, however, in contrast to analytic views it
is possible to join several fact tables in a calculation view. Calculation views always have at least one
Graphical views can be modeled using the graphical modeling features of the SAP HANA Information Modeler.
Scripted views are created as sequences of SQLScript statements (i.e SQLScript procedures)
SAP HANA View Processing
A basic understanding how SAP HANA processes views is required so that you can ensure that data
transfer within the database system is minimized.
A simplified view of the system is illustrated below.
From the diagram we can see that SAP HANA has three types of views that are used based on the
requirements of the model.
- Calculation views – used on top of analytic view and attribute views to perform complex calculations that cannot be achieved by the attribute or analytic views alone.
- Analytic views – used for calculation and aggregation “based on star schema” or similar
- Attribute views –used for all type of joins
An SQL optimizer decides the best way to call the differing functions of the database system based
on the models and queries involved.
This diagram is in someway simplified. So for example an analytic view with a calculated attribute or that
includes an attribute view containing a calculated attribute, will become a calculation view.
This should be taken into consideration during modeling because it can have a large impact on the
performance of the data model.
Read related post:
SAP HANA Modeling Guidance
SAP HANA Data Modeling and Data Replication