In Transform SAP BW Queries into OData Service: Part 2 - Analyze OData Analytics Service you have seen how to interpret the metadata of an OData Analytic service. In this post I will explain how you could execute HTTP GET; Query Operation on the Entity Type annotated as "sap:semantics=aggregate"
Let us continue with the example cube ZCCA_D21/ZCCA_D21_Q0020 for which we generated an OData Analytic Service. Following is a list of How-Tos showing the OData requests for different use cases. I have omitted the OData response as I would like you to give it a try and see how it looks :wink: .
The below table shows the measures and dimensions that are part of Entity Type ZCCA_D21_ZCCA_D21_Q0020Results.
| Property Name | Type | Annotation |
|---|---|---|
| AZCOSTACTUAL0020 | Measure | sap:aggregation-role=measure |
| AZCOSTPLAN0020 | Measure | sap:aggregation-role=measure |
| A2ZCOSTCENT | Dimension | sap:aggregation-role=dimension |
| A1ZCOSTCENT | Dimension Attribute | sap:attribute-for=A2ZCOSTCENT |
| A2ZCOSTELMN | Dimension | sap:aggregation-role=dimension |
| TotaledProperties | Totals | sap:totaled-properties-list |
-------------------------------------------------------------------------------
Case 1 - How to Fetch Non-Aggregated Measure Values
Let us take a simple case; Where I would like to see all non-aggregated measure values. Simply execute an HTTP GET operation on the Entity Set annotated with "sap:semantics=aggregate" i.e., on Entity Set ZCCA_D21_ZCCA_D21_Q0020Results.
OData Request
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results
For the above request you get all the records from the Cube; Non-aggregated. However you get retrieve at the max 99999 records only.
-------------------------------------------------------------------------------
Case 2 - How to Fetch Aggregated Measure Values on a Single Dimension
In this case I would like to aggregate measure values on a dimension. Execute a HTTP GET operation on the Entity Set with $select OData Command. In the $select OData command specify the list of properties representing a measure (annotated as "sap:aggregation-role=measure") and the property representing a dimension (annotated as "sap:aggregation-role=dimension")
OData Request - Fetch aggregated Actual Cost and Planned Cost for each Cost Center
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,AZCOSTPLAN0020,A2ZCOSTCENT
You could also select Dimension Attributes. For example Cost Center Text.
OData Request - Fetch aggregated Actual Cost and Planned Cost for each Cost Center along with Cost Center Name
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,AZCOSTPLAN0020,A2ZCOSTCENT,A1ZCOSTCENT
-------------------------------------------------------------------------------
Case 3 - How To Fetch Aggregated Measure Values on more than one Dimension
In this case I would like to aggregate measure values on more than one dimension. Specify the list of dimensions in the $select OData command.
OData Request - Fetch aggregated Actual Cost and Planned Cost for each Cost Center and Cost Element
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,AZCOSTPLAN0020,A2ZCOSTCENT,A2ZCOSTELMN
-------------------------------------------------------------------------------
Case 4 - How To Fetch Aggregated Measure Values on a Dimension restricted to certain condition
You could filter out the result set by using $filter OData Command. $filter OData command can be applied on those properties that are annotated as "sap:aggregation-role=measure" and "sap:aggregation-role=dimension".
OData Request - Fetch aggregated Actual Cost for Cost Center 100-1000 or 100-1001 and Actual Cost greater than 10,000 dollars
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,A2ZCOSTCENT&$filter=(A2ZCOSTCENT eq 100-1000 or A2ZCOSTCENT eq 100-1001) and AZCOSTACTUAL0020 gt 10000
Well How will I know the values for the Cost Centers? Only if I know the values for the Cost Center I could filter for it.
You could get the list of values for any dimension or dimension attribute by specifying them in $select OData command without any Measures. This gives you the list of members for the dimension.
OData Request - Fetch the members of a dimension
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=A2ZCOSTCENT
-------------------------------------------------------------------------------
Case 5 - How to Fetch Top "N" Aggregated Measure Values on a Dimension Ordered by Measure/Dimension
You could apply OData commands $top, $skip and $ordeby to your OData query request.
OData Request - Fetch Top 10 Actual Costs for Cost Center ordered by Cost Centers
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,A2ZCOSTCENT&$top=10&$orderby=A2ZCOSTCENT
-------------------------------------------------------------------------------
Case 6 - How to Slice the Cube before aggregating the Measures on a certain Dimension
You could slice the cube before aggregating the Measures on certain Dimension. Specify the list of Measures and Dimension in $select and sepecify the list of Dimensions on which slicing of the cube has to be done in $filter. The point to note here is that Dimensions specified in $filter should not be used in $select.
OData Request - Fetch Aggregated Actual Costs for Cost Center where Cost Element is not 400020.
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,A2ZCOSTCENT&$filter=A2COSTELMN ne '400020'
In the above case the Cube is first sliced and records belonging to Cost Element 400020 is filtered out and then Actula cost is aggregated for each Cost Center.
-------------------------------------------------------------------------------
Case 7 - How to get the Sum of aggregated Measure Values on a single/list of Dimensions
You could get a grand total/sub-totals on aggregated Measure values. "totals" is a custom query option that tells the system to find the total/sub-total on the list of dimensions. You would have seen a Property named as TotaledProperties and annotaed as 'sap-aggregation-role="totaled-properties-list"' in the Results Entity Type . Now when "totals" custom query option is specified you would see one or more Entities in the OData response with the property "TotaledProperties" filled with a comma separeated list of Dimensions on which totals was carried out on the aggregated Measure values.
OData Request - Fetch aggregated Actual Costs for each Cost Center along with Totals
/ZCOSTCENTER_1_SRV/ZCCA_D21_ZCCA_D21_Q0020Results?$select=AZCOSTACTUAL0020,A2ZCOSTCENT&totals=A2ZCOSTCENT
OData Response
--click on the Image --
-------------------------------------------------------------------------------
Well I hope now you could now start building your analytic applications using SAP NetWeaver Gateway.
- SAP Managed Tags:
11 Comments
