SAP-HANA
SLT (SAP Landscape Transformation Replication Server) in SAP HANA
What is SLT (SAP Landscape Transformation Replication)? SLT is the an ETL tool that allows you to...
What is SAP HANA Analytic view?
SAP HANA Analytic view is based on STAR Schema Modelling, and it represents OLAP/Multi-Dimensional Modelling objects.
In SAP HANA Analytic view, dimension table 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:
Here we create an analytic view for Purchase Order based on earlier created attribute view "AT_PRODUCT".
We use 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.
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) In this step,
STEP 2) Information View editor will display for 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-
STEP 4) Add attribute view in Star join Node.
STEP 5) In the same window in detail panel do as directed,
Click on OK Button.
STEP 6) in the same window,
All selected column (Orange color) will display in the output of Analytic view.
STEP 7) Now we join attribute view to our 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) In this step, we define attribute, measure and key for the view.
STEP 9) Validate and 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.
Again we use 3 options to see data in Data Preview Editor –
Note: SAP HANA Analytic view can contain only Attribute view and does not support Union.
What is SLT (SAP Landscape Transformation Replication)? SLT is the an ETL tool that allows you to...
What is Trigger? A trigger is also a stored procedure that automatically executes when an event...
SAP HANA Operator can be used for calculation, value comparison or to assign value. SAP HANA...
What is Data profiling? Data profiling is the process of analyzing the data available in an...
Most RDBMS database uses SQL as database language, the reason of being popular is – it is...
What is SAP HANA Studio? SAP HANA Studio is an Eclipse based, integrated development environment (IDE) for...