How to Create CDS View in SAP HANA Studio
ABAP programmers create SQL DDL source and activate to create CDS views in SAP HANA Studio running on Eclipse known as Core Data Services view. SAP HANA database provides great performance boosts for transactional and analytics processes on ERP systems. CDS views, user defined functions, stored procedures are created on HANA database using SAP ADT tools including SAP HANA Studio. ABAP developers can not create CDS view or change DDL sources of SAP HANA repository objects using SE11 transaction.
This SAP tutorial shows ABAP developers how to create CDS views on SAP HANA database using SAP HANA Studio which runs a custom Eclipse ABAP perspective.
Let's start with some basics
What does DDL stands for?
In database terminology, DDL stands for Data Definition Language which differs from DML, Data Manipulation Language.
As you can guess, SQL DDL or SQL Data Definition Language commands enables developers and database administrators to manage metadata operations like creating database objects including tables, views, function, stored procedures, etc. Using an appropriate SQL DDL command database professionals can alter existing database objects or delete them ,too.
Backup or restore operations, and many other SQL commands related with database tasks rather then tasks with manipulating data are SQL DDL statements.
On the other hand, DDL commands are directly related with data itself. The transactions taking place on a database table, like inserting a new row, or updating data in a table, deleting data from table, MERGE commands are Data Manipulation Language commands, aka DML statements.
SQL Server developers use Transact-SQL, Oracle developers use PL/SQL for DDL and DML tasks.
Now with SAP HANA, ABAP programmers can create DDL objects like SQL Views, Stored Procedures, User-Defined Functions (skalar functions or table valued functions) on database level.
To create database objects on SAP HANA database, ABAP developers will use SQL DDL afterwards.
What does CDS stands for?
CDS stands for Core Data Services in SAP HANA database terminology.
CDS documents are design-time source code files showing the definition and SQL DDL source of the database objects SAP HANA developers want to create.
When an ABAP programmer creates a CDS document, a SQL DDL source file with .hdbdd file extension.
When the SAP HANA developer activates CDS file (Core Data Services) a corresponding runtime object (a database catalog object) is created in the current database schema.
I hope this short intro is helpful to understand some basics behind CDS Views and SQL DDL source objects.
I assume that the parts afterwards in this tutorial will help you visualize the above concepts.
Before I continue, I just want to note that my current SAP HANA Studio installation runs on Eclipse Mars 2 release
Create CDS View in SAP HANA Studio Eclipse IDE
First of all, launch SAP HANA Studio
CDS views are different than database views
In SE80 Object Navigator SAP transaction, ABAP programmers can create database view or using SE11 tcode classic views can be edited in ABAP Workbench tool.
On the other hand, to create a DDL source like creating a CDS view, SAP HANA Studio is the required IDE for developers.
After SAP HANA Studio started, you can connect to target SAP system where you want to create the CDS view.
Add the ABAP Package into the Project Explorer window of the Eclipse integrated development environment.
I want to create a local ABAP dictionary object, so I'm going to add the new CDS view into my local package.
As you can see in below Eclipse ABAP perpective screenshot, I've already imported local package $TMP into the project.
Right click on the package.
Follow context menu selections:
New > Other ABAP Repository Object
Choose DDL Source under Dictionary objects node. Then click Next for following screen in the wizard. We will create a Data Definition Language DDL document using this selection.
Enter the view name and a description for your new DDL SQL view.
Click Next if you want to transfer this ABAP dictionary to an other system with a transport request.
As expected since I'm creating my first CDS view in local ABAP package, $TMP no change recording is enabled for package $TMP.
If I was creating an object to distribute to other SAP systems like productive systems, I will be choosing an existing transport request number or create a new transport request here.
Click Next
Last screen in the wizard enables ABAP programmers to choose a template to create DDL SQL View easily. There are a few number of templates with short explanations and code samples.
In above screen, ABAP programmers can see following templates are listed:
Define View:
Defines a simple projection view with one data source.
If you want to display data only from a single database table, you can use this template.
It is rather basic.
Define View with Join:
Defines a view which combines two data sources using a left outer join.
The join conditions are specified in the on clause.
In case you need to bring data together from more than one table, for example data from VBRK and VBRP, etc as a developer you have to join two tables' rows based on related fields.
This template includes JOIN condition where you can customize it for your tables.
Define View with Association:
Defines a view with a public association to another data source.
The association can be used in the select list as well as by other views which use this view as a data source.
Associations enable CDS view developers to map a data field to an entity other than basic data types.
ABAP developer can create CDS views with associations created having different kinds of cardinalities like one-to-one or one-to-many, etc.
Define View with Parameters:
Defines a view with a single input parameter.
The input parameter can be used as an element in the select list or as an operand in conditional or arithmetic expressions.
Parameters in CDS views enable SAP HANA developers create DDL views which accepts parameters outside enabling filtered results based on these input variables or parameters.
If you want to create parametric CDS views, please refer to SAP HANA tutorial Create CDS View with Parameters using SAP HANA Studio with a sample case.
Extend View:
Extends an existing view by adding the specified elements to the select list of the view using a view enhancement.
Define Table Function with Parameters:
Defines the type signature of a client dependent CDS table function with importing parameters. The table function is implemented in the specified ABAP method.
The table function can be used in Open SQL and as a data source in other view definitions.
After you select your SAP HANA SQL DDL source template, click Finish button.
In Eclipse on a new window the starting source codes for the DDL SQL view is displayed.
You see the DDL source is listed among local package objects under node:
$TMP > Dictionary > ABAP DDL Sources nodes
Until we activate the DDL Source document After we activate ABAP DDL source, corresponding DDL SQL view will be shown under Views node
Note: It is important to notice that the DDL Source name of the view definition is "Z_DDLSQLView".
We define the DDL Source name right after "define view" statement.
On the other hand, the CDS view name which will be generated as an ABAP repository object is different.
ABAP programmers define the CDS View name visible on SE11 transaction is set at the first line of the template.
@AbapCatalog.sqlViewName is used to define the SAP CDS View name
Please use different names for DDL Source name and CDS View name in the code.
Otherwise, during activation ABAP programmers will get an exception preventing the CDS view creation and which is not very informative :)
I will replace the default sql_view_name with Z_CDSView_VBRK
Let's use a very simple sample SQL Select statement for the CDS view DDL source
Above is the SELECT statement programmers are used to from SQL Server.
I read data from VBRK Billing Document header table.
SELECT mandt, vbeln, kunrg, erdat, netwr FROM vbrk
In CDS view template we will implement the above SELECT as follows
Replace data_source_name with ABAP transparent table name VBRK
Within the parentheses, we will list the field names of the view
I have already named the CDS view as Z_CDSView_VBRK which is different than the DDL Source name
The DDL source codes of the SAP HANA view document is as follows now
@AbapCatalog.sqlViewName: 'Z_CDSView_VBRK'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'First DDL SQL View created in SAP HANA Studio'
define view Z_Ddlsqlview as select from vbrk {
vbrk.mandt,
vbrk.vbeln,
vbrk.kunrg,
vbrk.erdat,
vbrk.netwr
}
After you define the table name, in SELECT list you can benefit from the intellisense support in SAP HANA Studio
If you have done something wrong, the SAP HANA Studio code editor will inform you.
You can use the suggested correction method in such a case. Or try to activate the object then check the errors.
After ABAP programmers activate SQL DDL source of the CDS view, the SQL View will be generated and listed under the node:
Project Explorer > Packages > $TMP > Dictionary > Views
To execute SAP CDS View and display data, right click on CDS View name, and choose Open Data Preview in context menu.
Here is sample data for CDS view displayed in Data Preview tool in SAP HANA Studio
