Any way to identify causes of Optimization failures ???

[Former Member]

Hi guys

We have recently been seeing regular optimization failures (06:45 every morning - Lite optimization).

Unfortunately, the BPC logs only tell me that the job has failed, with no reason as to why it has failed.

This job runs every 2 hours on our system and the 06:45 job is the only one that fails with any regularity.

Is there any way that I can track what has actually gone on with the job, or am I stuck with only the job logs and EventViewer for information?

BTW - This is BPC 5.1 SP5

Thanks

Craig

[Former Member · Accepted solution]

Hi Craig,

How many records do you have in the WB table? More number of records might pose this problem.

[Former Member · Accepted solution]

Hi Nilanjan.

At the time of the failure, I would expect there to be zero records in WB.

The work-force generally finishes no later than about 8pm and lite optimization continues every 2 hours.

At 03:30 each day, a full process occurs (without compression), so I would expect there to be not only nothing in WB, but nothing in the FAC2 table as well.

[Former Member · Accepted solution]

Hi Craig,

Have you checked the number of records in the WB table in SQL?

[Former Member · Accepted solution]

Was there any other package or activity running on the server concurrently at the same time?

[Former Member · Accepted solution]

Hi Karthik

That is basically what I need to know.

There was nothing planned for that time, but my suspicion is that something was running.

I need to know whether the sysem will tell me anywhere what was running.

[Former Member · Accepted solution]

Look for any other scheduled tasks on the server.

If any other activity is conflicting with the BPC tasks, it might be the root cause.

This may be a different problem too.

No data in the fact tables at that time means, the problem is not because of the application data.

Please check for any master data changes. Check if there are any other packages that changes the master data prior to invoking this package.

The job runs successfully before the scheduled time. Please check if you are able to run the package after the scheduled time or 30 mins later.

[Former Member · Accepted solution]

There are definitely no scheduled jobs that clash with this one.

How do I check the "Master data"? (And indeed, what is the master data?)

I am not aware of any other jobs that run overnight on the server, other than this lite optimization every 2 hours and a full optimize.

The results (every day) are as follows:

00:45 - Lite Optimize - Success

02:45 - Lite Optimize - Success

03:30 - Full Optimize (no compression) - Success

04:45 - Lite Optimize - Success

06:45 - Lite Optimize - Failure

08:45 - Lite Optimize - Success

10:45 - Lite Optimize - Success

..... and so on throughout the day

The job succeeds almost every time it is run (with the odd exception of a dimension update which can clash with the 08:45 job if we are changing it at the time).

The only thing that changes before the 06:45 lite optimize is that the dependencies are dropped and recreated on each of the Fact tables:

INS1..., INS2..., INS3..., INS11..., INS21..., INS31..., DMU... for each Application

This is done because of a known issue in v5.1 which drops the dependencies of each application after a full optimize.

As for running the job again 30 minutes later - That works perfectly as well.

I just really need to know whether I have any way of finding out what other job(s) is/are running at the same time that could block the optimization, or whether the rebuilding of the dependencies could cause the issue (although that has been done for months on end without issue until now).

[Former Member · Accepted solution]

Hi Craig,

By Master Data, i meant the dimensions.

So based on what you have posted, something else must have been happening between 04:45 till the end time of 06:45 Lite optimization package execution.

[Former Member · Accepted solution]

This is something that we can't trace. BPC doesn't have a system trace like R3 have in basis area.

hope helps