Tests for BWA Indexes in Transaction RSRV

Elementary Tests

The following tests are available under  All Elementary Tests   BW Accelerator :

BW Accelerator Consistency Checks

Master Data and Transaction Data

• Compare Data in BW Tables and BWA Indexes (Check Table Index Content)

  The system compares the content of each individual table with the content of the corresponding index on a record-by-record basis. This check is only suitable for tables or indexes that do not contain a large amount of data, such as dimension tables, certain SID tables (S) and attribute tables (X and Y). This is not generally the case with fact tables. If a table contains 10,000 records or more, it is not checked. In some situations, the content of the indexes of the BWA index may differ from the content of the corresponding database table. This may be the case if requests have been deleted from the InfoCube or if an InfoCube has been compressed.

• Check Totals of Key Figures of BWA Queries (Check Key Figure Totals Internally)

  First the system executes a query on the BW accelerator index, which is aggregated using all key figures. Next, all the characteristics and navigation attributes that exist in the InfoCube are included in the drilldown individually, and the totals are calculated. The system compares the result with the result of the first query. This test checks the completeness of the join path from the SID table, through the dimension table, to the fact tables.

  Runtime: Depends on the number of characteristics and navigation attributes and on the number of records in the fact table.

  If the test shows that the data is incorrect, you have to rebuild the BWA index and the indexes for the master data tables.

• Check Totals of Key Figures of BWA Queries with Database (Check Table Index of Key Figure Totals)

  Like with the Internally Check Key Figure Totals mode, the system executes highly-aggregated queries and compares the results of the InfoCube in the database with the results in the BW accelerator index.

  For large InfoCubes, the runtime may already be very long, since queries to the database are more time consuming.

• Check Existence of Indexes for Database Tables (Table-Index Relation)

  An index is created for almost every table in the BW InfoCube enhanced star schema: fact (F) tables, dimension (D) tables, SID (S) tables and attribute tables (X and Y). The only exception is SID tables with numeric characteristic values.

  This test checks whether the named indexes have been created on the BW accelerator server.

  Runtime: very fast

  If the test reveals that an index is missing, rebuild the index for the table.

• Check Hierarchy Index of a Characteristic

• Delete Hanging Requests and Activate BWA

Metadata

• Check Definition of Logical Index

  The system compares the definitions of each of the indexes for a BWA index with the current versions of the database tables. It checks whether the number, name, and type of the table fields in the database match the definition for the index on the BW accelerator server. An index can have changed if, for example, the InfoCube was changed. The BW accelerator index then has to be repaired (see test BWA Index Adjustments After InfoCube Activation).

  Caution

  If you do not specify an InfoCube, the system runs the test for all InfoCubes that have a BW accelerator index.

  If an index has been changed, the system deletes the old one, creates a new one with the correct definition and fills it. All BW accelerator indexes that use this index are set to "inactive". They cannot be used for reporting purposes during this time.

  Runtime: Depending on the size of the table, this process might be quite long.

• Check Hierarchy Metadata in BWA

BW Accelerator Performance

• Size of Delta Index

  If you have chosen delta mode for an index of a table, new data is not written to the main index but to the delta index. This can significantly improve performance during indexing. If the delta index is large however, this can have a negative impact on performance when you execute queries. When the delta index reaches 10% of the main index, the system displays a warning.

  The system performs a merge for the index in repair mode. The settings are retained.

• Propose Delta Index for Indexes

  It is useful to create a delta index for large indexes that are often updated with new data. New data is not written to the main index, but to the delta index. This can significantly improve the performance of indexing, since the system only performs the optimize step on the smaller set of data from the delta index. The data from the delta index is used at the runtime for the query.

  The system finds proposals based on the statistics data: the proposals are the indexes that received new data more than 10 times during the last 10 days. A prerequisite for these proposals is that the statistics for the InfoCube are switched on.

  Data in the main index and delta index should be merged at regular intervals (see test Size of Delta Index).

  In repair mode, the system sets the Has Delta Index property for the proposed indexes. The delta index is created when the data is next loaded for this index.

• Compare Size of Fact Tables with Fact Index The system calculates the number of records in both fact tables (E and F tables) for the InfoCube and compares them with the number of records in the fact index in the BW accelerator index. If the number of records in the BW accelerator index is significantly greater than the number in the InfoCube (more than a 10% difference), you can improve query performance by rebuilding the BWA index.

  There can be differences in the numbers of records in the following situations :

  • The InfoCube was compressed after the BW accelerator index was built. Since the BW accelerator index is not compressed, it might contain more records than the InfoCube.

  • Requests were deleted from the InfoCube after the BW accelerator index was built. The requests are deleted from the BWA index in the package dimension only. The records in the fact index are therefore no longer referenced and no longer taken into account when the query is executed; they are not, however, deleted.

    Caution

    Note that the database statistics for calculating the size of the fact table must be up to date, since the test does not recount; it uses the database statistics from the tables.

• Estimate Runtime of Fact Table Indexing

  The system estimates the time required to fill the fact index. It uses the current parameter values for background and dialog parallel processing. The time taken is calculated from the processes available and the estimated maximal throughput of data records in the database, the application server, and the BWA server.

  The calculated duration is an estimate; the load on the system, the distribution of data across block criteria and deviations during processing can all affect the actual time taken.

• Estimate Memory Consumption of Fact Table Index

  The system estimates the size of the fact table index in a BW accelerator index. Here the system analyzes the data in the fact table and generates a projection.

  Caution

  Note that if data distribution is poor, the actual memory consumption can deviate from the projected value. A more exact analysis would demand more time than that required to rebuild the index, since the number of different values in the fact table needs to be determined for each column (count distinct).

BW Accelerator Repair Programs

• Delete and Rebuild All BWA Indexes

  All BW accelerator indexes in the system are deleted. If you select the Execute option, the indexes are then recreated and filled. This is sometimes required for a successful restart with consistent data if a critical error occurs.

• BWA Index Adjustments After InfoCube Activation If an InfoCube is changed as a result of the addition of a key figure for example, the system does not automatically adjust the BW Accelerator index, since the relevant process can be time consuming and might even make partial reindexing necessary.

  When you execute this test, information about any changes identified are written to the log. The system makes the required changes in repair mode.

  Recommendation

  We recommend that you run this repair job as a background job, if required.

Combined Tests

The following tests are available under  All Elementary Tests   BW Accelerator :

Consistency Checks (Detailed)

Consistency Checks (Fast)

Performance Tests