SAP HANA exploits partitioning in SAP BW for optimization
Partitioning significantly improves query performance, based on the types of queries and on your hardware configuration as the system will find faster the requested information, because it will filter out all the irrelevant partitions and thus reduce the data volume to be read.
This is a standard feature in most databases but SAP did not truly exploit this feature being database agnostic until HANA. In SAP/R3, SAP provided partition keys for BW but did not partition data during installation. With HANA the table partitioning/distribution is automated for SAP NetWeaver Business Warehouse.
Database tables created for InfoCubes or classic DataStore objects are partitioned on the database. While the technical partitioning of data at database level helps optimizing the runtime of BW processes to a great extent, they alone are inadequate in building and maintaining data warehouses that scale easily with growing data volumes. Hence, to address some of the aspects of dealing with big data in BW systems, SAP recommends to partition InfoProviders semantically.
1. InfoCube: The fact table of an InfoCube is split into 4 database partitions: uncompressed requests, compressed requests, reference points, and historic movements – the later 2 are only relevant for InfoCubes with inventory keyfigures and are otherwise empty.
2. DSO: A DSO is partitioned on the database using a time characteristic – this is modeled in BW.
3. PSA tables: As part of the “BW post migration procedure” a new PSA version (and therefore a new table) is created. The new PSA tables are automatically partitioned based on the technical field PARTNO and new partitions are created automatically once one partition exceeds a certain size so avoiding the 2 billion limit. A PSA table MUST not be larger than 2 billion records before the DB migration to HANA.
4. Masterdata tables: As part of the data quality and referential integrity check in BW a unique integer value is created for each characteristic value, the “SID”.The Masterdata tables are then automatically partitioned to avoid hitting the 2 billion limit per part/table).
SAP is also working on tools to help you automate the partitioning and merge tasks for SAP BW. You might have a look on the SAP HANA Data Warehousing Foundation, which includes a tool called Data Distribution Optimizer (DDO). It supports you during:
1. Scale-Out landscape analysis
2. Creation and modification of the TABLE_PLACEMENT table
3. Generating, simulating and executing redistribution plans
4. Comparing different redistribution plans
5. Analyze Log files of executed redistribution jobs
For HANA, refer to SAP Notes
1908075 - BW on SAP HANA: Table placement and landscape redistribution
2143736 - FAQ: SAP HANA Table Distribution for BW
2081591 - FAQ: SAP HANA Table Distribution