Transform SAP BW Queries into OData Service: Part 3 – Execute OData Analytics Service
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 😉 .
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.
Excellet series. Thanks for that. Is there any restriction on variable handling?
Hi Durairaj,
A new series has been added. Refer Transform SAP BW Queries into OData Service: Part 4 - Query Parameters.
Regards
Chandan VA
Hii
Can you please tell how to aggregate one measure over all dimensions instead of specifying all the dimension names in the select query in odata service URI
Regards
Nitish
Hi Nitish,
You have to specify the list of dimensions in $select, if you would like to get the measures aggregated based on the dimensions. If you don't specify the dimensions you get the non-aggregated values only.
Regards
Chandan
But in odata service consuming HANA calc view even if we write $select=MeasureName it auto aggregates over all the data(dimension)available and gives back only one single aggregated measure. Is'nt there any other way to achieve this in BW?
Hi Nitish,
This is a bug in SAP NW Gateway system. The issue is now fixed with Note number = 1948203
Regards
Chandan
Hi Chandan,
Really great blog!! Thanks for the sharing.
I have followed your first 2 part and created 1 odata service. But when I tried to execute case:1 from this blog, I am getting the attached error. I have also tried the same for different queries, but getting same error for all.
The metadata screen-shot:
When I tried case 1 :
Please guide me to resolve this.
Regards,
Niket Talati
Hi Niket,
Thanks.
Did you check the error logs. You could check them as described here Troubleshooting a SAP Netweaver Gateway Service.
From the attached screen shot it is difficult to understand the root cause of the error.
Regards
Chandan
Hi Chandan,
Thanks for the response and link.
I have read that but not able to find my error's solution.
Please find below the screen-shot of application-log.
Please help me to resolve the same.
Thank you again!!!
Regards,
Niket Talati
Hi Chandan,
I've few questions here:
a) I've created the service implementation for the MDX query from TCode SEGW
b) Now When i went to TCode: /IWFND/MAINT_SERVICE and tried to add the service giving the System Alias as BW RFC Destination, then its not showing the generated service but when select LOCAL then its giving the generated servcie from step 1. Does that mean that if query changes then we need to generate the service again and we can access changes?
What shall be URL to access this query? This is the name of the Model/Service Entity set:
ZTPM_AG01_ZWEBSER_TPM_RBM_Q001Results
So what will be the final URL?
Thanks ,
Jomy
Hello Jomy,
Please maintain the system alias configuration as below and search for ur service.
Sample->
Regards,
Ashwin