Purpose

This WIKI page explains benefits and best practices for partioning a BPC application

Note: As of BPC 10 MS, Planning and Consolidation provides the Partition Wizard, tool that enables you to easily create custom partitions.

Overview

In a very large BPC application/model (partion file exceeding 2.5 GB or more than 10 million records), each query gets slower and slower mainly due to the amount of data to scan before retrieving the result of the query.

In this context, improving performance becomes necessary and partitioning the cube, corresponding to the BPC application/model, turns out to be the single most significant way of addressing the matter.

Typically, the method here below consists in segmenting the data by one or more dimension members, such as date or region, each slice of data being stored in a dedicated partition in this way.

Note : Partioning a cube may have a dramatic impact on performance so please first experiment with this on a TEST environment.

Creation of SSAS Partition

Sample Partition for Consolidation Cube

In the following step-by-step procedure, TIME dimension is used for the data segmentation within the long-term partition "CONSOLIDATION" of the BPC EnvironmentShell.

The main purpose is to split the data into several time slices :

Deletion of the Existing Partition in SSAS

Connect to the Analysis Services instance through Microsoft SQL Server Management Studio :

Once connected, drill down the folder corresponding to the BPC SSAS database, then within the Cubes folder, navigate to the partition linked to the long-term fact table (CONSOLIDATION in this example).

Delete the partition by right-clicking on it and select Delete :




In the Delete Object window, click OK to delete the initial partition :

Creation of a New Partition within the cube

Right click on the node Partitions then select New Partition :

Click Next until the following Specify Source Information window. Tick the check-box corresponding to the Fact table for your partition (here it's dbo.tblFactConsolidation) and click Next :

 


In the next window, you have to specify a SQL query with a WHERE clause (cf table in the section "Sample Partition for Consolidation Cube) to restrict the time periods :

Click Next and fill in an name of the new partition,tick the check-box Design aggregations for the partition now and click Finish :

At the next step Aggregation Design Wizard , click Next on the Welcome window.

Then, check the box corresponding to the new partition and click Next button :

At the Review Aggregation Usage window, click Next :




At the Specify Object Counts window, click Count button to calculate the row counts.

Then click Next :





At the Set Aggregation Options window, select I click Stop .

Then click Start button :





Once the aggregations have been designed, click the Next button :

At the next step, check the box Process now and click Finish :

At the Process Partition window, click OK :

Once the cube process succeeded, click Close :





The new partion now appears in the list of partions within the CONSOLIDATION cube :

Create in the same way the other partitions ( CONSOLIDATION2011, CONSOLIDATION2012Q1 etc..) that will contain the other data slices.

Conclusion

The partitions created within a cube can be processed either in parallel or individually and can have different storage modes.

We can see the immediate impact of the new data structure : the very frequent queries on the recent data will be done within a smaller set of data (partition) and then will return the result faster.

Note : Please notice that the partition structure will be reset after restoring the BPC environment (Application-set) through the BPC Server Manager .

Related Content

Related Documents

Related Notes

SAP Note 1562524 - BPC Performance Tuning guide