Optimize Clear Package

[sap_user62]

Env BPC 10.1 NW Standard/BW 7.4

Hello Friends,

We get annual plan data from a non-bpc system. It gets loaded every couple of hours. There is no delta mechanism.

I have to run a clear package before loading the data. The annual plan is for 12 months and the clear package takes around 20 min to run. (around 3 mil records)

Is there a faster way to clear the data. I created a test badi with the following code, not much of an improvement.

 LOOP AT ct_data ASSIGNING <ls_data> .
      IF <ls_data>-category = 'A_PLAN'.
        <ls_data>-signeddata = <ls_data>-signeddata * 0.
        ENDIF.
 ENDLOOP.

[former_member186338 · Accepted solution]

To clear data you can try to use script logic with RUNALLOCATION

//CLEAR.LGF
*RUNALLOCATION
*FACTOR=0
*DIM CATEGORY WHAT=%CATEGORY_SET%; WHERE=%CATEGORY_SET%
*ENDALLOCATION

With HANA RUNALLOCATION can use HANA optimization!

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

[former_member186338]

CLEAR package is optimized and you will not have significant improvements using custom logic badi. If you have HANA - it's another story...

By the way, why it's impossible to use replace and clear option during data load?

[sap_user62]

Thanks Vadim for this wonderful work around.

I have tested this script in sandbox and it completes in around 4 minutes- a significant decrease in run time from the standard clear package.

Is there any known issues that i need to look for before I implement this change in production.

( I will have test this if there are any SP upgrades)

[former_member186338]

No issues at all! Test it in production. And please accept the correct answer.

[sap_user62]

Vadim, do you have any useful links for Hana SQL operations on a BPC Model.

I see most of the information available, talks about SQL statements on a table.

[sap_user62]

Thanks Vadim. I will google for more information on HANA SQL. I have not used it in our system.

But this seems to be a good option.

Our source does not send zeroed out data, if an intersection is cleared we do not get a record as zero. Overwrite records with match key, might not clear out the old data. (it was the design decision made some time back)

[former_member186338]

With HANA you can use HANA SQL to clear data...

I am talking about:

"Overwrite records with match key","Replace data in same data region of Entity, Category, Time and Audit ID"

[former_member186338]

P.S. for sure "Replace data in same data region of Entity, Category, Time and Audit ID" is not 100% safe but it depends on your data!

[sap_user62]

Thanks Vadim for your reply.

1. We are on Hana. Is there another option that I can use.

2. when you say replace and clear - is it the package "Load transaction data from BW infoprovider UI" - "replace data in the same data region of Entity Category Time and Audit trail"? -

Data from non-sap system is loaded to BW and BPC pulls in the data every load.