rapid marts deltas

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



© SAP AG 2009. All rights reserved. /

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.




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)




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




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




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




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


http://slidepdf.com/reader/full/rapid-marts-deltas 8/49 © SAP AG 2009. All rights reserved. /

Agenda

1. Overview2. Financial Documents (Several Methods)

a) Overview

b) GL Rapid Mart

c) AP Rapid Mart

d) AR Rapid Mart

e) Additional Suggestions






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



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)



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



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



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




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




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




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




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.




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




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.



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



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



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


Data Services

Delete

Delete Closed


Delete Open




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


Data Services

DeleteDelete Closed


Delete Open


Load Closed:

where BSAD.CPUDT >=$G_ABSOLUTE_START_DATE

Load Open:

where BSID.CPUDT >=

$G_ORIG_SDATE



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)



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



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




Agenda

1. Overview



a) Overviewb) Table Partition Example

c) Additional Suggestions

4. Material Documents (Date of Last Change)5. Purchase Documents (Change History)




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




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.




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




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





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


FISC YEAR 2008

FISC YEAR 2007

FISC YEAR 2006

FISC YEAR 2005

b S d d S i l L d





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.


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





The Final step is to extract and reload data from the SAPSource system:


EC-PCA Ledger

GLPCT

FY 2007

Data Services


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




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




Agenda

1. Overview




a) Overview

b) Date of Last Change Example


4 M t i l D t




4. Mat er ia l Doc um ent s

a. Overview

b. Date of Last Change Example

4 M t i l D t




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




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 :




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


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




Agenda

1. Overview





a) Overview

b) Change History Example

c) Additional Information

5a Purc hase Doc um ent s :




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






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.






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 :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 :




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 :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 :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


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 :



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.