Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
I am directly reading SAP financial tables, including some large ones like FAGLFLEXA. Note that I am not using the SAP Connector.
Is anyone performing incremental loading of FAGLFLEXA? If so, which fields do you use for the timestamp and key?
Any help or tips are appreciated, for SAP FI in general and FAGLFLEXA in particular.
Thanks!
Kevin
Hi Deepak,
Thanks for the tip. Your post was helpful.
I did some experimentation with a Production FAGLFLEXA table (16 million rows over 8 years and multiple corporate entities). The following combination of fields provide a unique key in this instance:
RYEAR (Fiscal Year)
RBUKRS (Company Code)
DOCNR (Document Number)
DOCLN (Line Item)
As Deepak, points out, the usable date field is BUDAT. If you are using the QvSAPConnector.dll to read the table, you have to specify the where clause in this form:
BUDAT >= '20140121"
I would recommend that those facing this issue (or any table without a defined primary key) experiment with creating a key using Hash128() and various combinations of fields until you find a combination where the key field is unique. An easy way to check this is to display the key field on the dashboard in a list box with the Frequency selected, and also sorted by Frequency in descending order.
I did some experimentation with a Production FAGLFLEXA table (16 million rows over 8 years and multiple corporate entities). The following combination of fields provide a unique key in this instance:
RYEAR (Fiscal Year)
RBUKRS (Company Code)
DOCNR (Document Number)
DOCLN (Line Item)
As Deepak, points out, the usable date field is BUDAT. If you are using the QvSAPConnector.dll to read the table, you have to specify the where clause in this form:
BUDAT >= '20140121"
I would recommend that those facing this issue (or any table without a defined primary key) experiment with creating a key using Hash128() and various combinations of fields until you find a combination where the key field is unique. An easy way to check this is to display the key field on the dashboard in a list box with the Frequency selected, and also sorted by Frequency in descending order.
Depending on your SAP system you may also need to include RLDNR (Ledger) & RCLNT (Client) into your key.
More worrying however is that I've come across posting dates in a FAGFLEXA table that are several days later than the current date. I don't know whether this is typical, but it does introduce the potential for new records to be missed if they have posting dates prior to the latest posting date in the table.
For example, if today is 20140821 and the latest BUDAT value is 20140831, then if a new record is added in a few days time with a posting date of say 21040825, this record will get missed by the incremental load as it is earlier than the latest date.
My solution has been to create a cutoff date of the first of the previous month
LET vCutoffDate = Date( AddMonths( MonthStart( Today() ), -1 ), 'YYYYMMDD');
This does mean a fair few records will get repeatedly loaded each day but the upload will still take substantially less time than a full reload and it should emsure no records get missed. Does anyone have a better idea for handling this problem?