Introduction

In this blog post I would like to share my experiences with the SAP Cloud Application Programming Model (CAP) on SAP HANA Express. A good starting point for this topic are the tutorials Get Started with SAP HANA Native Development.

After having completed the first tutorials on my SAP HANA Express edition on Azure, I decided to build an analytical application based on native HANA database artifacts.

In the developer keynote of the virtual SAP TechEd 2020 an appealing dashboard was presented which was built with UI Integration Cards. As I have not been working with these cards before, I was glad to have the chance to study the relevant source code of the keynote project.

The card explorer is an awesome tool with plenty of examples which offers an amazing learning experience. You can see both the app and the corresponding source code in a split screen.

Card Explorer (Source: SAP)

Now it was clear what frameworks and tools to use. But one of the main ingredients was still missing: the data. Few weeks ago, Antoine Chabert published the blog post “Predictive Planning: Forecasting US National Park Visits” in which he mentions the NPS Stats website which offers statistical data for the US National Parks and other areas and places such as National Historic Sites. 

For the sake of simplicity, I downloaded statistical data for ten years (2010-2019) only. The entities of the data model are

Model Entities

Before proceeding to technical details I would like to show you two screenshots of the application. There is a global filter bar with three selects on top and an icon tab bar with two filters below.

Overview

In the first segment an analytical card shows a time series of annual visits. 

The second segment of the icon tab bar contains two analytical cards with the top 5 area types and areas.

Top 5 area types and areas for Washington (2017)

The source code of this project is available on bitbucket.

Generate SAP HANA tables from CDS

The first steps were straightforward. I started the wizard for the SAP Cloud Application Programming Model to create a project with a sample data model. Then I replaced the data model in the database module with the four entities listed below. 

namespace us.np;

type StateCode : String(2);
type StateCodeISO: String(5);
type AreaTypeCode: String(5);
type AreaCode: String(4);
type StateStatus: String(30);
type StateName: String(30);
type Abbreviation: String(10);
type CityName: String(30);
type Description: String(50);
type AreaName: String(50);
type CalendarYear: String(4);

entity States {
    key stateCode: StateCode;
    stateCodeISO: StateCodeISO;
    status: StateStatus;
    stateName: StateName;
    abbreviation: Abbreviation;
    capital: CityName;
    largestCity: CityName;
    population: Integer;
    areaMI2: Integer;
    areaKM2: Integer;
    landAreaMI2: Integer;
    landAreaKM2: Integer;
    numberOfReps: Integer;
    areas  : Association to many Areas on areas.state = $self;
}

entity AreaTypes {
    key areaTypeCode: AreaTypeCode;
    description: Description;
    areas  : Association to many Areas on areas.areaType = $self;
}

entity Areas {
    key areaCode: AreaCode;
    areaName: AreaName;
    description: Description;
    stateCode: StateCode;
    areaTypeCode: AreaTypeCode;
    grossAreaAcres: Decimal(15,2);
    grossAreaKM2: Decimal(15,10);
    state: Association to States on state.stateCode = $self.stateCode;
    areaType: Association to AreaTypes on areaType.areaTypeCode = $self.areaTypeCode; 
}

entity AreaAnnualVisitations {
    key areaCode: AreaCode;
    key calyear: CalendarYear;
    visits: Integer;
    visitsChange: Integer;
    area: Association to Areas on area.areaCode = $self.areaCode;
}

(file: db/data-model.cds)

I deleted the default coding generated by the wizard in the service module, too.

using us.np as np from '../db/data-model';

service USNPService {
    entity States @readonly as projection on np.States;
    entity AreaTypes @readonly  as projection on np.AreaTypes;
    entity Areas @readonly as projection on np.Areas;
    entity AreaAnnualVisitations @readonly as projection on np.AreaAnnualVisitations;
}

(file: srv/usnp-service.cds)

Then I built the CDS starting from the cds-file of the service module.

As a result, SAP HANA specific design-time artifacts have been generated in the folder db/src/gen, for example:

When we build the database module, SAP HANA database artifacts are being generated.

All these steps are described in detail in the tutorial HANA Native, Create Database Artifacts Using Core Data Services (CDS).

It was easy to load the data into the SAP HANA tables, since there is a very nice wizard for that purpose.

In the database explorer we can verify that everything worked fine.

We can also build the project and run the service module as a Node.js application to test the OData services:

Building SAP HANA Calculation Views

The charts in a dashboard usually contain highly aggregated data that come from database views. In SAP HANA there are calculation views to accomplish that task. So I built some calculation views using the graphical editor in SAP HANA WebIDE. 

Again the database explorer is the right tool to check the results.

Importing Calculation Views in CDS

But how can we expose these views as OData services?

Thomas Jung answers this question in part 6.4 of his video series “HANA Basics for Developers”.

The trick is to define synonyms for the calculation views.

We can import the SAP HANA calculation views in CDS using the annotation @cds.persistence.exists

@cds.persistence.exists
Entity AnnualVisits {
	key CALYEAR: String(4);
	VISITS: Integer;
	VISITSCHANGE: Integer;
}
...

(file: db/import.cds)

As suggested by Thomas Jung in his video, I created a separate cds file to expose the calculation views as OData services:

Creating a Web Module

This is the last fun part. As soon as the OData services are ready, you can connect the service endpoints with the cards in the manifest file:

You can also implement your own logic for fetching data using an extension file. In our case this was not necessary.

By the way, I found it convenient to do the UI development locally using Visual Studio Code.For that part you do not need a running SAP HANA Express instance (28 GB), it suffices to have some mockup data.

Conclusion

In this blog post I have shown how to develop a freestyle SAPUI5 application on SAP HANA Express with the Cloud Application Programming Model. This project is part of my CAP learning journey which started with the openSAP course Building Applications with SAP Cloud Application Programming Model and there is still much to explore.

Thanks for reading, I hope this blog post is helpful.