Skip to Content
Witalij Rudnicki
Witalij Rudnicki
January 21, 2007 2 minute read

Tuning of data load on example of BW Statistics – Episode 1 – Analysis

Follow RSS feed
0 Likes 16 Views 3 Comments
This might be a real weblog, i.e. created during several weeks as investigation goes. So let’s start.

[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),
   image
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.

Assigned tags

Related Blog Posts

    Related Questions

      3 Comments
      You must be Logged on to comment or reply to a post.
      • Hi dear!
        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

      • Former Member
        Like Roberto, I too like the real world problem solving approach to this blog.  As with much of BW, the journey is often more interesting than the destination.

        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. 

      • I hate to unleash the spoiler, but the Statistics datamodels in BW 3.x are not well designed! Imagine that. Some of them are missing some helpful time characteristics and more importantly, the dimension design that is delivered can be further optimized to include more line-item dimensions. I would definitely recommend modifying and enhancing the delivered content before going live with it.