Dirty Aggregate(s)... want to compare InfoCube with Aggregate

[Former Member]

We have InfoCubes with Navigational Attributes.

For performance reasons we have defined several Aggregates (which also includes the Navigational Attributes).

Our Navigational Attributes don't change very much, so in the Attribute Change Run we only process the "delta".

Today we found that a query showed us results that were absolutely wrong, specific on the Navigational Attributes.

With the query debugging tool we found that the problem was an aggregate. We got good results without using aggregates and wrong results with the use of aggregates. And we could determine which specific aggregate was the cause.

Now I can rebuild the aggregate. No problem.

But how can I be sure that only that one aggregate was "dirty" and not some more aggregates?

We don't want to make a monthly round in rebuilding aggregates for system time, logspace and other reasons.

<b>Question:</b>

Are there methods to compare the content of the InfoCube with their Aggregates??

Note: We don't want to discuss the integrity of SAP BW and/of Aggregates. The main cause this situation could arise is probably some bad watching of the loads (specially attribute change runs)...

[former_member188975 · Accepted solution]

Hi Marcel,

You can try comparing the content of the InfoCube with the aggregate in 2 sessions of LISTCUBE. In one, display the contents of the cube (CUBE and cube name)and in the second you can display the contents of the aggregate (object type: AGGR and data target: technical name of the aggregate, like 100023). If you make the same selections it should give you a fair enough comparison.

Hope this helps...

[Former Member · Accepted solution]

Bhanu,

Thanks for your effort to answer my question. I'll give you two points because your info can be helpfull for other readers because of the explanation of LISTCUBE.

We are thinking about an "automatic" comparisation, for example an automatic way to compare an aggregate view on the InfoCube with the same details as the aggregate itself and then a line-for-line comparisation of this view with the actual aggregate. Something like that....

Your manual way isn't workable with many aggregates and the change that some aggregates will slip through is too high.

[Former Member · Accepted solution]

See if you can run this module from SE38

RSDDK_AGGREGATE_CHECK

RSDDK_AGGREGATE_CHECK_SELOPT - a variation on the previous program, allowing you some additional input options.

There are some options and limitations, but basically when these programs are run, they create a temporary aggregate from the base cube and compare it row by row to the selected aggregate, produce a log file, and can write any unmatched rows to a temporary table for further review. They do NOT do any updating to the InfoCube or it’s aggregates. This can be setup to run in background with variants which might be a useful tool to run after any Service Pack or database update to try and catch any aggregate problems upfront.

There are also options to set an aggregate(s) to automatically be validated after loads, rollups, and change runs, I don’t think you would normally want to have that running for each aggregate on a daily basis due to resource consumption. Automatic validation of an aggregate would make sense if you had some sort of problem aggregate that you were always suspicious of whether it was correct, but I think having the ability to run the program on an ad hoc basis may be all you need.

The automatic check options are under Extras from the Aggregate Maintenence screen.

[Former Member · Accepted solution]

Pizzaman,

Great answer. Thanks.

The reports are:

RSDDK_CHECK_AGGREGATE

and

RSDDK_CHECK_AGGREGATE_SELOPT

Following your given direction I've read some additional notes:

646402

584609

537422

You earned the full 10 points!