How to Create and Expose a CDS View with Multiple Joins as an OData Service

Exposing a CDS view with multiple joins as an OData service allows complex data models to be accessed in a simplified and scalable way, making it ideal for integration with SAP Fiori applications and SAP Business Technology Platform (BTP) apps. In this guide, we’ll walk through the process, including a real-world business scenario, and provide code examples, transaction codes, and tips for each step.

Business Scenario

Imagine you’re working for a company that needs a consolidated view of vendor payments and purchase orders for reporting purposes. This data is scattered across multiple SAP tables, including LFA1 (Vendor Master), LFB1 (Vendor Master Company Code Data), and EKKO (Purchase Order Header). To provide an efficient, centralized view for your reporting team, you decide to create a CDS view that joins these tables and exposes it as an OData service.

More Such Questions

Step-by-Step Solution

Let’s go through each step to create a CDS view with multiple joins and expose it as an OData service.

Step 1: Define the CDS View with Multiple Joins

  1. Go to Eclipse or SAP Business Application Studio and open your SAP project.
  2. Create a New CDS View:
    • Right-click on the package where you want to create the view and select New > ABAP > Core Data Services > Data Definition.
  3. Define the View and Joins:
    • Use inner join or left outer join as required by your business logic.

Here’s an example CDS view that joins the LFA1, LFB1, and EKKO tables:

@AbapCatalog.sqlViewName: 'ZVENDOR_ORDERS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS View for Vendor Payments and Purchase Orders'
@OData.publish: true

define view Z_VENDOR_ORDERS as select from lfa1
    inner join lfb1 on lfa1.lifnr = lfb1.lifnr
    inner join ekko on lfa1.lifnr = ekko.lifnr
{
    key lfa1.lifnr            as Vendor,       // Vendor Number
    lfa1.name1                as VendorName,   // Vendor Name
    lfb1.bukrs                as CompanyCode,  // Company Code
    ekko.ebeln                as PurchaseOrder, // Purchase Order Number
    ekko.bstyp                as OrderType     // Order Type
}

Key Points:

  • @OData.publish: true: Publishes the view as an OData service, which we’ll register in the SAP Gateway.
  • key Fields: At least one key field is needed, like Vendor, VendorName, or PurchaseOrder, to uniquely identify each row.

Step 2: Activate the CDS View

  1. Activate the CDS view by right-clicking and selecting Activate.
  2. Verify that there are no syntax errors.

Tip: Activation will automatically generate an OData service named Z_VENDOR_ORDERS_CDS in the system.

Step 3: Register the OData Service in SAP Gateway

The next step is to register this OData service in the SAP Gateway to make it available for external applications.

  1. Go to SAP Gateway Service Builder (/IWFND/MAINT_SERVICE).
  2. Add the Service:
    • Select Add Service.
    • In System Alias, choose your system alias.
    • Search for the service Z_VENDOR_ORDERS_CDS and select it.
  3. Add Selected Services:
    • In the pop-up, provide a package or save locally.
    • Click OK to complete the registration.

Step 4: Test the OData Service

Once registered, it’s essential to test the OData service to ensure it returns the expected data.

  1. Go to SAP Gateway Client (/IWFND/GW_CLIENT).
  2. Enter the OData Service URL:rubyCopy codehttps://<your_sap_system>/sap/opu/odata/sap/Z_VENDOR_ORDERS_CDS/
  3. Test the GET Request:
    • Add /Z_VENDOR_ORDERS at the end of the URL to fetch all records.
    • Run the request to view the data.

Tip: Use tools like Postman for more flexible testing of filters, $select, and $expand parameters, if applicable.

Step 5: Connect the OData Service to SAP BTP

To access this OData service in SAP BTP, configure a destination.

  1. Create a Destination in SAP BTP:
    • Go to SAP BTP Cockpit > Connectivity > Destinations.
    • Click New Destination and provide the following:
      • Name: SAP_Backend_OData
      • Type: HTTP
      • URL: Enter your OData service URL (e.g., https://<your_sap_system>/sap/opu/odata/sap/Z_VENDOR_ORDERS_CDS/).
      • Authentication: Select the appropriate authentication method, like Basic Authentication.
      • Proxy Type: Use OnPremise if your SAP backend is on-premise.
    • Save the destination.

Tip: Use SAP Cloud Connector if you are connecting to an on-premise SAP system.

Step 6: Consume the OData Service in SAP BTP Application

Now that your destination is set up, you can consume this OData service within your SAP BTP application. Here’s a quick guide on how to integrate it:

  1. SAP Fiori or SAP UI5 Applications:
    • Use the destination as the data source for your app.
    • Bind the entity set (Z_VENDOR_ORDERS) to tables, lists, or other UI elements.
  2. API Calls:
    • If using a custom application, make HTTP requests to the OData service URL.
    • The destination automatically routes calls from SAP BTP to your SAP backend.

Tip: SAP Fiori Elements can simplify UI generation based on CDS views if you’re building a Fiori app.

Additional Tips

  • Authorization: Use @AccessControl.authorizationCheck if you want to restrict data based on roles.
  • Optimizing CDS Views: Limit data selection using filters and carefully select only the required fields.
  • Error Handling: Use SAP Gateway Client or Postman to troubleshoot OData responses.

Summary

By following these steps, you’ve created a CDS view with multiple joins, exposed it as an OData service, and connected it to SAP BTP for real-time data access. This approach simplifies complex data integration and makes it accessible to a wide range of applications, enhancing data visibility for business users.

Exposing SAP data through OData and integrating it with SAP BTP applications can help streamline reporting, reduce redundancy, and support real-time business insights.

Leave a Comment