Skip to Content

In this blog series I would like to share how to realize central definition and maintenance of partitioning patterns. The standard Semantically Partitioned Object (SPO) functionality of SAP NetWeaver BW 7.3 is enhanced by implementing Business Add-in (BAdI) RSLPO_BADI_PARTITIONING and few control tables. It facilitates an automated (re)partitioning, either individually or collectively, and enables partitions, criteria, texts and Data Transfer Processes (DTPs) to be generated automatically and consistently.

The blog series consists of the following blogs in addition to this blog:

I developed a working version of the BAdI implementation which I would like to share via two documents. Implementing Pattern-based Partitioning using the SPO BAdI – Part 1: Control Tables contains all technical details of creating the control tables and the related ABAP data dictionary objects. Implementing Pattern-based Partitioning using the SPO BAdI – Part 2: BAdI Implementation explains all technical details of implementing BAdI RSLPO_BADI_PARTITIONING and the necessary ABAP Object Oriented programming in the implementing class.

Authorized Partitioning Patterns

SAP BW Layered, Scalable Architecture (LSA – SAP’s best practice in Enterprise Data Warehousing) propagates a central definition of modeling patterns. In the context of logical partitioning, it would make sense to introduce authorized partitioning patterns to enable a central definition and maintenance of partitioning patterns. This leads to a uniform and consistent modeling throughout the entire LSA Enterprise Data Warehouse. It also offers an increased transparency of partitioning with criteria, texts and DTPs implemented by different people in the various project teams.

We can define and maintain these partition patterns in central tables. It will reduce the risk of input errors, multiple ways-of-working, different interpretations, duplicate and possibly inconsistent values, etc. The SPOs can reuse the partition patterns and the BAdI will take care of the generation of partitions by combining the partition patterns. The texts and DTPs are also in scope of the automatic generation procedure.

In the paragraph How to Use the Control Tables you will learn more which control tables are implemented and the way they are intended to be used.

Maximum Number of Partitions

Let’s have a look at the maximum number of partitions. A maximum of 1,295 partitions can be created. The maximum of 99 partitions was increased in SAP NetWeaver BW 7.3 SP4. Please refer also to the release notes and SAP Note 1590179.

The Partition ID field has data type Character (CHAR) and is 2 positions long. Before this support package, only digits were allowed where partition ID ‘00’ was reserved for the master partition. This resulted in a maximum of 99 partitions.

Currently, also the 26 uppercase letters are allowed. This yields a lot more combinations: 36 x 36 = 1,296. However, partition ID ‘00’ is still reserved for the master partition, so the maximum becomes 1,295 partitions.

Although it is technically possible to create up to 1,295 partitions, it’s wise to consider a few things:

  • What is the estimated data volume per year and how many years of history are required;
  • What is the underlying database;
  • Is In-Memory technology used (i.e. BW Accelerator or HANA);
  • What are the Information Lifecycle Management requirements (data archiving and near-line storage).

One should avoid creating too small partitions. Each partition should hold a reasonable volume of data. How much is dependent on the underlying database. Databases such as Oracle and IBM DB2 are designed to handle huge volumes of data, e.g. because of table partitioning or clustering technology. IBM DB2 even offers Database Partitioning Feature (DPF), a technology to assign tablespaces to multiple database partitions which can be spread across multiple database nodes.

Also using In-Memory technology should be taken into consideration. Such technologies offer out-of-the-box partitioning features, both hardware-based and software-based.

Last but not least, Information Lifecycle Management plays a role in determining the optimal configuration of logical partitions. It might be convenient to use partitioning in conjunction with data archiving and near-line storage.

The maximum number of partitions of the Pattern-based Partitioning solution is intentionally limited to 99 partitions. This will be sufficient under normal circumstances. However, it is technically possible to increase the number of partitions.

How to Use the Control Tables

The BAdI automated scenario should be implemented using control tables. You can find below an introduction on how to use the control tables. There are in total 5 control tables required:

  • YSPOINFOPROV – SPO BAdI Managed InfoProvider;
  • YSPOPATTERN – SPO Authorized Partition Pattern;
  • YSPOPATTERNT – SPO Authorized Partition Pattern Text;
  • YSPOCRITERIA – SPO Pattern Partition Criteria;
  • YSPODTP – SPO BAdI Managed DTP.

The first control table YSPOINFOPROV contains the BAdI managed SPO InfoProviders. Here you can store the SPO InfoProviders that have to be managed by the BAdI.

Picture 1: Control Table InfoProviders

Figure 1: Control Table BAdI Managed SPOInfoProvider

As you can see you can define up to 2 partitioning patterns. This concept is explained in the paragraph Authorized Partitioning Patterns.

The next two tables, YSPOPATTERN and YSPOPATTERNT, are necessary for the maintenance of partitioning patterns.

Picture 2: Control Table Partitioning Patterns

Figure 2: Control Table Partitioning Patterns

The text table is integrated with the main table and both can be maintained at the same time. Please take into account that the texts are only maintained in the logon language.

The next table YSPOCRITERIA makes it possible to maintain the partitioning criteria.

Picture 3: Control Table Partitioning Criteria

Figure 3: Control Table Partitioning Criteria

The partitioning group is required to group together multiple single values belonging to the same partition. Furthermore it’s not possible to combine single values and intervals within the same group simultaneously. By the way, this is a standard design limitation of SPO.

The last table YSPODTP contains all DTP settings for the SPO InfoProvider.

Picture 4: Control Table DTPs

Figure 4: Control Table DTPs

Here you can specify which data sources have to be used to extract data and the required DTP Template. It’s is possible to specify more than one data source object for every SPO InfoProvider.

Conclusion

In this blog you learned more about authorized partitioning patterns, the maximum number of partitions and how to use the control tables. The solution provides a way to define and maintain the partitioning patterns (including the partitioning criteria) centrally. For every new SPO InfoProvider you assign one or two partitioning patterns. The BAdI is able to combine the partitioning patterns and will generate all configuration automatically and consistently.

In the last blog Pattern-based Partitioning using the SPO BAdI – Part 4: Use Cases I will highlight some use cases of pattern-based partitioning based on LSA data flow templates.

To report this post you need to login first.

23 Comments

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

  1. Jeevan Chilukury

    Thanks Sander. This is very good article.

    We are trying to implement the control tables and automaic genereation of SPOs and we have a question.

    Can this control tables work for when generating DTPs via BADI when source and target are SPOs ?

    We are giving all the details on YSPODTP table & OBJNAME as our source SPO name and the DTP template , template ID but for some reason, its not generating the DTPs using BADI.

    Pls let us know if you have any workarounds.

    Regards,

    Jeevan

    (0) 
    1. Sander van Willigen Post author

      Hi Jeevan,

      First of all thanks for your positive feedback. I would like to react on your question.

      Let me give a practical example with the necessary steps:

      • Source SPO DSO which is semantically partitioned on Characteristic XDOMAIN
      • Target SPO InfoCube which is semantically partitioned on Characteristics XDOMAIN and 0FISCYEAR

      Step 1: Add Characteristic 0FISCYEAR as key field in the Source SPO DSO

      This seems a redundant action but I noticed that it helps to make the DTP generation in the Target SPO InfoCube run smoothly (moreover, the DTP Filter can be generated automatically). I also think that it will have a positive side-effect on the extraction performance since the DTP can use another key field for the extraction (e.g. XDOMAIN = ‘A’ and 0FISCYEAR = ‘2013’).

      Step 2: Maintain table YSPODTP

      The following record has to be entered:

      • SPONAME = Target SPO InfoCube
      • OBJTYPE = LPOA
      • OBJNAME = Source SPO DSO
      • DTPTEMPLATE = an appropriate DTP Template *)
      • DTPTEMPLATEID -> will be automatically filled in accordance with selecting field DTPTEMPLATE

      *) Make sure that the settings of the DTP Template correspond to the extraction scenario; most importantly the following settings:

      • Data Source: DataStore Object
      • Extraction Mode: most likely Delta
      • DTP Filter: Generate Automatically

      Step 3: Create Transformation within the Target SPO InfoCube

      Make sure that the source of the transformation is the Outbound InfoSource of the Source SPO DSO (i.e. Object Type ‘TRCS’ along with an InfoSource with suffix ‘_O’, e.g. DPFI00_O).

      Step 4: Generate the DTPs within the Target SPO InfoCube

      You should be able to generate the DTPs and their respective filters automatically using the presented approach.

      Please let me know if it works and do not hesitate to contact me for further help.

      Best regards,

      Sander

      (0) 
      1. Jeevan Chilukury

        Thanks a lot for the response Sander.

        I did exactly what you explained in the above response and it seems to not work when i mention SPO name as explained below.

        For eg: my source SPO name id ZSPO01 and my target SPO is ZSPO02.
        ZSPO01 has 10 partitons based on cal month and target SPO also has 10 partitions and my idea is to keep always 3-4 yrs worth of data and remove data as well partitons which are not needed and maintain the control tables in such a way i always have the data for last 3-4 yrs.

        Case 1:

        •SPONAME = ZSPO02 (10 partitions) – Target
        •OBJTYPE = LPOA
        •OBJNAME = ZSPO01 (10 partitons) – Source

        DTP templates are filled with F4 appropriately.

        This does not generate DTPs automatically.

        Case 2:

        •SPONAME = ZSPO02 (10 partitions) – Target
        •OBJTYPE = ODSO
        •OBJNAME = ZSPO01 (10 partitons)v- Source

        This does not generate DTPs automatically.

        Case 3:

        •SPONAME = ZSPO02 (10 partitions) – Target
        •OBJTYPE = ODSO
        •OBJNAME = ZSPO0101 (individual partitions) – Source

        that means, I need to maintain 10 table entries for each partition like below:

        •SPONAME = ZSPO02 (10 partitions) – Target
        •OBJTYPE = ODSO
        •OBJNAME = ZSPO0102 (individual partitions) – Source

        •SPONAME = ZSPO02 (10 partitions) – Target
        •OBJTYPE = ODSO
        •OBJNAME = ZSPO0103 (individual partitions) – Source       —- etc

        The problem with case 3 is : it generates 10*10 = 100 DTPs.

        It does not generate DTPs only to corresponding target partitions from source. for each partiton on target,it generates 10 dtps, in total 100 dtps for all 10 target partitions.

        In reality, we only need 10 dtps one each for each target partition and we need maintain those 10 entries on YSPODTP. Is this the correct method to proceed ?

        Your inputs appreciable.

        Regards,
        Jeevan

        (0) 
        1. Sander van Willigen Post author

          Hi Jeevan,

          Case 1 should work, assuming that all control tables (next to YSPODTP also YSPOINFOPROV, YSPOPATTERN and YSPOCRITERIA) are filled correctly and consistently.

          Sometimes I experienced that the BAdI is not triggered directly. This is in my opinion not a problem of the BAdI implementation but rather an inappropriate behavior of the GUI. The best is then to leave the SPO maintenance screen, start up again t/code RSA1 and change the SPO.

          The functionality of Pattern-based Partitioning includes an efficient generation of DTPs, taking the respective partioning criteria of source and target partitions into account. I.e. the BAdI won’t generate DTPs which will never load any data. In your example: the BAdI will generate 10 partitions (e.g. 01/2012 -> 01/2012, 02/2012 -> 02/2012, etc.).

          Best regards,

          Sander

          (0) 
      2. Jeevan Chilukury

        Sander,

        Forgot to ask, it those two SPOs i mentioned are already in production and which we did it manually earlier not using BADI.

        Can we still adjust the manually generated SPOs using control tables from now on ? what are the impacts ?

        regards,

        Jeevan

        (0) 
        1. Sander van Willigen Post author

          Hi Jeevan,

          To be sure I checked all RSLPO* tables which I know and I cannot find any field related to the BAdI. In others words the BAdI only facilitates during the generation of the partitions and DTPs. This means that basically it should be possible to work with the BAdI retrospectively, although I don’t recommend this approach.

          I performed a test to simulate your case and I was able to make it work. I started with creating a new SPO w/o help of the BAdI. Then I filled the control tables retrospectively (i.e. YSPOINFOPROV, YSPOPATTERN, YSPOCRITERIA and YSPODTP). You should really make sure that the partitioning patterns exactly match the manually created partitions. As the last step, I was able to generate the DTPs with help of the BAdI.

          So my conclusion is that technically it is possible to make it work. However, I don’t recommend this approach and you should be very careful in order to not loose any data in the existing partitions.

          Best regards,

          Sander

          (0) 
          1. Jeevan Chilukury

            Thanks a lot Sander for all the details and we are trying to completely automate using BADI’s.

            Case 1 did work for me when the source SPO pattern (based on cal month & same no of partitions) and target SPO pattern(based on cal month & same no of partitions) both same.

            But when i tried to put the pattern for source being calmonth and for the target as cal quarter. It does not generate the DTPs. Do we need to do anything other checks on the any of the control tables to make it work when source and target have different patterns(both being SPO’s).

            Thanks a lot for the comments on SPO’s being in production already and possibility of losing the data if proper care is not taken care.

            Also, another question on the same lines, if the SPO’s are already created manually and not in production and about to goto production. As we are trying to automate, down the line 2-3 yrs, we do not want any manual activity to auto generate ( drop and recreate partitions automatically using control tables), what is your recommendation for the manually created SPOs which are not in production ?

            Use the BADI now and regenerate all partiotions using control tables ?

            Regards,

            Jeevan

            (0) 
            1. Sander van Willigen Post author

              Hi Jeevan,

              The current BAdI implementation does not support the constellation with different time characteristics in Source SPO (0CALMONTH) and Target SPO (0CALQUARTER). In method GENERATE_DTPS scenario 2 (data source is an SPO) will be processed. In step 1 the program tries to determine matching partitioning characteristics. Here it will fail. I would have to investigate further how the program will react but I expect that it will lead to an inefficient generation of SPOs (e.g. a DTP from calendar month 12.2012 to calendar quarter 1.2011, i.e. a combination that will never lead to any data transfer).

              One remark I would like to repeat from my response of September 6 under step 1: you should add partitioning characteristic 0CALQUARTER as key field in the Source SPO DSO. Without that standard SPO has difficulties to generate the DTPs automatically. However in this case, it will still lead to an inefficient generation of DTPs as I explained before.

              I can only suggest work-arounds at the moment:

              • Keep the time partitioning characteristic the same (i.e. source 0CALMONTH and target 0CALMONTH; or source 0CALQUARTER and target 0CALQUARTER);
              • Combine SPO with table partitioning (e.g. using 0CALQUARTER as partitioning characteristic in both source and target SPO and combine it with table partitioning using 0CALMONTH);
              • Use in both Source SPO and Target SPO 0CALMONTH as partitioning characteristic. In the Source SPO you maintain partitions of 1 calendar month and in the Target SPO you maintain partitions of 3 calendar months (e.g. low value 01.2013 and high value 03.2013; i.e. an interval which equals Q1 of 2013);
              • Use another constellation of partitioning characteristics avoiding different time characteristics which cannot be related by the SPO BAdI.

              To conclude please be aware of your data volumes. In my opinion you should not create too small SPO partitions which do not hold a reasonable volume of data. It’s better in such cases to choose for a less granular time characteristic (usually 0CALYEAR or 0FISCYEAR) and combine it with table partitioning on 0CALMONTH or 0FISCPER), especially if the time partitioning is combined with another partitioning characteristic (e.g. XDOMAIN for domain partitioning).

              Re. your last question, I recommend for all SPOs which are not yet in production to recreate them according to the pattern-based partitioning concept. This is the most safe way-of-working and relatively easy since they are not yet used in production.

              Best regards,

              Sander

              (0) 
              1. Jeevan Chilukury

                Hi Sander,

                Thanks a lot for all your prompt responses.

                I guess, you might have tested this code for generating more than 10 partiotions and I find an issue in below code and sequence of SPO generation when we want more than 10 partitions:

                Per your articles, “yspopartgroup” is CHAR 30 fields: and we maintain this “yspopartgroup” on our YSPOCRITERIA table to generate no of partitions as desired. The issue I see is , when we have more than 10 partitions, below “sort l_t_partgroup” sorts the partition group numbers on YSPOCRITERIA as:

                Assuming i have 18 partitions:(SORT partgroup sorts like below due to its definition CHAR 30)

                1
                10
                11
                12
                13
                14
                15
                16
                17
                18
                2
                3
                4
                5
                6
                7
                8
                9

                ————————————————-

                After below code in the GENERATE_PARTITION_COUNTERS:

                itab: l_t_genpart consists the data for 18 partitons like below, which is incorrect:

                1  1
                2  10
                3  11
                4  12
                5  13
                6  14
                7  15
                8  16
                9  17
                10  18
                11  2
                12  3
                13  4
                14  5
                15  6
                16  7
                17  8
                18  9

                and due to this l_t_genpart having above values, the sequence of SPO partitions and the texts are all out of sync.

                Below pasted the code from the class:

                GENERATE_PARTITION_COUNTERS:(not all the code, only from that SORT statement).

                sort l_t_partgroup.

                delete adjacent duplicates from l_t_partgroup
                                             comparing all fields.

                * Generate partition counters
                  if not l_t_partgroup[] is initial and
                     not l_patternid1 is initial.
                    loop at l_t_partgroup assigning <partgroup1>
                         where patternid = l_patternid1.
                      clear l_s_genpart.
                      l_partcounter = l_partcounter + 1.
                      if not l_patternid2 is initial.
                        loop at l_t_partgroup assigning <partgroup2>
                             where patternid = l_patternid2.
                          clear l_s_genpart.
                          l_loopcount = l_loopcount + 1.
                          if l_loopcount > 1.
                            l_partcounter = l_partcounter + 1.
                          endif.
                          l_s_genpart-partcount  = l_partcounter.
                          l_s_genpart-partgroup1 = <partgroup1>-partgroup.
                          l_s_genpart-partgroup2 = <partgroup2>-partgroup.
                          append l_s_genpart to l_t_genpart.
                        endloop.
                        clear l_loopcount.
                      else.
                        l_s_genpart-partcount  = l_partcounter.
                        l_s_genpart-partgroup1 = <partgroup1>-partgroup.
                        append l_s_genpart to l_t_genpart.
                      endif.
                    endloop.
                  endif.
                ———————————————————————————-

                I am mentioning here, if it is an issue : If so, we can change the field yspopartgroup to NUMC 2, that way it can generate maximum 99 partitions. or is there any specific reason for keeping yspopartgroup CHAR 30.

                Appreciate your  inputs.

                (0) 
    1. Sander van Willigen Post author

      Hi Edward,

      The part providers of any SPO-based InfoCube or DSO can be connected to NLS like any other InfoCube or DSO. You will have to use the same DAP (Data Archiving Process) for it.

      Please have a look at SAP Help for some special features in the context of SPO.

      In my opinion the combination of SPO and NLS is especially very strong if a time characteristic like Fiscal Year or Calendar Year is used as one of the partitioning characteristics. It allows you to keep your historical yearly partitions available for reporting using NLS so without burdening your online database. This is of course also very interesting in the context of SAP HANA in-memory database management.

      Best regards,

      Sander

      (0) 
  2. Mr. V

    Sander,

    In the recent past I was working on this SPO stuff, and your blog is the base for all work I’ve done on SPO. This is informative and more useful information on SPO that I found in SCN. Thanks.

    (0) 
  3. Krishna Kishore Chivukula

    Hi Sander,

    Thanks for the info and this was very helpful. I am currently working on implementing this stuff. Just a quick question, excuse me if this was stupid.

    After implementing BADI maintaining and the control tables, If I delete the an entry in one of the partition control table, will this also delete the SPO and its data ?

    -KK

    (0) 
    1. Sander van Willigen Post author

      Hi Kishna,

      First of all, thanks for your positive feedback.

      Re. your question, the control tables certainly have an impact on your BAdI managed SPOs.

      E.g. you maintain the SPO Criteria control table. You remove the oldest year and add a new year. SPO will generate a new Part Provider for the new year but will drop the Part Provider for the oldest year. This is done during activation in individual maintenance or mass maintenance.

      E.g. you remove a partitioning pattern for a BAdI-managed SPO InfoProvider in the SPO InfoProvider control table. This will lead to totally different Part Providers. It cannot do anything else than dropping the current Part Providers and creating new ones.

      Pattern-based Partitioning is very powerful and it’s advisable to maintain those control tables centrally in order to avoid any mistakes, especially in combination with the mass maintenance functionality.

      Best regards,

      Sander

      (0) 
  4. Christian Sass

    Hi Sander. This is a great how to! I would like to use 3 or 4 different patterns instead of only 2. Could you give me some hints? What do I have to do? Thax

    (0) 
  5. Dietmar Heckmann

    Hello Sander,

    enjoyed your very helpful blog, thanks!

    I am unable, though, to find any useful information about scheduling the process chains. Say, if we have multiple (lots of!) partitions, do we really have to run all of the DTPs in our daily update chain? Even if most of them do not load any data, the chain would run very very long! Is there any suggestion how to deal with that problem?

    See this unanswered blog as additional information:

    Creation of Decision in Process Chain to load data to a SPO

    Thank you,

    best regars

    Dietmar

    (0) 
    1. Sander van Willigen Post author

      Hi Dietmar,

      Thanks for your nice feedback. Let me try to answer your question.

      First of all, you should not “over-partition”, i.e. every PartProvider should contain a reasonable data volume. It depends on your database but be sure that modern databases can deal with a lot of data and even have other features to improve performance (e.g. table partitioning).

      To make it a scalable solution over time, it is very often wise to use a Time Characteristic for partitioning. I recommend Calendar Year or Fiscal Year. It does not matter how long your data retention period is, every PartProvider will start at zero in the new year. Furthermore, it is very likely that the older years will become static data, i.e. do not change anymore. It means that it does not make a lot of sense to keep on loading them every day. It is even recommended to move such static data to Near-line Storage (NLS).

      I advise you to include only those DTPs in your Process Chain which actually load data. Again in conjunction with Calendar or Fiscal Year, you only need the current year. At year end closing you probably run both old and new year. After year end closing you only need the current year. Again at the end of the year you will include the new year, and so on. You have to plan such activities in your maintenance calendar.

      Best regards,

      Sander

      (0) 
      1. Dietmar Heckmann

        Hello Sander,

        thank you for that input. As a rule of thumb, I try to put not more than 1,5 Million records in a (Write Optimized) DSO or Cube to keep laoding times and index build times short. Whenever possible, I would partition by 0FISCPER, as this gives me exactly that number of records in my case. As of now, I have 450 million records in ONE DSO and subsequently uploaded to ONE cube. A test scenario partitioned as described performed very well, though! Can’t wait to put that into production! For loading processes within the process chain I will take your advise into account. Unfortunately the SPO generating process in combination with Open Hub Structures is very buggy, so we have to go to 7.40 SP4 to find these bugs corrected. The automatic generation of DTPs is also not covered by the report RSLPO_MASS_ACT yet, as I realized. But overall, I am glad SAP implemented the SPO feature I waited a very long time for!

        Best wishes!

        Dietmar

        (0) 
        1. Sander van Willigen Post author

          Hi Dietmar,

          Thanks for sharing details of your implementation.

          However, I have to repeat that I strongly recommend to limit the total number of SPO PartProviders by partitioning it by Fiscal Year and combine that with database partitioning on Fiscal Year/Period. This way you will get PartProviders of approx. 18 million records. Please be aware that modern databases can handle large data volumes (like 100 million records) easily. Some databases need compression (e.g. Oracle) to take advantage of the database partitioning.

          The reason behind a limited number of PartProviders is reporting on it. If you use a MultiProvider, then as a rule of thumb you should not exceed 10 PartProviders from which simultaneously data is extracted. Databases have their own mechanisms for parallel queries and partition pruning by the Cost-based Optimizer. In addition also the OLAP processor of BW will apply parallel query mechanisms to accelerate the data retrieval.

          Therefore a constellation with too many PartProviders can work contra-productive in my opinion.

          If you experience problems in DSO activation, then you should consider to switch off SID generation and increase the number of parallel processes for activation. This can significantly reduce the activation time.

          Best regards,

          Sander

          (0) 

Leave a Reply