Checking BI Accelerator Indexes (Transaction RSRV)
On the Analysis and Repair of BI Objects screen (transaction RSRV), various checks are available for:
- Testing for inconsistencies between the data in the InfoCube on the database and the data in the BI accelerator index (tests in the BI Accelerator Consistency Checks area)
- Testing whether a BI accelerator index is running with the most optimal performance (tests in the BI Accelerator Performance Checks area)
- Completely or partially building or rebuilding all BI accelerator indexes or a specific BI accelerator index (tests in the BI Accelerator Repair Programs area)
The exactness and duration of each of these checks vary.
In the BI Accelerator Monitor, you can specify that the system is to run a small number of tests on a daily basis. You do this by choosing BI Accelerator -> Execute/Display Index Checks.
- The BI accelerator index you want to check has been activated and filled with data.
- Some tests work with statistics data (see tests: Propose Delta Index for Indexes, Compare Size of Fact Tables with Fact Index).
As a prerequisite, the statistics have to be switched on for the relevant InfoProvider. You make this setting in statistics properties maintenance (on the Data Warehousing Workbench screen, choose Tools -> Settings for BI Statistics).
The following tests are available under All Elementary Tests ® BI Accelerator:
BI Accelerator Consistency Checks
Master Data and Transaction Data
Compare Data in BI Tables and BIA 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 BIA 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 Sums of Key Figures of BIA Queries (Check Key Figure Sums Internally)
First the system executes a query on the BI 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 BIA index and the indexes for the master data tables.
Check Sums of Key Figures of BIA Queries with Database (Check Table Index of Key Figure Totals)
Similar to mode Internally Check Key Figure Totals, the system executes highly-aggregated queries and compares the results of the InfoCube in the database with those of the BI accelerator index.
For large InfoCubes the runtime may already be considerable, since queries to the database take longer.
Check Existence of Indexes for Database Tables (Table-Index Relation)
An index is created for almost every table of the BI 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 BI accelerator server.
Runtime: Very fast
If the test reveals that an index is missing, rebuild the index for the table.
Check Definition of Logical Index
The system compares the definitions of each of the indexes for a BIA 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 BI accelerator server.
An index may have changed if, for example, the InfoCube was changed. If this is the case, the BI accelerator index has to be repaired (see test BIA Index Adjustments After InfoCube Activation).
Note that if you do not specify an InfoCube, the system executes the test for all InfoCubes that have a BI accelerator index.
If an index has been changed, the system deletes the old index, creates a new index with the correct definition, and fills it. All BI accelerator indexes that use this index are set to "inactive"; they are not available for reporting purposes during this time.
Runtime: Depending on the size of the table, this process may take some time.
Compare Index Definition in BIA with Table on Database
The system checks the logical index of a BI accelerator index. The logical index contains the metadata of the BI accelerator index, such as the join conditions and the names of the fields.
The logical index may change if, for example, the InfoCube has been changed. If this is the case, the BI accelerator index has to be repaired (see test BIA Index Adjustments After InfoCube Activation).
Note that if you do not specify an InfoCube, the system executes the test for all InfoCubes that have a BI accelerator index.
If the logical index has been changed, the system deletes the old index and creates a new index with the correct definition. The system temporarily sets the BI accelerator index to "inactive"; it is not available for reporting purposes during this time.
Find Indexes with Status "Unknown"
The system checks whether BI accelerator indexes contain indexes that have the status "unknown" (= U). This only occurs in exceptional cases when the commit call (commit optimize) terminates during indexing. Since in this case it is not clear whether the data from the preceding indexing call is available, the affected indexes are rebuilt in repair mode.
BI Accelerator Performance Checks
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. However, if the delta index is large, 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 of the query.
The system determines proposals from the statistics data: Proposals are those 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 of the BI accelerator index. If the number of records in the BI accelerator index is significantly greater than the number in the InfoCube (more than a 10% difference), you can improve query performance by rebuilding the BIA index.
The following circumstances can result in differences in the numbers of records:
- The InfoCube was compressed after the BI accelerator index was built. Since the BI accelerator index is not compressed, it may contain more records than the InfoCube.
- Requests were deleted from the InfoCube after the BI accelerator index was built. The requests are deleted from the BIA 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; however, they are not deleted.
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.
Load BIA Index Data into Main Memory
You use this test to load all the data for a BI accelerator from the file server into the main memory if the data is not already in the main memory.
This action is useful if you want to ensure that queries executed in the corresponding InfoCube achieve optimal performance the first time they are executed and do not have to read data anew from the file server.
Data for an index is deleted from the main memory, for example, when new data is added to this index (during roll up or a change run). In BIA index maintenance (choose BIA Index Properties), you can also adjust the settings for the BI accelerator index such that data is loaded automatically to the main memory every time changes are made.
Note that if you do not specify an InfoCube, the system executes the test for all BI accelerator indexes that are active and filled.
Delete BIA Index Data from Main Memory
You use this test to delete all data for a BI accelerator index from the main memory.
Master data indexes that are still required by other InfoCubes are not deleted from the main memory. The data on the file server is not deleted - the BI accelerator index is still active.
This action is useful if there is little space in the main memory on the BIA server and you have data in the main memory that can be deleted. This is useful in the following cases:
- There is data in the main memory that is no longer used or is rarely used.
- There is data in the main memory that does place a high load on system performance when the query is executed initially (and when the file server is read in the main memory).
If you do not specify an InfoCube, the system runs the test for all BI accelerator indexes that are active and filled.
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 BIA server.
The calculated duration is an estimation; 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 of a BI accelerator index. In doing so, the system analyzes the data in the fact table and provides a projection.
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 the time required to rebuild the index, since the number of different values in the fact table needs to be determined for each column (count distinct).
BI Accelerator Repair Programs
Delete and Rebuild All BIA Indexes
All BI accelerator indexes in the system are deleted. If you selected 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.
BIA 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 BI accelerator index, since the relevant process may take a long time and can even require a partial reindexing.
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: run this repair job as a background job, if required.
Rebuild All Master Data Indexes of a BIA Index
All indexes for master data tables in a BI accelerator index are rebuilt. This includes indexes for SID tables and attribute tables (X and Y tables). When an entire BI accelerator index is rebuilt, these tables are not always rebuilt since they are also used by other BI accelerator indexes. If this results in data consistency issues, it may be necessary to rebuild the indexes for the master data tables only, or in addition.
In repair mode, the system first deletes the relevant indexes and then recreates them. All BI accelerator indexes that use these indexes are set to "inactive"; they are not available for reporting purposes during this time.
The following tests are available under All Combined Tests ® BI Accelerator:
Consistency Checks (Detailed)
Consistency Checks (Fast)
The dialog box for specifying start date values appears. Specify the time(s) for the execution. You can view the results of the check in the logs in the application log.
In repair mode, the system performs certain repair tasks. (Repair tasks are not available for all tests).
- In the Selection of Check Mode for BI Accelerator Index dialog box, choose Display Logs. The Analyze Application Log screen appears for object RSDDTREX, subobject TAGGRCHECK.
- Enter the required data to restrict the number of logs.
- Choose Execute. The system displays the corresponding results of the check.
You select the test(s) and specify the mode of execution. You can view the results of the check in the logs in the application log.