Create a Calculated Column in SAP Datasphere
- How to create a new calculated column in the Data Builder
- How to write formulas for a new calculated column
- How to preview a newly added calculated column
- How to create a calculated expression using CQL
- You have your SAP Datasphere tenant, or you have signed up for a SAP Datasphere free tier tenant
- You have imported data into your Space
- Understand Core Data Services Query Language (CQL)
Please note that SAP Data Warehouse Cloud has evolved into SAP Datasphere. While some screenshots in this tutorial reference SAP Data Warehouse Cloud, the content applies to SAP Datasphere.
You can kick start this tutorial by checking out our tutorial on Model Data with a Graphical View to understand the basic data modeling in SAP Datasphere.
- Step 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 have your view.
Then, click on New Graphical View.
This is your canvas to start modeling. Use the left side panel to select your data source. You can either use the data already in the Repository of this Space or select from the Sources connected to this Space. You can also select an already existing view from the tables and view lists populated at the end of Step 1 to directly arrive at the modeling canvas.
To add data to your new view, select either one table or view, click on it, and drag it to the canvas in the middle of the screen. You can now drag and drop other tables or views on top of the one that is already in the canvas.
As you drag the first table or view, you will see a pop-up listing Union and Join. Once you have your first join, notice that the canvas rearranges itself to show a Join node and a Projection node.
If you have selected an already existing view after step 1, you will arrive at the modeling canvas with join node and projection nodes for your view.
In the example below, you can see the tables
To add a new calculated column, just click on the
fxicon next to the output, a projection, or a join node.
Then, In the Calculated Elements Properties sidebar on the right, click on the plus icon.
You have added a new calculated column. Next, we will look at how to set up the calculation that will show data in this column.
- Step 2
Now it is time to add technical and business name for the column and write a formula in the expression box as a logic which defines the column.
Based on the formula you use, the system suggests the data type. You can also adjust the data type later if required.
In the example below, you can see a formula which calculates the difference between
Delivery Datefrom the
SalesOrderItemstable to get a
You can see the available functions by clicking on the All Functions dropdown. In this example, you can use the
Datetimefunction which will give you the required difference.
After selecting the function you wish to use, scroll down to select the appropriate method under it, in this case you can select
DAYS_BETWEENsince the new calculated column is to get the difference between dates in days.
Finally, you can go to the Columns section next to the All Functions option to select the two required columns for the expression. In this example,
Then, click on Validate.
Below are the examples of common methods used under different functions available for calculated columns in the Data Builder to enhance the data modeling capabilities.
DAYS_BETWEEN– Calculating the days between two columns or a certain date and a specific column. Always helpful to know how much time you have left until the end of the quarter. For example
DAYS_BETWEEN (Date, '2021-12-31').
LENGTH– Even though sometimes less is more, it is crucial to understand how much or long something is. That is why this function helps you to derive the length of the string in a column – for example to understand the length of a list (knowing that especially here quality is superior to quantity), for example
NEXT_DAY– Especially for daily comparisons, the ability to calculate the date for the next day for a subsequent join is important. For example,
UPPER and LOWER– Transforming a string into upper or lower characters to harmonize columns. For example,
- Step 3
To preview the data after adding a new calculated column, follow these steps:
CalculatedElementsPropertiessidebar, click on Columns.
In the modeling canvas, click on the view icon.
You can now see, for example the new calculated column
Delivery_Time_in_Daysadded in Step 2.
- Step 4
Once you have arrived at the
CalculatedElementsPropertiessidebar and chose to create a new calculated column in the Data Builder, you will see the fields to enter Business name and Technical name followed by Expression box, Functions, Columns and the Operators that you can use in SAP Datasphere.
Now it is time to use the code editor in Data Builder and write a calculated expression as a logic which defines the new calculated column.
In the example below, you will see an expression which determines whether the deal is good or bad based on the
NETAMOUNTcolumn in the
CASE WHEN NETAMOUNT > 1000 THEN 'GOOD' ELSE 'BAD' END
You can validate the above expression by using the Validate option and then preview data to confirm if the new column has been added. The column should have either of two values ‘GOOD’ or ‘BAD’ as defined in the expression.
You have completed the Calculated Columns tutorial 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.
1. How do you create a new calculated column in the graphical view of Data Builder in SAP Datasphere?
- Step 5
2. Identify the options and sections involved in writing a formula to create a calculated column (Multiple answers are correct)