SAP HANA Analytic View

What is Analytic View in SAP HANA?

SAP HANA Analytic View is based on STAR Schema Modelling, and it represents OLAP/Multi-Dimensional Modelling objects. SAP HANA Analytic view forms a cube-like structure, which is used for the analysis of data. Analytic View is mainly used in a scenario where we need aggregated data from the underlying table.

In SAP HANA Analytic view, dimension tables are joined with the fact table that contains transaction data. A dimension table contains descriptive data. (E.g. Product, Product Name, Vendor, customer, etc.). Fact Table contains both descriptive data and Measureable data (Amount, Tax, etc.).

Example of SAP HANA Analytical View

Here, we will create an analytic view for Purchase Order based on the earlier created attribute view “AT_PRODUCT”.

We will use the Table Purchase Order Header and Purchase Order Detail table for it.

SQL Script for Create Table “PURCHASE_ORDER” in “DHK_SCHEMA”

CREATE COLUMN TABLE "DHK_SCHEMA"."PURCHASE_ORDER"					
(		
PO_NUMBER NVARCHAR(10) primary key,							
COMPANY NVARCHAR (4),			
PO_CATEGORY NVARCHAR(2),			
PRODUCT_ID NVARCHAR(10),			
VENDOR NVARCHAR(10),			
TERMS NVARCHAR(4),			
PUR_ORG NVARCHAR(4),			
PUR_GRP NVARCHAR(3),			
CURRENCY NVARCHAR(5),			
QUOTATION_NO NVARCHAR(10),			
PO_STATUS VARCHAR(1),			
CREATED_BY NVARCHAR(20),			
CREATED_AT DATE		
);		

INSERT	INTO "DHK_SCHEMA"."PURCHASE_ORDER" VALUES(1000001,1000,'MM','A0001','V000001','CASH' ,1000,'GR1','INR',1000011,'A','HANAUSER','2016-01-07');																													
INSERT INTO "DHK_SCHEMA"."PURCHASE_ORDER" VALUES(1000002,2000,'MM','A0002','V000001','CASH',1000,'GR1','INR',1000012,'A','HANAUSER','2016-01-06');																			
INSERT INTO "DHK_SCHEMA"."PURCHASE_ORDER" VALUES(1000003,2000,'MM','A0003','V000001','CASH',1000,'GR1','INR',1000013,'A','HANAUSER','2016-01-07');																			
INSERT INTO "DHK_SCHEMA"."PURCHASE_ORDER" VALUES(1000004,2000,'MM','A0004','V000001','CASH',1000,'GR1','INR',1000014,'A','HANAUSER','2016-01-07');

SQL Script for Create Table “PURCHASE_DETAIL” in “DHK_SCHEMA”

CREATE	 COLUMN TABLE "DHK_SCHEMA"."PURCHASE_DETAIL"					
(		
PO_NUMBER NVARCHAR(10) primary key,							
COMPANY NVARCHAR(4),			
PO_CATEGORY NVARCHAR(2),			
PRODUCT_ID NVARCHAR(10),			
PLANT NVARCHAR(4),			
STORAGE_LOC NVARCHAR(4),			
VENDOR NVARCHAR(10),			
TERMS NVARCHAR(4),			
PUR_ORG NVARCHAR(4),			
PUR_GRP NVARCHAR(3),			
CURRENCY NVARCHAR(5),			
QUANTITY SMALLINT,			
QUANTITY_UNIT VARCHAR(4),			
ORDER_PRICE DECIMAL(8,2),			
NET_AMOUNT DECIMAL(8,2),			
GROSS_AMOUNT DECIMAL(8,2),			
TAX_AMOUNT DECIMAL(8,2)			
);		


INSERT	INTO "DHK_SCHEMA"."PURCHASE_DETAIL"	VALUES(1000001,1000,'MM','A0001',1001,101,
'V000001','CASH',1000,'GR1','INR',10,'UNIT',50000.00,40000.00,50000.00,10000.00);																									
INSERT	INTO "DHK_SCHEMA"."PURCHASE_DETAIL"	VALUES(1000002,2000,'MM','A0002',1002,102,
'V000002','CASH',1000,'GR1','INR',10,'UNIT',60000.00,48000.00,60000.00,12000.00);																									
INSERT	INTO "DHK_SCHEMA"."PURCHASE_DETAIL"	VALUES(1000003,2000,'MM','A0003',1003,103,
'V000001','CASH',1000,'GR1','INR',20,'UNIT',40000.00,32000.00,40000.00,8000.00);																									
INSERT	INTO "DHK_SCHEMA"."PURCHASE_DETAIL"	VALUES(1000004,2000,'MM','A0004',1004,104,
'V000002','CASH',1000,'GR1','INR',20,'UNIT',20000.00,16000.00,20000.00,4000.00);																	

With this table script, two Table will be created “PURCHASE_ORDER” and “PURCHASE_DETAIL” with data.

How to Create Analytic View in SAP HANA

We are going to create a SAP HANA Analytic View with name “AN_PURCHASE_ORDER”, with the already created attribute view “AT_PRODUCT”, tables “PURCHASE_ORDER” and “PURCHASE_DETAIL”.

Step 1) Create New Analytic View

1. Select Modelling sub-package under DHK_SCHEMA package.
2. Right-Click ->New.
3. Select Analytic View option.

Step 2) Information View editor will display for Analytic View–

1. Enter Analytic View Name as “AN_PURCHASE_ORDERS” and Label for it.
2. Select View type as “Analytic View”.

Once data is selected, click on Finish Button.

Information View editor will be displayed for analytic view.

Step 3) Add Table from Schema in Data Foundation node under Scenario pane

There will be three nodes under Scenario Pane-

1. Semantics: This node represents output structure of the view.
2. Start Join: This node create join in order to join the attributes view with the fact table.
3. Data Foundation: In this node, we add FACT table for Analytic View. Multiple tables can be added, but measure from only one table can be selected.
4. Drag and Drop table “PURCHASE_ORDER” and “PURCHASE_DETAIL” From DHK_SCHEMA to Data Foundation Node of Scenario pane.

Step 4) Add attribute view in Star join Node

1. Select “AT_PRODUCT” Attribute view from Modelling package.
2. Drag and Drop Attribute View in Star Join Node.

Step 5) In the same window in detail panel do as directed,

1. Click on data foundation node. Table added in data foundation node will display in Detail section.
2. Join Table “PURCHASE_ORDER” To Table “PURCHASE_DETAIL” ON “PO_NUMBER” Field.
3. Enter Join type and Cardinality.

Click on OK Button.

Step 6) Select the following columns in the same window,

1. Select PO_NUMBER, COMPANY, PO_CATEGORY, PRODUCT_ID, PLANT, STORAGE_LOC from “PURCHASE_DETAIL” Table.
2. Select CURRENCY Column From “PURCHASE_DETAIL” Table.
3. Select GROSS_AMOUNT, TAX_AMOUNT.
4. Select PO_STATUS, CREATED_BY, CREATED_AT Column From “PURCHASE_HEADER” Table.

All selected column (Orange color) will display in the output of Analytic view.

Step 7) Now, join attribute view to the fact table (data foundation)

Click on Star join Node in semantic pane, as below–

Attribute view and fact table will be displayed in the detail pane. Now we Join attribute view to fact table as below –

Join Attribute View with Data Foundation on “PRODUCT_ID” Column.

Click on Join link, a Pop-Up for Edit Join will be displayed. Define Join type as “Referential” and Cardinality 1…1.

Click On OK Button.

Step 8) Define attribute, measure and key for the view

1. Select Semantics Node in Scenario Pane.

   

2. Select Columns Tab under Details pane.
3. Define column type as attribute and measure, I have defined all column as attribute except “GROSS_AMOUNT”, which is defined as a measure.

Step 9) Validate and Activate Analytic view

1. Validate the analytic view.
2. Activate Analytic view.

Now analytic view “AN_PURCHASE_ORDERS” will be created and activated in Analytic Folder of Modelling Sub-package as –

Step 10) Preview Data in Analytic View

1. Go to the toolbar section and click on “Data Preview” Icon.
2. Select Open in Data Preview Editor.

Again we use 3 options to see data in Data Preview Editor–

1. Analysis – In this tab, we have to drag and drop Attribute and measure in Label Axis and Value axis. We can see the output in Chart, table, Grid, and HTML format.

2. Distinct values – Distinct values show distinct value for selected attribute. We can select only one attribute at a time.

3. Raw Data – It will show in Table format from Raw Data tab as below –

   

Note: SAP HANA Analytic view can contain only Attribute view and does not support Union.