Skip to Content
Technical Articles
Author's profile photo Sascha Zygar

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

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

  1. 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%20duplicates

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%20duplicates

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%20extended

Overview extended

  1. 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%20purchase%20order

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%20of%20purchase%20order%20items

Values of purchase order items

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

  1. 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%20options%20how%20to%20associate%20the%20data%20sources

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%20option%20A

Association option A

Result Option A

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

Duplicates%20due%20to%20association%20option%20A

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%20option%20B

Association option B

Result Option B

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

Option%20B%20is%20correct

Option B is correct

 

Conclusion

Conclusion

  1. 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%20of%20error

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%20for%20the%20correct%20element

Searching for the correct element

Difference%20between%20old%20and%20new%20element

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

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.

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lingling Qian
      Lingling Qian

      Hello Sascha,

      The documentation is very helpful, but I have a  question and ask you.

      When creating a custom CDS , when I add an associated data source with parameters, clicking check fails with an error, error message is: SQL view Y956CA308268075D cannot be renamed as Y969D17E651FB7E8 (DDL source YY9_4B5C0A2798B075E99EBC2B is active).

      I see in the help document: “Whereas the paramters of your primary data source are copied and added to your custom CDS view as a user-defined parameter and are automatically mapped to this user-defined parameter, parameters from associated data sources remain unmapped and you need to define this mapping by yourself. You will be warned if you forget to do the mapping. For the primary data source you can of course modify the generated mapping as well.”(https://help.sap.com/viewer/0f69f8fb28ac4bf48d2b57b9637e81fa/2011.500/en-US/62cc30e14fb14200838b13503a9fc3e2.html)

      I also manually added parameters and mapped them in the parameters. I don’t know where to do it. Do you have any examples for me to refer to?  Thank you.

      Best Regards,

      Lingling

      Author's profile photo Andreas Riehl
      Andreas Riehl

      Hello Lingling,

      This should not happen. Please create a ticket, so that we can help you with this problem as quick and as effective as possible!

      Regarding on how to take over the parameters from the associated data source, the procedure is to go to the parameter mapping of your associated data source: On the data sources tab, click the parameters icon of the associated data source. There you will find the options that are described in the help you refer to above. The 2002 news blog post also contains some more details on that feature.

      Thanks & Best Regards,
      Andreas

      Author's profile photo Lingling Qian
      Lingling Qian

      Hello Andreas,

      OK,Thanks for your reply。

      Thanks Again,

      Lingling