- SAP Managed Tags:
Cheers ABAP folks!
The multidimensional cube view is the heart of the analytical model in ABAP CDS. In this blog post, I want to give you an insight into the modeling patterns of analytical cube views and their role within the ABAP Application Server.
New to analytics? Then I urgently recommend that you read the previous part of this blog post series: Embedded Analytics with ABAP Cloud – A Brief Overview (Part 1)
A multidimensional cube view spans up all the dimensions that exist for a specific data set to which an analytical reporting is applied. A sales order, for example, may contain dimensional attributes such as an order date, an associated product, as well as a business partner reference. Querying the multidimensional cube with parameterization of specific dimensions returns an aggregated result for the matching cells that represent a measure, e.g. the turnover, in the multi-dimensional model:
Pruning single dimensions when querying a multidimensional data set leads to the aggregation of the pruned dimension:
Based on this, a multidimensional cube always provides the overall data basis for your analytical reporting. This can include defining foreign key relationships to dimensions based on dimensional attributes for further data exploration, defaulting for aggregations, and defining text relationships for labeling your attributes.
Ok, enough theory. Let’s get concrete: Imagine you run a company that ships soft drinks to retailers, and you have the following given transactional data model: A sales order is defined at the header level with references to the paying business partner, the business partner to whom the products are to be shipped, and an order date. In addition, each sales order can consist of multiple sales order items that have priced products, as well as a quantity assigned. Additionally, each sales order must be paid for. All these details are represented by different CDS entities that are linked by a relational data model.
In the corresponding transactional UI service, all this relational data is properly joined so the transactional business user can quickly act on specific sales orders. However, it seems a bit challenging to get a summary of last quarter’s sales with this data representation, doesn’t it?
Therefore, it is recommended that you prepare your transactional data model before building an analytical model on top of it. I’ll show you how to do this.
First, ask yourself: What are the relevant metrics that I want to report on to best run my business? In our current transactional sales order model, there are several possible analytical use cases. Let’s focus on reporting on the revenue of sales order items sold and the payment history. The following relational CDS entities are involved in this reporting:
Isolated reporting on these CDS entities would be easy to implement but wouldn’t provide much value to our business. Therefore, it's essential to identify the relevant relationships between these CDS entities and to take CDS entities in between into the reporting scope. The following CDS entity is added to our reporting scope:
Working at a conceptual level is certainly fun for all the theorists out there. However, at this point we need to hit the keys and span up our multidimensional data model directly on top of our relational data model. To do this, it's important to:
In our example, the plain multidimensional data basis could be achieved with the following implementation of a CDS entity:
This CDS view helps to span up the data basis for our multidimensional reporting. Proper key handling is essential here. Further attributes can be added later in the cube again. Key elements that do not belong to the reported data set are initialized with corresponding default values. The result of a data preview should then look like the following picture:
Each data record contains a reference to either a concrete sales order item or to a concrete payment. Each record also contains a reference to a specific sales order header.
After preparing our data basis, we now have a fine-granular reporting of all available data records that can be aggregated safely by the HANA engine without counting any measure twice. To design a multidimensional cube, we now need to:
The cube simply selects from our prepared data model and creates joins to the original data sources to access further attributes. Don’t worry about performance: These additional joins are optimized by the HANA engine anyway. Just be sure to flag your cube with the annotation @analytics.dataCategory: #CUBE.
Next, add all measure-like fields to your cube that you're interested in for your analytical reporting. Measures are identified by their numeric data type, as well as by their capability to be aggregated by default, also called standard aggregation. This property can be set with the annotation @Aggregation.default.
Please consider the following recommendations for measures:
To fine-tune your reporting, it is mandatory to define dimensions in your cube, for example, to group aggregated measures based on the characteristics of a data record. Dimensions are modeled either as foreign key fields, such as a business partner ID, or as pure attribute, such as an order date. Foreign key fields should also be linked via an association to the corresponding dimension view to span up the analytical star schema around your cube. This gives you further possibilities to deep dive into your analytical model at the query level.
Please consider the following recommendations for dimensions:
There may be use cases where some attributes need to represent a measure in one analytical reporting, but also a dimension in another one. Ok – I’ll be honest – Dimeasure is not an official term, but I think you know what I mean, right? Like calculating the average response time of incident processing based on a timestamp field, as well as using the same timestamp field for grouping the results by month. In such a case, make sure to simply introduce the field twice in your cube view. One with a measure-like flavor, the other one with a dimension-like flavor.
The result of an analytical cube could look like the following:
I love your excitement! Building a dedicated CDS Analytical Query on top of our cube will be the next step – in the next part of this series. So, stay tuned!
Ok – I just can’t leave you behind with such a spoiler. Here's a preview of what an analytical reporting could look like when accessed via SAP Analytics Cloud.
Don't forget to checkout further material:
Developer Guide: Analytical Data Modeling
Cheerio ABAP folks!
The multidimensional cube view is the heart of the analytical model in ABAP CDS. In this blog post, I want to give you an insight into the modeling patterns of analytical cube views and their role within the ABAP Application Server.
New to analytics? Then I urgently recommend that you read the previous part of this blog post series: Embedded Analytics with ABAP Cloud – A Brief Overview (Part 1)
What Is a Multidimensional Cube?
A multidimensional cube view spans up all the dimensions that exist for a specific data set to which an analytical reporting is applied. A sales order, for example, may contain dimensional attributes such as an order date, an associated product, as well as a business partner reference. Querying the multidimensional cube with parameterization of specific dimensions returns an aggregated result for the matching cells that represent a measure, e.g. the turnover, in the multi-dimensional model:
f(Dimension 1, Dimension 2, Dimension 3) -> Measure
Example: f('Targot Corp.', '2023-02-25', 'Lemonade') -> turnover of this particular companyPruning single dimensions when querying a multidimensional data set leads to the aggregation of the pruned dimension:
f(Dimension 2, Dimension 3) -> Aggregation of Measure over Dimension 1
Example: f('2023-02-25', 'Lemonade') -> sum of turnovers of all companiesBased on this, a multidimensional cube always provides the overall data basis for your analytical reporting. This can include defining foreign key relationships to dimensions based on dimensional attributes for further data exploration, defaulting for aggregations, and defining text relationships for labeling your attributes.
Multidimensional Reporting on Transactional Data
Ok, enough theory. Let’s get concrete: Imagine you run a company that ships soft drinks to retailers, and you have the following given transactional data model: A sales order is defined at the header level with references to the paying business partner, the business partner to whom the products are to be shipped, and an order date. In addition, each sales order can consist of multiple sales order items that have priced products, as well as a quantity assigned. Additionally, each sales order must be paid for. All these details are represented by different CDS entities that are linked by a relational data model.
The Transactional Model - Represented as Relational Data Model
In the corresponding transactional UI service, all this relational data is properly joined so the transactional business user can quickly act on specific sales orders. However, it seems a bit challenging to get a summary of last quarter’s sales with this data representation, doesn’t it?
The Transactional Model - User Interface
Preparing your Data Model Is Key
Therefore, it is recommended that you prepare your transactional data model before building an analytical model on top of it. I’ll show you how to do this.
Identify Relevant Data Sources
First, ask yourself: What are the relevant metrics that I want to report on to best run my business? In our current transactional sales order model, there are several possible analytical use cases. Let’s focus on reporting on the revenue of sales order items sold and the payment history. The following relational CDS entities are involved in this reporting:
- Sales Order Item(s)
- Payment(s)
Identify Relevant Relationships
Isolated reporting on these CDS entities would be easy to implement but wouldn’t provide much value to our business. Therefore, it's essential to identify the relevant relationships between these CDS entities and to take CDS entities in between into the reporting scope. The following CDS entity is added to our reporting scope:
- Sales Order(s)
Span up Multidimensional Data Basis
Working at a conceptual level is certainly fun for all the theorists out there. However, at this point we need to hit the keys and span up our multidimensional data model directly on top of our relational data model. To do this, it's important to:
- Preserve the unique keys across all involved CDS entities
- Unite your measure-based data sets by using UNIONs in CDS entities
- Resolve to-many-relationships by denormalization via dimension-based relations
In our example, the plain multidimensional data basis could be achieved with the following implementation of a CDS entity:
@EndUserText.label: 'Sales Order Cube Basis'
define view entity P_DD_TSM_ANA_SALESORDER
as select from sdd_tsm_ana_soi as SalesOrderItem
left outer join SDD_TSM_ANA_SO as SalesOrder
on SalesOrder.sales_order_id = SalesOrderItem.sales_order_id
{
key SalesOrder.sales_order_id as SalesOrder,
key SalesOrderItem.sales_order_item_id as SalesOrderItem,
key abap.raw'00000000000000000000000000000000' as Payment
}
union all select from SDD_TSM_ANA_PAYM as Payment
left outer join SDD_TSM_ANA_SO as SalesOrder
on SalesOrder.sales_order_id = Payment.sales_order
{
key SalesOrder.sales_order_id as SalesOrder,
key abap.raw'00000000000000000000000000000000' as SalesOrderItem,
key Payment.payment_id as Payment
}This CDS view helps to span up the data basis for our multidimensional reporting. Proper key handling is essential here. Further attributes can be added later in the cube again. Key elements that do not belong to the reported data set are initialized with corresponding default values. The result of a data preview should then look like the following picture:
Data preview of the cubes data basis
Each data record contains a reference to either a concrete sales order item or to a concrete payment. Each record also contains a reference to a specific sales order header.
Slice and Dice Your Cube
After preparing our data basis, we now have a fine-granular reporting of all available data records that can be aggregated safely by the HANA engine without counting any measure twice. To design a multidimensional cube, we now need to:
- Classify the cube view as such
- Introduce measures, which are used for aggregations
- Introduce dimensions, which are used for grouping based on drilldowns
Definition of a Cube
The cube simply selects from our prepared data model and creates joins to the original data sources to access further attributes. Don’t worry about performance: These additional joins are optimized by the HANA engine anyway. Just be sure to flag your cube with the annotation @analytics.dataCategory: #CUBE.
Measures
Next, add all measure-like fields to your cube that you're interested in for your analytical reporting. Measures are identified by their numeric data type, as well as by their capability to be aggregated by default, also called standard aggregation. This property can be set with the annotation @Aggregation.default.
Please consider the following recommendations for measures:
| Do | Measures should be added to the cube if aggregated reporting is required for them. |
| Do | Aggregations are defined on element level for measures only. |
| Do | Aggregations are defined with annotation @Aggregation.default. |
| Do | Supported default aggregations on cube level are #SUM, #MIN, #MAX. Other aggregations need to be defined in concrete analytical queries. |
| Don't | Calculated measures based on formulas shouldn't be introduced in the cube, but in the analytical query on top. |
Dimensions
To fine-tune your reporting, it is mandatory to define dimensions in your cube, for example, to group aggregated measures based on the characteristics of a data record. Dimensions are modeled either as foreign key fields, such as a business partner ID, or as pure attribute, such as an order date. Foreign key fields should also be linked via an association to the corresponding dimension view to span up the analytical star schema around your cube. This gives you further possibilities to deep dive into your analytical model at the query level.
Please consider the following recommendations for dimensions:
| Do | Dimensions should be added to the cube if drill downs are to be applied. |
| Do | Dimensions may be represented by a dedicated dimension view associated with your cube. |
| Do | Be sure to link such an association with annotation @ObjectModel.foreignKey.association. |
Special Case: Dimeasures
There may be use cases where some attributes need to represent a measure in one analytical reporting, but also a dimension in another one. Ok – I’ll be honest – Dimeasure is not an official term, but I think you know what I mean, right? Like calculating the average response time of incident processing based on a timestamp field, as well as using the same timestamp field for grouping the results by month. In such a case, make sure to simply introduce the field twice in your cube view. One with a measure-like flavor, the other one with a dimension-like flavor.
TL;DR – Just Give Me Some Coding
The result of an analytical cube could look like the following:
@Analytics.dataCategory: #CUBE
define view entity I_DD_TSM_ANA_SALESORDERCUBE
as select from P_DD_TSM_ANA_SALESORDER as SalesOrder
left outer join I_DD_TSM_ANA_SALESORDERITEM as SalesOrderItem
on SalesOrder.SalesOrderItem = SalesOrderItem.SalesOrderItemId
left outer join I_DD_TSM_ANA_PAYMENT as Payment
on SalesOrder.Payment = Payment.PaymentId
association [0..1] to I_DD_TSM_ANA_BUPA as _Buyer
on $projection.Buyer = _Buyer.BupaId
{
key SalesOrder.SalesOrder,
key SalesOrder.SalesOrderItem,
key SalesOrder.Payment,
SalesOrder.OrderDate,
@Aggregation.default: #SUM
@Semantics.amount.currencyCode: 'ItemCurrency'
SalesOrderItem.amount as ItemAmount,
SalesOrderItem.currency as ItemCurrency,
@Aggregation.default: #MAX
Payment.PaymentDueDate as PaymentDueDate,
@Aggregation.default: #MAX
Payment.PaymentDate as PaymentDate,
Payment.PaymentMethod as PaymentMethod,
@Aggregation.default: #SUM
@Semantics.amount.currencyCode: 'PaymentCurrency'
Payment.PaymentAmount as PaymentAmount,
Payment.PaymentCurrency as PaymentCurrency,
@ObjectModel.foreignKey.association: '_Buyer'
SalesOrder.Buyer,
_Buyer
}Great – But Where’s My Fancy Analytical Reporting?
I love your excitement! Building a dedicated CDS Analytical Query on top of our cube will be the next step – in the next part of this series. So, stay tuned!
Ok – I just can’t leave you behind with such a spoiler. Here's a preview of what an analytical reporting could look like when accessed via SAP Analytics Cloud.
Analytical Story in SAP Analytics Cloud (SAC)
Don't forget to checkout further material:
Developer Guide: Analytical Data Modeling
Cheerio ABAP folks!
2 Comments
