Hi Abhay,
Sometimes it's very difficult to get millions/billions of records out of SAP , specially when you are dealing with big tables like MSEG , BSEG.
Below is my solution when you dealing with such tables:
Trick: First you will generate separate year wise QVD's and concatenate all generated yearly qvds into final one.
Script Solution:
// Delta or Initial Load
if not IsNull(QvdCreateTime('$(vQVD)\MKPF.qvd')) then
// delta load: read from existing qvd header
SET vLoadType_M = 'delta';
// 1. Get AEDAT and CPUDT
GetDate:
LOAD AEDAT, CPUDT
FROM $(vQVD)\MKPF.qvd (qvd);
// 2. Concatenate both dates in one column (e.g. to prevent null values)
ConDate:
LOAD AEDAT as DAT
Resident GetDate;
LOAD CPUDT as DAT
Resident GetDate;
// 3. Get the last date
MaxDate:
LOAD max(DAT) as MaxDAT
Resident ConDate
Group by 1;
LET vLastLoad_M = Date(Peek('MaxDAT', 0, 'MaxDate'), 'YYYYMMDD');
TRACE DELTALOAD for MKPF & MSEG, starting at $(vLastLoad_M);
DROP Tables GetDate, ConDate, MaxDate;
ELSE
// initial load
SET vLoadType_M = 'initial';
SET vLastLoad_M = '20120101'; // to be defined---------------------------------------------
Let vStartYear = Num(Year(Date#('$(vLastLoad_M)','YYYYMMDD'))); // Set Min Start Year
LET vEndYear = num(Year(Today())); // Set Max Start Year
YearString:
LOAD Concat(Year_,chr(39) & ',' & Chr(39)) as LoopYear; // Generate String for For each Loop if using initial Load
Load
'$(vStartYear)' + RecNo()-1 as Year_
AutoGenerate(vEndYear - vStartYear +1);
Let vYearLoopInitial = chr(39) & Peek('LoopYear',-1,'YearString') & chr(39); // Pick String for For each Loop if using initial Load
DROP Table YearString;
TRACE INITIALLOAD for MKPF & MSEG, starting at $(vLastLoad_M) for Years $(vYearLoopInitial);
ENDIF
if vLoadType_M = 'delta' then
MKPF:
LOAD
*,
MBLNR &'_'& MJAHR as MKPF_Key;
SQL SELECT
*
FROM MKPF
WHERE AEDAT GE '$(vLastLoad_M)' or CPUDT GE '$(vLastLoad_M)'
;
// add records from previous load:
concatenate(MKPF)
load * from MKPF.qvd(qvd)
// but exclude updated records:
where not exists (MKPF_Key);
STORE MKPF into MKPF.qvd;
DROP Table MKPF;
else
FOR Each vLoopYear in $(vYearLoopInitial) // Split MKPF in Years only for reduce amaount of Datasets during initial load
MKPF_$(vLoopYear):
LOAD
*,
MBLNR &'_'& MJAHR as MKPF_Key;
SQL SELECT
*
FROM MKPF where MJAHR = $(vLoopYear);
STORE MKPF_$(vLoopYear) into MKPF_$(vLoopYear).qvd;
DROP Table MKPF_$(vLoopYear);
NEXT
MKPF:
Load * from MKPF_*.qvd (qvd); // Concatenate all generated yearly MKPF Files to one
STORE MKPF into MKPF.qvd (qvd);
DROP Table MKPF;
endif
IF vLoadType_M = 'delta' THEN
MSEG:
LOAD
*,
MBLNR &'_'& ZEILE as MSEG_Key;
SQL SUBSELECT
* // please reduce to required fields, beside BUKRS, BELNR, GJAHR--------------------------------
FROM MSEG
WHERE MBLNR MJAHR IN (
SELECT MBLNR MJAHR FROM MKPF WHERE AEDAT GE '$(vLastLoad_M)' or CPUDT GE '$(vLastLoad_M)'
);
// add records from previous load:
concatenate(MSEG)
load * from MSEG.qvd(qvd)
// but exclude updated records:
where not exists (MSEG_Key);
STORE MSEG into MSEG.qvd;
DROP Table MSEG;
ELSE
FOR Each vLoopYear in $(vYearLoopInitial) // Split MSEG in Years only for reduce amaount of Datasets during initial load
MSEG_$(vLoopYear):
LOAD
*,
MBLNR &'_'& ZEILE as MSEG_Key;
SQL
* // please reduce to required fields, beside BUKRS, BELNR, GJAHR--------------------------------
FROM MSEG where MJAHR = $(vLoopYear);
STORE MSEG_$(vLoopYear) into MSEG_$(vLoopYear).qvd;
DROP Table MSEG_$(vLoopYear);
NEXT
MSEG:
Load * from MSEG_*.qvd (qvd); // Concatenate all generated yearly MSEG Files to one
STORE MSEG into MSEG.qvd (qvd);
DROP Table MSEG;
ENDIF
Hope this will help you!
AS
