Long-running Archive of Audit Data

[former_member187113]

Hi,

We are busy doing some (long overdue) housekeeping of BPC Audit Data in one of our Models. Because it's never really been done before, the record count is 2 billion audit records. Well, 2,031,866,496 records, if you want to get technical. They span multiple Categories, with most in Actual and then Budget, I suspect

We are archiving the "lesser" categories first, so as to get them out of the way. At the moment, we are lucky if we are getting 1,500,000 (1.5 million) records into the Audit Archive Tables per day. The records coming in seem to be overtaking the ones we need to Archive.

We run about 5 jobs simultaneously.

At this rate, we'll almost never finish.

This is SAP BPC 10.0 on BW 7.31. The hardware is pretty much as good as it will ever get and we have 32 CPU's.

It does seem, though, that /CPMB/ARCHIVE_DATA is very slow and inefficient.

Has anyone experienced this before? I can't seem to find a Note describing this or a fix.

Kind Regards

Nick

[former_member187113 · Accepted solution]

Hi All,

There is a SAP Note that applied. https://launchpad.support.sap.com/#/notes/0002082999

It refers to this SPRO Model Parameter UJU_AUDDATA_HEADER. For now we've set it to 10. This looks to be helping.

Just thought I'd let everyone know ...

Kind Regards

Nick

[former_member186338 · Accepted solution]

Yes, something like this 🙂 This note is a part of 801 SP09

[former_member187113 · Accepted solution]

Thanks, Vadim

Yip, ... we're well behind in SP's. 😞

But it's working and we're getting through all the Audit records. 🙂 Probably 250,000 per minute at a guess.

Regards

Nick

[former_member186338 · Accepted solution]

By the way even if you plan the migration to hana it's better to upgrade to some latest SP before upgrade to avoid possible migration issues.

[former_member186338]

I suspect that your issues are related to extremely old SP levels for both BPC and BW ...

[former_member187113]

Hi Vadim,

Thanks. You could well be right.

Kind Regards

Nick

[former_member187113]

Hi Vadim,

We are running:

• CPMBPC 801 SP04
• SAP_BW 731 SP08

(I hope that's enough.)

We're well behind on patch levels at this point, but since we are migrating to HANA, there is no point in using our testing resources for upgrades here.

Hope this helps.

Kind Regards

Nick

[former_member187113]

Hi Vadim,

It's just whether business wants the data deleted (which I need to confirm). I am sure UJU_DELETE_AUDIT_DATA works fine. (I just haven't got there yet).

It's more a case of getting the Audit records into the Audit Archive Tables. Surely the size of the destination table should not have too much of a bearing on the transfer time? Unless there are a huge number of indexes or something.

It seems the DM package takes all the records it needs (from the Audit Table), writes them into some holding area (memory / temp table or something) and then does a two-phase commit on the whole batch. (I am no ABAPer, so I am merely guessing), which appears to be hugely inefficient. We have three jobs running for 24+ hours right now and the DB Server is not working hard, so it's not a case of DB disk thrashing or anything like that.

Kind Regards

Nick

[former_member186338]

Please provide detailed info about you BPC and BW version and SP!

[former_member187113]

HI JP,

Much appreciate you coming back to me.

At the moment the plan is not to delete the Audit Archive Data. (Eventually we will move off the landscape onto HANA and we won't be taking this archive data with us. At the moment there are only 44 million rows there - compared to the 2 billion in the Audit Data Table.)

The main reason for archiving the Audit Data is that UJBR cannot handle it. After 13 hours of backing up, the process fails.

As far as the Audit Data is concerned, there are various retention dates. Actual and Budget are at 730 days (2 years) and some of the other categories a lot less. Mostly 30 days.

Let me check if it is a possibility to delete the Audit Archive Data, although I have a sneaky suspicion we're still going to have performance problems even if I do. Let's see ...

Kind Regards

Nick

[former_member186338]

But what is the issue to delete Audit Archive Data?

UJU_DELETE_AUDIT_DATA

[Former Member]

Hi Nick,

What is your retention time for archive data in archive tables? Do you plan to delete it or not??? Please delete the archive audit data also from the archive tables and then check again the performance of DM.

Check this blog and note

https://blogs.sap.com/2015/04/17/how-to-archive-the-data-change-audit-and-clearingdeleting-the-archi...

https://launchpad.support.sap.com/#/notes/2031162

Regards,

JP