When you work with SAP Business Warehouse (SAP BW), there’s a risk that data tables might overflow as InfoProviders get larger and larger. To prevent this, SAP BW offers several options to keep the number of rows in an InfoProvider small, for instance archiving data that you rarely need anymore, or collapsing InfoCubes.
Another option is partitioning – dividing data into smaller parts that are stored in separate containers. For queries, they can be accessed as one entity. For maintenance, they can be accessed separately to improve performance.
For SAP BW systems on IBM Db2 for i, there are two methods of partitioning available – partitioning of database tables and semantic partitioning of InfoCubes or DataStore objects.
In light of the recent release of SAP Note 2711358 – Conversion of Semantically Partitioned Objects to a Flat Format, we’ll have a closer look at both of these methods.
Database Partitioning
If you have the product IBM Db2 Multisystem installed on your database server, you can choose to partition database tables. A table can have up to 256 partitions, with each of them being a member of the database table.
The size of a database table is limited to 4.29 billion rows or 1.7 Terabyte. In a partitioned table, these limits apply to each member separately, so the table can grow up to 256 times as large as an unpartitioned table.
SAP Note 815186 – IBM i: Table partitioning in BW describes the prerequisites for using database partitioning with SAP BW on IBM Db2 for i, and how the database tables of certain BW object types (InfoCubes, DataStore objects, and PSA tables) are partitioned.
Once you have specified the partitioning options for your BW object, the SAP BW system creates the partitioned tables accordingly. During data load and maintenance, the system automatically takes care of adding or dropping partitions as needed. The database itself takes care of storing new rows in the correct table members, and of reading the requested data during query execution from all members as needed.
More information about partitioning of InfoProviders is available in the SAP BW documentation on SAP Help Portal.
Semantic Partitioning
In the Data Warehousing Workbench (transaction RSA1), when you create an InfoCube or a DataStore object (DSO), there is an option “Semantically Partitioned”. When you select it, a so-called semantically partitioned object (SPO) is created instead of only one single InfoCube or DSO.
An SPO consists of multiple InfoCubes or of multiple DSOs with identical structure. When metadata for the SPO is defined, the definition is saved in the MasterProvider, which acts as a template for the PartProviders. After you have defined the logical partitions for the SPO, PartProviders are created – one InfoCube or DSO for each partition, respectively.
To reduce manual effort, wizards are available to create Data Transfer Processes (DTP), transformations, filters for each PartProvider, and process chains. When you load data, the system automatically distributes the rows to the correct PartProviders.
You can use an SPO for reporting and analysis like any other InfoProvider. The OLAP processor makes sure that data from all involved PartProviders is read for BW queries. In addition, you can access the PartProviders independently, for instance for defining aggregates on part InfoCubes.
More information about Semantic Partitioning is available in the SAP BW documentation on SAP Help portal.
Comparing Database Partitioning and Semantic Partitioning
The following table lists the properties of database partitioning and semantic partitioning side by side to give you an indication which is preferable for you:
Database Partitioning Semantic Partitioning
Prerequisites: Additional product:
Minimum SAP BW release:
Directly in the database, without support by SAP BW functions:
Maximum number of partitions: 256 99
(x 256 if combined with database partitioning)
Possible partitioning keys: Partition by:
No partitioning by more than one characteristic supported.
Lowest and highest partition boundary is defined by the user, boundaries of individual partitions and number of partitions are calculated by the system.
Partition by:
Partition size is defined by the user (one value that applies to all PSA tables). Partition boundaries and number of partitions are calculated by the system.
Partition by:
Partitioning by a combination of up to 5 of these characteristics possible.
Number of partitions (up to 99) and individual partition boundaries can be freely chosen.
Add or remove partitioning: Yes No conversion of InfoCubes/DSOs into SPO or back.
Add or remove partitions: Yes Yes
Repartition: Yes Yes, as long as no data needs to be moved to different partitions and no partial deletion of partition data is required.
Effort for changing partitioning:
High
All data of the (re)partitioned tables is being copied.
Low
Only creation or deletion of InfoCubes/DSOs.
Retain partitioning when converting InfoCubes:
Standard InfoCubes can be converted to flat structure but will lose partitioning, because database partitioning for flat InfoCubes is not supported.
When you convert a flat InfoCube to the standard “star schema”, partitioning can be added again.
Conversion of standard InfoCubes that are part of an SPO to a flat structure is supported with SAP Note 2711358. The logical partitions are retained.
Conversion of flat InfoCubes that are part of an SPO back to a standard “star schema” is not supported.
Settings can be transported: No Yes
Reporting:
No visible difference compared to unpartitioned InfoCubes/DSOs for the user.
When BW queries are executed, the database optimizer may use partition pruning to speed up performance.
BW queries defined on SPOs automatically read from all PartProviders of the SPO.
For better performance, the OLAP processor automatically prunes PartProviders that do not match the query conditions.
Maintenance: No visible difference compared to unpartitioned InfoProviders for the user.
Data load: Individual DTPs for each PartProvider, automatic distribution of the data to the correct target.
Collapse/Rollup: Must be done separately for each PartProvider.
(You may create process chains for easier handling.)
Parallelization: Where possible, BW queries, administration and load processes are split into parallel jobs by BW background management. This happens independently of database table partitioning. In addition to the parallelization of BW queries, administration and load processes, activities that are strictly serial on one InfoProvider can be executed in parallel for each PartProvider.
Resiliency: If, during data load, a request ends with an error, the entire InfoProvider becomes unavailable for reporting until the issue is solved. If, during data load, a request ends with an error, only the PartProvider with the data that caused the error becomes unavailable for reporting until the issue is solved.
Conclusion:
Partitioning is a useful feature for managing mass data in SAP BW. Database partitioning and semantic partitioning each have their own advantages and strengths. Database partitioning can be used for more object types. Semantic partitioning is more flexible. Database partitioning and semantic partitioning are not mutually exclusive, you can combine them if you like.
Which method you choose in the end depends on what you want to achieve. Hopefully, this comparison helps a little to come to a carefully considered decision.
Okumaya devam et...
Another option is partitioning – dividing data into smaller parts that are stored in separate containers. For queries, they can be accessed as one entity. For maintenance, they can be accessed separately to improve performance.
For SAP BW systems on IBM Db2 for i, there are two methods of partitioning available – partitioning of database tables and semantic partitioning of InfoCubes or DataStore objects.
In light of the recent release of SAP Note 2711358 – Conversion of Semantically Partitioned Objects to a Flat Format, we’ll have a closer look at both of these methods.
Database Partitioning
If you have the product IBM Db2 Multisystem installed on your database server, you can choose to partition database tables. A table can have up to 256 partitions, with each of them being a member of the database table.
The size of a database table is limited to 4.29 billion rows or 1.7 Terabyte. In a partitioned table, these limits apply to each member separately, so the table can grow up to 256 times as large as an unpartitioned table.
SAP Note 815186 – IBM i: Table partitioning in BW describes the prerequisites for using database partitioning with SAP BW on IBM Db2 for i, and how the database tables of certain BW object types (InfoCubes, DataStore objects, and PSA tables) are partitioned.
Once you have specified the partitioning options for your BW object, the SAP BW system creates the partitioned tables accordingly. During data load and maintenance, the system automatically takes care of adding or dropping partitions as needed. The database itself takes care of storing new rows in the correct table members, and of reading the requested data during query execution from all members as needed.
More information about partitioning of InfoProviders is available in the SAP BW documentation on SAP Help Portal.
Semantic Partitioning
In the Data Warehousing Workbench (transaction RSA1), when you create an InfoCube or a DataStore object (DSO), there is an option “Semantically Partitioned”. When you select it, a so-called semantically partitioned object (SPO) is created instead of only one single InfoCube or DSO.
An SPO consists of multiple InfoCubes or of multiple DSOs with identical structure. When metadata for the SPO is defined, the definition is saved in the MasterProvider, which acts as a template for the PartProviders. After you have defined the logical partitions for the SPO, PartProviders are created – one InfoCube or DSO for each partition, respectively.
To reduce manual effort, wizards are available to create Data Transfer Processes (DTP), transformations, filters for each PartProvider, and process chains. When you load data, the system automatically distributes the rows to the correct PartProviders.
You can use an SPO for reporting and analysis like any other InfoProvider. The OLAP processor makes sure that data from all involved PartProviders is read for BW queries. In addition, you can access the PartProviders independently, for instance for defining aggregates on part InfoCubes.
More information about Semantic Partitioning is available in the SAP BW documentation on SAP Help portal.
Comparing Database Partitioning and Semantic Partitioning
The following table lists the properties of database partitioning and semantic partitioning side by side to give you an indication which is preferable for you:
Database Partitioning Semantic Partitioning
Prerequisites: Additional product:
- IBM Db2 Multisystem
Minimum SAP BW release:
- SAP BW 7.0 for partitioning of InfoCubes and their aggregates, PSA tables
- SAP BW 7.1 for partitioning of DataStore objects
- SAP BW 7.3 for support of SPOs
- Standard InfoCubes and their aggregates
- DataStore objects
- PSA tables
Directly in the database, without support by SAP BW functions:
- every database table
- InfoCubes (standard and flat)
- DataStore objects
Maximum number of partitions: 256 99
(x 256 if combined with database partitioning)
Possible partitioning keys: Partition by:
- For InfoCubes: Time characteristic 0CALMONTH, 0CALWEEK, or 0FISCPER
- For DSOs: Time characteristic 0CALMONTH, or 0FISCPER
No partitioning by more than one characteristic supported.
Lowest and highest partition boundary is defined by the user, boundaries of individual partitions and number of partitions are calculated by the system.
Partition by:
- For PSA tables: artificial partitioning key PARTNO
Partition size is defined by the user (one value that applies to all PSA tables). Partition boundaries and number of partitions are calculated by the system.
Partition by:
- For SPOs consisting of InfoCubes: every characteristic that is part of the InfoCube
- For SPOs consisting of DSOs: every key field of the DSO
Partitioning by a combination of up to 5 of these characteristics possible.
Number of partitions (up to 99) and individual partition boundaries can be freely chosen.
Add or remove partitioning: Yes No conversion of InfoCubes/DSOs into SPO or back.
Add or remove partitions: Yes Yes
Repartition: Yes Yes, as long as no data needs to be moved to different partitions and no partial deletion of partition data is required.
Effort for changing partitioning:
High
All data of the (re)partitioned tables is being copied.
Low
Only creation or deletion of InfoCubes/DSOs.
Retain partitioning when converting InfoCubes:
Standard InfoCubes can be converted to flat structure but will lose partitioning, because database partitioning for flat InfoCubes is not supported.
When you convert a flat InfoCube to the standard “star schema”, partitioning can be added again.
Conversion of standard InfoCubes that are part of an SPO to a flat structure is supported with SAP Note 2711358. The logical partitions are retained.
Conversion of flat InfoCubes that are part of an SPO back to a standard “star schema” is not supported.
Settings can be transported: No Yes
Reporting:
No visible difference compared to unpartitioned InfoCubes/DSOs for the user.
When BW queries are executed, the database optimizer may use partition pruning to speed up performance.
BW queries defined on SPOs automatically read from all PartProviders of the SPO.
For better performance, the OLAP processor automatically prunes PartProviders that do not match the query conditions.
Maintenance: No visible difference compared to unpartitioned InfoProviders for the user.
Data load: Individual DTPs for each PartProvider, automatic distribution of the data to the correct target.
Collapse/Rollup: Must be done separately for each PartProvider.
(You may create process chains for easier handling.)
Parallelization: Where possible, BW queries, administration and load processes are split into parallel jobs by BW background management. This happens independently of database table partitioning. In addition to the parallelization of BW queries, administration and load processes, activities that are strictly serial on one InfoProvider can be executed in parallel for each PartProvider.
Resiliency: If, during data load, a request ends with an error, the entire InfoProvider becomes unavailable for reporting until the issue is solved. If, during data load, a request ends with an error, only the PartProvider with the data that caused the error becomes unavailable for reporting until the issue is solved.
Conclusion:
Partitioning is a useful feature for managing mass data in SAP BW. Database partitioning and semantic partitioning each have their own advantages and strengths. Database partitioning can be used for more object types. Semantic partitioning is more flexible. Database partitioning and semantic partitioning are not mutually exclusive, you can combine them if you like.
Which method you choose in the end depends on what you want to achieve. Hopefully, this comparison helps a little to come to a carefully considered decision.
Okumaya devam et...