Introduction

Background

SAP Planning and Consolidation is a star product of SAP in
planning and consolidation area. It is an OLAP application which is based on
SAP BW. The performance is a key factor to the success of this product. Most of
the performance is about reporting and writing data. As a technical expert I choose
to leverage the SAP BW semantic partitioning cube to improve the performance of
our product. I’d like to share my research result to your all.

Due to license issue I don’t have a hand on the MSCS and
MSAS and Oracle OLAP application. All information is gathered from public
sources. My time on this research is also limited because I also have official development
work to do. But I tried to do this research once I am not so busy and also I
took some of my spare time for this research. There must be some errors, please
help me to correct them.

Pros / Cons

Better query performance

Only a small set of data is involved in
a query. Possible parallel data query.

Reduce Cube processing Time

The processing of cube can be parallelized on multi-core CPU
servers.

Shorter downtime

Data maintenance is on a small set of
data. The total maintenance time is less compare to process the entire cube.

Error Handling

If a request for a region ends with an
error, only the partition that caused the error is unavailable for data
analysis instead of the entire cube.

Working with different time zone

The time zones can be separated by the
partitions. Data loading and administrative tasks can therefore be scheduled
independently of the time zone.

More maintenance overhead

More partitions means additional effort needed because you
may have to merge partitions over time.

Industry Research

Microsoft Commerce Server 2002 Enterprise Edition / Microsoft Analysis
Services 2000 Enterprise Edition

Defining Partition

The cube partition can be done automatically or manually. Automatic
partition is initiated by a system task called ‘Report preparation task’. It
creates a partition for every 20 million key column values in the fact table of
an OLAP cube. An OLAP cube can also be manual configured to partition
separately.

Partitioning Key

User must define a Partitioning Key column for
auto-partitioning. The PartitioningKey column must be one that can be converted
to an 8-byte integer and back to its base type.

Repartition

Repartition is necessary for business use. E.g. when you
delete a large amount of data from cube, some of the partitions may be empty.

The MSCS or MSAS support repartition. The simplest way is to
delete the partition definitions in the CubePartitionInfo table. Then the
Report preparation task will regenerate new partition definitions. It is also
possible to manually define the partitions, but there are some rules to follow.

Partition specific aggregation

Both MSAS 2000 and MSAS 2005 allow you to have different
sets of aggregations for each partition.

But MSAS only allows you merge partitions with identical
aggregations.

SAP BW 7.30

Defining Partition

You can create a semantic partitioning cube in BW modeling
(transaction RSA1). By toggle the option ‘Semantically Partitioned’. RSA1 will
lead you to a wizard which contains 6 steps to complete the cube defining. The
second step is to define the partition strategy. You can define the data filter
criteria for each partition. The last three steps are to load data into the
semantic partitioning cube. The semantic partitioning cube must be created from
scratch and load data into it.

Partitioning Key

BW does not have a so-called ‘Partitioning Key’ concept. The
partition filter criteria have the same function. BW can define a fair complex
filtering criterion for each partition.

Repartitioning

• Not Contains Data

You can repartition it any way
you like.

• Contains Data

You cannot repartition it anyway.
Repartitioning is only possible if no data needs to be moved to different partitions
and if no partial deletion of partition data is required.

Oracle

Defining Partition

The Cube Partitioning Advisor is the tool to partition an
OLAP cube. It analyzes the source tables and develops a partitioning strategy. Users
can accept the recommended partitioning strategy or they can make their own
decision.

The Cube Partitioning Advisor needs to be run before data
loading. The partitioning strategy can be changed anytime, but the data need to
be reloaded afterward.

Partitioning Key

Time dimension is recommended as partitioning key because it
can be utilized by life-cycle management considerations. Old time periods can
then be dropped as a unit, and new time periods added as a new partition.

User defined partitioning strategy only can be done when
creating the cube.

Example of a Partitioned Dimension

The Cube Partitioning Advisor might recommend partitioning
at the Quarter level of the Calendar hierarchy of the Time dimension. Each
Quarter and its descendants are stored in a separate partition. If there are
three years of data in the analytic workspace, then partitioning on Quarter
produces 12 bottom partitions, in addition to the default top partition. The
top partition contains all remaining levels, that is, those above Quarter (such
as Year) and those in other hierarchies (such as Fiscal Year or Year-to-Date).

Functional Matrix

Planning functionality research on SAP BW 7.30

Defining Real-Time cube (Planning Mode)

The option to create a ‘Real-Time’ cube is automatically
disabled when the ‘Semantically Partitioned’ option is selected when defining
the cube.

Data Loading

Data loading for semantic partitioning cube follows the
standard procedures as loading data into a standard cube. DTP, Transformation
and Process Chain are needed. These are the last three steps to defining a
semantic partitioning cube.

Reporting

The semantic partitioning cube can be accessed as a normal
BW cube. Most of the OLAP functions work find on the semantic partitioning
cube.

E.g.

RSDRI_INFOPROV_READ

BAPI_MDDATASET_SELECT_DATA

SAP BI Tools

Most of the BI tools use ODBO protocol to communicate with
SAP BW. The semantic partitioning cube is not visible as a data source via ODBO
driver. Thus most of the BI tools are not able to connect to such cubes.

BEx Query Designer

The semantic partitioning cube can be selected to design
queries on it. The sub-partition cubes are also visible in the data source
selector. So you can define queries on top of the sub-partition cube as well.

BEx Analyzer

Since a query can be created on top of the semantic
partitioning cube, the query can surely be used in BEx Analyzer.

Write in Real-Time

As the BW document clearly stated that: ‘The partitions are
write-protected to make sure that they remain Identical.’  So the write data into the semantic partitioning
cube is not possible.

Conclusion

We can see from the functional matrix SAP BW’s semantic
partitioning cube has the least functionalities. This feature is introduced in
BW from version 7.30. It is not mature. It cannot expose data via ODBO. It
cannot change data once the data is loaded. The repartition is quite inconvenient
if there is data in the cubes.

In summary, due to the fact that writing data in real-time to
the semantic partitioning cube is not possible, Planning and Consolidation
cannot utilize the semantic partitioning cube currently.

References

Defining
OLAP Cube Partitions

Oracle
OLAP User’s Guide

Use
cube partitions to improve Analysis Services performance

Repartitioning
Semantically Partitioned Objects