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;
  }
}

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