This blog is about how to use @AnalyticsDetails.elimination to eliminate the internal business volume when executing an analytical query.
Internal business volume
When revenues made between two subsidiaries of company, for company’s total revenue, these revenues should not be considered, as transaction is happening within the company. For example, a company is divided into several subsidiaries. These subsidiaries are divided into further subsidiaries. This means there exists a hierarchy of subsidiaries. Subsidiaries are selling products to each other. For each subsidiary this is external sales. But for a parent subsidiary this is just internal sales, as both subsidiaries are descendants. By using this annotation, revenues made between two subsidiaries in an organization are no longer displayed.
More information about the concept can be found Elimination of Internal Business Volume | SAP Help Portal
Syntax
elimination: {
pair : array of {
dimension1: String(30);
dimension2: String(30);
}
rule : String(20) enum { ELIMINATE_IF_ALL; ELIMINATE_IF_ONE; };
}
The annotation can be used in cube-views (Analytics.dataCategory: #CUBE) or in analytical queries (define transient view entity with provider contract analytical query).
pair: this list defines the pairs (dimension1 and dimension2) which are checked for elimination. Both dimensions have needed a foreign key association to the same target view. If the annotation is used on cube level, both dimensions must be elements of the cube. If the annotation is used in an analytical query, both dimensions must be part of the source cube view.rule: if set to #ELIMINATE_IF_ALL, then the volume is eliminated if all pairs are internal. If set to #ELIMINATE_IF_ONE the volume is eliminated if at least one pair is internal. If “rule” is not set, the AND-logic (ELIMINATE_IF_ALL) is applied.
Example
Following example displays the number of seat booked in flight according to the elimination rule specified on destination airport, departure airport, destination country, departure country.
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Elimination of Internal Busniess volumn’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [#ANALYTICAL_QUERY]
define transient view entity ZLR_PC_FLIGHTICE_SIMPLE
provider contract analytical_query
as projection on ZOQ_FLIGHT_ICE
{
_airportfrom._hier( p_HierarchyID : ‘GEO’ ) as _airportFromHier,
@Consumption.hidden: true
_airportfrom.AirportID as dummyAirportFrom,
_airportto._hier( p_HierarchyID : ‘GEO’ ) as _airportToHier,
@Consumption.hidden: true
_airportto.AirportID as dummyAirportTo,
@AnalyticsDetails.query: {
axis: #ROWS,
displayHierarchy: #ON,
hierarchyAssociation: ‘_airportFromHier’
}
airportfrom,
@AnalyticsDetails.query: {
axis: #ROWS,
displayHierarchy: #ON,
hierarchyAssociation: ‘_airportToHier’
}
airportto,
@Consumption.filter.defaultValue: ‘DE’
@AnalyticsDetails.query.axis: #ROWS
countryfrom,
@AnalyticsDetails.query.axis: #ROWS
countryto,
seatsmax,
@AnalyticsDetails.elimination: {
pair: [{ dimension1 : ‘countryfrom’ , dimension2: ‘countryto’ }]
}
@EndUserText.label: ‘SeatsMax Q-ICE country’
seatsmax as seatsmaxICEQ_C,
@AnalyticsDetails.elimination:{
pair: [ { dimension1 : ‘airportfrom’ , dimension2: ‘airportto’ },
{ dimension1 : ‘countryfrom’ , dimension2: ‘countryto’ } ],
rule: #ELIMINATE_IF_ALL
}
@EndUserText.label: ‘SeatsMax Q-ICE airport and country’
seatsmax as seatsmaxICEQ_AC,
@AnalyticsDetails.elimination:{
pair: [ { dimension1 : ‘airportfrom’ , dimension2: ‘airportto’ },
{ dimension1 : ‘countryfrom’ , dimension2: ‘countryto’ } ],
rule: #ELIMINATE_IF_ONE
}
@EndUserText.label: ‘SeatsMax Q-ICE airport or country’
seatsmax as seatsmaxICEQ_A_C
}
Output
In this example for columns (cells marked with colour):
SeatsMax Q-ICE country – elimination pair mentioned as ‘countryfrom’ and ‘countryto’. Therefore, there is no entry where departure country and destination country are same.
SeatsMax Q-ICE airport and country- elimination pairs are ‘airportfrom’ and ‘airportto’, ‘countryfrom’ and ‘countryto’ And rule as #ELIMINATE_IF_ALL, Therefore, there is no entry where destination airport and departure airport are same AND destination country and departure country are same.
SeatsMax Q-ICE airport or country – elimination pairs are ‘airportfrom’ and ‘airportto’, ‘countryfrom’ and ‘countryto’ And rule as #ELIMINATE_IF_ONE, Therefore, there is no entry where destination airport and departure airport are same OR destination country and departure country are same.
Constraints
The annotation can be used in cubes or in queries.
The dimension names in one pair (AnalyticsDetails.elimination.pair) must have a foreign key association to the same dimension view and this dimension view must have an hierarchy association.In the query definition:It is not necessary that any of the dimensions in the list of pairs is part of the queryIf a hierarchy is assigned to a dimension, then the pair dimension must be assigned to the same hierarchy or must be without hierarchyIf a dimension is not in drill down, then the condition of the one pair is always true (independent of the value of the other dimension of the pair). The same is true on totals/subtotal lines.
This blog is about how to use @AnalyticsDetails.elimination to eliminate the internal business volume when executing an analytical query.Internal business volumeWhen revenues made between two subsidiaries of company, for company’s total revenue, these revenues should not be considered, as transaction is happening within the company. For example, a company is divided into several subsidiaries. These subsidiaries are divided into further subsidiaries. This means there exists a hierarchy of subsidiaries. Subsidiaries are selling products to each other. For each subsidiary this is external sales. But for a parent subsidiary this is just internal sales, as both subsidiaries are descendants. By using this annotation, revenues made between two subsidiaries in an organization are no longer displayed.More information about the concept can be found Elimination of Internal Business Volume | SAP Help PortalSyntaxelimination: { pair : array of { dimension1: String(30); dimension2: String(30); } rule : String(20) enum { ELIMINATE_IF_ALL; ELIMINATE_IF_ONE; }; }The annotation can be used in cube-views (Analytics.dataCategory: #CUBE) or in analytical queries (define transient view entity with provider contract analytical query).pair: this list defines the pairs (dimension1 and dimension2) which are checked for elimination. Both dimensions have needed a foreign key association to the same target view. If the annotation is used on cube level, both dimensions must be elements of the cube. If the annotation is used in an analytical query, both dimensions must be part of the source cube view.rule: if set to #ELIMINATE_IF_ALL, then the volume is eliminated if all pairs are internal. If set to #ELIMINATE_IF_ONE the volume is eliminated if at least one pair is internal. If “rule” is not set, the AND-logic (ELIMINATE_IF_ALL) is applied.Example Following example displays the number of seat booked in flight according to the elimination rule specified on destination airport, departure airport, destination country, departure country. @AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Elimination of Internal Busniess volumn’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [#ANALYTICAL_QUERY]
define transient view entity ZLR_PC_FLIGHTICE_SIMPLE
provider contract analytical_query
as projection on ZOQ_FLIGHT_ICE
{
_airportfrom._hier( p_HierarchyID : ‘GEO’ ) as _airportFromHier,
@Consumption.hidden: true
_airportfrom.AirportID as dummyAirportFrom,
_airportto._hier( p_HierarchyID : ‘GEO’ ) as _airportToHier,
@Consumption.hidden: true
_airportto.AirportID as dummyAirportTo,
@AnalyticsDetails.query: {
axis: #ROWS,
displayHierarchy: #ON,
hierarchyAssociation: ‘_airportFromHier’
}
airportfrom,
@AnalyticsDetails.query: {
axis: #ROWS,
displayHierarchy: #ON,
hierarchyAssociation: ‘_airportToHier’
}
airportto,
@Consumption.filter.defaultValue: ‘DE’
@AnalyticsDetails.query.axis: #ROWS
countryfrom,
@AnalyticsDetails.query.axis: #ROWS
countryto,
seatsmax,
@AnalyticsDetails.elimination: {
pair: [{ dimension1 : ‘countryfrom’ , dimension2: ‘countryto’ }]
}
@EndUserText.label: ‘SeatsMax Q-ICE country’
seatsmax as seatsmaxICEQ_C,
@AnalyticsDetails.elimination:{
pair: [ { dimension1 : ‘airportfrom’ , dimension2: ‘airportto’ },
{ dimension1 : ‘countryfrom’ , dimension2: ‘countryto’ } ],
rule: #ELIMINATE_IF_ALL
}
@EndUserText.label: ‘SeatsMax Q-ICE airport and country’
seatsmax as seatsmaxICEQ_AC,
@AnalyticsDetails.elimination:{
pair: [ { dimension1 : ‘airportfrom’ , dimension2: ‘airportto’ },
{ dimension1 : ‘countryfrom’ , dimension2: ‘countryto’ } ],
rule: #ELIMINATE_IF_ONE
}
@EndUserText.label: ‘SeatsMax Q-ICE airport or country’
seatsmax as seatsmaxICEQ_A_C
} Output In this example for columns (cells marked with colour): SeatsMax Q-ICE country – elimination pair mentioned as ‘countryfrom’ and ‘countryto’. Therefore, there is no entry where departure country and destination country are same.SeatsMax Q-ICE airport and country- elimination pairs are ‘airportfrom’ and ‘airportto’, ‘countryfrom’ and ‘countryto’ And rule as #ELIMINATE_IF_ALL, Therefore, there is no entry where destination airport and departure airport are same AND destination country and departure country are same.SeatsMax Q-ICE airport or country – elimination pairs are ‘airportfrom’ and ‘airportto’, ‘countryfrom’ and ‘countryto’ And rule as #ELIMINATE_IF_ONE, Therefore, there is no entry where destination airport and departure airport are same OR destination country and departure country are same. ConstraintsThe annotation can be used in cubes or in queries.The dimension names in one pair (AnalyticsDetails.elimination.pair) must have a foreign key association to the same dimension view and this dimension view must have an hierarchy association.In the query definition:It is not necessary that any of the dimensions in the list of pairs is part of the queryIf a hierarchy is assigned to a dimension, then the pair dimension must be assigned to the same hierarchy or must be without hierarchyIf a dimension is not in drill down, then the condition of the one pair is always true (independent of the value of the other dimension of the pair). The same is true on totals/subtotal lines. Read More Technology Blog Posts by SAP articles
#SAP
#SAPTechnologyblog
