Core Data Services (CDS) Views are at the heart of SAP HANA Live, enabling real-time, semantically rich data models directly on the SAP HANA database. Efficient data retrieval is critical for performance and user experience in reporting and analytical applications. Two fundamental techniques to optimize data access in CDS Views are filtering and sorting.
This article explores how filtering and sorting are implemented in CDS Views, their best practices, and their importance in building efficient, scalable, and responsive SAP HANA Live applications.
Filtering refers to the process of restricting data records returned by a CDS View based on specified conditions. It enables you to focus on relevant data, reduce dataset size, and improve query performance.
Static Filtering: Defined directly within the CDS View using WHERE conditions or path expressions.
@AbapCatalog.sqlViewName: 'ZSALES'
define view Z_SalesData as select from sales_order
{
key sales_order_id,
customer_id,
sales_amount
}
where sales_amount > 1000
Dynamic Filtering: Applied externally when consuming the CDS View, such as via analytical tools or OData services, using parameters or filter expressions.
Parameter-Based Filtering: CDS Views can accept input parameters that act as dynamic filters.
define view Z_SalesByRegion
with parameters
p_region : region_t
as select from sales_order
{
key sales_order_id,
customer_id,
sales_amount
}
where region = :p_region
@Analytics.dataCategory: #FILTERABLE to expose fields for UI-level filtering.Sorting arranges the result set of a CDS View based on one or more fields, either in ascending or descending order. Sorting improves data readability and supports business requirements for ordered data presentation.
Using the ORDER BY Clause:
CDS Views support the ORDER BY clause in their definitions to enforce sorting.
define view Z_SortedSales as select from sales_order
{
key sales_order_id,
sales_amount,
sales_date
}
order by sales_date desc, sales_amount asc
Sorting at Runtime:
When CDS Views are exposed as OData services or consumed in SAP Fiori apps, sorting can be applied dynamically by the UI or query tool without hardcoding it in the view.
SAP HANA Live provides predefined CDS Views and Virtual Data Models for operational reporting. Effective filtering and sorting:
Filtering and sorting are essential mechanisms to control the data output of CDS Views in SAP HANA Live. Proper use of these techniques not only optimizes system performance but also improves the overall usability of SAP analytical applications. Understanding how to implement and balance static, dynamic, and parameter-based filters along with sorting strategies empowers SAP professionals to build efficient and user-centric real-time reporting solutions.