Model Data with SQL in SAP Datasphere

To start modeling your data using the graphical view in the Data Builder of SAP Datasphere, follow these steps:

1. Go to the Data Builder in the menu of left-hand side of the SAP Datasphere welcome page and select the Space in which you want to model your data.

   

2. Then, click on New SQL View.

   

3. You can see on the left side of the screen a list of your local and remote tables and views. In the center of the screen, you can see an area to input your SQL code. As you type your code, SAP Datasphere will offer suggestions.

   

   Suggestions include SQL commands as well as table and column names found in your sources. This way, you can move faster and make sure you avoid typos on the way.

4. Below is an example of self-join in SQL code which fetches all the operators, address, postal code and state of electric charging stations in the same postal code. The table used as an example is Charging_Stations_ECars.

   SQL

   Copy

   SELECT DISTINCT t1."Betreiber" AS "Operator 1",
   t2."Betreiber" AS "Operator 2",
   t1."Adresse",
   t1."Postleitzahl Ort" AS "Operator 1 Pincode",
   t2."Postleitzahl Ort" AS "Operator 2 Pincode",
   t1."Bundesland"
   FROM "Charging_Stations_ECars" t1,
   "Charging_Stations_ECars" t2
   WHERE t1."Betreiber" <> t2."Betreiber"
   AND t1."Postleitzahl Ort" = t2."Postleitzahl Ort"
   
   

   

Exposing the SQL views makes the object available for consumption in SAP Analytics Cloud with which you can invoke business intelligence tools on your data and use them for data analysis. To do this you will have to define the data model type as a Fact in the model properties sidebar of the view and toggle on the option Expose for consumption. Upon deploying the current SQL view, you’ll need to create the Analytic Model from the Model Properties screen. Deploy your Analytic Model. The Analytic Model can now be consumed within SAP Analytics Cloud.

You can also convert your Graphical View in the SQL statements. You can refer the step 4 of Preview and Transform Your Data in a Graphical View for information on converting your graphical view to SQL statements.

1. Once you have the SQL code finished, you can click on the shield icon next to the deploy icon to validate your code. You will see a confirmation on the bottom of the screen if your code is valid or not.

   

2. To preview the data, you can click on the view icon from the view dropdown menu at the top.

   

3. You can use the sidebar on the right to add business semantics to your data model, just as you would using the graphical view. When you are ready and your code is valid, just save and deploy your data model.

   

If you are unsure about the SAP HANA based SQL Script, please refer our SAP HANA database implementation of SQL.

If you are already familiar with the SAP HANA-based SQL Script language, you will be pleased to know that you can re-use your existing skills and knowledge when creating data models in the SAP Datasphere.

With the SQL Script, you can define data intensive logic: use control structures, variables, create IF and FOR loops. These are just a few possibilities to name.

Note that the use of SQL Script is currently limited to read-only user defined table functions, therefore Data Definition Language (DDL) or Data Manipulation Language (DML) are not currently supported.

Well done!

You have completed the tutorial on Model Data with a SQL View in SAP Datasphere!
Now it is time to continue learning. Please check out the other SAP Datasphere tutorials available here, and do not forget to follow the SAP Datasphere tag in the SAP Community to hear about the most up to date product news.