SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability65
SAP supports SQL Server 2005 range partitioning on the following classes of tables in SAP BW versions 3.5 and higher:
Persistent Staging Area (PSA) table. This is the main table for the staging data that is coming from the outside into SAP BW. Here the partition criteria is the LoadID. The LoadID is an artificial partitioning key for PSA tables that correlates to the number of rows per partition.
F-Fact (part of a SAP BW infocube). The F-Fact table in the cube allows duplicate rows. The F-Fact table contains the Request ID. This is the request that is associated with loading the data into the cube. The Request ID is the dimension to which the partitions are lined. Each new load is stored in its own partition.
E-Fact table (part of a SAP BW infocube). The partitioning of the E-Fact table is not mandatory, but it is recommended because the data from the F-Fact table is compressed into the E-Fact table. Duplicates in the F-Fact table are rolled up into one row, making the E-Fact table smaller. Because the Request ID is not part of the E-Fact table, the implementation of SAP uses the time dimension for partitioning. Either the month or the fiscal year can be used for E-Fact tables. The time range is defined at the creation time of the cube.
Missing or outdated optimizer statistics
The Automatic Statistics feature in SQL Server 2005 does not work as expected for small tables of less than 500 rows. For more information, see SAP OSS Note 542468. Following the instructions in the SAP OSS Note is strongly recommended.
For a quick test, use the Query Analyzer to directly run Transact-SQL statements that update statistics. The Query Analyzer can send Transact-SQL statements directly to SQL Server, bypassing SAP BW. Because the problem only occurs on small tables, it is sufficient to update the information on the dimension tables. For more information, see SAP OSS Note 542468. The recommendation is to use SQL Server to periodically update statistics, such as on a schedule or update them manually on an exception basis only. SAP typically relies on SQL Server AUTO_UPDATE_STATISTICS.
Materialized aggregates and preaggregated query results are important for SAP BW. In cases where many well-defined reports are running on a daily basis, typical practice is to create SAP BW aggregates to preaggregate reports or parts of reports. When there are hundreds of interactive users, it is vital to build SAP BW aggregates for well-known reports. Creating SAP BW aggregates avoids marginalizing database resources to perform aggregation on the fly while executing reports.
When SAP BW aggregates are created against a SAP BW cube, performance is improved by using the aggregates during the interactive query phase. However, during the delta load phase, these aggregates must be updated (rolled-up), which extends the run time of the delta load phase. The alternative to this trade-off is to marginalize resources to read huge volumes of data from the fact tables of the SAP BW cubes.
Microsoft Corporation © 2005