CDS Associations and Propagation with SQL in SAP HANA

As Part of HANA 1.0 SPS11, the New feature called CDS Associations implementation with the help of SQL instead having the dependency of CDS to create association among Database objects like tables, views.

Later as part of HANA 2.0 SPS00, an interesting new feature and an enhancement of the above one where the user is allowed to propagate the association while creating views.

What is Associations in Database?

Association defines that the relation of the database objects with other objects in database.


e.g.  Employee_official table has an association with Employee_personal and Employee_Finance tables where employee_id column present in all the table with different cardinality basis.

CDS Associations

While creating the entities using CDS, user has an provision to define the relationships between the entities using associations. The following is the simple example of creating two tables and define the relationship using associations. This feature helps the user to access the associated/target entity fields from the source entity.

namespace sam.db;

entity Employee
{
key id : Integer;
address1 : Association to Address;
};

entity Address {
key id : Integer;
zipCode : Integer;
city : String(80);
type : String(10);
e : Association[*] to Employee on e.addressId = id;
};

In the above e.g the Address is associated address1 in Employee entity

Why CDS Associations with SQL ?

There is a dependency for the user should design time artifact (CDS), if the user wants to use the association feature for his entities.

So, Here we GO for the CDS Association with SQL where the user will be able to create entities with associations (relationships) with each other.

ASSOCIATION_CLAUSE

<with_association_clause> ::=

WITH ASSOCIATIONS (<association_def_list>)

<association_def_list> ::= <association_def>, ...

<association_def> ::=

[<join_cardinality>] JOIN <table_name> [AS <identifier>] ON <predicate>

<join_cardinality> ::=

MANY TO ONE| MANY TO MANY| ONE TO ONE| ONE TO MANY

<table_name> ::= <identifier>

This CDS associations with sql feature is introduced in HANA 1.0 SPS11. The entities can be tables, views, Dynamic tiering tables and views.

Come. Let us create a table with CDS association to another table

CREATE COLUMN TABLE EMPLOYEE_OFFICIAL(EMP_ID INTEGER PRIMARY KEY, EMP_NAME VARCHAR(1000), ROLE VARCHAR(100));

CREATE COLUMN TABLE EMPLOYEE_PERSONAL_DETAILS(EMP_ID INTEGER PRIMARY KEY, ADDR1 VARCHAR(1000), CITY VARCHAR(110), STATE VARCHAR(100), COUNTRY VARCHAR(100))
WITH ASSOCIATIONS(ONE TO ONE JOIN EMPLOYEE_OFFICIAL AS E ON E.EMP_ID = EMP_ID);

Insert few records into both tables with below sqls

INSERT INTO EMPLOYEE_OFFICIAL VALUES(1,'JOHN','DEVELOPER');

INSERT INTO EMPLOYEE_OFFICIAL VALUES(2,'AEGON','TESTER');

INSERT INTO EMPLOYEE_OFFICIAL VALUES(3,'SNOW','ARCHITECT');

INSERT INTO EMPLOYEE_OFFICIAL VALUES(4,'HODOR','ADMIN');


INSERT INTO EMPLOYEE_PERSONAL_DETAILS VALUES(1,'HOUSE # - 10, 5TH STREET 1ST CROSS ROAD','BANGALORE','KARNATAKA','INDIA');

INSERT INTO EMPLOYEE_PERSONAL_DETAILS VALUES(2,'Hardenbergstraße 28','Berlin','Berlin','Germany');

INSERT INTO EMPLOYEE_PERSONAL_DETAILS VALUES(4,'FLAT # - 555, 8TH STREET 1ST CROSS ROAD','BANGALORE','KARNATAKA','INDIA');

INSERT INTO EMPLOYEE_PERSONAL_DETAILS VALUES(3,'HOUSE # - 11, 3TH STREET 1ST CROSS ROAD','BANGALORE','KARNATAKA','INDIA');

In the above code, we create a table named EMPLOYEE_OFFICIAL where the employee’s official details are stored and the other table (EMPLOYEE_PERSONAL_DETAILS) has the personal info and EMPLOYEE_OFFICIAL is associated with EMPLOYEE_PERSONAL_DETAILS with one to one cardinality

Cool… let’s try to query the associated table fields

SELECT E.*,ADDR1,CITY,STATE,COUNTRY FROM EMPLOYEE_PERSONAL_DETAILS;

Wooow…. We are able to get the associated table fields.

What If the user wants to access the associated fields in view which is created on top the table which has association?

Solution is here:

Association clause in Create view statement

< association_clause> ::= WITH ASSOCIATIONS (<association_def_list>)

<association_def_list> ::= <association_def>, ...

<association_def> ::= <forward_join_def>

<forward_join_def> ::= <join_cardinality_class>

[<join_cardinality_class>] ::= JOIN <table_or_view_identifier> [AS <table_alias>] ON <condition>

<join_cardinality> ::= MANY TO ONE| MANY TO MANY| ONE TO ONE| ONE TO MANY

Create a view with association clause

CREATE VIEW VIEW_EMPLOYEE AS SELECT * FROM EMPLOYEE_PERSONAL_DETAILS WITH ASSOCIATIONS(ONE TO ONE JOIN EMPLOYEE_OFFICIAL AS E ON E.EMP_ID = EMP_ID);

In above create view sql, we create a view on top of a EMPLOYEE_PERSONAL_DETAILS with association clause where the relation between both table specified.

Now the user will be able to access the EMPLOYEE_OFFICIAL table fields by select query on the created view. 

SELECT E.*,ADDR1,CITY,STATE,COUNTRY FROM VIEW_EMPLOYEE;

Results:

Didn’t you find difficult to repeat the association definition in both while create table  and view on top the created table.

So here is HANA enhancement on the above CDS Association feature where the association definition can be propagated to the view definition. This enhancement is introduced in HANA2.0 SPS00

< association_clause> ::= WITH ASSOCIATIONS (<association_def_list>)

<association_def_list> ::= <association_def>, ...

<association_def> ::= <forward_join_def> | <propagation_def>

<forward_join_def> ::= <join_cardinality_class>

[<join_cardinality_class>] ::= JOIN <table_or_view_identifier> [AS <table_alias>] ON <condition>

| <propagation_def>

<join_cardinality> ::= MANY TO ONE| MANY TO MANY| ONE TO ONE| ONE TO MANY

<propagation_def> ::= [<schema>.][<table>.]<association_identifier> [AS <alias>]

Here the user decides either to define the association or propagate the association (need to specify only alias name) while create view.

CREATE COLUMN TABLE EMPLOYEE_OFFICIAL(EMP_ID INTEGER PRIMARY KEY, EMP_NAME VARCHAR(1000), ROLE VARCHAR(100));

CREATE COLUMN TABLE EMPLOYEE_PERSONAL_DETAILS(EMP_ID INTEGER PRIMARY KEY, ADDR1 VARCHAR(1000), CITY VARCHAR(110), STATE VARCHAR(100), COUNTRY VARCHAR(100))

WITH ASSOCIATIONS(ONE TO ONE JOIN EMPLOYEE_OFFICIAL AS E ON E.EMP_ID = EMP_ID);

Insert few records into both tables with below SQLs

INSERT INTO EMPLOYEE_OFFICIAL VALUES(1,'JOHN','DEVELOPER');

INSERT INTO EMPLOYEE_OFFICIAL VALUES(2,'AEGON','TESTER');

INSERT INTO EMPLOYEE_PERSONAL_DETAILS VALUES(1,'HOUSE # - 10, 5TH STREET 1ST CROSS ROAD','BANGALORE','KARNATAKA','INDIA');

INSERT INTO EMPLOYEE_PERSONAL_DETAILS VALUES(2,'Hardenbergstraße 28','Berlin','Berlin','Germany');

Create view with association propagation

CREATE VIEW VIEW_EMPLOYEE1 AS SELECT * FROM EMPLOYEE_PERSONAL_DETAILS WITH ASSOCIATIONS(E);

If you see the association clause, we just specified the alias of the association defined in the table definition.

Opening the Definition of the View created using association propagation

So the definition shows the definition which was propagated from table definition to view.

Querying the view with select the associated fields

SELECT E.EMP_ID,ADDR1,CITY,STATE,COUNTRY FROM VIEW_EMPLOYEE1

Results: