- SAP Managed Tags
Hello Experts,
i posted this question initially in another Q&A Forum because i was not sure if this Learning Group is the right place to ask questions.
But since i recieved no answer to my question i will post the question in this Learning Group again.
Some background information:
Back in R/3 we used KE35 (Form) and KER1 (Key Figure Scheme) to prepare for KE30 (Profitability Report) in SAP CO-PA modul.
So far i have done significant tests only with transient view entity … analytical query and was able to replace our custom reports from R/3 costing based COPA to S/4 Margin Analysis queries. The results are very good and V3 consumption views are nice to use.
However, i noticed that using a custom consumption view offers very little reuseability. A new requirement triggers a new consumption view and copy/paste of the CDS view. So i want to achieve REUSEABILITY for restricted measures and calculated measures. Looking at the SAP VDM i think it is time to build custom cubes, which i successfully circumvented so far.
So how can i replace R/3 „Forms“, „Key Figures Schemes“ and „Reports“ with Embedded Analytics? I am trying to do a crossover to the VDM which uses cubes, querys, restricted measures, and calculated measures. (Screenshot shows R/3 setup of CO-PA reporting)
I am not trying to replicate the old world 1 by 1, but take over what is still useful e.g. „Prepare a Form“ and „Prepare Key Figures“ and reuse this in multiple querys.
I created the above „custom VDM“ and while the values of the restricted measures are shown correct (see below, RSRT) in the consumption view, the calcuated measures are always inital. What am i doing wrong?
Formulas are very simple:
Revenue - COGS = ContributionMargin1 and FIDirect - OverhAlloc = FIMinusOverhead
My guess is that the calculated measures may have lost their dimensions? Could that be the reason?
Or do i not understand how aggregation works?
Greatful for any tipps. I can share the CDS Views if needed.
Thank you
Regards
David
Hello Experts,
i posted this question initially in another Q&A Forum because i was not sure if this Learning Group is the right place to ask questions.
But since i recieved no answer to my question i will post the question in this Learning Group again.
Some background information:
Back in R/3 we used KE35 (Form) and KER1 (Key Figure Scheme) to prepare for KE30 (Profitability Report) in SAP CO-PA modul.
So far i have done significant tests only with transient view entity … analytical query and was able to replace our custom reports from R/3 costing based COPA to S/4 Margin Analysis queries. The results are very good and V3 consumption views are nice to use.
However, i noticed that using a custom consumption view offers very little reuseability. A new requirement triggers a new consumption view and copy/paste of the CDS view. So i want to achieve REUSEABILITY for restricted measures and calculated measures. Looking at the SAP VDM i think it is time to build custom cubes, which i successfully circumvented so far.
So how can i replace R/3 „Forms“, „Key Figures Schemes“ and „Reports“ with Embedded Analytics? I am trying to do a crossover to the VDM which uses cubes, querys, restricted measures, and calculated measures. (Screenshot shows R/3 setup of CO-PA reporting)
I am not trying to replicate the old world 1 by 1, but take over what is still useful e.g. „Prepare a Form“ and „Prepare Key Figures“ and reuse this in multiple querys.
I created the above „custom VDM“ and while the values of the restricted measures are shown correct (see below, RSRT) in the consumption view, the calcuated measures are always inital. What am i doing wrong?
Formulas are very simple:
Revenue - COGS = ContributionMargin1 and FIDirect - OverhAlloc = FIMinusOverhead
My guess is that the calculated measures may have lost their dimensions? Could that be the reason?
Or do i not understand how aggregation works?
Greatful for any tipps. I can share the CDS Views if needed.
Thank you
Regards
David
Bluesky
- SAP Managed Tags
Hello Koushik, thank you for your reply. The calculated measure is already blank in ADT preview.
Please have a look at the screenshot. The Preview shows single rows. On single row level the calculation is of course impossible. It needs to aggregate the rows first so that RevenueGlobalCurr and COGSGlobalCurr are summarized.
But ADT is not doing it.
I am using DefaultAggregation SUM in the Restricted Measure Cube
//// Restricted Measures ////
@EndUserText.label: 'Revenue'
@Semantics.amount.currencyCode: 'GlobalCurrency'
@DefaultAggregation: #SUM
case when SemanticTag = 'ZGS_C_PRIC'
and Ledger = '0L'
then curr_to_decfloat_amount ( AmountInGlobalCurrency )
else null
end as RevenueGlobalCurr,
@EndUserText.label: 'COGS'
@Semantics.amount.currencyCode: 'GlobalCurrency'
@DefaultAggregation: #SUM
case when SemanticTag = 'ZGS_L_PRIC'
and Ledger = '0L'
then curr_to_decfloat_amount ( AmountInGlobalCurrency )
else null
end as COGSGlobalCurr,And also using it in the Calculcated Measure Cube
//// Key Figures with Semantic Tag
@EndUserText.label: 'Revenue'
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'GlobalCurrency'
RevenueGlobalCurr,
@EndUserText.label: 'COGS'
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'GlobalCurrency'
COGSGlobalCurr,It looks like the aggregation is not working as intended? What can be done to fix it?
I also removed all dimensions that are not needed to allow aggregation but this also did not fix the issue.
Thank you
David
- SAP Managed Tags
Hello Koushik,
thanks for the info. I was able to fix my issue with the aggregation. I didn´t know the difference between OLAP aggregation (which i used in the consumption view) and SQL aggregation (which i have to use here in the cube). I tried to aggregate data with OLAP annotation and that does not work in the cube.
The raw measure cube - no aggregation
@AbapCatalog.sqlViewName: 'ZCO_MA_ST_RM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'MarginAna DF Raw Measure CUBE Part 2'
@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions: true
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
@Analytics.internalName: #LOCAL
@Search.searchable: false
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
define view ZCO_I_MA_ST_DF_RM_CUBE
with parameters
P_GLAccountHierarchy : fins_sem_tag_hryid,
P_CompanyCode : bukrs
as select from I_MargAnlysProdAndSrvcCube
( P_GLAccountHierarchy : $parameters.P_GLAccountHierarchy )
{
//// Dimensions ////
key cast( Ledger as fins_ledger ) as Ledger,
key cast( CompanyCode as bukrs ) as CompanyCode,
key cast( FiscalYear as fis_gjahr ) as FiscalYear,
key FiscalYearPeriod,
key GLAccountHierarchy,
key SemanticTag,
//// Raw Units of Measure ////
cast( BaseUnit as meins ) as BaseUnit,
cast( CompanyCodeCurrency as fis_hwaer ) as CompanyCodeCurrency,
cast( GlobalCurrency as fis_rkcur ) as GlobalCurrency,
//// Restricted Measures ////
@EndUserText.label: 'Revenue'
@Semantics.amount.currencyCode: 'GlobalCurrency'
case when SemanticTag = 'ZGS_C_PRIC' and Ledger = '0L'
then AmountInGlobalCurrency
else 0 end as RevenueGlobalCurr,
@EndUserText.label: 'COGS'
@Semantics.amount.currencyCode: 'GlobalCurrency'
case when SemanticTag = 'ZGS_L_PRIC' and Ledger = '0L'
then AmountInGlobalCurrency
else 0 end as COGSGlobalCurr,
@EndUserText.label: 'FIDirect'
@Semantics.amount.currencyCode: 'GlobalCurrency'
case when SemanticTag = 'ZGS_DISCLP' and Ledger = '0L'
then AmountInGlobalCurrency
else 0 end as FIDirectGlobalCurr,
@EndUserText.label: 'OverhAlloc'
@Semantics.amount.currencyCode: 'GlobalCurrency'
case when SemanticTag = 'ZGS_PR_SUR' and Ledger = '0L'
then AmountInGlobalCurrency
else 0 end as OverheadAllocGlobalCurr
// _SemanticTag
}
where CompanyCode = $parameters.P_CompanyCode
and Ledger = '0L'On top a Cube for aggregation
@AbapCatalog.sqlViewName: 'ZCO_MA_ST_AGG'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'MarginAna DF Raw Measure CUBE AGG Part 2'
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
define view ZCO_I_MA_ST_DF_RM_AGG
with parameters
P_GLAccountHierarchy : fins_sem_tag_hryid,
P_CompanyCode : bukrs
as select from ZCO_I_MA_ST_DF_RM_CUBE(
P_GLAccountHierarchy : $parameters.P_GLAccountHierarchy,
P_CompanyCode : $parameters.P_CompanyCode
)
{
key Ledger,
key CompanyCode,
key FiscalYear,
key FiscalYearPeriod,
key GLAccountHierarchy,
GlobalCurrency,
// In classic CDS (SQL semantics), the moment you use an aggregate function like sum( ),
// the SELECT becomes an aggregating SELECT. Then the rule is: every element that is not aggregated must be listed in a GROUP BY.
@EndUserText.label: 'Revenue'
@Semantics.amount.currencyCode: 'GlobalCurrency'
sum( RevenueGlobalCurr ) as RevenueGlobalCurr,
@EndUserText.label: 'COGS'
@Semantics.amount.currencyCode: 'GlobalCurrency'
sum( COGSGlobalCurr ) as COGSGlobalCurr,
@EndUserText.label: 'Revenue + COGS'
@Semantics.amount.currencyCode: 'GlobalCurrency'
sum( RevenueGlobalCurr ) + sum( COGSGlobalCurr ) as RevenuePlusCOGSGlobalCurr,
@EndUserText.label: 'FIDirect'
@Semantics.amount.currencyCode: 'GlobalCurrency'
sum( FIDirectGlobalCurr ) as FIDirectGlobalCurr,
@EndUserText.label: 'OverhAlloc'
@Semantics.amount.currencyCode: 'GlobalCurrency'
sum( OverheadAllocGlobalCurr ) as OverheadAllocGlobalCurr
}
group by
Ledger,
CompanyCode,
FiscalYear,
FiscalYearPeriod,
GLAccountHierarchy,
GlobalCurrencyAnd the result is that 58 rows were compressed/aggregated to 5 rows. 5 rows is correct because of FISCALYEARPERIOD.
Using SUM and GROUP BY was the solution.
Happy with the result 🙂
Edit: The downside seems to be that a drilldown from the query is no longer possible, because the cube aggregates. This needs to be taken into consideration.
Some thoughts: A better solution would be something like a consumption view on top of a consumption view, but this is technically not possible. First consumption view for restricting measures and formula calculation. The second consumption view (as a query) with characteristic structure.
Or a complete different approach like in "old" CO-PA with a "key figure scheme" pulled from somewhere and included in the CDS View. Maybe the introduction of semantic tags had this in mind, but the semantic tags used in "Universal Hierarchies" only allow assignment to accounts but no custom calculation/formula.
| User | Count |
|---|---|
| 8 | |
| 6 | |
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 |
