Getting started with SAP Vora Modeler: Creating SQL views

Getting started with SAP Vora Modeler: Creating SQL views

Getting started with SAP Vora Modeler: Creating SQL views with a join of two tables

You will learn

You will learn how to use Modeler artifacts to create SQL views with joins. It will be the first step to build a view to find the average revenue per region per year.

Details

Now open the Modeler, where you will create an SQL view.

Choose + in the navigation pane, name a name STAR_CUSTOMERS. The view type will remain SQL. Click Ok.

Click on SubSelect and place it on the canvas. Right click on it, choose Add Alias and call it CUSTOMER_INFO.

Double-click the CUSTOMER_INFO box to get into its edit screen. You should see the object reference changed to STAR_CUSTOMERS / CUSTOMER_INFO in the header.

Now you want to select different attributes for the customers and for that you need to join CUSTOMER and CUSTOMER_GROUP. Therefore add CUSTOMER as data sources.

Follow the same steps to add CUSTOMER_GROUP as a data source.

To create a join between these two tables, highlight the CUSTOMER table and click on the join symbol.

Drag and drop it to CUSTOMER_GROUP table.

The join window will pop up. Click on Propose condition and you see the common column between two tables are added as join condition. Please note that you accepted the default type of join which is INNER join. But you can change it by clicking on the arrow next to the Type depending on requirements.

Notice the join symbol and a condition in the subselect view.

Choose CUSTOMER_ID, CUSTOMER_GROUP, LOCATION and CATEGORY_ABBREVIATION columns by clicking on the small circle next to each column name. The circle will become black once a column is selected. As well column names are added to Columns part of the view in the right pane.

Go back to your main view by clicking on the STAR_CUSTOMERS hyperlink in the header. Click on the Star pictogram on top of your subselect rectangle to add all selected columns to the output of the view. Save your work.

Click on SQL icon in the Modeler editor to open the generated SQL and copy the statement. Click Close

Go back to the SQL Editor view and paste the generated SQL. Add second statement to select directly from the view. Click on Execute All button to run both statements.