Attribute View in SAP HANA: How to Create?

What are Attributes?

Attribute view acts like a dimension. It join multiple tables and act as Master. Attribute view is reusable objects.

Attribute view has the following advantage-

• Attribute View act as Master data context, which provides Text or Description for Key/Non-key field.
• Attribute View can be reuse in Analytic View and Calculation View.
• Attributes View is used to select a subset of columns and rows from a database table.
• Attributes (fields) can be calculated from multiple table fields.
• There is no measure and aggregation option.

Attribute View Type

Attribute View are 3 types-

Attribute View Type	Description
Standard	It is a standard attribute which is created by table fields.
Time	It is Time attribute view, which is based on default time table – For calendar type Gregorian – M_TIME_DIMENSION M_TIME_DIMENSION_ YEAR M_TIME_DIMENSION_ MONTH M_TIME_DIMENSION_WEEK For calendar type Fiscal – M_FISCAL_CALENDAR
Derived	It is an attribute view which is derived from another existing attribute view. Derived attribute view will be opened in read-only mode. The only editable field is its description. Copy From – When you want to define an attribute view, by copying an existing attribute View, then you can use “Copy From” option.

Note: Difference between Derived and Copy from is, in the case of derived, you can only edit the description of new attribute view while in the case of copy, you can modify everything entirely.

How to Create Attribute View in SAP HANA

Standard view creation has pre-define step as below-

Table Creation for Attribute View

Here we are going to create Standard Attribute View for product table, so firstly we create “PRODUCT” and “PRODUCT_DESC” Table.

SQL Script is shown as below for table creation –

Product table Script –

CREATE	 COLUMN TABLE "DHK_SCHEMA"."PRODUCT"
(	
"PRODUCT_ID"	NVARCHAR (10) PRIMARY KEY,								
"SUPPLIER_ID"	NVARCHAR (10),				
"CATEGORY" NVARCHAR (3),				
"PRICE"	 DECIMAL (5,2)
);		

INSERT	INTO "DHK_SCHEMA"."PRODUCT" VALUES ('A0001','10000','A', 500.00);																	
INSERT	INTO "DHK_SCHEMA"."PRODUCT" VALUES ('A0002','10000','B', 300.00);																	
INSERT INTO "DHK_SCHEMA"."PRODUCT" VALUES ('A0003','10000','C', 200.00);																	
INSERT	INTO "DHK_SCHEMA"."PRODUCT" VALUES ('A0004','10000','D', 100.00);																	
INSERT	INTO "DHK_SCHEMA"."PRODUCT" VALUES ('A0005','10000','A', 550.00);

Product Description table Script-

CREATE COLUMN TABLE "DHK_SCHEMA"."PRODUCT_DESC"									
(	
"PRODUCT_ID" NVARCHAR (10) PRIMARY KEY,								
"PRODUCT_NAME" NVARCHAR (10)				
);

INSERT INTO	"DHK_SCHEMA"."PRODUCT_DESC"	VALUES ('A0001','PRODUCT1');															
INSERT INTO	"DHK_SCHEMA"."PRODUCT_DESC"	VALUES ('A0002','PRODUCT2');															
INSERT INTO	"DHK_SCHEMA"."PRODUCT_DESC"	VALUES ('A0003','PRODUCT3');															
INSERT INTO	"DHK_SCHEMA"."PRODUCT_DESC"	VALUES ('A0004','PRODUCT4');															
INSERT INTO	"DHK_SCHEMA"."PRODUCT_DESC"	VALUES ('A0005','PRODUCT5');

Now table “PRODUCT” and “PRODUCT_DESC” is created in schema “DHK_SCHEMA”.

Attribute View Creation

STEP 1) Select SAP HANA System.

1. Select content Folder.
2. Select Non-Structural Package Modelling under Package DHK_SCHEMA in the content node and right click->new.
3. Select Attribute view option.

STEP 2) Enter information.

1. Enter Attribute Name and Label.
2. Select View Type, here Attribute View.
3. Select subtype as “Standard”.
4. Click on Finish Button.

STEP 3) Check view editor screen.
Information view editor screen will open. Detail of each part in Information Editor is as below –

1. Scenario Pane: In this pane the following node exists-

• Semantics
• Data foundation

2. Detail Pane: In this pane following tab exists –

• Column
• View Properties
• Hierchery

3. Semantics (Scenario Pane): This node represents output structure of the view. Here it is Dimension.
4. Data Foundation (Scenario Pane): This node represents the table that we use for defining attribute view.
5. Here we drop table for creating attribute view.
6. Tab (columns, view Properties, Hierarchies) for details pane will be displayed.
7. Local: Here all Local attribute detail will be displayed.
8. Show: Filter for Local Attribute.
9. Detail of attribute.
10. This is a toolbar for Performance analysis, Find column, validate, activate, data preview, etc.

STEP 4) Click on data foundation node.
To include database table for creating attribute view, click on data foundation node and follow the instruction step by step as below –

1. Drag table “PRODUCT” and “PRODUCT_DESC” from TABLE node under DHK_SCHEMA
2. Drop “PRODUCT” and “PRODUCT_DESC” to data foundation node.
3. Select field from “PRODUCT” Table as Output In detail pane. Field icon color changes from grey to orange.
4. Select field from “PRODUCT_DESC” Table as Output in the detail pane. Field Icon color change from grey to orange.
5. The field selected as output from both the table appear Under Column list in Output Pane.

Join “PRODUCT” table to “PRODUCT_DESC” by “PRODUCT_ID” field.

STEP 5) Select the path.
Select Join path and Right Click on It and choose Edit option. A screen for Edit Join Condition will appear

1. Select Join Type as Type “Inner”.
2. Select cardinality as “1..1”.

After selecting join type click on “OK” button. In next step, we select the column and define a key for output.

STEP 6) Select column.
In this step, we will select column and define the key for output

1. Select Semantic Panel.
2. Column tab will appear under Detail pane.
3. Select “PRODUCT_ID” as Key.
4. Check Hidden option for field PRODUCT_ID_1 (PRODUCT_DESC table field).
5. Click on validate Button.
6. After successful validation, click on activate Button.

Job Log for validation and activation activity is displayed on bottom of screen on the same page, i.e. Job Log section as below –

STEP 7) View is created.
An attribute view with name “AT_PRODUCT” will be created. To view, refresh the Attribute View folder.

1. Go to DHK_SCHEMA->MODELLING Package.
2. AT_PRODUCT Attribute view display under Attribute view folder.

STEP 8) Check data.
To view data in Attribute view, Select data Preview option from the toolbar. There will be two option for data view from attribute view.

• Open in Data Preview Editor (This will display data with analysis option).
• Open in SQL Editor. (This will display output as only SQL query Output).

STEP 9) Check view data in editor.
To see View Attribute data in data Preview editor, There are 3 options – Analysis, Distinct and Raw data.

Analysis: This is a Graphical representation of the attribute view.

1. By selecting Analysis tab, we select Attributes for Label and Axis format view.
2. Drag and drop attribute in label axis, it will display in Label axis(X Axis).
3. Drag and drop attribute in value axis, it will display in value axis (Y Axis).
4. The output will be available in the format of Chart, Table, Grid, and HTML.

Distinct Values: The distinct value of the column can be displayed here. This will show total no. records for selected attribute.

Raw Data tab: This option display data of attribute view in table format.

1. Click on Raw data tab
2. It will display the data in table format

STEP 10) Check data from SQL editor.
View Attribute data in from SQL editor as below –

This option display data through SQL Query from the column view under “SYS_BIC” schema. A column view with name “will created after activation of attribute view “AT_PRODUCT”. This is used to see SQL query used for displaying data from the view.

1. Display SQL Query for data selection.
2. Display output.

Attribute View when activated, a column view under _SYS_BIC schema is created. So, when we run Data Preview, system select data from column view under a _SYS_BIC schema.

Screen shot of column view “AT_PRODUCT” under “_SYS_BIC” Schema of catalog node is as below –