How to analyze duplicates with Custom CDS Views in SAP S/4HANA Cloud

Introduction

Many users struggle to use the SAP S/4HANA Cloud embedded analytics key user tools efficiently. CDS Views, associations, cardinalities, etc. can be very confusing in a world, where SE16 seemed to be the silver bullet in order to analyze data. One of the most common challenges is the usage of correct associations with multiple data sources. Wrong associated data sources lead to data sets with duplicates, that impact the aggregated values in a negative way.

In this blog you will see how to analyze data sets in order to identify wrong associated data sources. I will demonstrate how to properly use the SAP S/4HANA Cloud embedded analytics tools such as Custom CDS Views. The Analysis contains five steps:

1. Get an overview
2. Identify the duplicates
3. Identify the root data source
4. Analyze the associations
5. Replace the wrong associations

Problem: The aggregated values in a custom query are incorrect.

1. Get an overview

First, the user will create an overview of the CDS-View set-up, which helps to better understand the context:

Overview

The query combines project data with sales order (SO) and purchase order (PO) data. Several Custom CDS View were created and associated. For example, the two yellow ones are associated with a [0…*] cardinality.

Please be aware of the blog Custom CDS Views – Using Multiple Data Sources, written by Product Owner Andreas Riehl:

He says:

Using fields from “to many” associations can be dangerous. It can have negative effects on the performance of your view and can sometimes have unwanted effects on the size of your result set. There might be cases when you want to build your view that way, but you should be aware of the effects this can have. Generally, you should try to avoid using fields from “to many” associations

2. Identify the duplicates

Second, the user will analyze the raw data of the YY1_OverviewCube. Using “Custom CDS Views”, the user can open a data preview in order to identify redundant sales orders.

• Open the app Custom CDS Views
• Search for the relevant CDS View (YY1_TKProjectOverviewCube)
• Open the CDS View by clicking on the row
• Click on “Preview”
• (If parameters are necessary, insert the values)

In this case, the user found duplicates based on the calculated field “OpenPrice”, which has three different values in one sales order:

Identified duplicates

• Click on back
• Click on Elements
• Look up the calculation of “OpenPrice”

The calculation of “OpenPrice” is based on the element with the label “Net Order Price” (Alias:  NetPriceAmount). The user has to open the Custom CDS View, where the element is used.

• Open the app Custom CDS Views
• Search for the relevant CDS View (YY1_SOSchedule_PR_PO)
• Open the CDS View by clicking on the row
• Click on “Preview”
• (If parameters are necessary, insert the values)

“Net Order Price” has three different values:

Identified duplicates

Assumption: The user gets a lot of duplicates, because many SO have multiple net order prices. That should not happen, because net order price is SO line specific and should have only one value.

That’s why the user must dig deeper into the associated data sources.

Overview extended

3. Identify the root data source

Third the user will check the “Purchasing Document” number of the example in the preview of YY1_SOSchedule_PR_PO.

Identified purchase order

The relevant purchase order is 4500000043.

Using the app “Custom CDS Views – To be deprecated”, users can preview released CDS-Views. The user is interested in “I_PurchaseOrderItemAPI01” in order to get the “Net Order Prices” of the purchase order 4500000043.

• Open the app “Custom CDS Views – To be deprecated”
• Search for the relevant CDS View “I_PurchaseOrderItemAPI01“
• Open the CDS View by clicking on the arrow
• Click on “Preview”
• (If parameters are necessary, insert the values)

The purchase order 4500000043 has two lines with one net order value each.

Values of purchase order items

Due to the duplicates in the query there must be a problem with the associations.

4. Analyze the associations

Next the user will create a new Custom CDS View using the app “Custom CDS Views – To be deprecated”, because he wants to analyze “I_PurOrdAccountAssignmentAPI01” in more detail.

• Open the app “Custom CDS Views – To be deprecated”
• Search for the relevant CDS View “I_PurOrdAccountAssignmentAPI01”
• Select the row
• Click on Create
• Choose a name, e.g. YY1_ PurOrdAccountAssign
• Continue with
  • Option A or
  • Option B

Two options how to associate the data sources

The user  is interested whether there is a difference in using the field “NetPriceAmount” with different associations:

1. Option A uses an association, where I_PurchaseOrder is associated to I_PurchaseOrderItemAPI01 with the cardinality 0…*
2. Option B is using a 1:1 association to I_PurchaseOrderItemAPI01

Option A

• Open the tab “Field selection”
• Add the associations _PurchaseOrder and _PurchaseOrder. _PurchaseOrderItem
• Add the key fields
• Add the field NetPriceAmount
• Click on “Save Draft”
• Click on “Preview”

Association option A

Result Option A

As you can see the result is wrong. There are multiple values in our sales order item:

Duplicates due to association option A

Option B

• Open the tab “Field selection”
• Add the association _PurchaseOrderItem
• Add the key field
• Add the field NetPriceAmount
• Click on “Save Draft”
• Click on “Preview”

Association option B

Result Option B

The result is correct, because only one “Net Order Price” is related to the sales order item:

Option B is correct

Conclusion

5. Replace the wrong associations

With this info, the user will check the elements in YY1_SOSchedule_PR_PO.

• Open the app Custom CDS Views
• Search for the relevant CDS View (YY1_SO_Schedule_PR_PO)
• Open the CDS View by clicking on the row
• Click on “Elements”

The elements were added based on association of option A:

Source of error

The user must replace these element with the elements of the correct association (with the cardinality [1..1]):

• Click on “Edit”
• Click on “Add”
• Search for the correct association (Option B)
• Select the element
• Click on “OK”

Searching for the correct element

Difference between old and new element

The duplicates should be cleared once the wrong associations are deleted.

• Select the elements with the wrong association
• Click on “Delete”

If the fields can’t be deleted, there are dependencies to other CDS-Views.

• To visualize the dependent CDS Views, click on the icon on the bottom right corner
• 

Dependencies

In this case the user must top-down delete every element, that is using NetPriceAmount in any way (directly or indirectly via calculations).

• Open the app “Custom Analytical Queries”
• Open the relevant query
• Delete all dependent elements
• Open the app Custom CDS Views
• Open the relevant Custom CDS Views
• Delete all dependent elements (even the code in calculated fields)

Finally, after every single dependency is deleted, the user can delete the wrong associated “NetPriceAmount” in “YY1_SOSchedule_PR_PO”.

Afterwards the user will add the correct associated “NetPriceAmount” and recreate every single elements or calculations in the other Custom CDS Views and Queries.

In the end, the duplicates, that were caused by multiple prices, are vanished.

Conclusion

In this blog post, we saw how to quickly identify the root cause of a wrong data set and fix it in five steps.

1. Get an overview
2. Identify the duplicates
3. Identify the root data source
4. Analyze the associations
5. Replace the wrong associations

Getting an overview of the structure is important. Otherwise you could be confused and overwhelmed at the beginning. If you are building your own Custom CDS Views, please always be aware of the cardinalities. If you have two options, analyze the associations first (see step 4). That will help you avoiding step 5, where you must invest a lot of effort.

In case you have any questions or feedback please drop a comment below. Thank you.