Search

Using Native SAP HANA Artifacts

Content

Context

There’s an existing database object (table, view, table function, calculation view) and you want to use it in your CDS model, for instance for exposing it in an OData service.

Make the object known to CDS.

This entity then serves as a facade for the database object and can be used in the model like a regular entity. In the following, we refer to this entity as facade entity.

Steps to match the signature:

The resulting database name is the name (of the entity or its elements) after applying the SQL name mapping. We can distinguish two types of names - plain and quoted.

Plain Names

When using plain names, the database name is converted to uppercase and all dots are replaced by underscores. This conversion is the default behavior. If a database name is all in uppercase and is a regular SQL identifier, then it’s possible to construct a corresponding name in the CDS model that matches this name.

Quoted Names

If the existing database name also contains lower-case characters or characters that can’t occur in regular SQL identifiers, it’s not possible to choose a name in the CDS model that matches this name. Let’s call such a database name “quoted”, as the only possibility to create such a name is to quote it in the CREATE statement.

In this case, it’s necessary to introduce an additional database object (a synonym or a view) on top of the existing database object and construct the facade entity for this newly introduced mapping object.

Tables and Views Without Parameters

As the approach described here only depends on the signature of the existing database object, it applies to:

For simplicity, we only talk about tables in this section, but everything applies to the other mentioned objects in the same way.

Plain Names

Assume that all names in the existing database table are plain names. Define the facade entity in such a way that the resulting database names match those of the table.

existing-table-without-params-plain.hdbtable

COLUMN TABLE DATA_MODEL_BOOKSHOP_BOOKS (
  ID integer,
  THE_TITLE nvarchar(100),
  primary key ( ID )
)

facade-entity-without-params.cds

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity Books {
    key id : Integer;
    the_title : String(100);
  }
}

Quoted Table Name, Plain Column Names

Assume that the name of the existing table contains lower case characters, “.”, and “::”. It isn’t possible to define a CDS name that is mapped to this name. So, we introduce a synonym that maps data.model::Bookshop.Books to DATA_MODEL_BOOKSHOP_BOOKS.

existing-table-quoted-table-name.hdbtable

COLUMN TABLE "data.model::Bookshop.Books" (
  ID integer,
  THE_TITLE nvarchar(100),
  primary key ( ID )
)

existing-table-quoted-table-name.hdbsynonym

{
  "DATA_MODEL_BOOKSHOP_BOOKS" : {
    "target": {
      "object" : "data.model::Bookshop.Books"
    }
  }
}

Now, define a facade entity in CDS. The table columns have plain names and thus need no mapping.

facade-entity-without-params.cds

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity Books {
    key id : Integer;
    the_title : String(100);
  }
}

Quoted Table Name, Quoted Column Names

Assume that the table name and column names are quoted names. There, a synonym isn’t sufficient, because it can’t map the column names. In this case, put a “mapping” view on top of the existing table that maps all the names to plain ones:

existing-table-quoted-names.hdbtable

COLUMN TABLE "data.model::Bookshop.Books" (
  "id" integer,
  "the.title" nvarchar(100),
  primary key ( "id" )
)

existing-table-quoted-names.hdbview

VIEW DATA_MODEL_BOOKSHOP_BOOKS AS SELECT
  "id"        AS ID,
  "the.title" AS THE_TITLE
FROM "data.model::Bookshop.Books"

Now, define a facade entity in CDS.

facade-entity-without-params.cds

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity Books {
    key id : Integer;
    the_title : String(100);
  }
}

Views with Parameters

Assume that the existing database object is an SQL view with parameters, a table function with parameters, or a calculation view with parameters.

Parameters can be added to the CDS model starting with CDS 3.4.1 / cds-compiler 1.7.1.

Plain Names

Assume that all names are plain. You can directly define the facade entity with parameters.

existing-view-with-params-plain.hdbview

VIEW DATA_MODEL_BOOKSHOP_BOOKINFO (in AUTHOR nvarchar(100)) AS SELECT
  ID,
  'The book: ' || THE_TITLE || ' and the author ' || :AUTHOR AS BOOK_AUTHOR_INFO
FROM DATA_MODEL_BOOKSHOP_BOOKS;

facade-entity-with-params.cds

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity Bookinfo (AUTHOR : String(100)) {
    key id : Integer;
    book_author_info : String(100);
  }
}

Quoted Names

Assume the SQL view with parameters has quoted names. Put a mapping view on top of the existing one that maps all the names, except the parameter names, to plain ones.

Names of parameters in SQL views and in table functions can’t be quoted.

existing-view-quoted-names.hdbview

VIEW "data.model.Bookshop.Bookinfo" (in AUTHOR nvarchar(10)) AS SELECT
  ID AS "id",
  'The book: ' || THE_TITLE || ' and the author ' || :AUTHOR AS "book.author.info"
FROM DATA_MODEL_BOOKSHOP_BOOKS;

mapping-view-quoted-names.hdbview

VIEW DATA_MODEL_BOOKSHOP_BOOKINFO (in AUTHOR nvarchar(10)) AS SELECT
  "id"        AS ID,
  "book.author.info" AS BOOK_AUTHOR_INFO
FROM "data.model.Bookshop.Bookinfo"(AUTHOR => :AUTHOR)

Now, define a facade entity with parameters.

facade-entity-with-params.cds

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity Bookinfo (AUTHOR : String(100)) {
    key id : Integer;
    book_author_info : String(100);
  }
}

In contrast to SQL views or table functions, the names of calculation view parameters can be quoted, too. The following is the definition of a calculation view data.model.bookshop.CalcBooks with elements id, the.title, and calculated, and in addition there’s a parameter Param.

existing-calc-view-quoted.hdbcalculationview

<?xml version="1.0" encoding="UTF-8"?>
<Calculation:scenario xmlns:Calculation="http://www.sap.com/ndb/BiModelCalculation.ecore" schemaVersion="3.0" id="data.model.bookshop.CalcBooks" applyPrivilegeType="NONE" defaultClient="$$client$$" visibility="reportingEnabled" calculationScenarioType="TREE_BASED" dataCategory="CUBE" enforceSqlExecution="false" outputViewType="Aggregation" cacheInvalidationPeriod="NONE">
<descriptions defaultDescription="Calculation View w/ parameters and quoted names"/>
<localVariables>
  <variable id="Param" parameter="true">
    <variableProperties datatype="INTEGER" mandatory="true">
      <valueDomain type="empty"/>
      <selection multiLine="false" type="Single"/>
      <defaultRange/>
    </variableProperties>
  </variable>
</localVariables>
<variableMappings/>
<dataSources>
  <DataSource id="DATA_MODEL_BOOKSHOP_BOOKS">
    <resourceUri>DATA_MODEL_BOOKSHOP_BOOKS</resourceUri>
  </DataSource>
</dataSources>
<calculationViews/>
<logicalModel id="DATA_MODEL_BOOKSHOP_BOOKS">
  <attributes>
    <attribute id="the.title" order="2" attributeHierarchyActive="false" displayAttribute="false">
      <keyMapping columnObjectName="DATA_MODEL_BOOKSHOP_BOOKS" columnName="THE_TITLE"/>
    </attribute>
  </attributes>
  <calculatedAttributes>
    <calculatedAttribute id="calculated" order="3" semanticType="empty" displayAttribute="false" attributeHierarchyActive="false">
      <keyCalculation datatype="INTEGER" expressionLanguage="COLUMN_ENGINE">
        <formula>&quot;id&quot;+$$Param$$</formula>
      </keyCalculation>
    </calculatedAttribute>
  </calculatedAttributes>
  <baseMeasures>
    <measure id="id" order="1" aggregationType="sum" measureType="simple">
      <measureMapping columnObjectName="DATA_MODEL_BOOKSHOP_BOOKS" columnName="ID"/>
    </measure>
  </baseMeasures>
</logicalModel>
</Calculation:scenario>

For this calculation view, a mapping view is required. Note the weird syntax for passing parameters to a calculation view.

mapping-calc-view-quoted.hdbview

VIEW DATA_MODEL_BOOKSHOP_CALCBOOKS (in PARAM nvarchar(10)) AS SELECT
  "id"                AS ID,
  "the.title"         AS THE_TITLE,
  "calculated"        AS CALCULATED
FROM "data.model.bookshop.CalcBooks"(placeholder."$$Param$$" => :PARAM)

Define the facade entity in CDS.

facade-entity-calc-view.cds

namespace data.model;
context Bookshop {
  @cds.persistence.exists
  entity CalcBooks (PARAM : String(10)) {
    key id : Integer;
    the_title : String(100);
    calculated : Integer;
  }
}

Associations and Compositions

This section describes how associations and compositions to artifacts with @cds.persistence.skip/exists are treated during the generation of the database model with forHana.

@cds.persistence.skip denotes that the artifact isn’t available in the database but eventually implemented by custom code.

entity Orders
{
  key id: Integer;
  orderName: String;
  items: composition of Items on $self = items.parent;
};
@cds.persistence.skip
entity Items
{
  key id: Integer;
  name: String;
  parent: association to Orders;
};
view OrdersView as select from Orders
{
  id,
  orderName,
  items.name
};
view ItemSelection as select from Items;

No association can point to a nonexisting database object and no query can be executed against such a nonexisting source. As @cds.persistence.skip is propagated, projections also don’t become part of the database schema.

All association definitions to nonexisting database objects are removed from the defining database objects and all usages of such associations produce an error.

This means that view Orders will be rejected with an error message as items.name isn’t resolvable to a valid JOIN expression and view ItemSelection is effectively annotated with @cds.persistence.skip.

@cds.persistence.exists denotes that there already exists a native database object, which should be used during runtime. The CDS artifact merely acts as a proxy artifact, representing the signature of the native database artifact in the CDS model. Since the database object really exists, it’s indirectly possible to associate these native database objects.

entity Orders
{
  key id: Integer;
  orderName: String;
  items: composition of Item on $self = items.parent;
};

@cds.persistence.exists
entity Items
{
  key id: Integer;
  name: String;
  parent: association to Orders;
};

view OrdersView as select from Orders
{
  id,
  orderName,
  items.name
};

entity ItemSelection as projection on Items;

Associations to artifacts annotated with @cds.persistence.exists are removed from the defining database objects and all usages of such associations produce an error.

However, as the annotation @cds.persistence.exists is not propagated, this allows using such proxy artifacts as query sources and to be valid association targets.

The example can now be rewritten to:

entity Orders
{
  key id: Integer;
  orderName: String;
  items: composition of ItemSelection on $self = items.parent; // <--- compose ItemSelection instead Items
};

@cds.persistence.exists
entity Items
{
  key id: Integer;
  name: String;
  parent: association to Orders;
};

view OrdersView as select from Orders
{
  id,
  orderName,
  items.name // <--- is transformable into a valid JOIN expression
};

entity ItemSelection as projection on Items;

By composing ItemSelection instead of Items, it’s possible to use this composition in Orders.

Example Index

Download from here a fully fledged model with even more examples.

What Database Object Mapping Object Facade Entity
      data.model.Bookshop…
table DATA_MODEL_BOOKSHOP_BOOKS n/a Books
table data.model::Bookshop.Books DATA_MODEL_BOOKSHOP_BOOKS Books
view with param DATA_MODEL_BOOKSHOP_BOOKINFO n/a Bookinfo
view with param data.model.Bookshop.Bookinfo DATA_MODEL_BOOKSHOP_BOOKINFO Bookinfo
cv with param data.model.bookshop.CalcBooks DATA_MODEL_BOOKSHOP_CALCBOOKS CalcBooks