[Few weeks ago]
In our team I am responsible as well for BW Statistics on customer’s landscape. They are loaded weekly (on Sundays) and all my duty is just to check if loads went Ok last Sunday. It is just really marginal fraction of my time. But still it happened quite regularly that one load (0BWTC_C02 – BW Statistics – OLAP) was failing. I had no really time to do any deep investigations, so the only thing I did that time is rescheduled this particular load to happen after 5 others (0BWTC_C04, 0BWTC_C05, 0BWTC_C09, 0BWTC_C11, 0BWTC_C03).
This helped (no more regular failures), but…
[2007-01-14]
…the load still takes very long time. This surprised me, and I decided to finally llok into it and learn something from it.
Here are statistics for the loads:
| Cube | Records | Load time |
| 0BWTC_C02 | 343067 | 3h 6m 52s |
| 0BWTC_C03 | 729661 | 29m 1s |
Load that is 2 times smaller in number of records takes 6 times more time! Something is wrong, and I decided to look into this and learn something.
[2007-01-21]
My basic assumption was that it is extractor that takes so long and that the problem then might be in its logic or in tables (or their indexes) where data is taken from.
So I went to Details tab of the last load to check how long it takes to extract the data. To my big surprise I found that there are only 5 minutes between Data Selection
and return of first Data Package
So it should not be problem with extracting the data, but somewhere in the next steps. And indeed I found that the longest step is Update (writing to the cube tables),
which took 43 minutes for one Data Package.
So I will need to figure out the reason (indexes, cube tables, buffering etc?) and resolution to slow updates in the next episode. For the moment, I just increased a bit DataPackage max kB size to check what this will do.
First of all, congratulations for your idea: writing a weblog starting from a real case is a good approach to talk in an effective way about maintenance issues and dayly BW events…
Then, my first thought is: who is the murderer (of the performance of your dataload)?
Sorry for this commonplace, but in this thriller my first suspect is about the house steward (!) outdated cube statistics (and indexes), but, to check that, it’s enough to verify its alibi (“do you remember the last time you run something to improve performance of your cube?”)…
Anyway, I will see the sequel in the next episodes!
About your data monitor analysis and your question about monitor system info, you can look at RSMON* tables…
I would prefer mainly RSMONMESS table: here you can find (especially in AUFRUFER field and clearly in Timestamp too, besides other available precious info…) what you need, even if you have to build an ABAP pgm to link a couple of tables and to have a meaningful output (or do you want to access directly on it via SE16?).
Clearly you already know that all information is available on 0BWTC_C05 cube in 0TCTWHMACT object, but if you need a different approach…
Bye,
Roberto
I also like Roberto’s murder mystery analogy – Perhaps the killer will be Colonel Oracle in the tablespace with an index.
By way of comparison – we load our BW Tech Content on a daily basis:
0BWTC_C03 – 39,428 records loaded in 1:34(mm:ss)
vs
0BWTC_C02 – 6,595 records loaded in 3:50(mm:ss)
While not as long as your times, it does seem as if my C02 load is also a good bit longer relative to the C03 loads. So I’ll be interested to see what you find.