How to use interaction string fields in analytical queries
The interaction contains three fields which cannot be used in an analytical query. The fields store unstructured text data of unrestricted length like email content, Facebook or Twitter posts or URLs, and cannot therefore be used in aggregations. In most use cases, there will be no business reasons to aggregate these fields, but there might be some use cases where you want to aggregate these fields.
The interaction field names are:
There is a workaround to enable you to use at least a part of the content of these fields in an analytical query. The solution is to create a custom field and either fill it before importing the data or copy the data during the import from one of the string fields to your custom field.
Note: Because the maximum field length of a custom field is 500 characters, filling before import or copying the data during import may result in data loss.
Note: Be careful when you choose the length of the custom field, as this may negatively impact import performance (memory consumption).
Creation of a custom field is mandatory for both approaches:
- Create a custom field yy1_<your_field_name>_mia of the type Text and the number of characters that fits your needs (maximum is 500) and publish it.
If you want to copy the data during import, you can execute the following steps:
- Implemenent BAdI “Revise Interaction Data Before Import”. To fill the custom field just use a simple move. If there are more characters in field source_data_url than defined for the custom field, they will be cut off.
To prevent the field from being overwritten if you have already filled it outside, you can add a condition, to ensure that the field is only filled if it is empty
if interaction_data-yy1_<your_field_name>_mia is initial. interaction_data-yy1_<your_field_name>_mia = interaction_data-source_data_url. endif.
Publish your implementation.
- Use the custom field in your custom CDS view(s). Here you can also select one of the string fields to display the details of one record. Publish your view.
- Create an analytical query based on your custom CDS view. You can select your custom field and you will be able to aggregate based on it. But you won’t be able to select one of the string fields because they will be filtered out automatically.
This blog provides a workaround which enables you to use at least part of the content of string fields in use cases where you wish to include this information in an analytical query. Please take care when using this workaround because of the potential impact on performance and memory consumption as well as possible data loss.