Creating Attribute views in SAP HANA Studio
Go to Modeler perspective of SAP HANA studio. In the content node, select a package, right click and select ‘New’ – ‘Attribute View’.
Specify the name and description of the attribute view-
In this window, you can also copy an exiting attribute view. Here in subtype, you can select different types of attribute views – ‘Time’ and ‘Derived’
Click the ‘Finish’ button and the corresponding modeling editor opens.
The editor of an attribute comprised two sections – ‘Data Foundation’ and ‘Semantics’
The DATA FOUNDATION is used to add tables, define joins and add attributes. Below figure shows a simple example based in SFLIGHT table-
In the Sematics node, you can define the following metadata about the attribute view:
- Key fields in attribute view. Please note that every attribute view must contain at least one key field. You can also define text(labels) for attributes or hide attributes.
- You can define how client field is handled
- You can define hierarchies if any
The layout of the semantics section is shown in figure-
All the selected columns from the SFLIGHT table are marked as key fields.
To able to use the attribute view, you have to now save and activate it.
Sometimes you may see an error message while activation. The error can come due to missing key fields, incorrectly defined calculated fields, incorrect joins etc. The cause of the error may not be sometimes obvious.
Here, the table is client dependent. You can define in Semantics property, the default client as ‘Session client’ or ‘Cross Client’.
Based on the session, the only the relevant client data will be displayed by the attribute view. However, if cross-client is selected, data from all the clients is displayed.
Please note: For every database connection in HANA studio, session context stores certain properties of the connection for example default client of the user.
Go to the data foundation tab of the same attribute view. Here you can add more tables. You can either add them individually, or select the table and choose ‘Propose tables’ from the context menu.
Select SCARR and SPFLI.
Define the joins between these tables-
Select each join and define it as ‘Referential’ and ‘n:1’ in the properties pane.
Select the fields which are required in the output. Key fields remains the same. So, without changing anything in Semantics, you can save and activate your attribute view.
Check the data by selecting the magnifying glass option and going to the ‘Raw Data’ tab.
Using Text Joins in Attribute View
To illustrate the usage of text joins in attribute view, create a new attribute view on the tables flight meals (SMEAL) which has its corresponding text stored in the table SMEALT.
Since, filtering is done based on the language, the cardinality of text join is always 1:1
Select any join and define it as ‘Text join’ in the properties pane. Select the language column.
You can also apply filters for the columns in attribute views. Select MEAL_TYPE from the SMEAL table and define filter as equal to ‘FI’. That is, the attribute view will show data only for the FI type of meals.
Define the key fields in the semantics. Save and activate the view, check the data preview.
Calculated Fields
In attribute views, calculated fields can be defined which are also known as ‘virtual attributes’ or ‘derived columns’
Open the attribute view AT_SFLIGHT_1 created earlier in this post.
In the data foundation, in the output window, select the calculated columns folder, right click and select ‘New’.
In the window that opens, specify the name, description and a data type.
Give the expression and validate the syntax.
Save and activate the view, observe the data in the new column.
Calculated views are also supported in other view types (analytical and calculation views), where they are used mainly for currency and unit conversions.
Hierarchies
Many times, data has hierarchical structure, for example, Years-Months-Days, Country-Region-City etc.
Hierarchies play an important role in data analyses. You can drill up and drill down to see aggregated or detailed data in the reports according to the defined hierarchies.
For attribute views, hierarchies are defined in the SEMANTICS section.
SAP HANA supports two kinds of hierarchies:
- Parent-Child hierarchy –
In this type, view has a single attribute with hierarchical relationship within itself (Self-Referencing). Here, a root node must be defined. For example, Manager and employee both have an employee id attribute in the view. Their relationship is defined by creating Manager as root node and assigning employee ids under it.
- Level hierarchy – Here there are two or more columns in the view with hierarchical relationship. For example, Region, Country and City.
In the attribute view created earlier in this post for SFLIGHT table, go to the semantics section and define hierarchies as below:
Time in Attibute Views
Many times data has time or validity period in the tables. For example, flight date (FLDATE) in the SFLIGHT table. There is a need for determining the corresponding calendar week, month and year or fiscal year and period for the date column.
In ABAP, this information is stored in the T009 and T009B tables. These tables are pool or cluster tables. While migrating to SAP HANA, these are converted to standard tables. Using the fiscal year variant setting stored in transaction SPRO, various function modules were used in ABAP to convert the date to corresponding fiscal period.
In SAP HANA, you can define these mapping in the attribute view (both normal or gregorian calendar or the fiscal calendar)
To do so, we need to first generate the time data in special SAP HANA technical tables.
You can select the option ‘Generate time data’ in the Quick View screen of the Modeler perspective for this purpose. Specify the details for calendar type and time period.
For our example, we will select Fiscal calendar from 1999 till 2021.
You can now use the underlying table M_FISCAL_CALENDAR (schema _SYS_BI) in attribute views.
Create a new attribute view and include the above table and the SFLIGHT table in the view. Since we want to use only a fixed variant, we can define a static filter for the calendar_variant in the view.
Now generate time data for the gregorian calendar also and create an attribute view to display the SFLIGHT data as per the calendar year.
(Note: In this case, the technical table M_TIME_DIMENSION will be create in the _SYS_BI schema)
Attribute Views of type Time
You can also define an attribute view containing only time data. To do so, select the type as ‘Time’ while creating an attribute view.
Below view is created as type time based on Gregorian calendar-
Since the view is created with date as a key field, this can be used as time dimension in analytical views where the transaction data has date column.
Runtime objects and SQL Access for Attribute Views
When attribute views are activated, column views are created in the schema _SYS_BIC that can be accessed via normal SQL.
In addition to this, one column view is created for each hierarchy defined in the attribute view.
For our attribute view AT_SFLIGHT_1, below column views exist in the _SYS_BIC schema-
Note here that the names of the runtime objects contain the package names. This is because you can create an object with the same name in a different package.
Please note that the attribute views are not optimized for calculations like column aggregations. They are efficient for join calculations. Thus, while accessing views via SQL in SAP HANA, make sure that you use statements that are optimal for the view type. (For example, avoid using sum over a column while accessing attribute view)
To learn more, go for complete course in SAP HANA Modeling.
