In this example, we use CDS view again, but unlike previous posts, we will use CDS view as ODP provider with delta capability. This technology is used by SAP to provide many S/4HANA extractors. Infact, some BW extractors are deprecated and replaced by extraction-enabled CDS Views.
In this exercise, we will build CDS View on top of SFLIGHT table in S/4HANA. For delta enablement, we need to flag a field to track changes. A time stamp or date. SFLIGHT table has flight date field and delta will load only new flights to adso.
- Create CDS View from SFLIGHT table in your S/4HANA system. This view should be allowed for use with analytics.
- Go to ABAP development perspective in Eclipse
- In your ABAP project, find your package. In the context menu of the package, choose New -> Other ABAP Repository Object. Then choose core data services -> Data Definition. Choose Next.
- Enter the name and description and choose Next.
- Select the radio button to create a new transport request. Enter the description and choose Finish.
- Replace all code with the code shown below in the CDS view
//CDS view ZSVIEWSLFT // @AbapCatalog.sqlViewName:'ZSVIEWSLFT' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EnduserText.label:'ZSVIEWSLFT' @Analytics:(dataCategory: #CUBE, dataExtraction:(enabled:true, delta.byelement.name:'DateLastFlown' delta.byelement.maxDelayInSeconds: '100')) define view ZSVIEWSLFT as select from sflight ( key carrid, key connid, carrid as Airline, connid as FlightConnection, fldate as DateLastFlown, @Semantics.amount.currencyCode: 'Currency' @DefaultAggregation: #MIN price as FlightPrice, @Semantics.currencyCode currency as Currency, planetype as AircraftType, @DefaultAggregation: #SUM seatsocc as NumberOfOccupiedSeats ) where carrid = 'DL'
- Activate the CDS view using the matchstick icon
- This activation creates SQL view in the S/4HANA schema. Look with more attention to the CDS view
- The data extraction annotation allows this CDS view to be used as extractor
- The delta.byElement.name:’DaleLastFlown’ syntax defines which field is used for enable delta load.
2. To build the data flow from this CDS view to a field based adso, first create a data source from the CDS view with name ZDSVIEWSLFT and description
- In the BW modeler, expand the datasource tree ODP-CDS-> ODP-CDS and use the context menu on the ODP-ODP node to choose New -> Datasource.
- Choose Next on the new datasource dialog box.
- On the select template or source object option, use propose from ODP option and choose Next.
- Select your view ZSVIEWSLFT and choose Next.
- Give name and description for the datasource, select type as Transaction Data and choose Finish.
- Check once the tabs Extraction and Data preview.
Note: The CDS view is able to provide delta records as annotation is used for delta enablement.
- On the Fields tab, select FLIGHTPRICE field and on right side choose Currency as unit/currency field. Then activate the datasource.
- Now finish the persistence to this datasource by linking a dataflow to an adso with name and description ZADSOSLFT
- Use the context menu of the datasource and select Explore data flow.
- Drag and adso into the dataflow and name it as ZADSOSLFT
- Hover over the datasource and choose connector icon to connect to the Datasource
- In the context menu of the adso, choose create Persistent BW Object. Use your infoarea, copy name and description and choose Finish.
- Select the last field with the description Occupied Seats in Economy. On the right side of the UI, shorten the name to OCSEAT and data type as NUMC to allow BW reporting on this field because there are some restrictions on data types.
- Review all the tabs of the ADSO and then activate it. On the details tab, choose Manage keys to review the key. In this scenario, if the airline and connection match, the last flown date is overwritten. Therefore, we have only log of the latest flight.
- Return to the dataflow, choose transformation -> Create, Choose Continue to accept default settings.
- Map the fields and activate transformation
- Create DTP in the data flow
- Activate and execute the DTP
- Display DTP monitor, go to administer data target, activate the request.
- Go to display active data. Look at the data records.
- Go to SFLIGHT table in S/4HANA and do some changes to data
- Execute DTP again
- Activate your request, display data. You can notice that flight of type DL is updated with new data which is there in SFLIGHT table in S/4HANA
- Look at the ODQMON transaction in S/4HANA system, double click on your queue name and then double click on the DTP name. You should see two requests, double click on the request at the bottom and you should see the metadata about load time and number of rows
- Finally double click the TSN and review the details of the load, the 1 record.