Leveraging Semantic Partitioning Cube in Planning and Consolidation
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
|
MSCS/MSAS |
SAP BW 7.30 |
Oracle |
Remarks |
|
|
Automatic Partitioning Creation
|
Y
|
–
|
Y
|
|
|
Manual Partitioning Creation
|
Y
|
Y
|
Y
|
|
|
Repartition with no data change
|
Y
|
Y
|
Y
|
|
|
Repartition with data change
|
Y
|
–
|
Y
|
|
|
Automatic Partitioning Strategy
|
Y
|
–
|
Y
|
|
|
User-Defined Partitioning Strategy
|
Y
|
Y
|
Y*
|
Oracle: User-defined strategy can be done when creating a
|
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.