How to Do Partitioning What is Partitioning? Partitioning splits up the whole dataset for an Info-Cube into several, smaller, physically independent and redundancy-free units. There are two different ways of Partitioning: Physical partitioning: This is done to improve the query performance with the cube and has to done after the cube has been created and before data has been loaded into the cube. Logical Partitioning: Partitioning on the basis of specific criteria and create new independent structure. For eg : one cube will be split into multiple cube on some criteria eg Fiscal year as per business requirement.
|Published (Last):||26 July 2015|
|PDF File Size:||10.73 Mb|
|ePub File Size:||8.55 Mb|
|Price:||Free* [*Free Regsitration Required]|
How to Do Partitioning What is Partitioning? Partitioning splits up the whole dataset for an Info-Cube into several, smaller, physically independent and redundancy-free units. There are two different ways of Partitioning: Physical partitioning: This is done to improve the query performance with the cube and has to done after the cube has been created and before data has been loaded into the cube.
Logical Partitioning: Partitioning on the basis of specific criteria and create new independent structure. For eg : one cube will be split into multiple cube on some criteria eg Fiscal year as per business requirement. What is the use of Partitioning? Improves the reporting performance and data deletion from the cube. What are the pre-requisites for Partitioning? Partitioning can be implemented using time info-object only.
Hence the cube must contain atleast one of the two info-objects. We will assume the value range from It calculates in the following way…. Now enter the inputs as per the above calculation…. Now load the cube with some sample data which as shown below…. Re-partitioning: The concept of re-partitioning is useful if we have already loaded the data into the cube with follwing one of the following steps… a If we did not create the partition when we have created it.
Note: It always recommended taking data backup before doing re-partitioning. Info cube partitions are either merged at the bottom or added at the top. If we want to merge the partitions which are empty or no data has been loaded outside of the time period initially defined. In this case the runtime takes only a few minutes. If there is data in the partitions in which we want to merge or if data has been loaded beyond the time period than initially defined, then the system will save the data in a shadow table and then copies it to original table.
Here the runtime depends on the amount of data the system has to copy. We can also merge and add partitions for aggregates as well as cubes and we can reactivate all the aggregates after the cube has been changed. We can start re-partitioning by right clicking on the cube and selection Repartitioning option as shown….
For appending partitions follow the below procedure As mentioned before it is recommended to take a backup before repartitioning as shown….
Enter the value range in the upper limit upto which you want to extend the partition and specify the partitions. In this case we are appending from Now a request will be created to attach new partitions The job will scheduled in background to complete the request created In the monitor below it is clear that the patitions are appended Logical partitioning This method is used if your data in your cube become huge.
So you create multiple cubes say for 1cube for , another cube for , etc You can then use a multiprovider - which consists of all these cubes and then report on all the data. For eg a Single cube can be separate on fiscal year basis.
Refer below screen shot which shows Financial COPA cube being separated into multiple cubes based on fiscal year. The advantage of this method is we are allowed to keep different parameters for separating depending on business requirement and reporting layer requirements.
Disadvantages are increase in no of objects manages in the landscape.
Partitioning in BW
Step Maximum No of Partitions Once the value range is updated, an optional parameter called maximum no of partitions can be included. When activating the InfoCube, the fact table is created on the database with one of the number of partitions corresponding to the value range and maximum no. You can also determine how many partitions are created as a maximum on the database for the fact table of the InfoCube. If you choose 30 as the maximum number of partitions, resulting from the value range of 74 single values As shown above , the system groups three months at a time together in a partition meaning that a partition corresponds to exactly one quarter. The performance gain is only gained for the partitioned InfoCube if the time dimension of the InfoCube is consistent. Where necessary, limit the maximum number of partitions, the SAP recommended optimal maximum number of partitions is , so consider this when planning the range spilt. Summary In BW 3.
SAP BI 7.0 InfoCube Partitioning
The following documentation provide a short introduction, how the partitioning works under Oracle database. In situations with a high load frequency it will most certainly run into problems in this area sooner or later. If a statistics update will be done, all these Additionally there is lots of DB cost for returning the allocated disk space. When the number of partitions is low, the user will not experience trouble from this direction at the beginning,. But it will run into problems randomly at first, when the number of partitions increase, and permanently, after the number of partitions have exceeded some not specifiable, system and context dependent threshold.