HANA Cloud’s VECTOR EMBEDDING in CAP and Compariso...

lalitmohan · ‎2025 Apr 09

Introduction

In the Q1 2024 release of SAP HANA Cloud, the REAL_VECTOR datatype was introduced to address the increasing demand for efficient storage and processing of high-dimensional vector embeddings. These embeddings are key in a variety of AI and machine learning applications, particularly in tasks such as semantic understanding and similarity search.

For developers using the SAP Cloud Application Programming (CAP) model, the @cap-js/hana package is the recommended tool for connecting to SAP HANA Cloud and utilizing its vector engine capabilities. In practice, we can define entities in our CDS data model that include elements of type cds.Vector. Once deployed, these elements are automatically mapped to the REAL_VECTOR datatype in the underlying SAP HANA table.

entity Books : managed {
    key ID        : Integer;
        title     : String(111) @mandatory;
        descr     : String(5000);
        embedding : Vector
}

SAP AI Core and the CAP LLM Plugin collaborate to bring advanced AI capabilities, focusing on large language models (LLMs) and vector embeddings, into CAP applications.

Custom handlers, written in NodeJS or Java, are used within CAP applications to extend and customize the framework's default behaviour. Specifically, custom handlers are utilized for tasks like embedding generation and other AI-driven operations.

getEmbedding = async (content) => {
    const vectorPlugin = await cds.connect.to("cap-llm-plugin");
    const embeddingModelConfig = await cds.env.requires["gen-ai-hub"][
      "text-embedding-ada-002"
    ];
    const embeddingGenResp = await vectorPlugin.getEmbeddingWithConfig(
      embeddingModelConfig,
      content
    );
    return embeddingGenResp?.data[0]?.embedding;
  };


this.after(["CREATE", "UPDATE"],"Books", async (res) => {
      const embedding = await this.getEmbedding(res.descr);
      await UPDATE(Books, res.ID).with({
        embedding: embedding,
      });
    })

Once the embeddings are stored in the REAL_VECTOR columns, they enable similarity searches, such as COSINE_SIMILARITY and L2DISTANCE. These searches allow for measuring the relationships between vectors, leading to more accurate data analysis and comparisons.

getSimilaritySearchForAlgorithm = async (searchWord, algoName) => {
    const vectorPlugin = await cds.connect.to("cap-llm-plugin");
    const embedding = await this.getEmbedding(searchWord);
    const entity = cds.entities["descr"];
    const similaritySearchResults = await vectorPlugin.similaritySearch(
      entity.toString().toUpperCase(),
      entity.elements["embedding"].name,
      entity.elements["descr"].name,
      embedding,
      algoName,
      5
    );
    return similaritySearchResults
};

The Q4 2024 release of SAP HANA Cloud introduced the VECTOR_EMBEDDING functionality, which allows for the generation of vector embeddings directly within the database. The available model is SAP_NEB.20240715, featuring a vector dimension of 768 and a token limit of 256. It supports the following languages: German (de), English (en), Spanish (es), French (fr), and Portuguese (pt). This is a significant advancement because, previously, embedding generation often relied on external services like SAP AI Core or CAP LLM Plugin.

In this blog post, I’ll show you how easy it is to use this new feature in CAP, enabling more efficient and seamless management of vector embeddings within the SAP HANA Cloud ecosystem.

Prerequisite

During the SAP HANA Cloud instance provisioning and configuration process, the option to enable advanced settings for Natural Language Processing (NLP) is available. This allows for the use of text embedding and text analysis models. This capability is also accessible with the SAP HANA Cloud trial.

Let's get Started.

Defining Entities with Vector Data Types

As mentioned above, we can define entities in our CDS data model that include elements of type cds.Vector and Once deployed, these elements are automatically mapped to the REAL_VECTOR datatype.

Calculated elements in the CAP enable you to define fields in your data model where the values are automatically derived from other elements or expressions and "On-write" (stored) calculated Elements are computed when the entity is created or updated, and their values are then stored in the database.

So, when defining entities with a vector data type, you can set up the embedding as a calculated field. This ensures that the embedding is computed and stored in the database whenever the corresponding field is created or updated.

entity Books : managed {
    key ID        : Integer;
        title     : String(111) @mandatory;
        descr     : String(5000);
        embedding : Vector = VECTOR_EMBEDDING(
            descr, 'DOCUMENT', 'SAP_NEB.20240715'
        ) stored;
}

To quickly check the HANA-specific SQL commands that would create your database tables based on your data model, use cds compile. It shows you the generated SQL DDL statements.

cds compile db/schema --to sql --dialect hana

By examining the SQL generated by cds compile, you can see how text embeddings are automatically generated during data input and updates. This process is similar to what is described in the Hana documentation (Reference: How to Automatically Create Vector Embeddings). Please note : while the generated columns approach is simpler, it does not support referencing NCLOB columns.

COLUMN TABLE cap_vector_embedding_Books (
  createdAt TIMESTAMP,
  createdBy NVARCHAR(255),
  modifiedAt TIMESTAMP,
  modifiedBy NVARCHAR(255),
  ID INTEGER NOT NULL,
  title NVARCHAR(111),
  descr NVARCHAR(5000),
  embedding REAL_VECTOR GENERATED ALWAYS AS (VECTOR_EMBEDDING(descr, 'DOCUMENT', 'SAP_NEB.20240715')),
  PRIMARY KEY(ID)
)

Define Entity to Perform Similarity Search

Exposing entities as views with parameters is a key feature of SAP CDS that enhances data modelling flexibility, reusability, and efficient data retrieval. This allows you to easily create an entity designed to perform similarity searches, such as COSINE_SIMILARITY and L2DISTANCE, to assess the relationships between vectors.

entity Search(query : String) as
        select from db.Books {
            ID,
            title,
            descr,
            :query as searchWord : String,
            cosine_similarity(
                embedding, to_real_vector(
                    vector_embedding(
                        :query, 'QUERY', 'SAP_NEB.20240715'
                    )
                )
            ) as cosine_similarity : String,
            l2distance(
                embedding, to_real_vector(
                    vector_embedding(
                        :query, 'QUERY', 'SAP_NEB.20240715'
                    )
                )
            ) as l2distance : String, 
        }
        order by
            cosine_similarity desc
        limit 5;

Similarly To quickly check the HANA-specific SQL commands that would create your database view based on your data model, use cds compile. It shows you the generated SQL DDL statements.

cds compile srv/service --to sql --dialect hana

By reviewing the generated SQL DDL statements for creating a VIEW, you can see how generated columns or triggers are used to automatically compute the cosine similarity and L2 distance between the embedding column and the parameterized query. This process is similar to what is described in the Hana documentation ( Reference : How to Write Queries)

VIEW EmbeddingStorageService_Search(IN query NVARCHAR(5000)) AS SELECT
  Books_0.ID,
  Books_0.title,
  Books_0.descr,
  :QUERY AS searchWord,
  cosine_similarity(Books_0.embedding, to_real_vector(vector_embedding(:QUERY, 'QUERY', 'SAP_NEB.20240715'))) AS cosine_similarity,
  l2distance(Books_0.embedding, to_real_vector(vector_embedding(:QUERY, 'QUERY', 'SAP_NEB.20240715'))) AS l2distance
FROM cap_vector_embedding_Books AS Books_0
ORDER BY cosine_similarity DESC
LIMIT 5

Finally, the service.cds looks like:

using {cap.vector.embedding as db} from '../db/schema';

service EmbeddingStorageService {
    
    entity Books                            as
        projection on db.Books
        excluding {
            embedding
        };

    entity Search(query : String) as
        select from db.Books {
            ID,
            title,
            descr,
            :query as query : String,
            cosine_similarity(
                embedding, to_real_vector(
                    vector_embedding(
                        :query, 'QUERY', 'SAP_NEB.20240715'
                    )
                )
            ) as cosine_similarity : String,
            l2distance(
                embedding, to_real_vector(
                    vector_embedding(
                        :query, 'QUERY', 'SAP_NEB.20240715'
                    )
                )
            ) as l2distance : String, 
        }
        order by
            cosine_similarity desc
        limit 5;

}

Test & Output:

You can use the REST Client extension in Visual Studio Code to execute HTTP requests define in .http files. For testing the CAP services you can create new file with the .http extension and copy past the following code

@server = http://localhost:4004/odata/v4/embedding-storage

### post books
#  post_book
POST {{server}}/Books
Content-Type: application/json

{
    "ID":252,
    "title":"Wuthering Heights",
    "descr":"Wuthering Heights, Emily Brontë's only novel, was published in 1847 under the pseudonym \"Ellis Bell\". It was written between October 1845 and June 1846. Wuthering Heights and Anne Brontë's Agnes Grey were accepted by publisher Thomas Newby before the success of their sister Charlotte's novel Jane Eyre. After Emily's death, Charlotte edited the manuscript of Wuthering Heights and arranged for the edited version to be published as a posthumous second edition in 1850."
}

### Similarity Search
#  similarity_search
@query = Catweazle British
GET {{server}}/Search(query='{{query}}')/Set

By using the first POST request, you'll be able to create a new record in the Books entity.

When a new book record is added, any calculated store column has its value determined by an associated expression. This expression can utilize other book properties, constants, and functions. The outcome of this calculation is immediately persisted in the store column of the book's database record.

In your case, it will evaluate the VECTOR_EMBEDDING on the description using the 'SAP_NEB.20240715' model and store the result directly in the Books table.

Following the same approach, adding further records will trigger the generation of embeddings for each new entry. For this blog post, we will utilize the Books.csv file provided in the SAP-samples/cloud-cap-samples repository.

To enable similarity searches, the entities are accessible via a view that accepts parameter. The output of this view includes the COSINE_SIMILARITY and L2DISTANCE scores, which indicate the degree of similarity between the input query and the Book description vectors.

We can observe that creating an entity with an embedding field and performing similarity searches by exposing it as a view is a straightforward approach that avoids the need for custom handlers. Nevertheless, the necessity of implementing custom handlers is entirely dictated by the specific demands of your project.

SOURCE CODE

Comparing HANA and Azure OpenAI Text Embeddings

As discussed earlier in this blog, before SAP HANA Cloud introduced its native VECTOR_EMBEDDING functionality, developers within the SAP ecosystem had to rely on external providers for text embeddings. A key provider in this space is Azure OpenAI, which offers the text-embedding-ada-002 model, widely used for its strong balance between performance and cost-effectiveness. Let’s compare the embeddings generated by HANA’s native VECTOR_EMBEDDING with those produced by Azure OpenAI’s text-embedding-ada-002 model.

Update Entities Definition

We'll add a new embedding element of type cds.Vector into the Books entity definition, which will be used to store embeddings from OpenAI’s text-embedding-ada-002 model.

entity Books : managed {
    key ID        : Integer;
        title     : String(111) @mandatory;
        descr     : String(5000);
        embedding : Vector = VECTOR_EMBEDDING(
            descr, 'DOCUMENT', 'SAP_NEB.20240715'
        ) stored;
        embedding_openai : Vector;
}

We will also update the entity to enable similarity search by adding two new elements to store the cosine similarity and L2 distance calculated using OpenAI.

entity Search(query : String) as
        select from db.Books {
            ID,
            title,
            CONCAT(SUBSTRING(descr,0, 50),'...') as description,
            :query as query : String,
            cosine_similarity(
                embedding, to_real_vector(
                    vector_embedding(
                        :query, 'QUERY', 'SAP_NEB.20240715'
                    )
                )
            ) as cosine_similarity : String,
            l2distance(
                embedding, to_real_vector(
                    vector_embedding(
                        :query, 'QUERY', 'SAP_NEB.20240715'
                    )
                )
            ) as l2distance : String,
            0.0 as cosine_similarity_openai : String,
            0.0 as l2distance_openai : String
        }
        order by
            cosine_similarity desc
        limit 5;

Add Custom Event Handler

Until now, we haven't created a handler for entities, as it wasn't necessary for the SAP HANA Cloud native VECTOR_EMBEDDING functionality. However, to retrieve the embedding from OpenAI and retrieve the results of a similarity search based on COSINE_SIMILARITY and L2DISTANCE algorithms, we now need to create.

module.exports = class EmbeddingStorageService extends cds.ApplicationService { init() {

  const { Books, Search } = cds.entities('EmbeddingStorageService')

  this.before (['CREATE', 'UPDATE'], Books, async (req) => {
    console.log('Before CREATE/UPDATE Books', req.data)
  })
 
  this.after ('READ', Search, async (search, req) => {
    console.log('After READ Search', search)
  })

  return super.init()
}}

Generating Embeddings with the CAP LLM Plugin

As mentioned earlier, the CAP LLM Plugin, is commonly used for generating embeddings. To generate a vector for the content, we need to connect to the cap-llm-plugin with the appropriate model configuration in order to retrieve the embedding.

getEmbedding = async (content) => {
  const vectorPlugin = await cds.connect.to("cap-llm-plugin");
  const embeddingModelConfig = await cds.env.requires["gen-ai-hub"][
    "text-embedding-ada-002"
  ];
  const embeddingGenResp = await vectorPlugin.getEmbeddingWithConfig(
    embeddingModelConfig,
    content
  );
  return embeddingGenResp?.data[0]?.embedding;
};

Performing Similarity Searches

The CAP LLM Plugin also furnishes APIs tailored for executing similarity searches based on the entity structure of the CAP model. The plugin supports the COSINE_SIMILARITY and L2DISTANCE algorithms and will continue to support future available algorithms too. The following code snippets highlight the steps involved in retrieving the results of a similarity search based on COSINE_SIMILARITY and L2DISTANCE algorithms.

getSimilaritySearch = async (searchWord) => {
  var [cosineSimilarities, l2Distances] = await Promise.all([
    this.getSimilaritySearchForAlgorithm(searchWord, "COSINE_SIMILARITY"),
    this.getSimilaritySearchForAlgorithm(searchWord, "L2DISTANCE"),
  ]);

  return cosineSimilarities.map((item, i) =>
    Object.assign({}, item, l2Distances[i])
  );
};

getSimilaritySearchForAlgorithm = async (searchWord, algoName) => {
  const vectorPlugin = await cds.connect.to("cap-llm-plugin");
  const embedding = await this.getEmbedding(searchWord);
  const entity = cds.entities["Books"];
  const similaritySearchResults = await vectorPlugin.similaritySearch(
    entity.toString().toUpperCase(),
    entity.elements["embedding_openai"].name,
    entity.elements["descr"].name,
    embedding,
    algoName,
    5
  );

  return similaritySearchResults.map(result => {
    return Object.assign({}, {
      [algoName.toLowerCase() + "_openai"]: result.SCORE,
      "ID": result.ID
    });
  })
};

Update Custom Event Handler

Finally, we can update the handlers using the following code snippet. Whenever a book is created or updated, it will generate an embedding and update the Book entity with the vector data and during a read operation using the search entity, it will perform both the similarity search and update the entity records accordingly.

this.after (['CREATE', 'UPDATE'], Books, async (req) => {
    const embedding = await this.getEmbedding(req.descr);
    await UPDATE(Books, req.ID).with({
      embedding_openai: embedding,
    });
  })
 
  this.after ('READ', Search, async (searches,req) => {
    const [{ query: query }] = req.params;

    const scores = await this.getSimilaritySearch(query);
    searches.map((search) => {
      const score = scores.find((score) => score.ID === search.ID);
      search.cosine_similarity_openai = score.cosine_similarity_openai;
      search.l2distance_openai = score.l2distance_openai;
    });
    return searches;
  })

Test & Output:

Now test it again, you can create new file with the .http extension and copy past the following code:

@server = http://localhost:4004/odata/v4/embedding-storage

### update books
#  update_book
PATCH {{server}}/Books(ID = 271)
Content-Type: application/json

{
    "title":"Catweazle",
     "descr":"Catweazle is a British fantasy television series, starring Geoffrey Bayldon in the title role, and created by Richard Carpenter for London Weekend Television. The first series, produced and directed by Quentin Lawrence, was screened in the UK on ITV in 1970. The second series, directed by David Reid and David Lane, was shown in 1971. Each series had thirteen episodes, most but not all written by Carpenter, who also published two books based on the scripts."
}

### Similarity Search
#  similarity_search
@query = describe me the British television series Catweazle
GET {{server}}/Search(query='{{query}}')/Set

In first call you will update an existing record in Books entity, by sending PATCH request to the specific record's endpoint. As we implemented a custom handler for the after UPDATE Event your custom logic perform after the book record has been updated in the database. In your case, it will generate an vector embedding from OpenAI embedding model and update the Book entity with the vector data.

To perform similarity searches leveraging the OpenAI embedding model, we've implemented a custom handler that executes after the Search entity is read in our service. This handler, triggered by accessing a parameterized view, calculates the COSINE_SIMILARITY and L2DISTANCE to determine the relationship between the search query and the book description vectors, subsequently modifying the results object.

SOURCE CODE

Analysis of the Model Comparison:

While cosine similarity focuses on the angle between vectors, and L2 distance focuses on the magnitude of the difference, they are related. If two vectors have a small angle between them (high cosine similarity), their difference vector will likely have a smaller magnitude (low L2 distance). Conversely, if the angle is large (low cosine similarity), the difference vector will likely have a larger magnitude (high L2 distance).

However, it's important to remember that this relationship isn't always perfectly strict. Factors like the dimensionality and scaling of the vector space can introduce some variations. The slight variations in the trends for the HANA embedding model indicate that while the inverse relationship may not be perfectly linear in this specific dataset, it still demonstrates a meaningful and insightful pattern.

Metric	Hana (Text Embedding)	OpenAI (Text Embedding)
Mean Cosine Similarity	0.464	0.7285
Std Dev Cosine Similarity	0.1732	0.0914
Mean L2 Distance	0.9673	0.7254
Std Dev L2 Distance	0.185	0.145

Above graph shows an snapshot of comparison of similarity metrics based on the dataset we have used. The key parameters compared are mean cosine similarity (0.7285 vs. 0.464), mean L2 distance (0.7254 vs. 0.9673) and standard deviations for both cosine similarity (0.0914 vs. 0.1732) and L2 distance (0.145 vs. 0.185). These are some of the parameters to analyze to check to decide on preference of embedding model. However, it's important to note that, without understanding the specific task and desired outcome of the models, it's not possible to determine which model is "better" based solely on this comparison of similarity metrics.

Conclusion:

Despite the overall performance, there might be some potential positive aspects or situations where Hana Embedding Model could be considered:

Potentially Simpler and Faster: If It has a significantly simpler architecture than OpenAI embedding model, it might be faster to train and generate embeddings. In resource-constrained environments or applications where speed is critical and a slight drop in accuracy is acceptable, Hana embedding model could be a viable option.
Potentially Lower Computational Cost: A simpler model often translates to lower computational cost for both training and inference. If your infrastructure has limitations, Hana embedding model might be more feasible to deploy and run at scale.
Potentially More Interpretable (Depending on the Model): If it is based on a more interpretable architecture, it might be easier to understand why it produces certain embeddings, which can be valuable for debugging and analysis.
Performance on Specific Data Subsets: it's possible that on a very specific subset of the data it might perform comparably or even slightly better than OpenAI. This would require a more granular analysis of the performance across different categories or types of text within your dataset. In this specific small dataset, this isn't evident, but it's a possibility in larger, more diverse datasets.