Retrieving data from BKPF table without using key fields

[Former Member]

Dear Friends,

As per my project requirement I need to retrieve 1.5 yrs of data from BKPF on the basis of CPUDT(Accounting document entry date) weekly. I cannot use key fields. This data will flow in BI through extractor.

Problem is even if I try to get one month data from BKPF, it fails with error "maximum runtime reached". I thought to create a separate Z table with only required 5 fields but I am not sure even that would work. In development environment I have only few thousand records hence I cannot check the performance properly.

Please advice the best possible solution to retrieve data from tables like BKPF without using key fields.

Regards

Randhir Jha

[Former Member]

Hi Randhir,

can u try your report in background it may

give  correct result , try once.....

[ThomasZloch]

How many rows is 1.5 years in your case?

Anyway, try adding "BKPF-BUKRS EQ <company code>" to your WHERE-conditions, this will (or should at least) make use of secondary index BKPF~5 for CPU date. It probably can't hurt to add "BKPF-BSTAT EQ space" as well, this will return only actual postings (and not parked documents etc.)

Thomas

[Former Member]

Hi Thomas,

Thanks for your prompt response.

Let me tell you the problem in detail. Actually I have a extractor to pull parked document in BI based on table VBKPF & VBSEGK. Now table VBKPF contains only parked document. Problem is when the document is posted/deleted then that document gets deleted from VBKPF and get stores in BKPF.

Now the current status of the document is not pulled in BI since the extractor is using VBKPF & not BKPF. The only table which contains the latest status(BSTAT) is BKPF.

In BI, we get 1.5 years of data from this extractor on CPUDT. Hence I thought to pull data directly from BKPF instead of VBKPF so that I can get the updated status of the document.

Now to answer your question: I don't have any selection apart from CPUDT. Hence I cannot provide company code or status in the selection.

The only selection I can give alongwith CPUDT is Document Type 'KG'. i.e. All documents which are converted to any other status from parked will have document type 'KG'.

But even with document type 'KG', record count is huge. I am not sure if CPUDT & BLART(Doc type) will work.

Regards

Randhir Jha

[Former Member]

Hi Thomas,

You asked about number of rows for 1.5 years. It is approx. 50+ Millions..

Regards

Randhir Jha

[ThomasZloch]

Please have a look at all available indexes of table BKPF via SE11 (in the ERP system, of course).

There is index BKPF~3 as well for document types, however it has BUKRS and BSTAT as preceding fields before BLART, so you would need to provide these as well, if possible with single EQ conditions.

BSTAT = space is always possible. BUKRS is the single most important criteria for FI related data, I don't want to fully believe that you have no way to derive a selection criteria. How many company codes are involved? Can you derive the value from somewhere, e.g. from a controlling area or the likes?

As a last resort another index could be created for BKPF, but discuss this with your system administrator first. Since 1.5 years worth of CPUDT does not sound very selective, maybe it would be better to create it for BLART without preceding BUKRS.

You could use transaction TAANA (on ERP side) to find out how selective such an index would be, e.g. for  BLART = 'KG' , does this return 90% of all BKPF records (bad) or just 2% (good).

Thomas

[volker_borowski2]

Hi,

Usually all selections on BKPF are pretty good indexed.

So if this extraction is the only "bad" access you have to deal with, you might consider to

lock transaction SE16, so nobody can do stupid things during your extraction,

and then alter the table to a parallel degree of 4 or 8 or 16 depending on available CPUs.

The extractor should go for a full table scan in this case utilizing the corresponding number of PQ slaves.

If it is not extracting in packages and with only a single workrocess, you can watch ST04old for the PQ procs to appear an then alter the table back to noparallel so that no additional stupid stuff can take place while the extraction is running.

But I would test this in QA first, if it is possible to take this back while the extraction is running.

If it is doing repetition in the select on BKPF, you might need to avoid concurrent access, but with 16 slaves even a BKPF of several GB should be serviced soon.

There is a risk! If enough users end up forcing selections that do pull parallel query plans, you might get a serious performance loss.

To counter that you could try to set a proper limit of parallel_max_servers, allthough that did not always work for me...

You might see the problem, that selecting BKPF is not the bottleneck when going PQ 16.

You need to write the result into the BW as fast as possible.

Good luck

Volker

Addition: The given stuff is for Oracle. Other DBs might also be capable to do parallel processing, but the syntax, commands and parameter might differ.

Message was edited by: Volker Borowski