Attribute Views

Attribute views are joins on multiple tables wherein the underlying tables are mostly related to master data like customer information, fiscal period, employee details etc.

While defining attribute views, we need to take care of the key fields of the base tables, foreign key relationships, unit and currency conversions and hierarchical relationships.

Attribute views are mainly important because of two reasons:

• These are used as dimensions in Analytical views or as nodes in calculation views
• These serve as data provider for text search across several tables

In this post, several attribute views will be created to demonstrate different types of functionalities.

Before starting with the modeling of Attribute views in SAP HANA, let us clear some basic principles.

Modeling Concepts

Attributes refer to the columns of the base tables. For example, carrid column in SCARR table. Attribute views can have columns from one or several physical tables. Calculated columns can also be defined in attribute views.

Key Attributes are those attributes that uniquely specify an entry.Key fields play an important role when attribute view is used as dimension in analytical view.

Filters define restrictions applied on the values of a column. For example carrid equal to ‘UA’ is a filter.

Hierarchies are used to specify parent child relationships between columns. For example employee ids can be assigned to another employee id as a employee-Manager relationship.

In contrast to the views in SAP ABAP which can have only inner join, attribute views in SAP HANA can have more kinds of join.

Join types in Attribute Views

Sample Data

Tables SFLIGHT and SCARR are sap standard tables and have a foreign key relationship via carrid. (For the sake of simplicity, the client is ignored here)

The tables have an n:1 relationship. (For 1 flight in SCARR, there can be n entries in SFLIGHT and for any flight in SFLIGHT, there can be only 1 entry in SCARR)

Also, the SCARR table may contain airlines for which there is no entry in SFLIGHT.

Inner Join

If there is a matching entry in both the tables, all combinations are included in the result. For example for airline LH, two entries will be shown in the result for inner join between SFLIGHT and SCARR

Outer Join

If it is a left outer join, the result will  have all the entries from the left table even if there is not matching entry in the right table.

If it is a right outer join, the result set will have all the entries from the right table.

For Full outer join, the result set will have all the entries from both the tables.

For SFLIGHT as Left table and SCARR as right table, below will be the result set for different types of joins:

Please note: Full Outer joins are not supported by attribute views

In addition to these standard joins, two other joins are also used while modeling views in SAP HANA. These are:

• Text joins – These joins are used when there are language dependent texts in a table. At runtime, based on the context, the view result is displayed in correct language. For this purpose, the column with the language key must be included in the text table.
• Referential Join – Referential join is a special type of inner join where system assumes that there is referential integrity maintained between the two tables. Thus if no field from the right table in queried, the right table is ignored and it is not checked if there is a matching entry. Mostly referential joins are used in attribute views as they have better performance over inner join (right table is only looked up when there is a field from right table in the query, otherwise system scans only the left table and ignores the right table)

In joins , using SQL, you can also define ‘less than’ or ‘greater than’ conditions. However, attribute views only support equi joins.

In the next post, the step by step procedure for creating and modeling views will be discussed.