Partitioning in BW
Partitioning in SAP BW 7.3
BW, by its virtue has to handle a huge amount of data. Over a period, the data is bound to increase and in most of the case, this data can’t be deleted, because, they play a very important role in the decision making of a company.
From the perspective of a BW consultant/developer, it is important that the data is kept organized and made available for reporting without much delay. The role of the data partitioning comes to fore here.Partitioning is an activity that happens at the database level (logical partitioning is not discussed here) and with 7.3; this comes with a gamut of features on the BW side. Here, we’ll look at the partitioning of various BW objects such as DSO, Infocube and Semantically partitioned objects (SPO).
Features of Partitioning:
- 1. This feature is dependent on the database and is not supported by all the databases available in the market. ORACLE, Informix, IBM DB2/390 (this has range partitioning) and IBM DB2 (LUW) (Hash partitioning) support partitioning as of now.
- 2. The database table is actually divided into smaller parts when the partitioning is carried out.
- 3. The partitions on the database actually remain transparent to the user.
- 4. Partitions enable the parallel processing of the queries. The partitions are accessed simultaneously during the query processing and then the result is consolidated to provide the final output.
In SAP BW, automatic partition of the F-table of the infocube, PSA table and change log table of the DSO takes place based on the request.This has also been taken up towards the end of the document.
With 7.3, the DSO can also be partitioned. However, following points should be noted:
- 1. DSO with data cannot be partitioned.
- 2. The partition can be created only if a time characteristic is present as the key of the DSO.
The DB partitioning option can be accessed from the EXTRAS->DB Performance->DB Partitioning as shown below:
Once you select this option, you’ll get the following window showing the time characteristics.
After selecting the required time characteristic and clicking ok, you get the following screen asking to enter the time interval.
Creation of Partitions:
Let us suppose that the time characteristic used is 0Calmonth and the range of month given is 01.2013 to 12.2013.
Total number of months = 12.
Therefore, number of partitions = 12+2 = 14.
Two extra partitions are created to accommodate the data which is less than the time selection given and the data which lies beyond the given time selection.
The partitioning in the infocube also is done exactly in the same way as the DSO.
Semantically partitioned objects:
With the Launch of the BW 7.3, the new concept of SPO has been launched and this has added a host of features taking the partitioning capability of BW to another level. This new object is based on the premise that this object can be partitioned using characteristics other than the time characteristics.
1. SPO based on DSO:
A. the following is the maintenance screen for the Semantically partitioned DSO:
B. Clicking on “ MAINTAIN PARTITIONS” opens up the following window:
Kindly note that only the key fields of the DSO are available for partitioning.
C. We select the billing document number as the portioning criteria:
D. Clicking on OK leads to the following screen:
Here the various parameters can be edited.
E. Once a partition has been created and the object activated, this is reflected under the “components” tab to show the data flow:
F. The next step is to create transformation, clicking on the create transformation button, throws the following screen:
G. Click on OK to further come to this screen and continue creating transformation:
H. When you click on the create DTP, you get the following screen, this can be used to create a new template for the DTP or use and existing one.
I. This screen shows the complete data flow of the SPO:
J. Data flow after creating two partitions in the SPO:
2. SPO based on cube:
A. The following is the first screen that you get when you create SPO based on a cube:
B. All the characteristics of the cube are available for the partitioning:
C. The following screen shows two partitions created based on the company code and the 0calmonth.
D. Transformation created for this SPO: