Skip to Content

Let’s have a contest!

One of my last blog posts started off a discussion (e.g. Bala posted his thought in a blog) about the importance of the partitioning feature for a SAP BW installation.

No doubt about it – partitioning is something we use heavily for SAP BW.
Breaking the work down into smaller pieces that are easier to handle is one of the great common problem solving heuristics and it really works wonders in many cases.

Still, partitioning is something that needs to be done right and unfortunately also something that can be done wrong.

Beware, it moves!

What many DBAs and SAP Basis admins don’t seem to fully get is that partitioning is not a static thing. Of course, the handling of partitions is very much like dealing with rather fixed stuff as tables and indexes. For the later objects you usally set them up, use them and leave them alone for the rest of the lifetime. Sometimes a reorg may be required, but this pretty much is it.

Partitioned objects on the other hand are usually way more ‘vivid’ (dynamic, volatile, changing… hard to find a good matching word for this).
These objects change with the data you store in them.
And this data changes over time.
So your partitioned table from yesterday will be a different one than the one of today.

In SAP BW we use partitioning for InfoCubes in two ways:
1. the F-fact tables are partitioned by request.
Every new request that gets loading into the InfoCube is stored in its own partition.
That way, we can easily remove requests e.g. if the data is not correct or during compression/condension.

2. the E-fact table CAN by partitioned by a time-InfoObject.
With that, we can improve query and archiving performance, when these actions are based on a time dimension-InfoObject (which is most often the case).

So far so good.

The problem now is, that the first kind of partitioning is done fully automatic.
Whenever a new request is loaded into an InfoCube, the F-fact table gets a new partition and the data is stored in it.
What doesn’t happen fully automatic is that the partitions are removed again.

To remove the partitions from the F-fact table the corresponding request (and all requests that have been loaded before that) needs to be compressed or better condensed into the E-fact table.
Basically this operation does nothing else then adding up the numbers from the F-fact table partition to the E-fact table, stores the result in the E-fact table and then drops the partition from the F-fact table.

Of course, now you cannot remove the data anymore based on the loading request, since it has been summed together with the other data in the E-fact table. On the other hand, now this addition doesn’t need to be performed at query runtime anymore and the database can use the partitioning scheme of the E-fact table for a more efficient execution plan.

Our performance is good – so what’s the hassle about?

Besides performance issues, having many partitions can lead to multiple problems:

  • usually aggregate tables tend to have even more partitions than their basic cubes (for technical reasons), so there is a multiplication effect
  • DDL statements that are generated for the F-fact tables can become too large for export/import/migrations or reorganisations on DB level.
  • Index creation can become very slow for so many partitions, since all indexes on F-fact tables are also locally partitioned, again a multiplication factor.
  • during attribute change runs a high number of partitions can lead to crahes as explained in notes
    #1388570 – BW Change Run
    #903886 – Hierarchy and attribute change run
  • It may even happen, that it’s not even possible anymore to perform change runs or compression of requests, if there are too many partitions!

For all these reasons there’s a recommendation out for a long time now:

COMPRESS! COMPRESS! COMPRESS!

Note #590370 – Too many uncompressed request (f table partitions)

I really don’t know how many support messages have already been closed by simply compressing the requests.
And because of that and because it’s so simple to figure out whether or not there are F-fact tables with too many partitions (usually not more than 20 – 30 are recommended) I decided to start a little competition here.

Just run the following little SELECT command on your BW database to get a list of F-fact tables that have more than 50 partitions:

\

select table_name, substr(table_name, 7) infocube_name, partition_count
\
from user_part_tables
\
where table_name like '/BI_/F%'
\
and partition_count >50
\
order by partition_count desc;
\

\
-----------------------------------------------
\
|TABLE_NAME     |INFOCUBE_NAME|PARTITION_COUNT|
\
-----------------------------------------------
\
|/BIC/FZ123456  |Z123456      |         8.279 |  <<< come on, beat this :-)
\
|/BIC/F123456784|123456784    |           999 |
\
|/BIC/FTPEDBIF5X|TPEDBIF5X    |           636 |
\
|/BI0/F0RKG_IC3 |0RKG_IC3     |           375 |
\
|/BIC/F100197   |100197       |           334 |
\
|/BIC/FRSTTREP01|RSTTREP01    |           281 |
\
|/BIC/FRS_C5    |RS_C5        |           253 |
\
|/BIC/F100184   |100184       |           238 |
\
|/BIC/F100183   |100183       |           238 |
\
[...]
\
-----------------------------------------------
\

\

\

(be aware that this statement obviously does only work for InfoCubes tables in the standard name schema /BIC/, /BI0/, /BI… – you can of course adapt it to your naming scheme).

If you like to, just post your TOP partition count into the comments section – would be interesting to see, what extreme examples come up…

Although there’s no price to win, you might at least get awareness that there is something to keep an eye onto in your BW database.

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Martin Maruskin
    Thanks Lars for stressing out (one more time) an importance of doing regular compression.
    I’ve seen costumers having couple of thousands requests in their cubes. This is not rare experience.
    Regards, Martin
    (0) 
    1. Lars Breddemann Post author
      Hi Martin,

      this blog really was written out of a kind of frustration about support messages that come in each and every day.
      And very often the problems described in these messages were so easy to avoid, simply by following the recommendations for BW system operations, where regular request compression is just of amongst others.

      By the way: I do read and like your blog as well ๐Ÿ™‚
      Keep that up (and maybe post a thing a two here in SDN as well).

      Cheers,
      Lars

      (0) 
  2. Bala Prabahar
    Hi Lars,

    No, I give up. I’ve seen between 1000 and 2000; certainly not 8,279. EW report reports anything greater than 50 (I believe). Compression is very, very critical. No doubt.

    Recently I had the honor of speaking about partitioning in SAP Inside Track Chicago event. I focused on how to partition tables in any system R/3 or BW or CRM or SRM. I also mentioned partitioning would help archiving. One participant-archiving specialist-came to me and said he was not aware of the benefits of partitioning to archiving; he mentioned his customer spends 3+ weeks deleting records. He told me he was going to suggest partitioning to speed up archiving(drop partition instead of deleting records). As you implied, partitioning is not something you can just turn on. It requires lot of work, skills, time to get it right. I hope customers would understand the benefits of partitioning and implement it more widely.

    Thanks,
    Bala

    (0) 
    1. Lars Breddemann Post author
      Hi Bala,

      thanks for the feedback and congrats for your speaking gig!
      Yes, to get partitioning right is not easy and it’s even more difficult when you move outside the clear/simple data design of BW and move to the rather very specific application data designs.

      One approach SAP has done to tackle this is the so called “Partitioning Engine” which allows semi-automatic partitioning for defined use cases in the OLTP world.
      Implementing it can really make a big difference, e.g. if you’re a super-large retailer and want to get out the closed positions of the last quarter..

      Cheers,
      Lars

      (0) 
      1. Praveen Reddy

        Lars,

        I have a question. I am loading data from 4 datasources to cube out of which 2 are delta loads and 2 are daily full loads. If i compress the cube I cannot delete the requests… is it? how can I avoid this.

        (0) 
        1. Lars Breddemann Post author

          Hello Praveen,

          obviously, once a request had been compressed in an Infocube there is no way to get the data that belonged to the request out again.

          When we compress/condense a request in an Infocube, we through away the request -id information and sum up the key figures for all dim-ID-combinations.

          So from a request management perspective a condensation can’t be undone.

          To remove the data from the Infocube nevertheless, you might consider using selective deletion (although this doesn’t let you select for a specific request).

          regards,

          Lars    

          (0) 

Leave a Reply