You will learn
You will learn how to use Modeler artifacts to create Group By, Aggregations, Order By, etc. You want to figure out the average revenue per region per year.
Aggregation in SQL view with SAP Vora Modeler
You will learn how to use Modeler artifacts to create Group By, Aggregations, Order By, etc. You want to figure out the average revenue per region per year.
Go back to SAP HANA Tools’ Modeler. Create a new SQL view and name it STAR_CUSTOMERS2
. Add CUSTOMER
and SALE
as data sources.
Create an inner join between CUSTOMER
and SALE
accepting proposed condition.
Select REGION
, YEAR
, REVENUE
as output columns.
In the right pane right-click on the REVENUE
and choose the Edit… from the drop down menu.
Then choose the AVG
as aggregation function in the popped up window and type AVG_REV
as Alias. Click OK.
Add YEAR
and REVENUE
as GROUP BY columns.
Right click on AVG_REV
and add to Order By.
Save the view and copy the generated SQL
Execute the generated SQL and compare the result to data from the view.
SELECT REGION , YEAR , AVG(REVENUE) AS AVG_REV
FROM CUSTOMER
INNER JOIN SALES
ON CUSTOMER.CUSTOMER_ID = SALES.CUSTOMER_ID
GROUP BY YEAR, REGION
ORDER BY AVG_REV;
SELECT * FROM STAR_CUSTOMERS2;
Updated 12/19/2017
Contributors Provide Feedback
5 Min.