Best Practice on SAP HANA Modeling for Segmentation
24 min read
Overview
Are the business users already using the Segmentation to define your target audience, but missing certain attributes to further define your target group?
Have you as an integration expert already checked that the required data for your additional attributes exists in your system, but are looking for guidance on how to extend the Segmentation accordingly?
Continue reading this article to learn about options for an Segmentation Extensibility and when to choose which option. Additionally, after reading this article you will not only know how to build Custom Views for Segmentation, but also how to build SAP HANA views to customize or extend business objects (e.g. Contact or Interaction) for the use within Segmentation.
Table of Contents
Introduction
As described in the article Campaign Build - Segmentation Best Practice, Segmentation is part of the Build phase within the campaign operation process.
Segmentation is essentially the tool which leads to the setup of the target group to be included in campaigns, as shown in image one.
Image one: Campaign Build process
In this article, we will not go into more detail on Segmentation itself. In case you want to learn more about the function and features of the Segmentation we recommend the Segmentation section in the Application Help. A step-by-step Segmentation approach, along with tips to build effective Segmentation models and avoid common pitfalls is included in the article Campaign Build - Segmentation Best Practice.
Main Segmentation Extensibility options
The main extensibility options for SAP Marketing Cloud are Custom Data Sources, Segmentation Objects and Segmentation Profiles.
The following Microlearning video explains:
- how you can create a custom data source via directly assigning a Custom Business Object to the Segmentation
- how you can define a Segmentation Object and assign one or more data sources
- how you create a Segmentation Profile based on a Segmentation Object
Microlearning video: Segmentation Extensibility in SAP Marketing Cloud
Make sure you know the main extensibility options for Segmentation before continuing. The Microlearning video above or the Segmentation chapter in the Extensibility Guide explains the basic concepts.
The next chapters assume that you are already familiar with these extensibility options.
It's best practice to reuse the standard Segmentation Objects and profiles whenever possible. However there are specific situations for which a standard object or profile doesn't work and a custom Segmentation Object or profile needs to be created. The product documentation outlines the reasons for creating custom Segmentation Profiles and objects in the following page: Best Practices for Segmentation Configuration .
Reuse the standard Segmentation Objects and profiles whenever possible.
Check the Best Practices for Segmentation Configuration for scenarios when custom Segmentation Profiles and objects are required.
Advanced Segmentation Extensibility options
In case you have business requirements which are not covered by the available standard Segmentation Objects, there are multiple options to extend the Segmentation accordingly. The options you are having are displayed in the following image two - when to choose which Segmentation Extensibility option.
Please evaluate the alternatives by going through this decision tree since some options (especially Custom Views) have a bigger impact on the Segmentation performance than others.
Image two: when to choose which Segmentation Extensibility option
Sometimes Segmentation attributes are already available but hidden by standard and can be enabled through the Segmentation Configuration without even creating custom views. Please check the Segmentation Configuration of your Segmentation Object accordingly to identify attributes which might fit to your business requirement.
In case you have created Custom Fields for standard business objects such as contact, interaction or product and you need to assign these custom fields to Segmentation, you simply use the Assign to Segmentation app under the Custom Field section in the Marketing Extensibility app. You can find more details in Custom Fields in Segmentation .
To assign interaction contact data from Custom Business Objects to Segmentation you can use the Assign to Segmentation app under the Custom Business Object section in the Marketing Extensibility app. The main prerequisite is that the root node of the Custom Business Object requires fields containing the ID_ORIGIN and the ID of the interaction contact. This process is described in Custom Business Object in Segmentation .
In case you have attribute requirements which might be relevant for other customers as well, please also check if there is already an improvement request in the Continuous Influence Session for SAP Marketing Cloud around this topic.
If not, you can create an improvement request through the SAP Customer Influence platform. The product team regularly reviews the improvement requests and communicates the review and delivery results after each review cycle. This will reduce the need for Segmentation extensibility via growing the Segmentation content which is available out of the box.
Only if none of these options help, you can go ahead and create Custom Views which you can assign to Segmentation Objects to subsequently use its attributes in Segmentation. The chapter Custom Views in Segmentation explains in detail which roles are required to work with custom views. It also explains how the custom view creation flow and the usage in Segmentation look like.
Evaluate your options before creating custom views and a void creating custom views if possible. You can use the decision tree in image two to evaluate the alternatives.
Remember that each custom view you create will have an impact on the Segmentation performance.
Custom Views are required in case you need advanced calculation capabilities in Segmentation. Examples for business requirements where advanced calculation capabilities are required can be:
- a) Custom key figure "Number of sessions participated in last event"
based on event data required for building a target group
- → Creating a custom key figure based on standard or custom business object data
- b) Custom attribute "email domain" from individual email addresses (e.g. map all @gmail.com and
@googlemail.com addresses to "Google Mail") required for building a target group
- → Deriving custom attributes based on a combination of other attributes and/or SAP HANA functions (e.g. using String or DateTime functions)
- c) Custom attribute "contact permissions" is required for building target group based on accounts
(Segmentation Object "all accounts")
- → Creating advanced joins between standard or custom data sources (apart from ID & ID_ORIGIN Custom Business Object assignment) such as joining marketing permission and contact-account relationship data sources
Environments for modelling Custom HANA Views
Creating custom SAP HANA views is supported for many years, but please note that SAP Marketing Cloud only supports SAP HANA 2.0 from release 2008 onwards.
There are multiple SAP HANA 2.0 environments which can be used to model custom SAP HANA views. These cloud and on-premise SAP HANA 2.0 environments are described in the following blog post: Environment for modelling Custom HANA Views for SAP Marketing Cloud
Step-by-step guides and best practices to model custom SAP HANA views via SAP HANA Cloud are described in the article How to Model Custom SAP HANA Views for SAP Marketing Cloud in Your Browser. The SAP HANA Cloud is available with a free trial on the SAP Business Technology Platform as well as a subscription or paid service afterwards.
Custom View modeling guidelines
General view design
In general, there are two ways of adding custom views as data sources to a Segmentation Object.
- Build the custom views and join them in the central star join as dimension views
- Build the custom views and assign them as data sources via the Segmentation configuration
From a view design perspective, it's strongly recommended to join custom views through the central star join to the Segmentation.
Always try to minimize the number of data sources per Segmentation Object, if possible keep the number exactly at one. Having multiple data sources joined to one Segmentation Object requires expensive joins.
This approach is displayed below in image two, where four calculation views are connected to the central node via star join. Please notice that only the central calculation view is assigned to the Segmentation.
Image three: Central star join
That approach is already implemented for all standard Segmentation Objects such as the "All Contacts" Segmentation Object. For all extensions, please follow that approach where possible, even if the central star join then becomes a custom view as well.
Reducing the number of data sources can significantly improve the Segmentation performance, especially for complex Segmentation models.
Adding key figures
Sometimes key figures are required within Segmentation in order to realize a business requirement for building a target group. Key figures are usually implemented as aggregations or counters. Simple examples for key figures can be interaction counters (e.g. number of registrations) or sums on monetary values (e.g. total sales order value). More complex key figures might even require further multiple key figures. The following example in image four shows a more complex key figure, the average order amount which is calculated in an aggregation node and added via a separate Cube view to the Segmentation.
Image four: Complex key figure in separate Cube view
Key figures should not be confused with scores which can be created in the Score Builder (e.g. to score contacts according to their contact engagement score value). The definition of key figures is typically static and does not change over time (see examples above). The definition of scores can vary and can easily be adapted in the Score Builder by adapting the underlying scoring rules or adding new rules or rule models to the score. Please find a more complex example for a contact score in the article Measure Brand Love and Customer Value in SAP Marketing Cloud Using a RFE-Based Score.
Add simple key figures (e.g. counters) in the central star join if possible . Add more complex key figures with separate Cube views.
To learn more about the dimension and cube views, check out Supported Data Categories for Calculation Views.
Segment on attribute codes
For Segmentation attributes of type 'Characteristic', you can define how the system displays attributes and its codes and descriptions in the Segmentation user interface. In the Segmentation Configuration you can choose from the following options to display those attributes:
- Code and Description or Description and Code
- Code
- Description
Depending on which option you choose, the system not only displays the attribute values in the preview and value help, but also interprets the user input differently (e.g. as code or as description). Learn more about this topic in Semantics and Data Source for Values.
Since the system does not persist language dependent descriptions, the descriptions are derived via SAP HANA views on the fly inside Segmentation models. Because of that, segmenting on code values is the only recommended approach from a performance perspective. Segmenting on language dependent descriptions can also easily lead to errors, especially if the business users use the system in different languages. To lessen the performance impact, you can also build tailored SAP HANA views specifically to provide descriptions for a given code for a specific attribute.
Set up the label for attributes of type 'Characteristic' to Code in the Segmentation Configuration.
Try to avoid attribute descriptions if you don't require them, especially if they are language dependent because of the performance impact and potential unwanted Segmentation results. Check if you can build tailored SAP HANA views to provide the descriptions if necessary.
Filter data early
In almost all Segmentation Objects, filters are required to be applied in every Segmentation model.
A typical example is to only show contacts with an existing email address. This example is explained in the article Validate the Go-Live Readiness of Your SAP Marketing Cloud Project.
First of all, you can apply simple filters at the Segmentation Profile level. This increases the usability, removes a potential source of error and authorization on Segmentation Profile level is possible.
However, from a performance point of view, it's recommended to push down the filters down to the underlying custom SAP HANA view. This increases the performance compared to Segmentation Profiles filters.
Push filters down from Segmentation Profiles to the underlying custom SAP HANA views to increase the performance.
This guideline should always be followed, especially if the filters are not expected to change.
Please notice that the Segmentation Profile filters also can't be used for more complex filter requirements, so here the filters must be pushed down to the SAP HANA view level anyways.
Also inside the SAP HANA views, the same concept to filter data early applies. Sometimes, a custom SAP HANA view contains multiple layers, such as a combinations of projections, views and aggregations in a layered structure.
Take the following SAP HANA view as an example. This view contains many layers and nodes. When comparing filtering at the lower layers (e.g. the IC_ROOT projection in the example) and filtering at higher layers (e.g. the topmost nodes), you can achieve the same filter results from a result set or correctness perspective. However, from a performance perspective, the two alternatives differ strongly since calculations, aggregations or joins in middle layers in are either calculated based on the unfiltered or filtered dataset.
Image five: Filter data as early as possible
This also applies to custom views which are built for custom business objects. Here it can help to apply additional filters which filter out empty values from the custom business object to reduce the number of rows and improve the performance accordingly.
Within SAP HANA views, always try to filter data early in the lower layers or projections.
This helps to reduce the data set as early as possible and improves the performance.
Use calculated columns carefully and late
In a lot of custom scenarios you need calculated columns at some point to calculate the column values at runtime base on the result of an expression. You can use other column values, functions, input parameters or constants in the expression as described in Create Calculated Columns. Calculated columns might be required to define custom key figures (e.g. average sales order value) or custom attributes (e.g. "email domain" attribute by mapping email addresses to domains).
Calculated columns can easily be performance expensive depending on the underlying expression logic and since they have to be evaluated during runtime (e.g. while refreshing segments within the Segmentation model, while recalculating target groups or during email personalization).
Sometimes it's possible to materialize calculations rather than calculating them during runtime. This means that certain calculations are computed in advance, for example during data import. Materializing calculations can improve the performance of calculation views heavily.
The aspect of materialization is also important in the context of data quality. You should rather fix the data quality in the persistence layer (e.g. before integration) rather than trying to fix bad data through calculations in custom views. Examples for this can be formatting unformatted fields or extracting parts of string fields.
If you can't materialize, you can often delay the use of a calculated column until the upper levels of the calculation view (e.g. in an upper aggregation node rather than in a lower projection node).
Try to avoid the use of calculated columns in order to improve the performance. Remove any calculated columns which are not required anymore.
Evaluate if you can materialize the calculation to compute certain calculations in advance or if you can move the calculated columns to upper levels of the calculation views.
These guidelines should be followed more strictly when using calculated columns in combination with further calculations, aggregations or joins:
- Create a calculated column and use this calculation column as a filter criteria in a subsequent node of the calculation view
- Reference or re-use a calculated column within another calculated column
- Aggregate calculated column values within aggregation node
Let's assume you want to prevent any underage contacts to appear in Segmentation. After looking at your data, you recognize that 1% of the contacts are falling into this specific age range. You might want to create a c alculated column Age (based on the birthdate and the current date) and maybe even a calculated column Underage based on the specified age range. In a subsequent node of the view you then use this calculated column to filter out contacts which belong to this age group. Please notice that this calculated column is recalculated during runtime each time for all of your contacts while refreshing segments within the Segmentation model, while recalculating target groups or during email personalization. Since a contact's age never changes during one specific day, only changes once per year and most contacts in your system (99% in this example) might never be identified as underage contacts, it's highly recommended to materialize and update this information on a regular basis (e.g. daily) instead.
The same applies for calculations or conversions of numeric or date fields which are aggregated in an aggregation node later. You might want to identify the first year the contact has been in contact with our company based on the interaction year. One way could be to first extract the interaction year out of the interaction date and then perform an aggregation on top of the interaction year to identify the year of first interaction. Again, this calculation is recalculated during runtime each time for all of your contacts while refreshing segments within the Segmentation model, while recalculating target groups or during email personalization. Since the year of first interaction will also not change over time, it can easily be calculated once and persisted during data import for all contacts. Also if legacy data from an additional data source appears and enters the system at a later point in time, you should recalculate and persist the updated result again rather than recalculating during runtime.
Don't apply filters on calculated columns and consider to materialize these columns.
Minimize the references of calculated columns to each other. Collapse the calculations in single calculated columns instead.
Avoid calculation before aggregation and try to perform calculations always after aggregation and filtering.
To create custom measures which are based on attribute restrictions, you maybe plan to create these measures using calculated columns and IF-THEN-ELSE expressions within the rule editor.
You might want to create a column which restricts the values for the AMOUNT measure only to YEAR = 2020 to easily segment customers based on their revenue in 2020. From a performance perspective, you should rather use restricted columns instead of building rule-based expressions using calculated columns as outlined above. Please see image six for an example on how to build such a restricted column in a SAP HANA view. Learn more about Restricted Columns in Create Restricted Columns .
Image six: Restricted columns to build measures based on attribute restrictions
Within the calculation view properties, you can set the "Execute In" property to execute the calculation view in the SQL engine or in the column engine. Setting this property to "SQL engine" might be beneficial as the SQL interface involves the standard SQL optimizer which implements a lot more optimization rules than the calculation engine. However, since the SAP HANA database and the underlying engines have evolved over the last years, this setting might not have a big impact anymore.
Use restricted columns for custom measures based on attribute restrictions rather than building IF-THEN-ELSE expressions within calculated columns.
Setting the "Execute In" property in the calculation view properties to "SQL engine" might be beneficial. For more information, see SAP Note 1857202.
Minimize the usage of join and rank operations
Joins are required within calculation views to combine data sources. Especially since joins are required in the majority of scenarios, learning how to best use them can affect the Segmentation performance drastically.
Similar to calculated columns, also joins can be performance expensive operations, especially with a growing data volume. That's why unnecessary joins should be avoided whenever possible. Examples can be text descriptions for columns which are not displayed later in Segmentation to the business users.
Also make sure that the join cardinality is correctly set for all joins (especially left outer joins) since this information is used by the database to build the execution plan. Learn more about Join Cardinality in Setting Join Cardinality.
Avoid joins whenever possible and remove joins which are not absolutely necessary.
If required, join as late as possible, when the rows involved in the joins are most filtered and most aggregated (see tips above).
Specify the join cardinality if possible.
The rank operation within calculation views helps to partition data by a set of partition columns (e.g. rank interactions by interaction date). Ranks also belong to the operations which are performance expensive, so they should be avoided if possible. Another aspect of the rank operation is that the rank results might appear "random" since it's not defined which entry wins in case the same rank gets assigned.
Try to minimize the usage of rank operations to improve the Segmentation performance.
Segmentation diagnostics
To check the performance of attributes within the Segmentation models coming from SAP HANA views, we recommend to use the Segmentation diagnostics feature. By using the Segmentation diagnostics, you can also see the performance impact of your custom views. Always test the performance of your modeling by running Segmentation diagnostics as they are generated by Segmentation. If you have loaded data into the SAP HANA database where you model your views, please don't take the performance you will see in the data preview of your SAP HANA IDE as a reliable source to predict Segmentation performance as outlined in SAP Note 2075429.
As described in the article Campaign Build - Segmentation Best Practice, you can enable the SQL trace and Segmentation diagnostics via the Segmentation user interface. Image three shows how to get to the Segmentation diagnostics within the Segmentation app.
Image seven: How to turn on Segmentation Diagnostics
The Segmentation diagnostics allow you to trace the runtime of your Segmentation model in detail, including runtimes for the individual segments as displayed in image four.
Image eight: Exemplary Segmentation diagnostic including SQL runtime per segment
The Segmentation diagnostics can also be exported to a CSV file, which allows you to further analyze the data in external tools like Excel.
Check the performance of attributes coming from SAP HANA views via the Segmentation diagnostics feature.
Don't take the performance you will see in the data preview of your SAP HANA IDE as a reliable source to predict Segmentation performance.
If you see higher SQL execution run times, please check your modeling against the guidelines outlined in this article.
Conclusion
This article explained Segmentation Extensibility and how to evaluate the standard and custom options for Segmentation Extensibility. The main focus of this article was SAP HANA view modeling to build performant custom views. You have learned what our custom view modeling guidelines around general view design, filtering, calculations and joins are. For further insights, we recommend the SAP HANA Modeling Guide for SAP HANA Web Workbench and the SAP HANA Troubleshooting and Performance Analysis Guide .
For Segmentation Best Practices, please make sure to go through the article Campaign Build - Segmentation Best Practice.
If you're interested in learning more about our services and recommendations, view our Portfolio of Services.
