SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability67
Using compression for aggregates means removing the Request ID, as described in later in this paper. Compression reduces the size of aggregate tables. This improves query performance and saves space.
Use the SAP BW RSA1 transaction to start compression. To run RSA1, select InfoProvider on the left side and then right-click the cube where the aggregates are to be compressed. In the pop-up window, click Manage. In the next window, select Rollup and then mark Compress after rollup in the Aggregates check box.
Analyzing aggregate creation on a database level
Aggregates in SAP BW are normal database tables that are created and then filled. An Execute + Debug button is not used in this process. The SAP ST05 transaction or an SQL Server Profiler trace can be used to view Transact-SQL statements that are sent to the database to create the aggregate. For more information, see in this paper.
The aggregate can be created by using one large query or a set of queries, depending on the specific settings in SAP BW, such as the block size.
Loading and maintaining infocubes
Indexes on the fact table
The initial load of an infocube can insert many millions of rows into the fact table, which is usually "/BIC/F....". In this case, SAP BW creates a few indexes on every fact table. These indexes improve query performance, but slow down inserts, updates, and deletions. However, for the initial load of an infocube, it is a best practice to drop the indexes before the load. This improves the load time dramatically. Then recreate the indexes after the load. After creating a new infocube and performing an initial load, see for more information.
Cube compression in SAP BW can have a massive impact on database tables. On a database level, every infocube uses the E-Fact table and F-Fact table. The E-Fact table contains rows without a Request ID; the F-Fact table contains rows with a Request ID. SAP BW stores a Request ID in the fact tables of the infocubes. Every load job that inserts data into the cube is given a new Request ID.
Rows in the fact tables are identified by a number of key columns. Because of the Request ID, a unique row can occur many times. This allows data from a certain load job to be deleted if necessary. However, creating aggregates or running queries directly on the cube increases space requirements and processing power use.
Cube compression removes the Request ID and combines all rows that have the same key columns. Depending on the load job history, the reduction in the number of rows can be huge. The result is placed in the E-Fact table. The SAP BW RSA1 transaction can be used to initiate cube compression.
Select the infocube after selecting InfoProvider. Right-click the cube and select Manage from the pop-up window. In the next window, select Collapse to activate the compression.
Microsoft Corporation © 2005