Skip to Content

Full Optimitation takes too long in SAP BPC 7.5 MS SP5

Some time ago, our full optimization process used to take about 30 minutes. Nowadays, it takes 1 hour and a half and I am starting to worry about this. We

need to add more companies to the group in the medium term.

We are in a multinational firm, and the times to do the daily full optimization are ver short, since we have users in Spain, USA... where the hourly difference is 9 hours, so if we run the optimization at 6 a.m. for finishing at 8a.m. in Spain, these hours are 9 p.m. to 11 p.m. in USA , and USA users need to get access to SAP BPC from 9 p.m to 11 p.m. in some peak work times during year.

Is there a way of making the full optimization quicker?

I guess it depends on the number of registers in de DB, but it is increasing quickly.

We have not tested the option of partioning the cube, due to in the guide "BPC Performance for BPC 5.x.pdf", it is said "...If the customer modifies one or more dimensions before starting each reportingcycle, this requires a full processing, and in the presence of multiple partitions, it may end up being slower...". In our case, we usually change different members in different dimensions, so this solutions is not proper for us.

Other solution is to create a new appset with old data, leaving only most recent data in the active appset. I don't like very much this solution either because if we want to take data comparing years in different appsets, we must do a log on/log out in BPC excel or do a SQL query using macros in Excel, which is not very useful. Furthermore, you have to maintain 2 appsets (for instance, with the users and security, or if you do a NEWID in one appset, you must do it in the other too if you want to compare data in the future...)

We have also tested changing the database to simple mode and shrinking the transaction log, but the times are very similar (2 hours in full optimization).

Other solution, maybe the best one until now, is doing incremental optimization daily, and once a week, the full optimization. But we would like to find a better solution to reduce the time of full optimization.

Thank you very much in advance.

Best regards,

Fernando

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Apr 24, 2012 at 01:37 PM

    Hi Fernando,

    how many records are in your application?

    How many new records will be added from packages?

    Kind regards

    Roberto

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Roberto,

      Nowadays, we have next configuration of optimization, very similar to your proposal:

      1. Every half an hour, if there is more than 80000 registers in factwb tble, a lite optimize without checks.
      2. Every night, an incremental optimization with defragment index checkbox.
      3. Every week, a full optimize with checks (compress and index).

      I like your solution of moving historical data to a new application instead of a new appset. It is simple solution but I have never thought it. Thank you very much.

      Best regards,

      Fernando

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.