Overview

Customers who have specific requirements for Segmentation, Scores or Recommendations in SAP Marketing Cloud sometimes need to create custom SAP HANA views, to make additional attributes available which are not part of the standard content.

In this article, you are going to learn about SAP HANA calculation views, which enable you to perform advanced data modelling on your marketing data. This gives you the freedom to combine and create data from various options like Tables, Column Views, Analytic Views, Attribute Views, and more. To make the business requirement and the process more tangible, let us introduce an exemplary business case in which custom data modeling would be required:

Your goal is to maintain and raise the engagement level of your loyalty program members. For this you want to establish a loyalty anniversary campaign. It is a recurring marketing campaign which offers special incentives (like coupons) for loyalty members, whenever their loyalty anniversary is coming up in the following month.

To establish such a campaign, you need to be able to segment your audience relative to their loyalty program registration date. This registration date is not a standard attribute, but can be added as a custom field on the contact object. Assuming you have implemented this custom field with loyalty registration date in the SAP Marketing Cloud system, we now want to introduce two calculated attributes to support the segmentation required for your loyalty anniversary campaign:

1. Next Loyalty Anniversary: Shows the next date on which the contact has a loyalty registration anniversary. This attribute has similar functionality to the standard attribute "Next birthday", just taking the loyalty registration date as calculation basis, instead of date of birth.
2. Loyalty program tenure in years: Shows the contacts tenure in the loyalty program in completed years.       

In the next chapters, we will focus on how to build and deploy a custom SAP HANA view to extend the data model with the previously mentioned custom attributes.

Table of Contents

Process Diagram 

Before going into the implementation details, lets first get an overview about the process steps and systems involved to create and import Custom HANA views, enhancing the SAP Marketing Cloud data model for usage in Segmentation and Personalization.

The following diagram lists the sequence of all the relevant steps:

Implementation steps

Now we should be ready to get started. We will explain the implementation steps to build and import the SAP HANA view and attributes in detail, based on the exemplary loyalty anniversary campaign.

SAP Marketing Cloud Export Metadata

To obtain the data model and create the custom HANA view, you can export standard or custom business object tables to the required SAP HANA system. 

The exported file is in SQL format and contains SQL statements of the tables. You can model the new graphical calculation views as required, using the data model exported.

• Search for the App Add Custom View to export the tables and view.

• Within the "Add Custom View" app we have the Export and the Import Options. Here you can perform the extraction of the standard HANA view definition and the table definition. To export all tables and views you have to select Export All. A zip file will be created, which you can import in the SAP HANA environment.

 

• To export a table, select the table to be exported and click Preview

 

• The preview will show the table definition, now click Export Table Definition an HDB file will be downloaded, ready to import in the HANA environment.

• To export a HANA View definition, go to the export view section and select the Usage type of the view in this case is Segmentation, search the view that you want to export. Now click Preview to obtain the definition and Export View Definition to export the view an HDB file will be downloaded, ready to import in the HANA environment.

Create TRIAL ACCOUNT in SAP BTP Cockpit

The Trial account lets you try out SAP Business Technology Platform (BTP) for free with a restricted use of the platform resources and services.

Create your trial account in the following site:

https://account.HANAtrial.ondemand.com/trial/#/home/trial

 

After you press the Continue button you are ready to access your new trial account.

• Click button: Enter Your Trial Account

• Select Subaccount: trial

• Select Space name: dev

Enable SAP HANA Cloud

SAP HANA Cloud is a complete database and application development platform. It lets you use advanced data processing capabilities — text, spatial, predictive, and more — to pull insights from all types of data.

• Select SAP HANA Cloud from Applications

• Click on button: Create and select SAP HANA database

• Input your email and click Next

• Review Location and Basic values and define:

Location:

Organization: keep the value

Space: dev - Keep the value

Basics Data

Instance Name: hc-db

Description: SAP HANA Cloud DB for Customer View Dev

User: DBADMIN

Administrator Password: Set Password

Confirm Administrator Password: Confirm Password

• Then click on Step 2

• Review SAP HANA Database information and click on Step 3:

• Review SAP HANA Database Advanced Settings information and click on Step 4:

Keep default values

Note: if you have connectivity issues in BTP, choose option 3 to allow only specific IP ranges. 

For hint information, please see the section Hints in this document.

• Click on button: Create Instance

• Wait until Status is Running

• Now status is running

Use and configure new trial account

In the SAP Business Application Studio, you need to create your development space. Development spaces are like isolated virtual machines in the cloud, providing a comprehensive set of tools to support the creation of database artifacts (for example, tables, SQL views, calculation views and analytic privileges, SQLScript procedures, etc.).

• Back to SAP BTP Cockpit and Select Instances and Subscriptions from Services

• Click on Application - SAP Business Application Studio:

• Press Button: OK

• Click on button: Create Dev Space

• Create a New Dev Space: HANA_DEV and select SAP HANA Native Application as kind of application to be created, then press button: Create Dev Space

• Wait until development space HANA_DEV status is running

• Now development space HANA_DEV is running

• Click on HANA_DEV: Then press OK

• Now in SAP Business Application Studio, click on Import.

• Wait until the project has been imported:

• Select your exported zip file from SAP Marketing Cloud. When the process is finished click on Open in New Workspace button.

Process will load your project information.

• Now you will see your project MKT_METADATA, created in the workspace:

• Click and open >db to select and edit package.jason and review "@sap/hdi-deploy": "7.0" and replace this value with "^4"

• Then click on > SAP HANA PROJECTS

• Verify if hdi_db have a green plug symbol and click on it:

• After you click it, you will need to enter the Cloud Foundry endpoint: https://api.cf.eu10.HANA.ondemand.com Press <Enter> to confirm.

Note: In BTP cockpit you can find your API Endpoint:

• The system will ask for your email (used to create your BTP trial account) and press Enter: <user>@<domain.com> Press <Enter> to confirm.

• Now enter your SAP Password used to log in to BTP: <PASSWORD> Press <Enter> to confirm.

• Next step is to select Organization:

• Select Space: Dev

• Wait until following message appears:

• Now Create a New service Instance, or select an existing HDI container created before:

• Keep or enter a new name for the Instance, for example: MKT_Metadata-hdidb-ws-2dndv, then press Enter.

• Wait until the creation process finished:

• Now your instance is ready:

• Click over hdbtables (pending deployment), click on deploy icon :

• You will receive a log content, like the sample below:

• Now Expand the project tree, to find CUAN_DATA.hdsynonym and click on the launch icon:

• You should see log details like on the below screenshots:

Create Sample View in SAP Business Application Studio

Now we can start creating or designing a calculated HANA view using the data model exported in the previous steps.

• Create a new Folder: Right click on MKT_METADATA/db/src and select New Folder: 

• Input the folder name and press <OK>:

• Select the new folder and then click on View within the main menu:

• From the dropdown list select the option: Find a Command

• On the search box enter the word: Create

• Click on SAP HANA: Create SAP HANA Database Artifact

• To create the view, you need to fill the following form and then press on the <Create> button:
  • Select the folder to store your views: custom_views
  • Namespace: custom_views
  • Choose the database version: HANA Service
  • Choose the artifact type: Calculation View (hdbcalculationview)
  • Specify the artifact name: Z_CV_IC_ATTR_VIEW
  • Specify the label: Contact Attributes Custom View
  • Choose a data category: CUBE
  • Use a star join? <No>
  • <Create>

• Now double-click on the new created view:

The new view will be display in the work area:

• Click on the Create Projection icon:

• Drag and drop a new projection element, down to the aggregation element:

• Select the new Projection box and press the plus (+) icon:

• Next select the data source:
  • In types selected: choose only Table:

• In the search box, write the following table name: CUAND_CE_IC_ROOT

• System should return the result as shown below. Click on the required table and then press the <Finish> button.

• Your selected table is now in the projection box. You can rename the Projection_1 name with a right click on the name, displaying a drop-down menu with the rename option:

 

• Now it is time to connect the new projection-table to the aggregation step:
  • Click on the Proj_Contacts element and select the arrow icon from the right:

• • Drag and drop the arrow to the bottom of the aggregation box.

• To calculate the Anniversary dates, we need to have a new Projection level. Click on the Create Projection icon on the left toolbar:

• Drag and drop the new projection between Proj_Contacts and Aggregation Box, then you can rename the new Projection as shown below:

• Next step, double click on the Proj_Contacts and the projection definition will be showed:
• Click on the fields you will use in your view, from Mapping tab:
  • MANDT
  • DB_KEY
  • COUNTRY
  • CITY1
  • LOYALTY_REG_DATE 
• When the fields are highlighted, click on the add to output icon:

• Your projection should contains the attributes like show in the screenshot below in the Columns tab. After checking, proceed to the Filter Expression tab:

• In Filter Expression tab, from the column list select MANDT attribute:
  • In the Expression box, you should see: “MANDT”
  • Here you should write the filter value: = “$$client$$

• Now you can build the calculated attributes, as specified in the business scenario.
  • Double click on the Proj_Contact_Loyalty and the projection definition will be shown.

• • First you need to map the attributes from Proj_Contacts into Proj_Contact_Loyalty: Go to Mapping tab, highlight all the fields and press Add to column button on the top

• • Your projection should looks like the screenshot below, then click on the Calculated Columns tab:

• • Click on the plus  button to create the new Calculated Column and then press the arrow (>) button:

• • Name: CC_LOYALTY_ANIVERSARY_LEAP_YEAR

1. • • Data Type: DATE
     • Expression: SQL
     • Loyalty Anniversary Leap Code Expand source

       CASE
           WHEN (EXTRACT_MONTH(TO_DATE("LOYALTY_REG_DATE"))=2 AND EXTRACT_DAY(TO_DATE("LOYALTY_REG_DATE"))=29) THEN
                CASE WHEN EXTRACT_MONTH(NOW()) < 3 THEN
                     CASE WHEN MOD(EXTRACT_YEAR(NOW()), 4) = 0 THEN
                           TO_DATE("LOYALTY_REG_DATE")
                      ELSE
                           ADD_DAYS(TO_DATE("LOYALTY_REG_DATE"),1)
                      END
                ELSE
                      CASE WHEN MOD(EXTRACT_YEAR(NOW()) + 1, 4) = 0 THEN
                           TO_DATE("LOYALTY_REG_DATE")
                       ELSE
                           ADD_DAYS(TO_DATE("LOYALTY_REG_DATE"),1)
                       END
                 END
           ELSE
                TO_DATE("LOYALTY_REG_DATE")
        END

       Copy this code!

Your new attribute should look similar to the image below:

1. • • Click on the Back button

1. • Click on the plus  button again, to create the second calculated column and then press the arrow (>) button:

1. • • • Name: CC_LOYALTY_NEXT_ANNIVERSARY
       • Data Type: DATE
       • Expression: SQL
       • Loayalty Next Anniversary Code Expand source

         CASE
              WHEN (EXTRACT_YEAR(TO_DATE("LOYALTY_REG_DATE")) != 0) THEN
                  CASE WHEN (EXTRACT_MONTH(TO_DATE("CC_LOYALTY_ANIVERSARY_LEAP_YEAR"))=2 AND EXTRACT_DAY(TO_DATE("CC_LOYALTY_ANIVERSARY_LEAP_YEAR"))=29) THEN
                        CASE WHEN EXTRACT_MONTH(NOW()) < 3 THEN
                              ADD_YEARS("CC_LOYALTY_ANIVERSARY_LEAP_YEAR",EXTRACT_YEAR(NOW()) - EXTRACT_YEAR(TO_DATE("CC_LOYALTY_ANIVERSARY_LEAP_YEAR")))
                        ELSE
                              ADD_YEARS("CC_LOYALTY_ANIVERSARY_LEAP_YEAR",EXTRACT_YEAR(NOW()) - EXTRACT_YEAR(TO_DATE("CC_LOYALTY_ANIVERSARY_LEAP_YEAR"))+1)
                        END
                 ELSE
                      CASE WHEN (ADD_YEARS("CC_LOYALTY_ANIVERSARY_LEAP_YEAR",EXTRACT_YEAR(NOW()) - EXTRACT_YEAR(TO_DATE("CC_LOYALTY_ANIVERSARY_LEAP_YEAR"))) < NOW()) THEN
                            ADD_YEARS("CC_LOYALTY_ANIVERSARY_LEAP_YEAR",EXTRACT_YEAR(NOW()) - EXTRACT_YEAR("CC_LOYALTY_ANIVERSARY_LEAP_YEAR")+1)
                      ELSE
                           ADD_YEARS("CC_LOYALTY_ANIVERSARY_LEAP_YEAR",EXTRACT_YEAR(NOW()) - EXTRACT_YEAR("CC_LOYALTY_ANIVERSARY_LEAP_YEAR"))
                      END
                 END
               ELSE
                    TO_DATE("LOYALTY_REG_DATE")
         END

         Copy this code!

         Your new attribute should look like the image below:

1. • • • Click on the Back button

1. • Click on the plus  button again, to create the new Calculated Column and then press the arrow (>) button:

1. • • • Name: CC_LOYALTY_AGE
       • Data Type: INTEGER
       • Expression: SQL
         • Loyalty Age Code Expand source

           CASE
                WHEN (EXTRACT_YEAR(TO_DATE("LOYALTY_REG_DATE")) != 0) THEN
                      CASE WHEN (EXTRACT_MONTH(TO_DATE("LOYALTY_REG_DATE")) < EXTRACT_MONTH(NOW())) THEN
                           EXTRACT_YEAR(NOW()) - EXTRACT_YEAR(TO_DATE("LOYALTY_REG_DATE"))
                      ELSE
                           CASE WHEN (EXTRACT_MONTH(TO_DATE("LOYALTY_REG_DATE")) = EXTRACT_MONTH(NOW()) AND EXTRACT_DAY(TO_DATE("LOYALTY_REG_DATE")) <= EXTRACT_DAY(NOW())) THEN
                                  EXTRACT_YEAR(NOW()) - EXTRACT_YEAR(TO_DATE("LOYALTY_REG_DATE"))
                           ELSE
                                  EXTRACT_YEAR(NOW()) - EXTRACT_YEAR(TO_DATE("LOYALTY_REG_DATE")) - 1
                           END
                    END
                ELSE
                   NULL
           END

           Copy this code!

1. • Now it is time to work in the Aggregation level:

     • Click on the Aggregation box, then select all attributes in Mapping tab.

1. • • Select all attributes as shown on the image above, then press add to output

     • The aggregation should now be mapped as shown in the image below. After confirming the mapping, proceed to the Calculated Columns tab:

1. • • In the Calculated Columns tab, you will need to create a new column using the plus <+> sign on the top right:

1. • • From the drop-down list, select the Calculated Column option:

1. • • Once the new calculated column is created, open the properties by clicking on the > icon:

1. • • Fill the general information:

       • Name: DUMMY_KEY_FIGURE

       • Label: DUMMY_KEY_FIGURE

       • Data Type: BIGINT

       • Semantics:  

         • Column type: Measure

       • Expression:

         • Column Engine: 1

1. • The next step to have the custom view, is to work with Semantics level:

1. • • Click on Semantics box, to see the Properties, Columns, Hierarchies and Parameters from the Calculated View:

1. • • You can update the column labels, which will be the attribute names you will be using in SAP Marketing Cloud.

     • If everything is fine with your view, you can deploy the view using the deploy icon in front of the custom view on the left side:

1. • • After the deploy icon is pressed you should see a deployment log with a success message, to have your view ready to be exported and used in SAP Marketing Cloud.

1. • • You can verify your new table in the SAP HANA Database by clicking on the Open HDI Container icon:

1. • • SAP HANA Database Explorer will be open in your Database schema. Next, select the Column views:

1. • • In the Search Column views, search for your view Z_CV_IC_ATTR_VIEW

1. • • Click on the custom view and you will see your new view definition:

1. • The last step before you can import your custom view in SAP Marketing Cloud, is to export the view in XML format:

     • Navigate back to the SAP Business Application Studio – HANA_DEV and right click on the custom view: hdbcalculationview

1. • • Select Download from the options and the system will save an XML copy of your view on your local disk in the download folder.

1. • • Rename the file extension from <.hdbcalculationview> to xml

1. • • Now the file is ready to be imported into SAP Marketing Cloud.

Import and verify the new view in SAP Marketing Cloud

You can now import the new calculation view from SAP Business Application Studio into the SAP Marketing Cloud system.

• To Import the View, go to Add Custom View

• • Go to the section Import View Definition select the Usage and the View to be imported, click Import View

• • The system shows the following message if the view was imported successful

• To assign the HANA view to a segmentation model go to Marketing Extensibility

• • Select Assign to Segmentation

• Click in the <+> and select the Custom View, the Contact Key and the Segmentation Object, and finally Save.

• To verify the assignment, go to Segmentation configuration

• • Navigate to Segmentation Objects and Attributes

• • Search for the same Segmentation Object that you used in the Assign view step.

• Please select the two attributes you want to use in segmentation; we will make this business case visible for segmentation Loyalty Years and Loyalty Next Anniversary.

• Go to Segmentation Model to validate that the attributes from the calculation view are displayed and working.

Congratulations, you have now extended your marketing data model with two additional contact attributes which can be used for your loyalty anniversary campaign.

In the following, we are complementing this article with a few hints on possible issues you may face during the process, as well as guidance on how to continue with data modelling on existing custom views.

Hints

In this section you will find hints and insight for possible issues, during the use of SAP Hana Cloud in Business Application Studio.

• Potential error in Business Application Studio: "Connection failed"

  • Fix: Allow specific IP address of SAP Business Application Studio for accessing the SAP HANA Cloud instance, e.g. via running the following command in a BAS terminal window: “curl -s http://whatismyip.akamai.com”

• Cannot open custom view in the graphical editor.

• • If the XML is not formatted with line breaks, the above error appears.

  • Solution:

    • Open the XML of the custom view in the code editor.

    • Copy the content

    • Format the content with line breaks using the XML formatter.

    • Add it back to the code editor.

    • Save and open the custom view in the Graphical editor.

• If you exported all metadata from SAP Marketing cloud, in the current version of SAP HANA Cloud, it is not possible to build and deploy the full src/sap folder in a single step. If you try to do so, you will run into several error messages related to obsolete data type annotations, and alike. We are working on getting these errors addressed in our standard content for future release.

• If errors related to calculated attributes occur upon deployment, it might be due to the usage of the outdated calculation engine syntax. Instead, the SQL syntax needs to be used. For example, formulas using "IF" need to be translated to a formula using "CASE".

• In addition, outdated data type annotations might need to get removed, e.g., annotations related to column store data types or binary data types (BLOB, NCLOB) of type "CS_..." or "MEMORY THRESHOLD ...", review this content in the XML file from the view.

How to work with your view after creation. 

When you create your development space and your HANA database for the first time, you can automatically develop your graphical view in SAP HANA. Once you finish and close your new environment, you may want to return later to continue with your development or modify your graphical view in SAP HANA. The following steps show you how to continue working on existing SAP HANA Views.

• Open BTP – Business Technology Platform – Trial Account:

• https://account.HANAtrial.ondemand.com/trial/#/home/trial

1. • Then: Go to Your Trial Account:

• Click on your Trial Account:

• Now in your trial account, click on your space: dev

• On the left side Applications:

  • Select SAP HANA Cloud, you should see SAP HANA Database Instance(s)

  • Then click on Actions and

  • Select "Open in SAP HANA Cloud Central" 

• SAP Business Technology Platform will ask you to Sign in to continue, choose the account you used to create your HANA Database instance:

• Now you are in SAP HANA Cloud Central, Organization and Space, you will find the status for your SAP HANA Cloud DB for Customer view Dev: STOPPED

  • Click on the 3 dots […] at the end of the Database line:

  • Select the option Start from the drop-down list

• • You will see Status: STARTING

• • Note: sometimes after a while your database need to be updated with the latest version. Wait a little bit more for the changes to be applied.

  • Wait until status is: Running to continue working with your database

• Now you can navigate back to your trial account page and select Instances and Subscriptions from the left side menu and then click on SAP Business Application Studio:

• • You should see the screen message to Explore the SAP Business Application Studio, click on OK button:

• SAP Business Application Studio status screen is loaded from your Dev Space:

  • If your Status is: STOPPED

• • Click on the play button:

• • SAP HANA Native Application: HAN_DEV, now status is: STARTING

• • Wait until status is: RUNNING and then you can click on your HANA_DEV – SAP HANA Native Application

• • Following message will be showed:

• • Click on OK button

  • Wait until your development environment is ready to use:

• You are now in SAP Business Application Studio – HANA_DEV

• • You should find your project in the recent list of projects or create a new one.

  • If you cannot find the tables when searching for tables due to inactive connection, you must log into the Database as follow:

1- Click on the left side icon  - Cloud Foundry: Targets

2- Then click on Login required under Services

3- Use the same information as the connection asked for the first time.

• • Click on your project, for example: MKT_Metadata

• • When your environment is open, click on your custom view created:

• • You are ready to continue working with your view:

Conclusion

In this article, you learned which resources can help you with building SAP HANA HDI views for SAP Marketing Cloud using Business Application Studio and SAP HANA Cloud. Now you can enhance your marketing data model with custom views, which offers a lot of flexibility for your segmentation and marketing personalization capabilities. 

We recommend you to also study the following related blogs and CX Works articles to find more best practices in the context of SAP HANA modeling and segmentation:

• SAP Native HANA best practices and guidelines for significant performance

• Best Practice on SAP HANA modeling for Segmentation

• Campaign Build - Segmentation Best Practice

To learn more about additional usage of custom views in SAP Marketing Cloud, please visit the following links:

• Custom Views in Segmentation

• Add Custom Views as Recommendation Data Source Pre-filters.

• How to Implement Custom Algorithms for Product Recommendations in SAP Marketing Cloud

• How to Implement Custom Predictive Scores for SAP Marketing Cloud

To access all our community or out of the box product documentation, please check out our List of Online Resources.