i have huge data for SAP cluster table BSEG which does not contain date in it only year i have , so i m struggling with incremental load in it.
so i want to split my data on yearly basis,, let say once i will extarct the data of 2015 ans store it into qvd..and now on daily basis i will extract the data of 2016 and use to append the 2016 data into 2015 data qvd...
but i m facing the problem like when i am appending 2016 data into 2015 data on daily basis my records are getting increasing...
i am using the script like below
Tab1:
load
PK,
F1,
F2
from path...
Tab2:
load
PK,
F1,
F2
from path where not exist(PK)..
Pls correct me in this if i m wrong some where ....
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;
