Performance Optimization in CDS Entities : Best Practices in SAP S/4 Hana Embedded Analytics

***** Please be informed that this article will always be updated based on recent SAP S/4 Hana guidelines or best practices *****

In SAP S/4HANA, creating efficient Entity CDS views is crucial for delivering high-performance analytics. Below are detailed strategies, best practices, and additional considerations to guide developers in building robust CDS views for embedded analytics:

In SAP S/4HANA, creating efficient Entity CDS views is crucial for delivering high-performance analytics. Below are detailed strategies, best practices, and additional considerations to guide developers in building robust CDS views for embedded analytics:

?? 1. Use Associations Over Joins:

• Associations enable navigation on demand, unlike joins that fetch data regardless of necessity. This approach reduces data retrieval overhead and enhances performance.
• Advantages of Associations: They support path expressions, key/text handling, and F4 help, making the data model more intuitive and user-friendly.
• Example: Replace LEFT OUTER JOIN with an association between the Sales Order and Customer tables to improve query execution.

?? 2. Restrict Data Early:

• Always filter data at the basic CDS view level using WHERE clauses or input parameters. This minimizes the data volume passed to higher layers, reducing memory usage and processing time.
• Example: In a Sales Data Basic view, filter Region = 'North' or Order Status = 'Completed' to avoid loading irrelevant data.

?? 3. Use Parameterized Views:

• Parameterized views allow dynamic filtering during runtime, fetching only relevant data without overloading the database.
• Parameters Should Be Pushed Down to the Basic Level :

To optimize performance, always apply parameters at the basic CDS view level. This ensures data filtering happens at the earliest stage, reducing the volume of data passed through to composite and consumption views.

For example:

• In financial scenarios, use parameters like BUDAT (Posting Date) or BUKRS (Company Code) from the ACDOCA table (I_JOURNALOPERATIONALENTRY CDS View) in the custom basic view.
• This prevents fetching unnecessary data at higher layers and allows more streamlined processing.

By maintaining this approach across all layers (Basic → Composite → Consumption), performance improves as only relevant data flows through the data model.

?? 4. Avoid SELECT * Statements:

• Always specify required fields instead of fetching all columns. This practice minimizes the amount of data retrieved and processed.
• Tip: Explicitly list fields such as Sales Order ID, Customer Name, and Amount instead of using SELECT *.

?? 5. Reduce Nested CDS Views:

• Avoid overly complex nested views as they increase runtime complexity. Flatten the structure by combining logic in fewer layers to improve query performance.

?? 6. Follow Layered Architecture (VDM Model):

• Ensure your data model adheres to the Virtual Data Model (VDM) architecture, which separates views into basic, composite, and consumption layers. This approach ensures modularity, reusability, and clarity.

?? 7. Design Composite Views with Detailed Data:

• Composite views should contain detailed, transactional-level data. Avoid aggregating data within CDS views (e.g., using GROUP BY). Aggregations should be performed in front-end tools like Web Dynpro, SAP Fiori, AFO, or SAC for better flexibility and performance.

?? 8. Leverage Annotations:

• Use annotations like @Analytics.dataCategory for dimension or fact views, and @Consumption.filter to optimize runtime queries and improve the end-user experience.

?? 9. Optimize Aggregations:

• Perform aggregations at the most granular level possible within the CDS view. Avoid runtime aggregations in higher layers as they can increase processing time.
• Example: Use SUM(SalesAmount) in the base view instead of aggregating in composite views.

?? 10. Use CDS Hierarchies:

• Hierarchy annotations (@Hierarchy) allow structured and drillable reporting. Define hierarchies for fields like cost centers or organizational units for better analytical insights.

?? 11. Utilize Standard CDS Views:

• SAP provides a vast library of standard CDS views that cover various business domains. Reuse these views by extending them using the EXTEND VIEW concept to add custom fields or logic, ensuring compatibility with updates and leveraging SAP’s tested functionality.
• Tip: Avoid direct access to SAP database tables. If no standard CDS view exists, create custom CDS views with only the required fields instead of querying all table columns.

?? 12. Avoid DISTINCT Usage:

• Using DISTINCT in CDS views is not recommended due to potential performance impacts. Instead, debug redundancy by checking data in the database tables and ensuring all relevant key fields are used between entities based on functional requirements.

?? 13. Add Case Statements in Composite Views:

• Use case statements in composite views for complex business logic. Avoid adding such logic in basic views as it may limit reusability.
• Example: Create a calculated field for categorizing customers into "High Value" or "Low Value" based on sales figures in a composite view.

?? 14. Perform Calculations at the Right Level:

• Perform basic calculations in the basic views and complex aggregations or business logic in composite views. This separation ensures modularity and better maintainability.

?? 15. When to Use AMDP:

• For extremely complex scenarios requiring custom SQL scripts, use ABAP Managed Database Procedures (AMDP). However, avoid this approach in a clean-core implementation as per SAP’s recommendation.
• In What Scenarios Can AMDP Be Used?

While ABAP Managed Database Procedures (AMDP) are powerful, they deviate from SAP's Clean Core approach. Use AMDP only when:

1. CDS Views Are Insufficient: For advanced computations(Usage of Window(), Row_number() , Rank() functions) or recursive logic.
2. High Performance Is Crucial: When database-specific SQL is needed.
3. Mass Data Processing: For large-scale batch operations.

Clean Core Note: Prioritize CDS views, annotations, or external services. Use AMDP only as a last resort.

?? 16. Use Date Functions for Data Restriction:

• Restrict data using date functions to fetch only the required timeframe. SAP provides standard views like C_SglGregorianCalDateFunction for YTD or MTD calculations.
• Example: Filter data for the last three years using a join with C_SglGregorianCalDateFunction in the basic view.

?? 17. Virtual Data Model Best Practices:

• Design CDS views following SAP’s Virtual Data Model (VDM) guidelines. Separate views into analytical (facts and dimensions), reuse, and consumption layers to ensure scalability and clarity.

?? 18. Optimize Consumption Views:

• Use consumption views to expose data to analytical tools like SAP Fiori or SAC. Include all user-required fields, filtering options, and meaningful labels using annotations like @EndUserText.label.

?? 19. Test for Performance:

• Regularly test the performance of CDS views under real-world scenarios. Use SAP tools like Planviz(ADT Tool) to analyze and resolve bottlenecks.
• This tool helps to know the runtime performance , memory and costlier join statements in an analytical query in S4.

Why Follow These Practices?

By following these best practices, you can create high-performing CDS views that support complex analytical queries, reduce system load, and ensure scalability. These strategies align with SAP’s clean-core approach, ensuring smooth upgrades and compatibility with future innovations.

? Start implementing these techniques today to deliver powerful, scalable analytics with SAP S/4HANA CDS views!