Cube compression and DB Statistics

[Former Member]

Hi,

I am going to run Cube compressions on a number of my cubes and was wondering few facts about DB Statistics. Like:

1) How does the % of Info Cube space used for DB stats helps. I know that the more % we use the bigger is the stat and faster is the access but stats run longer. But would increasing the default value of 10% make any difference or overall performance improvements.

2) I will compress the cubes on a weekly basis and most of them will have around one request per day so will probably compress 7 requests for each cube. So it is advisable to run stats also on a weekly basis or can it be run on bi-weekly or monthly basis? and what factors does it depend on?

Thanks. I think we can have a good discussion on these apart from points.

[Former Member]

Hi,

1. The larger the InfoCube is, the smaller the percentage rate you should select, as the system expenditure for compiling statisics increases with the increase in size.

2. Yes you can compile the statistics once a week after compressing the requests. The sequence would be Compress -> rebuild indexes -> Refresh the stats.

Hope this helps.

Bye

Dinesh

[Former Member]

What DB are we talking about?

Oracle provides so many options on when and how to collect statistics, even allowing Oracle itself to make the decisions.

At any rate - no point in collecting statistics more than weekly if you are only going to compress weekly. Is your polan to compress all the requests when you run, or are you going to leave the most recent Reqs uncompressed in case you need to back out a Req for some reason. We compress weekly, but only Reqs that are more 14 days old so we can back out a Req if there is a data issue.

As far as sampling percent, 10% is good, and I definitely would not go below 5% on very large tables. My experience has been that sampling at less than 5% results in useful indices don't get selected. I have never seen a recommendation below 5% in any data warehouse info I have seen.

Are you running the statistics on the InfoCube by using the performance table option or process chain? I can not speak to the process chain statistics aproach, but I imagine it is similar, but I know when you run the statistics collection from performance tab, it not only collects the stats on the fact and dimension tables, but it also gos after all the master data tables for every InfoObject in the cube. That can cause some long run times.

[Former Member]

In 3.1 SP22 - OSS Note <a href="https://websmp201.sap-ag.de/~form/sapnet?_FRAME=CONTAINER&_OBJECT=012003146900000348972005">862176</a> indicates that statistics collection on all dependent tables for a cube from the performance tab or as a result of automation settings should <b>NOT</b> be ocurring - just collection on F fact and dimension tables.

This note provides a fix. This should speed up statistics collection if you use these methods.