In the era of real-time analytics and in-memory computing, SAP HANA Live provides a powerful platform for live reporting directly on transactional data. A crucial part of designing effective data models in SAP HANA Live involves defining calculated fields and aggregations. These features enable business users and developers to enrich raw data with derived metrics and summarized insights—delivered instantly without the need for data duplication or pre-aggregation.
This article explores how calculated fields and aggregations are defined and leveraged within SAP HANA Live to enable sophisticated, real-time reporting.
Calculated fields are virtual columns derived from existing data fields using expressions or formulas. Unlike physical database columns, calculated fields do not store data but compute values on the fly when queried.
In SAP HANA Live, calculated fields are typically defined within Core Data Services (CDS) views or Analytical Views:
CDS Views: Using SQL expressions or built-in functions within the view definition.
Example:
@CalculatedField: true
define view Sales_Calculated as select from Sales {
Sales.Amount,
Sales.Quantity,
(Sales.Amount / Sales.Quantity) as AveragePrice
}
Analytical Views: Via calculation columns that use formulas in the HANA Studio or Web IDE.
Aggregation refers to summarizing data by applying functions like SUM, COUNT, AVG, MIN, MAX over groups of data records. Aggregations are vital for transforming transactional details into meaningful summaries, such as total sales by region or average delivery time by month.
In SAP HANA Live CDS views, aggregations are often achieved by:
SUM(), COUNT(), AVG().@Aggregation.default to specify default aggregation behavior.Example of an Aggregation in CDS:
define view Sales_Aggregated as select from Sales {
Sales.Region,
sum(Sales.Amount) as TotalSales,
count(Sales.OrderID) as OrderCount
}
group by Sales.Region
Often, calculated fields and aggregations are used together to create complex business metrics. For example, calculating the average sales price by dividing the aggregated sales amount by the total quantity sold:
define view Sales_Metrics as select from Sales {
Sales.Region,
sum(Sales.Amount) as TotalSales,
sum(Sales.Quantity) as TotalQuantity,
(sum(Sales.Amount) / sum(Sales.Quantity)) as AvgPrice
}
group by Sales.Region
This approach allows businesses to build key performance indicators (KPIs) directly in the data layer, ensuring consistency and accuracy in reports and dashboards.
Calculated fields and aggregations are fundamental capabilities in SAP HANA Live, enabling powerful, real-time data enrichment and summarization without compromising performance. By defining these constructs at the database layer using CDS views or Analytical Views, organizations gain flexible, consistent, and high-performance reporting solutions.
Mastering calculated fields and aggregations helps SAP professionals unlock the full potential of SAP HANA Live, transforming raw transactional data into actionable business insights in real time.