BI_STAT job

[Former Member]

Hi experts,

when I use infopackage loading data, I notice that there is BI_STAT* job running after data loading is finished (traffic light is green).

this job is re-build index and statistic but it last so much time. meanwhile, other infopackage which try to upload the same cube will show error.

1. I check the SAP LOCK conception, it's not saying we can upload data when index & statistic is builting.

2. anything can reduce the running time of BI_STAT job

Thanks in advance

[Former Member · Accepted solution]

Hi Jie,

I guess it refreshes the statistics for all the data in the cube.

For changing the value contact somebody who has access to change the values OR contact your BASIS.

I guess in process chain you can specify the percentage so try there.

Bye

Dinesh

[Former Member · Accepted solution]

Hi Jie,

in PC maintenance (RSPC) double click on the dbstat process than you can change the 100 % to 10 % , save and activate

/manfred

[Former Member · Accepted solution]

Couple of points -

When the cube is set to drop and rebuild indices, yes it is rebuilding them for all the secondary indices of the cube, not jsut a Request. This can take a while for a very large cube and there is an OSS Note that talks about an RSADMIN setting that allows you to control this behaviour a little, but I suspect your delay has more to do with the Statistics more so than the index rebuild.

When the statistics are collected, it is collecting them not just on the fact and dimension tables, but looks at all the master data tables as well.

Before talking about statistics collection options further, lets talk about the issue of the second set of loads failing becasue the index/statistics job running. The failure is probably related to one or more of the indices not being finshed rebuilding rather than statistics collection, since statistics collection should not prevent a load from occuring <b>The only real solution to this is to use a Process Chain</b> where you can drop the indices prior to the first load, run 2nd, and 3rd loads etc., then when all loads are done, rebuild the indices.

You could set it up to not drop the indices, which will slow the load process, but then you would want to periodically rebuild the indices witha a batch job, perhaps weekly. You don't identify the DB you are using, and a lot to do with index and statistics collection is DB dependent.

<b><u>Statistics</u></b>

There are some options to collect statistics in nightly batch jobs using BRCONNECT that you should talk to your DBA about. The statistics for all the cubes would be colleced during a batch job rather than associated with each cubes load process.

SAP recommends 100% (compute) statistics collection on cubes with less than 10 million rows in the fact table, so I wouldn't rush out change this to sample at a lower rate, and generally, would never go below 10% without beng very familiar with the data distribution or you could end up with the optimizer making poor query execution plan decisions resulting in slower queries.

A related issue - make sure you compress the InfoCubes regularly.

[Former Member · Accepted solution]

Thanks Pizzaman,

we are going to use process chain to any data loading.

then build statistic in midnight of each of cube.

[Former Member]

Hi Jie,

BI_STAT* job is for rebuilding statistics for the cube. Make sure the cube is loaded and only then the index and stats are rebuilt. Another way to reduce the time is to use lesser percentage of cube data for stats. SAP suggests 10%.

Bye

Dinesh

[Former Member]

Hi,Dinesh,

how to change statistic percentage setting? I can not directly change in "performance" tab of cube management.

and for rebuilding statistics and index, I thing BW just rebuilding them for single request ( 10 thousands recodes), why it take so long time for large cube( almost half hour, but small cube is fast ), is means that BW will delete and rebuild all of index and statics of all of record when one request is coming?