Skip to Content
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 —
/wp-content/uploads/2012/12/totals_164029.png
     
   
——————————————————————————-
     
Well I hope now you could now start building your analytic applications using SAP NetWeaver Gateway.
To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member

    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

    (0) 
    1. Former Member Post author

      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

      (0) 
      1. Former Member

        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?

        (0) 
  2. Former Member

    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:

    Capture1.JPG

    When I tried case 1 :

    Capture.JPG

    Please guide me to resolve this.

    Regards,

    Niket Talati

    (0) 
      1. Former Member

        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.

        Capture2.JPG

        Please help me to resolve the same.

        Thank you again!!!

        Regards,

        Niket Talati

        (0) 
      2. Former Member

        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

        (0) 

Leave a Reply