rapid marts deltas
Post on 05-Apr-2018
226 Views
Preview:
TRANSCRIPT
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 1/49
Rapid Mar t s pac k ages XI 3 .2 for SAP so lu t ionsIncremental Loads
Rapid Marts – Implementation Highlights
October 2009
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 2/49
© SAP AG 2009. All rights reserved. / Page 2
Disc la imer
The information in this presentation is confidential and proprietary to SAP and may not bedisclosed without the permission of SAP. This presentation is not subject to your licenseagreement or any other service or subscription agreement with SAP. SAP has no obligation topursue any course of business outlined in this document or any related presentation, or to
develop or release any functionality mentioned therein. This document, or any relatedpresentation and SAP's strategy and possible future developments, products and or platformsdirections and functionality are all subject to change and may be changed by SAP at any timefor any reason without notice. The information on this document is not a commitment, promiseor legal obligation to deliver any material, code or functionality. This document is provided
without a warranty of any kind, either express or implied, including but not limited to, the impliedwarranties of merchantability, fitness for a particular purpose, or non-infringement. Thisdocument is for informational purposes and may not be incorporated into a contract. SAPassumes no responsibility for errors or omissions in this document, except if such damageswere caused by SAP intentionally or grossly negligent.
All forward-looking statements are subject to various risks and uncertainties that could causeactual results to differ materially from expectations. Readers are cautioned not to place unduereliance on these forward-looking statements, which speak only as of their dates, and theyshould not be relied upon in making purchasing decisions.
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 3/49
© SAP AG 2009. All rights reserved. / Page 3
Agenda
1. Overviewa) What is an Incremental Load?
b) Identifying Changed Records
c) Types of Incremental Load in Rapid Marts
2. Financial Documents (Several Methods)
3. Standard & Special Ledgers (Partitioned Tables)
4. Material Documents (Date of Last Change)
5. Purchase Documents (Change History)
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 4/49
© SAP AG 2009. All rights reserved. / Page 4
1a. Overv iew:What is an Inc rem ent a l (or Del t a) Load?
The terms “Incremental” or “Delta” Load are interchangeable
They are designed to minimize load time for Rapid Marts
They load only records that have changed in the SAP sourcesystem since last update of the Rapid Mart
This is referred to as the “Delta” between source (SAP) and target (Rapid Mart)systems
They have a Prerequisite: They need a way of identifying changedrecords in the source (SAP) system
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 5/49
© SAP AG 2009. All rights reserved. / Page 5
1b. Over v iew :Ident i fy ing Changed Rec ords
Rapid Marts use 2 basic ways of identifying changed records in theSAP source system:
“Date of Last Change” – a date column in the source table
“Change History” – Entries in SAP table CDHDR
However:
There are many variations on how these 2 methods are implemented in
the SAP source systemMany SAP source tables use no method of identifying a changed record
at all
This is challenging for Rapid Marts
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 6/49
© SAP AG 2009. All rights reserved. / Page 6
1c . Over v iew :Types of Inc rem ent a l Load in Rap id Mar t s
Rapid Marts handle this challenge in many ways
Dimensions – Small, low volume tables
Most use no method to identify changed records
Most are Fully Reloaded in Rapid Marts
This is not a big impact on Rapid Marts
Fact Tables – Large, high volume tables
Very inconsistent in identifying changed recordsSome use “Date of Last Change”
Some use “Change History”
Some use no method to identify changed recordsThis has a BIG impact on Rapid Marts
This presentation focuses on the various Incremental methods used to loadthe Fact tables
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 7/49
© SAP AG 2009. All rights reserved. / Page 7
1c . Over v iew :Types of Inc r em ent a l Load in Rap id Mar t s
The presentation shows examples for each method used:
Financial Documents
– GL, AP, & AR Rapid Marts
– Various Methods
Standard & Special Ledgers
– GL & Cost Rapid Marts
– Partitioned Tables MethodMaterial Documents (also called Material Movements)
– Inventory Rapid Mart
– “Date of Last Change” Method
Purchasing Documents
– Purchasing Rapid Mart
– “Change History” Method
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 8/49 © SAP AG 2009. All rights reserved. / Page 8
Agenda
1. Overview2. Financial Documents (Several Methods)
a) Overview
b) GL Rapid Mart
c) AP Rapid Mart
d) AR Rapid Mart
e) Additional Suggestions
3. Standard & Special Ledgers (Partitioned Tables)
4. Material Documents (Date of Last Change)
5. Purchase Documents (Change History)
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 9/49 © SAP AG 2009. All rights reserved. / Page 9
2a. Financ ia l Doc um ent s :Overv iew
SAP does not have a method to identify changes in the source tables forFinancial Documents:
BKPF - Accounting Document Header
BSEG - Accounting Document Line ItemsBSEG_ADD - Accounting Document Line Items (Non-Leading)
BSID - Secondary Index for Customers - Open Items
BSAD - Secondary Index for Customers - Cleared Items
BSIK - Secondary Index for Vendors - Open Items
BSAK - Secondary Index for Vendors - Cleared Items
Furthermore, SAP allows:
Adjusting Entries in past fiscal periodsAdjusting Entries in future fiscal periods
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 10/49 © SAP AG 2009. All rights reserved. / Page 10
2a. Financ ia l Doc um ent s :Overv iew
These conditions pose challenges for Incremental (or Delta) Loads in theRapid Mart environment
This section will explain
The Incremental Load Used in each Rapid Mart
The Alternatives Available
Additional Customization Suggestions
Replication
CDC (Change Data Capture)
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 11/49 © SAP AG 2009. All rights reserved. / Page 11
2b. Financ ia l Doc um ent s :General Ledge r Rapid Mar t
General Ledger: FINANCIAL_DOCUMENT_FACT Incremental LoadDeletes & Reloads a range of fiscal years between
$G_SDATE-1 and $G_EDATE+1
$G_SDATE & $G_EDATE are typically a day or two apart for Incremental Load
Example:
– $G_SDATE = 01-Oct-2009
– $G_EDATE = 02-Oct-2009
– Range of records deleted: – Year of $G_SDATE – 1 to Year of $G_EDATE + 1
– FY 2008 to FY 2010
FINANCIALDOCUMENT
FACT
Range of Fiscal
Years Deletedand ReloadedNote: $G_SDATE and $G_EDATE areGlobal Variables set in the DataServices Load Job. For incrementalloads, values are typically:
$G_SDATE = Date of Last SuccessfulLoad
$G_EDATE = Current (Today’s) Date
FY 2008 - 2010
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 12/49 © SAP AG 2009. All rights reserved. / Page 12
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t
Consider Changing Delta Load range if:
You post more than 1 Fiscal Year in the Past or Future
Your Fiscal Year does not start on January 01 as fiscal year may not coincide
with year in $G_SDATE and $G_EDATEConsider Partitioning FINANCIAL_DOCUMENT_FACT
Partition by Fiscal Year
Drop & Re-add Partitions is much faster than deleting range of Fiscal Years
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 13/49 © SAP AG 2009. All rights reserved. / Page 13
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
See additional “Project” in GL ATL called “Example General Ledger RapidMart SAP”
Uses Entry Date (BKPF.CPUDT) as basis for load
Entry Date is only reliable timestamp date in Financial DocumentsEntry Date is date a financial document is created (system assigned)
Uses Original Start Date and Absolute Start Date Global Variables to computeincremental range
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 14/49 © SAP AG 2009. All rights reserved. / Page 14
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
AW FIRST RUNDATE(JOB START DATE =
01-Jan-2000)
1. On First Run (Initial Load) of Rapid Mart, value of GlobalVariable $G_SDATE is stored in tableAW_FIRST_RUN_DATE
2. On Incremental Loads, this value is retrieved and stored inGlobal Variable $G_ORIG_SDATE
$G_ORIG_SDATE = 01-Jan-2000
Note: in First Run, $G_SDATE and $G_EDATEtypically load many years of data
Example:
$G_SDATE = 01-Jan-2000
$G_EDATE = 31-Dec-2009
10 years of data
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 15/49 © SAP AG 2009. All rights reserved. / Page 15
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
3. On Incremental Loads, get earliest ENTRY DATE for Open Itemsfrom Financial Document Fact.
4. This is stored in Global Variable $G_ABSOLUTE_START_DATE
Example: $G_ABSOLUTE_START_DATE = 04-May-2005
FINANCIALDOCUMENTFACT
Select Min(ENTRY_DATE)
From FINANCIAL_DOCUMENT_FACT
where CLEARED_FLAG = ‘N’
Note: BKPF.CPUDT (Entry Date) is stored as
FINANCIAL_DOCUMENT_FACT.ENTRY_DATE in the Rapid Mart
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 16/49 © SAP AG 2009. All rights reserved. / Page 16
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
5. Delete records from Financial Document Fact.
Delete where CLEARED_FLAG = ‘Y’ and ENTRY_DATE >=$G_ABSOLUTE_START_DATE
FINANCIALDOCUMENT
FACT
Delete Closedrecords
Delete All Openrecords
Delete where CLEARED_FLAG = ‘N’
This ensures records that were open but have closed since last loadcan be reloaded as closed items
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 17/49 © SAP AG 2009. All rights reserved. / Page 17
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
6. Reload From SAP solutions.
Where BKPF.CPUDT >=$G_ABSOLUTE_START_DATE and
BSEG.AUGDT <> ‘00000000’
SAP TablesBKPF & BSEG
Load Closed
Load OpenWhere BKPF.CPUDT >= $G_ORIG_SDATE and
BSEG.AUGDT = ‘00000000’
Note: BSEG.AUGDT is the Clearing Date. It is ‘0000000’ for OpenRecords and set to the clearing (or closing) date for ClosedRecords.
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 18/49 © SAP AG 2009. All rights reserved. / Page 18
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
Advantage:
Loads all possibly changed records
Disadvantage:
Incremental Load can be almost as large as Initial (First) LoadConsider Partitioning Financial Document Fact table to mitigate this impact.
Partition by Fiscal Year
Drop & Re-add Partitions is much faster than deleting range of Fiscal Years
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 19/49 © SAP AG 2009. All rights reserved. / Page 19
2b. Financ ia l Doc um ent s :Genera l Ledger Rapid Mar t A l t er nat ive Load
Note: You can override the setting of $ABSOLUTE_START_DATE if you are sure recordsbefore a certain date can be ignored.
Example: As computed, $G_ABSOLUTE_START_DATE = 14-Jan-2003.
This is because you have some very old bad debts that cannot be collected.These are open financial documents.
Without these bad debts, $G_ABSOLUTE_START_DATE would be set to22-Feb-2009.
Override the setting to 22-Feb-2009 in your job initialization script in DataServices.
The Data Services job will use your override instead of the value in theFinancial Document Fact table.
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 20/49 © SAP AG 2009. All rights reserved. / Page 20
2c . Financ ia l Doc um ent s :AP & AR Rapid Mar t s
Incremental Logic very similar to Alternate Incremental Logic for GL Rapid Mart on previousslides
However some variation is introduced because of differing table architecture in SAP and theRapid Marts
Selection of $G_ABSOLUTE_START_DATE and $G_ORIG_SDATE is the same
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 21/49 © SAP AG 2009. All rights reserved. / Page 21
2c . Financ ia l Doc um ent s :AP Rapid Mar t (1 o f 2)
Accounts Payable: VENDOR_ITEM_FACT BSIK – Secondary Index for Vendors - Open Items
BSAK – Secondary Index for Vendors - Cleared Items
These 2 tables only contain records with Account Type = “K”
BSIK
BSAKVENDOR ITEMFACT
Source Data Services Rapid Mart Target
Data Services
DeleteDelete Closed
Where CLEARED_FLAG = ‘C’ andENTRY_DATE >=$G_ABSOLUTE_START_DATE
Delete Open
Where CLEARED_FLAG = ‘O’
Load Closed:
where BSAK.CPUDT >=$G_ABSOLUTE_START_DATE
Load Open:
where BSIK.CPUDT >=
$G_ORIG_SDATE
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 22/49 © SAP AG 2009. All rights reserved. / Page 22
Load Open:
whereBSEG.AUGBL = ‘ ‘ and
BKPF.CPUDT >=$G_ORIG_SDATE
Load Closed:
Where
BSEG.AUGBL <> ‘ ‘ andBKPF.CPUDT >=$G_ABSOLUTE_START_DATE
2c . Financ ia l Doc um ent s :AP Rapid Mar t (2 o f 2)
Accounts Payable: VENDOR_FIN_DOC_FACTBKPF - Accounting Document Header
BSEG - Accounting Document Line Items
Where Account Type = “K” or Purch Doc ID is not Blank.
BKPF
BSEG
VENDOR FIN DOCFACT
Source Data Services Rapid Mart Target
Data Services
Delete
Delete Closed
Where CLEARED_FLAG = ‘C’ andENTRY_DATE >=$G_ABSOLUTE_START_DATE
Delete Open
Where CLEARED_FLAG = ‘O’
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 23/49 © SAP AG 2009. All rights reserved. / Page 23
2d. Financ ia l Doc um ent s :AR Rap id Mar t
Accounts Receivable: CUSTOMER_ITEM_FACT BSIK – Secondary Index for Customers - Open Items
BSAK – Secondary Index for Customers - Cleared Items
These 2 tables only contain records with Account Type = “D”
BSID
BSADCUSTOMER ITEMFACT
Source Data Services Rapid Mart Target
Data Services
DeleteDelete Closed
Where CLEARED_FLAG = ‘C’ andENTRY_DATE >=$G_ABSOLUTE_START_DATE
Delete Open
Where CLEARED_FLAG = ‘O’
Load Closed:
where BSAD.CPUDT >=$G_ABSOLUTE_START_DATE
Load Open:
where BSID.CPUDT >=
$G_ORIG_SDATE
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 24/49 © SAP AG 2009. All rights reserved. / Page 24
2e. Financ ia l Doc um ent s :Addi t iona l Suggest ions
These are additional suggestions for optimizing Incremental Loads forFinancial Documents
Replication (Mirror)
CDC (Change Data Capture)
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 25/49 © SAP AG 2009. All rights reserved. / Page 25
2e. Financ ia l Doc um ent s :Mi r ror Repl ic at ion
Near Real-Time replication (< 1 minute latency)
Offered by DBMS or 3rd party software vendors
Replicates SAP solutions base tables to a target “mirror” database
The “mirror” is simply a database – No SAP application layer.Usually done by reading database logs on SAP source system.
Advantages
Minimal performance impact on source and mirror databases
True incremental functionality
Disadvantages
Substantial cost for 3rd party software
Need to write database triggers to capture “date of last change” for SAP base table(BKPF, BSEG, BSID, BSAD, etc) on mirror DB
Extensive modification of Rapid Marts to use trigger derived data
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 26/49 © SAP AG 2009. All rights reserved. / Page 26
2e. Financ ia l Doc um ent s :CDC (Change Dat a Capt ur e)
Publishes or streams history of transactions
Offered by DBMS or 3rd party software vendors
History available on demand
Usually done by reading database logs on SAP source system intoadditional history tables or via an API
Advantages
Minimal performance impact on source and target databasesHistory can capture changes for financial documents in near real-time
True incremental functionality
Mirror Database & Triggers not needed
Disadvantages
Substantial cost for 3rd party software
Extensive modification of Rapid Marts to use history derived data
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 27/49
© SAP AG 2009. All rights reserved. / Page 27
Agenda
1. Overview
2. Financial Documents (Several Methods)
3. Standard & Special Ledgers (Partitioned Tables)
a) Overviewb) Table Partition Example
c) Additional Suggestions
4. Material Documents (Date of Last Change)5. Purchase Documents (Change History)
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 28/49
© SAP AG 2009. All rights reserved. / Page 28
3a. St andard & Spec ia l Ledgers :Overv iew
SAP does not maintain a method to identify changes in these sourcetables for Ledgers in “Classic GL”:
GLFUNCT - Totals table with functional area for IDES
GLPCT - EC-PCA: Totals TableGLT0 - GL Acct. master record transaction figures
This presentation will focus on Partitioned Tables used in “Classic” GL toaccommodate the Incremental Load for these tables
Partitioned tables technique is used in:
Cost Center Rapid Mart
General Ledger Rapid Mart
Plant Maintenance Rapid MartProject Systems Rapid Mart
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 29/49
© SAP AG 2009. All rights reserved. / Page 29
3a. St andard & Spec ia l Ledgers :Overv iew
Each DBMS uses a different architecture for Partitioned Tables
Partitions are transparently implemented for 3 of the 4 target data DBMS’ssupported by Rapid Marts:
OracleDB2
SQL-Server
The 4th DBMS (Teradata) optimizes the SQL “Delete” statement so nopartitions are needed.
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 30/49
© SAP AG 2009. All rights reserved. / Page 30
3a. St andard & Spec ia l Ledgers :Advant ages o f Par t i t i ons
They allow quick deletion of data by partition
Typically the partition is “dropped” or “truncated”
Details vary by DBMS
Example: Delete Data for a Fiscal Year (Oracle syntax example)
With Non-partitioned table, SQL delete:
– Delete from table XYZ where FISCAL_YEAR = 2007;
– Database intensive & SlowWith Partitioned table, Truncate:
– Alter Table XYZ truncate partition FY2007;
– Not database intensive & Quick
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 31/49
© SAP AG 2009. All rights reserved. / Page 31
3b. St andard & Spec ia l Ledgers :Table Par t i t ion Ex am ple
The target tables are “Range” partitioned by Fiscal Year
SAP Source Table: GLPCT (EC-PCA Ledger)
Rapid Mart Target: PROFIT_CENTER_SUMMARY_FACT_VR
PROFIT CENTER SUMMARYFACT VR
FISC YEAR 2008
FISC YEAR 2007
FISC YEAR 2006
FISC YEAR 2005
Data for eachFiscal Year in a
separatePartition
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 32/49
© SAP AG 2009. All rights reserved. / Page 32
3b. St andard & Spec ia l Ledgers :Table Par t i t ion Ex am ple
Incremental Load Deletes & Reloads a range offiscal years between
$G_SDATE and $G_EDATE
$G_SDATE & $G_EDATE are typically a day or twoapart for Incremental Load
At year end this can be a 2 year range
Example:
– $G_SDATE = 01-Oct-2007
– $G_EDATE = 02-Oct-2007
– Range of records deleted:
– Year of $G_SDATEto Year of $G_EDATE – FY 2007 to FY 2007
The incremental load starts by dropping ortruncating the partition for the year specified
PROFIT CENTER SUMMARYFACT VR
FISC YEAR 2008
FISC YEAR 2007
FISC YEAR 2006
FISC YEAR 2005
b S d d S i l L d
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 33/49
© SAP AG 2009. All rights reserved. / Page 33
3b. St andard & Spec ia l Ledgers :Table Par t i t ion Ex am ple
Depending on target DBMS, a new empty partition maybe added to the database for FY2007
However, for all DBMS targets, the end result is an emptyPartition for FY2007, ready to receive new data.
PROFIT CENTER SUMMARYFACT VR
FISC YEAR 2008
FISC YEAR 2007 (Empty)
FISC YEAR 2006
FISC YEAR 2005
Ready fornew Data
3b S d d & S i l L d
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 34/49
© SAP AG 2009. All rights reserved. / Page 34
3b. St andard & Spec ia l Ledgers :Table Par t i t ion Ex am ple
The Final step is to extract and reload data from the SAPSource system:
Source Data Services Rapid Mart Target
EC-PCA Ledger
GLPCT
FY 2007
Data Services
PROFIT CENTER SUMMARYFACT VR
FISC YEAR 2008
FISC YEAR 2007 (Empty)FISC YEAR 2007 (Reloaded)
FISC YEAR 2006
FISC YEAR 2005
3 St d d & S i l L d
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 35/49
© SAP AG 2009. All rights reserved. / Page 35
3c . St andard & Spec ia l Ledgers :Addi t iona l Suggest ions
We recommend your DBA tune your target database for optimalperformance with Partitioned tables
As delivered, target tables have partitions defined for:
Fiscal Years 1999 – 2012 If you are loading data outside this range, adjust the target tables with the
appropriate range
Consider Changing Delta Load range if:
You post more than 1 Fiscal Year in the Past or Future
Your Fiscal Year does not start on January 01 as fiscal year may not coincidewith year in $G_SDATE and $G_EDATE
A d
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 36/49
© SAP AG 2009. All rights reserved. / Page 36
Agenda
1. Overview
2. Financial Documents (Several Methods)
3. Standard & Special Ledgers (Partitioned Tables)
4. Material Documents (Date of Last Change)
a) Overview
b) Date of Last Change Example
5. Purchase Documents (Change History)
4 M t i l D t
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 37/49
© SAP AG 2009. All rights reserved. / Page 37
4. Mat er ia l Doc um ent s
a. Overview
b. Date of Last Change Example
4 M t i l D t
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 38/49
© SAP AG 2009. All rights reserved. / Page 38
4a. Mat er ia l Doc um ent s :Overv iew
SAP maintains “date of last change” to identify changed MaterialDocuments
Two fields are used in source table MKPF (Material Document Header)
– MKPF.CPUDT – Entry Date (Created On Date) – MKPF.AEDAT – Date of Last Change
Both are compared to global variables $G_SDATE and $G_EDATE in the DataServices job
The target table in the Inventory Rapid Mart isMATERIAL_MVMT_FACT
Note: the terms Material Document and Material Movement are synonymous
4b M t i l D m t
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 39/49
© SAP AG 2009. All rights reserved. / Page 39
4b. Mat er ia l Doc um ent s :Example
Incremental Load is for range of dates between
Global variables $G_SDATE and $G_EDATE
$G_SDATE & $G_EDATE are typically a day or two apart for Incremental
LoadExample:
– $G_SDATE = 01-Oct-2007
– $G_EDATE = 02-Oct-2007
The incremental load selects records from MKPF and MSEG created or changedwithin this date range.
Typically this is a very small set of records
4b Mat er ia l Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 40/49
© SAP AG 2009. All rights reserved. / Page 40
4b. Mat er ia l Doc um ent s :Example
Material Documents: MATERIAL_MVMT_FACT MKPF – Header: Material Document
MSEG – Document Segment: Material (Line Items)
MKPF
MSEG
MATERIAL MVMTFACT
Source Data Services Rapid Mart Target
Data Services
Selection Logic:
(MKPF.CPUDT >=$G_SDATE ANDMKPF.CPUDT <=$G_EDATE)
OR(MKPF.AEDAT >=
$G_SDATE ANDMKPF.AEDAT <=$G_EDATE)
Uses “Table ComparisonTransform” to Insert or
Update Target Table
Agenda
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 41/49
© SAP AG 2009. All rights reserved. / Page 41
Agenda
1. Overview
2. Financial Documents (Several Methods)
3. Standard & Special Ledgers (Partitioned Tables)
4. Material Documents (Date of Last Change)
5. Purchase Documents (Change History)
a) Overview
b) Change History Example
c) Additional Information
5a Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 42/49
© SAP AG 2009. All rights reserved. / Page 42
5a. Purc hase Doc um ent s :Overv iew
SAP maintains a set of records in table CDHDR “Change History” to identifychanged Purchase Documents.
Within CDHDR these fields are used:CDHDR.OBJECTCLAS = ‘EINKBELEG’
– Identifies the subset of Purchase Documents in CDHDR
– (Einkbeleg is German for Purchase Document)
CDHDR.UDATE
– Identifies the date of last change
CDHDR.OBJECTID
– The Purchase Document ID
5a Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 43/49
© SAP AG 2009. All rights reserved. / Page 43
5a. Purc hase Doc um ent s :Overv iew
However, CDHDR entries only identify changes
They do not identify new Purchase Documents
To identify new documents, the “item change date” on the PurchaseDocument Line Item is used:
EKPO.AEDAT
Note: Although called “change date” in the SAP DataDictionary, EKPO.AEDAT is actually set to the current
(system) date when a new Purchase Document iscreated. Thus it serves as the creation date.
5a Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 44/49
© SAP AG 2009. All rights reserved. / Page 44
5a. Purc hase Doc um ent s :Overv iew
Therefore, the incremental load uses two ABAP data flows to identify new orchanged Purchase Documents
1st pass to identify NEW Purchase Documents
2nd pass to identify CHANGED Purchase Documents
The Rapid Mart uses a text file (flat file) on the SAP server to store the list ofchanged or new Purchase Documents
The text file contains only a list of Purchase Document ID’s
A 3rd
pass reads the text file as input, and collects the remaining fields needed toload Purchase Documents to the Rapid Mart target table.
5a Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 45/49
© SAP AG 2009. All rights reserved. / Page 45
5a. Purc hase Doc um ent s :Change His t or y Ex am ple
Incremental Load is for range of dates between
Global variables $G_SDATE and $G_EDATE
$G_SDATE & $G_EDATE are typically a day or two apart for Incremental
LoadExample:
– $G_SDATE = 01-Oct-2007
– $G_EDATE = 02-Oct-2007
The incremental load selects records from CDHDR and EKPO created orchanged within this date range.
Typically this is a very small set of records
5b Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 46/49
© SAP AG 2009. All rights reserved. / Page 46
5b. Purc hase Doc um ent s :Change His t or y Ex am ple
1st
Pass: Collect New Purchase Document ID’s Input: - EKPO - Purchasing Document Line Items
Output: “PurchDoc.dat” (Text File)
EKPO
Data Services
Where:
EKPO.AEDAT >= $G_SDATE and
EKPO.AEDAT <= $G_EDATE
PurchDoc.dat
(Delete)
SAP Server
Any existing data (fromprevious loads) isdeleted before new
data is added.
5b Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 47/49
© SAP AG 2009. All rights reserved. / Page 47
5b. Purc hase Doc um ent s :Change His t or y Ex am ple
2nd
Pass: Collect Changed Purchase Document ID’s Input: - CDHDR - Change document header (Change History Header)
Output: “PurchDoc.dat” (Text File)
CDHDR
Data Services
Where:
CDHDR.OBJECTCLAS = 'EINKBELEG' and
CDHDR.UDATE >= $G_SDATE and
CDHDR.UDATE <= $G_EDATE
PurchDoc.dat
(Append)
SAP Server
Purch. Doc. ID’sAppended to resultsof 1st Data Flow
5b Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 48/49
© SAP AG 2009. All rights reserved. / Page 48
5b. Purc hase Doc um ent s :Change H ist or y Ex am ple
3rd
Pass: Extract New and Changed Purchase Documents Input: “PurchDoc.dat” (Text File)
Input: EKKO – Purchase Document Header
Input: EKPO – Purchase Document Line Items
Output: PURCH_DOC_FACT – Purchase Documents
EKKO
EKPO
PURCH DOCFACT
Source Data Services Rapid Mart Target
Data Services
Where:PurchDoc.dat.EBELN =
EKKO.EBELN and
EKKO.EBELN =
EKPO.EBELN
Uses “Table ComparisonTransform” to Insert or
Update Target Table
PurchDoc.dat
(Input)Note: “EBELN” is the
SAP column name
for Purchase Doc. ID
5c Purc hase Doc um ent s :
8/2/2019 Rapid Marts Deltas
http://slidepdf.com/reader/full/rapid-marts-deltas 49/49
5c . Purc hase Doc um ent s :Add i t iona l In fo rm at ion
Table CDPOS (Change History Details): Additional Table for Change History
Carries details (which field changed? What was previous value? etc.)
This is a clustered table in SAP solutions
Very cumbersome to use
Slows load performance dramatically
We recommend you DO NOT use it
Table CDHDR can be used to identify changes to other documents byusing different values for OBJECTCLAS column:
EINKBELEG – Purchase Document
VERKBELEG – Sales Document (used in Sales Rapid Mart)
MELDUNG – Notifications (Plant Maintenance)
MATERIAL – Material Master
LIEFERUNG – Vendor Master
However, to the best of our knowledge:
No definitive list of all possible OBJECTCLAS values exists Many values are in German and can require translation.
top related