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




Comments

Post a Comment

Popular posts from this blog

Large SAP tables which are good candidates for partitioning

Image
Not all tables are good candidates for partitioning. The tool we have developed runs through multiple checks for large tables to identify the right candidate. Generally if the answer is yes to the following questions, table partitioning may be a suitable strategy for improving performance. 1. Is the database large enough? 2. Is the table large enough? 3. Do your reports and queries have distinguishable access patterns? 4. Are you experiencing slowness and performance degradation? 5. Is the skew factor for the data distribution under reasonable limits? Below are some of the large tables which have been candidates for table partitioning amongst our SAP customers. Large material master tables MARA                   Material Master Table MARC                   Plant Material Data MARO...
Read more

Partitioning in SAP HANA database

SAP HANA provides two ways of distributing data 1. Database Partitioning - different tables being distributed across several hosts (SAP Note 2044468 - FAQ:SAP HANA Partitioning) 2. Table Partitioning - splitting a column store table and distributing across several hosts (SAP Note 2081591 - FAQ: SAP HANA Table Distribution)  SAP HANA supports: 1. Hash Partitioning 2. Range Partitioning 3. Round-robin Partitioning In SAP HANA, we can create 1. Single-Level Partitioning 2. Multi-Level Partitioning The optimizing algorithm takes factors into account such as: - Balance tables equally in a way that they are spread over all the slave nodes and make optimal use of the available physical memory on each node - Ensure that tables/table partitions which are belonging to the same table groups (see tables _SYS_RT.TABLE_PLACEMENT, SYS.TABLE_GROUPS) are located on the same nodes, as they are logically frequently queried from application side via joins and if t...
Read more