Data Extraction at Line-Item Level in Financial Accounting and Controlling

1
Data Extraction (Line Item Level) FI/CO
(Financial Accounting and Controlling) Revised
06/05/03
Paula Gist (CPA), BW RIG Lothar Schubert, BW
RIG October 3rd, 2002
2
Content

• Business Drivers
• FI Extraction
• FI line-item extraction
• Timestamp mechanism
• Customer extensions
• Global Settings
• Availability and further information
• CO-OM Extraction
• CO-OM line-item extraction
• Delta mechanism (Revised 06/05/03)
• Customer extensions (Revised 06/05/03)
• Availability and further information

3
Business drivers

• Flexibility
• Need for project specific choice of fields.
• Check on status fields, like open / cleared
  items.
• Drill-down and drill-through (analysis)
• Drill down capability from summarized data to
  line-item details.
• Full through capability, e.g. from Accounts
  Payable to G/L accounts information.
• Actuality
• Access to recent (or even real-time) data.
• Scalability
• High number / wide variety of users.
• Large volumes of detailed R/3 transaction data
  are transferred to BW ? Daily full loads are
  inefficient.
• In other words Detailed data and delta capability

4
FI line-item extraction Data Sources and Data
Targets
BW
Accts. receivable
Accts. payable
G/L accounts
InfoCubes -gt summarized data analytical queries
on cube data
0FIAP_C03 FIAP Line item
0FIAR_C03 FIAR Line item
Drilldown
ODS -gt document details operational queries on
line-item data
0FI_GL_O02 FIGL Line item
0FI_AR_O03 FIAR Line item
0FI_AP_O03 FIAP Line item
InfoSources
0FI_AR_4 FIAR Line item
0FI_AP_4 FIAP Line item
0FI_GL_4 FIGL Line item
DataSources
R/3
BKPF Header
BSAD Cleared Items
BSAK Cleared Items
R/3 Tables
BSID Open Items
BSEG Segment
BSIK Open Items
5
FI line-item extraction Features

• 0FI_GL_4 (G/L Accounts line items)
• No redundant fields are transferred into BW
• Only fields from the FI document tables
  (BKPF/BSEG) that are relevant to general ledger
  accounting (compare table BSIS),
• No customer or vendor related fields.
• 0FI_AP_4 (AP line items) and 0FI_AR_4 (AR line
  items)
• Vendor / Customer related information (e.g.
  payment/dunning data).
• Coupled extraction ? consistent snapshot of
  FI data in BW
• G/L account extraction determines selection
  criteria (comp.code, fiscal period) and upper
  time limit of all extracted FI line-items.
• AP and AR extraction no further selection
  criteria necessary / possible.
• Uncoupled extraction possible with PlugIn PI
  2002.2, see OSS note 551044.
• Actuality of extracted data
• The most recent FI documents, which are extracted
  to BW, are posted in R/3 the day before.
• See OSS note 485958 if you need to circumvent
  this restriction.

6
FI line-item extraction Delta mechanism

• 0FI_GL_4, 0FI_AP_4, 0FI_AR_4 use an After Image
  Delta
• Delta type Extractor
• Delta records are directly selected from the R/3
  tables using a timestamp mechanism.
• Delta records are directly transferred to BW. No
  record is written to the BW delta queue.
• After Image Delta
• FI line items are transferred from the source
  system in their final state ( After Image).
• This delta method is not suitable for direct
  InfoCube update.
• ODS object is obligatory to determine the delta
  for InfoCube update.
• Where BW Delta Queue is used
• Delta queue and BW scheduler ensure correct
  serialization of the records (e.g. inserts must
  not pass changes)
• Distribution of delta records to multiple BW
  systems.
• Selection criteria of Delta-Init upload are used
  to couple the datasources logically.

7
Timestamp Mechanism New documents

• New FI documents
• Posted in R/3 since the last line-item
  extraction.
• Selection based on the field BKPF-CPUDT.

Table BKPF (FI document header)
MANDT BUKRS BELNR GJAHR
CPUDT CPUTM AEDAT
Table BWOM2_TIMEST (Timestamp table)
FI document key
BWOM2_TIMEST
Upper limit previous day
new documents
Lower limit upper limit of the last G/L
line-item extraction
changed documents
8
Timestamp Mechanism Entries in BWOM2_TIMEST
Table BWOM2_TIMEST (Timestamp table)

• Timestamps are stored as Seconds since
  01.01.1990.
• Other DataSources (e.g. CO line-item extraction)
  also use this table.
• Documentation of upload history Recording of FI
  data that has already been extracted into BW.
• Well-defined sync-points, if the delta upload
  has failed.

9
Timestamp Mechanism Changed documents

• Changed FI documents
• Changed in R/3 since the last line-item
  extraction
• Cannot be selected based on field BKPF-AEDAT
• BKPF-AEDAT is not updated by all BW relevant
  changes of FI
• Example Clearing of an open invoice (by incoming
  payment) changes the document state, but does not
  update BKPF-AEDAT.
• Solution
• Recording of all changed FI documents (FI
  document key, date of last change) in table
  BWFI_AEDAT.
• FI update programs in R/3 had to be changed.
• Selection of the changed FI documents based on
  tables BWFI_AEDAT and (timestamp table)
  BWOM2_TIMEST.

10
Timestamp mechanism Recording of changed docs
Simplified flow chart

• R/3 transactions that record
• changed FI documents in
• table BWFI_AEDAT
• Change document
• Incoming/outgoing payments
• Dunning
• ...

• This entry in table TPS31 starts the recording
  of changed FI documents.
• It should be inserted before the delta-init
  upload.
• See OSS note 401646

11
Customer Extensions

• Extension of extract structures using customer
  includes
• Data source R/3 table Extract struct. Customer
  include
• 0FI_GL_4 BSEG DTFIGL_4 CI_BSIS
• 0FI_AP_4 BSIK/BSAK DTFIAP_3 CI_BSIK
• 0FI_AR_4 BSID/BSAD DTFIAR_3 CI_BSID
• Case 1 All fields of the customer include are
  part of the R/3 table.
• No additional ABAP-coding is required. The fields
  of the customer include is automatically filled
  by the extractor.
• Case 2 Some fields of the customer include are
  not part of the R/3 table.
• Use Business Transaction Event 00005021.
• Build a function module that fills the fields of
  the customer include. Use the following template
  SAMPLE_PROCESS_00005021.
• No modification of SAP standard extractors
  required.
• For more information See OSS note 410799
• Clean separation of SAP standard and customer
  extraction

12
Global Settings (BWOM_SETTINGS)

• BWFILOWLIM
• Here, you can set the lower time limit of the
  time stamp selection for data requests in update
  mode initialization of the delta method. Together
  with the selection criteria, this parameter is a
  decisive factor for the data volume with data
  requests in update mode initialization of the
  delta method.
• BWFISAFETY
• Here, you can the upper time limit of the time
  stamp selection for data requests in all update
  modes. Value 1 in this parameter means that the
  upper time limit of the time stamp selection is
  the previous day. With larger parameter values,
  the upper limit of the time stamp selection is
  correspondingly further (in days) in the past.
• Note that you cannot choose a parameter value
  cannot be smaller than 1, as this would prevent
  the delta dataset being found completely.
• ORGSYSONLY
• If you SAP R/3 system is part of an ALE scenario,
  set this parameter to X. This ensures that only
  those line items that were originally posted to
  the BW system will be transferred there from the
  SAP R/3 system. This prevents line items being
  transferred several times from various SAP R/3
  system.
• DELTIMEST
• With this parameter, you can set the retention
  period of entries that are no longer required in
  time stamp table BWOM2_TIMEST. The default
  settings for deletion of entries that are no
  longer required in the time stamp table is 60
  days. The minimum retention period for entries in
  the time stamp table is 15 days. When entries are
  deleted from table BWOM2_TIMEST, the
  corresponding entries for the changed FI line
  items are deleted simultaneously from log table
  BWFI_AEDAT.

13
Further information and availability

• System requirements
• R/3 source system
• Release 4.0B
• PlugIn 2001.1
• BW
• Release 3.0A
• (Release 2.0b / 2.1c See OSS note 401646)
• SAPNet (OSS) notes
• Implementation of new DataSources 401646
• Extension of extract structures 410799 /
  430303
• Extraction of recent updates 485958
• Migration from 0FI_AP_3 / 0FI_AR_3 410797 /
  200576
• Coupled extraction 428571
• Uncoupled extraction 551044
• Online documentation
• Check documentation on business content, BW 3.0b.

14
CO-OM line-item extraction
BW
InfoCubes -gt summarized data analytical queries
ODS (optional) -gt document details operational
queries on line-item data
InfoSources
DataSources
R/3 Tables
R/3
CO documents
15
CO-OM Delta mechanism

• 0CO_OM_CCA_9, 0CO_OM_OPA_6, etc. use an Additive
  Delta
• Delta type Extractor
• Delta records are directly selected from the R/3
  tables using a timestamp mechanism (Timestamp
  table BWOM2_TIMEST).
• Field COEP-TIMESTMP is used as a reliable
  timestamp for selecting new CO-OM line-items.
• Reference Field name revised 06/05/03
• Delta records are directly transferred to BW. No
  record is written to the BW delta queue.
• Additive Delta
• Only new CO-OM line-items are transferred from
  the source system (Additive Delta). No changes
  on CO documents are relevant for BW.
• This delta method is suitable for direct InfoCube
  update.
• ODS object is optional to store document details
  (for operational queries on line-item data).
• Where BW Delta Queue is used
• Delta queue and BW scheduler ensure correct
  serialization of the records (e.g. inserts must
  not pass changes)
• Distribution of delta records to multiple BW
  systems.

16
CO-OM Customer extensions

• Extension of extract structures
• If there is a requirement to extract new fields
  into BW, use transaction RSA6. The extract
  structure must be increased with the new fields
  required to be extracted. The new data will be
  written automatically with the extractor ,but
  please note, this will not automatically be sent
  to BW if the fields are not in the extract
  structure.
• The includes CI_COBL e.g. for 0CO_OM_CCA_9 do
  not have anything to do with BW but it is normal
  CO include where the customer can fill in some
  new fields he needs in CO.
• Data source R/3 tables Extract struct. Customer
  include
• 0CO_OM_CCA_9 COBK/COEP ICCTRCSTA1 CI_COBL
• 0CO_OM_OPA_6 COBK/COEP ICORDCSTA1 CI_COBL
• 0CO_OM_WBS_6 COBK/COEP ICWBSCSTA1 CI_COBL
• 0CO_OM_ABC_7 COBK/COEP ICBPRCSTA1 CI_COBL
• Please review OSS Note 394491 Handling Errors
  from DataSource Append Structures (released
  05/14/03) for further details
• Revised for content correction 06/05/03

17
CO-OM Further information and availability

• System requirements
• R/3 source system
• Release 4.0B or higher
• BW
• Release 2.0B or higher
• SAPNet (OSS) notes
• Technical background info 416265
• Performance optimization 382329, 387964
• Online documentation
• Check documentation on business content, BW 3.0b.