Creating an Extractor View

@AbapCatalog.sqlViewName: 'ZSQL_EXTRACTOR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@Analytics.dataCategory: #CUBE
@Analytics.dataExtraction.enabled : true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Extractor for SO Data'
define view zcds_extractor as
  select from snwd_so as so

  left outer join snwd_so_i as soi
  on so.node_key = soi.parent_key

  left outer join snwd_pd as pd
  on soi.product_guid = pd.node_key 
{
      so.so_id,
      so.created_at,
      ...
}

Consuming Extractors from BW

Delta with Timestamp or Date

If an appropriate timestamp/date exists in the data, annotate on view level:
@Analytics.dataExtraction.delta.byElement.name: <fieldname>

And annotate the field:
@Semantics.systemDateTime.lastChangedAt: true
or
@Semantics.systemDate.lastChangedAt: true

Delta with Deletion Detection

To extract deletions:

@Analytics.dataExtraction.delta.byElement.detectDeletions: true

However, this requires comparing full datasets and can be slow.
Limit deletion search with:

@Analytics.dataExtraction.delta.byElement.ignoreDeletionAfterDays: 30

Exercise – Delta Method using CDPOS and CDHDR

Create an extractor for address data (table ADRP) that returns only changes since last load.

Every time address data changes (e.g. via SU01), a change document is written to CDHDR/CDPOS.

Delta via CDPOS/CDHDR – Solution

@AbapCatalog.sqlViewName: 'ZSQL_EXT_CDPOS'
...
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction: { enabled: true,
                             delta: { byElement.name: 'TSTMP' } }

define view zcds_extractor_cdpos
  as select distinct from cdpos as pos

    inner join cdhdr as hdr 
         on pos.objectclas = hdr.objectclas
        and pos.objectid   = hdr.objectid
        and pos.changenr   = hdr.changenr

    inner join adrp as ad  
         on ad.persnumber = substring(pos.tabkey, 4, 10)
        and ad.date_from  = substring(pos.tabkey, 14, 8)
{
  key ad.persnumber as Persnumber,
  key ad.date_from  as DateFrom,

  @Semantics.systemDateTime.lastChangedAt: true
  dats_tims_to_tstmp(hdr.udate, hdr.utime, 'UTC', $session.client, 'NULL') as Tstmp,

  ad.nation as Nation,
  ad.date_to as DateTo,
  ...
}

CDC Mapping for JOINs in CDS

For JOINs, mapping rules must be defined:

@Analytics.dataExtraction: { enabled: true,
  delta.changeDataCapture.mapping: [
    { table: 'ZBC_TASKS', role: #MAIN,
      tableElement: ['TASK_KEY'], viewElement: ['TaskKey'] },
    { table: 'ZBC_USERS', role: #LEFT_OUTER_TO_ONE_JOIN,
      tableElement: ['USER_ID'], viewElement: ['Assignee'] }
  ]
}

View element order must match DB key element order.

Observer Job

Job /1DH/OBSERVE_LOGTAB (DHCDCR_OBSERVE_LOGTAB) runs continuously.
It checks ML tables and processes entries, then waits 1 second.

Subscriber Logging Table (SL)

Same fields as ML, but same key as DB table.
Name begins with /1DH/SL....

CDC Transactions (1/2)

ODQMON – Monitor Delta Queues

• List of queues
• Subscribers
• DTP requests with timestamps

DHCDCMON – CDC Monitoring

• Registered CDC objects
• ML/SL table names
• Job settings
• Links to logs and statistics