1. Under which component must I open a message if I have problems with BW with MaxDB?
Open a message under the BW-SYS-DB-SDB component.
2. When are versions of BW taken out of maintenance?
You can find out the maintenance durations for BW versions in the SAP Support Portal under:
Maintenance & Services
- > Maintenance Offering
- > Overview Maintenance Durations
- > SAP BW
3. On which SAP Basis versions are the BW systems integrated?
BW System 3.0 - SAP Basis 6.20
BW System 3.1 - SAP Basis 6.20
BW-System 3.5 - SAP Basis 6.40
All BW systems should use at least MaxDB Version 7.5.00 Build 18.
4. Where can I find information about the heterogeneous system copy of a BW system?
You can find the documentation in SAPNet under the alias 'instguides', service.sap.com/instguides
1.) Path for BW 3.5:
SAP NetWeaver -> Release 04 -> Installation
SAP Web AS -> SAP Web AS 6.40 and Related Documentation -> Homogeneous and Heterogeneous System Copy
See also the BW supplementary 771209.
2.) Path for BW 3.0/3.1:
SAP Components -> SAP Web Application Server -> Release 6.20
System Copy for SAP Systems Based on Web AS 6.20
See also the BW supplementary 777024.
After a heterogeneous system migration, see also 931333.
5. What can cause performance problems in the BW system?
The causes can be both in the database and in the application.
a) Causes in the database
Inaccurate table statistics increase the reading load on the database because the optimizer can no longer access the tables in the most favorable way. Consequently, the data cache hit rate deteriorates. The statistics are therefore estimated using a sample rate. If this sample rate is not set correctly for large tables, this leads to inaccurate statistics being generated. For large tables, the sample rate must be set to 10%. For more information, see 808060.
Database parameters that have been set incorrectly can also impair the performance of the BW system. This contains detailed information on this problem.
When you configure the BW database, you should pay particular attention to the disk configuration (data volumes). The size of the data cache and the correct setting of the JOIN parameters have a critical effect on the performance of a BW system. This note contains tips and references to other sources of detailed information on the configuration.
Missing indexes may result in performance problems. See 930768 and 931333.
b) Causes in the application
If indexes or aggregates are missing, this will impair the response time of your BW system. The selected data model can also be responsible for poor response times.
6. What should I be aware of when data modeling my BW system?
The selective characteristics in an InfoCube should be defined as a line item dimension, if possible. You should define characteristics that have many attributes but uneven distribution as a line item.
If possible, you should create flat aggregates. An aggregate is only useful for queries if the data can be aggregated to a great extent.
7. What should I be aware of when setting up structures?
You should be aware of the following points when setting up structures:
- the data is transferred using INSERT....SELECT
- Large datasets are processed in blocks.
- You can set the block size in transaction RSCUSTV8. The default value for the block size is 10 million. You should only change this default value if aggregates with a low level of aggregation are defined on large cubes.
- The parallel creation of several aggregates can be inefficient because this may result in data cache displacements. Indexes are created after the aggregates are filled. If more than one CREATE index is active at the same time for MaxDB, only one CREATE index can be processed in parallel with several server tasks.
8. Are there special parameter recommendations for a BW system with MaxDB?
See 767635 (MaxDB 7.4) and 814704 (MaxDB 7.6) for general parameter recommendations for MaxDB. See 901377 for special recommendations for BW systems.
See also 514907 for information about settings for the RSADMIN parameters. For MaxDb, you should set the RSADMIN parameters as follows:
SPLIT_DATAMART_TABL_THRES <= 64 (MaxDB Version 7.5)
SPLIT_QUERY_TABL_THRES <= 64 (MaxDB Version 7.5)
SPLIT_DATAMART_TABL_THRES <= 254 (MaxDB Version 7.6)
SPLIT_QUERY_TABL_THRES <= 254 MaxDB Version 7.6)
9. Is the data cache hit rate important for analyzing the performance of a BW system?
The data cache hit rate alone is not enough to analyze the performance of a BW system. You must also consider the absolute I/O accesses (transaction DB50). In addition, the area that is occupied temporarily should not exceed the size of the data cache.
10. What should I know about parallel access to joins?
You activate the parallel index using the OPTIMIZE_JOIN_PARALLEL_SERVERS parameter (see 901377).
The parallel importing of index blocks can greatly improve the performance for join transfers that have to be processed using an index.
Several server tasks are activated when index blocks are imported in parallel. In the B* tree of an index, the corresponding primary key list of the records for the Basis table is stored for each index entry. The primary key list is no longer processed sequentially using a server task; instead, it can be processed in parallel using several server tasks.
For example: Server task 1 reads the first key entry and retrieves the corresponding data page of the Basis table in the data cache; server task 2 reads the second key entry and so on.
11. What are the naming conventions for BW tables?
BW tables created by SAP have the namespace /BIO. BW tables created by customers have the namespace /BIC.
12. What types of tables are contained in the BW system?
A BW system contains fact tables, dimension tables, master data tables and hierarchical tables.
13. Are there temporary objects in BW?
Yes, see 449891 for more information.
14. How will I be able to recognize the table type?
After the /BIO or /BIC prefix, fact tables begin with 'F' (the data is not compressed) or 'E' (the data is compressed), for example: /BIC/FZBCS_BC01.
Dimension tables begin with 'D' after the prefix, for example: /BIC/DZBCS_BC017.
After the prefix, master data tables begin with 'S', 'X' (time-independent), 'Q' (time-dependent), 'Y' (time-dependent) or 'P' (time-independent), for example: /BI0/SCS_VERSION.
Hierarchy tables begin with 'H' after the prefix, for example: /BIC/HZBCS_FIK.
15. Is there a naming convention for alias table names?
Yes. BW contains very complex table names. Aliases are provided for the tables to define the analysis of the commands more clearly.
The alias 'F' is assigned for fact tables.
For dimension tables, the alias starts with 'D' followed by the following dimension identifiers:
- 'P' for the package dimension (for example, alias 'DP')
- 'T' for the time dimension (alias 'DT')
- 'U' for the unit dimension (alias 'DU')
- Numbers are used for user-defined dimensions [1-9, a, b, c, d] (for example, D1, D9, DA, and so on.)
For the master data tables, the aliases begin with 'S' followed by a number, for example, alias 'S34' for the /BIC/SZBCS_FIKO master data table.
16. Do fact tables have a user-defined primary key?
Up to Version 7.5, no. Up to this version, fact tables have a SYSKEY rather than a user-defined primary key. The primary key (and consequently the secondary indices) would become too large because the key would have to be created from the fields of all the dimension tables. In addition, fact tables ('F') that are not compressed cannot contain duplicate records - these would be rejected with a 'Duplicate key' error if a primary key were defined.
As of the MaxDB BW Feature Pack (Version 7.6), it makes sense to create a primary key in fact tables. In this version, the primary key is created from a time dimension and the SYSKEY, not from the fields of all the dimension tables.
For more information, see 1040431 FAQ: MaxDB BW Feature Pack.
17. The fact tables in my BW system with MaxDB 7.5 mistakenly have a user-defined primary key. As a result, the 'Duplicate key' error occurs when I add new records. What can I do to correct this?
After you convert tables in the SAP ABAP Dictionary (transaction SE11 or SE14), fact tables are mistakenly given a primary key. In the BW system, you are not allowed to convert tables using the SAP ABAP Dictionary; instead, you must use the BW functions to convert these tables (create cube and copy data). You can use 897165 to correct this problem or you can import the relevant Support Package to avoid it in the future.
18. What is the maximum number of dimensions that I can create for a fact table?
You can define a maximum of 16 dimensions.
19. How is the relationship between the fact table and its dimension tables formed?
The fact table contains all key fields of the associated dimension tables in its table definition. the following naming convention here: The key fields of the dimension tables in the fact table begin with the 'KEY_' prefix followed by the short form of the name of the fact table, and end with an identifier for the dimension. For example, the 'P' indicator means package dimension, 'T' is the time dimension, 'U' is the unit dimension, or a sequential number may be used to specify other dimensions.
For example: The following dimension tables have been assigned to the /BIC/FZBCS_BC01 fact table: /BIC/DZBCS_BC01P, /BIC/DZBCS_BC01T, /BIC/DZBCS_BC0U, /BIC/DZBCS_BC011, /BIC/DZBCS_BC012, /BIC/DZBCS_BC013, /BIC/DZBCS_BC015, /BIC/DZBCS_BC016, /BIC/DZBCS_BC017, /BIC/DZBCS_BC018.
The column names are created as follows:
KEY_ZBCS_BC01P: represents the reference to the package dimension using the DIMID lead column of the /BIC/DZBCS_BC01P package dimension.
The same applies to the following columns: KEY_ZBCS_BC01T, KEY_ZBCS_BC01U, KEY_ZBCS_BC011, KEY_ZBCS_BC012, KEY_ZBCS_BC013, KEY_ZBCS_BC015, KEY_ZBCS_BC016, KEY_ZBCS_BC017, KEY_ZBCS_BC018.
The relationship of the tables to each other is defined in the JOIN condition of the query. Single indexes are defined on all dimension fields of the fact table.
Special feature of aggregates: They have names that are partly the same names as those in the corresponding fact table.
20. How is the relationship between the dimension table and its master data tables formed?
The columns of the dimension table that form a relationship to the master data table are defined as key fields in the master data table.
To ensure that the assignment of master data tables to the dimension table is immediately apparent for a performance analysis, the dimension table is sorted and stored in the FROM clause, followed by its master data tables.
For example: SELECT .....FROM
/BIC/FZBCS_BC01 " "F" -> Fact table
/BIC/DZBCS_BC01T " "DT" , -> Dimension table
/BI0/SFISCVARNT " "S44" , -> Master data table
/BI0/SFISCYEAR " "S45" , -> Master data table
For example, the S44 master data table has the FISCVARNT column as a key field. The S45 master data table has the FISCVARNT and FISCYEAR columns as its keys (columns that are contained in the dimension table as SID_0<NAME>, for example, SID_0FISCVARNT and SID_0FISCYEAR).
The relationship of the tables to each other is defined in the JOIN condition of the query.
21. What is a line item dimension?
Line item dimensions have only one attribute. This means that an interim step using the dimension table is not necessary when the fact table accesses the master data table.
22. How do I know whether I can optimize a query in the database?
Create a SQL trace of the query that causes performance problems. You can use the SELECT statement from the SQL trace to execute an EXPLAIN either directly in the SQL trace or in the SQL Studio.
The EXPLAIN may display a selection strategy where a very high number of dimension tables are processed before the fact table is accessed. This is only useful if just one record is accessed (EXPLAIN JOIN) when the dimension tables are accessed. However, this is rarely the case with large BW systems. Therefore, you could say that if more than 3 dimensions are accessed before the fact table, this query can be optimized over 2 or (at most) 3 suitable columns (characteristics) on the fact table with a multiple index.
23. Is it useful to create a multiple index on the fact table using the fields of different dimensions?
If several restrictions on different dimensions are relatively unselective in queries, a multiple index on the corresponding dimension fields can improve the performance. A multiple index should generally be defined on two or a maximum of three dimension fields.
The data in the fact table is sorted (by the SYSKEY) and therefore stored according to the sequence of inserted records. Since the data is loaded on time into the BW system, records with time characteristics are close to each other, which means that (when data is read using a time characteristic) you can expect a lot of hits on a data page - this in turn means that fewer pages have to be loaded into the data cache. The MaxDB optimizer does not have this information.
It can therefore be more beneficial to select data using time characteristics, even if other characteristics are more selective.
You can optimize access by creating a suitable multiple index on the fact table that contains a time characteristic and the selective characteristic.
24. In which column sequence should I create multiple indexes on fact tables?
The sequence of the indexed columns in the index is a crucial factor if you want to create a multiple index on a fact table. You should therefore consider the following when choosing the sequence:
- if the OPTIMIZE_JOIN_PARALLEL_SERVERS parameter is activated (>0), you should place the more selective column in the index in front of the time characteristic. This is necessary because of the way that the PARALLEL_SERVERS parameter works (see above in this ).
- if the parallel access is not activated for the join (OPTIMIZE_JOIN_PARALLEL_SERVERS=0), you should place the time characteristic in the index in front of the more selective column.
- if you want to create a multiple index on two characteristics and one of the two characteristics is restricted with a BETWEEN, the column where the BETWEEN qualification is specified should appear at the back in the index.
25. When can secondary indexes be counterproductive on fact tables?
The data in fact tables is stored in chronological order (due to the SYSKEY). Fact tables are usually very large and therefore cannot be loaded completely into the data cache. If a time period is read in a query, it may be useful to create an index using less selective columns that contain time data (for example, an index using columns that contain periods or fiscal years). However, the optimizer program will only choose this type of index if it assumes that a more selective index does not exist. If an index of more selective columns exists for this query but the index has no chronological reference, the optimizer will select this index because it does not know anything about the favorable storage format of the index that contains time-related data.
As a result, more pages might have to be loaded into the cache than the number of pages loaded when (from where the optimizer is concerned) the least favorable index is used.
You should bear this in mind when creating indexes.
If you do notice this behavior, you should not deactivate any standard indexes without first consulting MaxDB Development Support. A better course of action in this case would be to consider whether a suitable multiple index can achieve the required effect on the fact table.
26. What are SID tables?
SID is an abbreviation of the German "Stammdaten-ID" (which means master data ID in English). SID tables are master data tables.
27. What must I be aware of with master data tables?
Fields in master data tables are usually not indexed.
If selective filter conditions are defined on master data fields in a query, these fields must be indexed. Otherwise the database optimizer ignores the restrictions.
Therefore, when analyzing the performance of your MaxDB BW system, you should check whether you can create suitable SINGLE indexes to solve the performance problem. The qualification of a selective column of a master data table in the WHERE clause, which can considerably limit the result set, is a crucial factor in deciding whether you should create a SINGLE index.
28. What does the /*+ SHORT_SUM_VALUES */ hint mean in BW queries?
This hint is not relevant for the strategy and therefore does not have any effect on the query processing sequence. This hint ensures that, when the totals are being formed using the columns, the actual length of a numeric field is used for the calculation rather than the maximum field length.
For more information, see 723020. In relation to the hints, see also s 652096 and 832544.
29. What is the significance of the hint /*+ QUERYREWRITE_OP */ in BW queries?
As of MaxDB Version 7.6 and New York (BW 7.10) this hint activates the use of QUERYREWRITE in queries, including with the parameter setting OPTIMIZE_QUERYREWITE < > OPERATOR. For additional information, see 832544.
30. Where can I find information about updating the statistics in the BW environment?
See s 927882, 808060 and 797667.
31. Can I prevent temporary views from being deleted when a performance analysis is being carried out?
Temporary views that no longer exist after the SQL statement is processed are also partially created in the BW environment and therefore cannot be used for a performance analysis (EXPLAIN).
You can prevent temporary views from being deleted by making an entry in the RSADMIN table. See 373738.
As an alternative to the procedure described in 373738, you can also create a SQL trace (transaction ST05). This SQL trace contains the CREATE view statement of the temporary views. You can create this view in SQL Studio and then execute the Explain on the corresponding SELECT.
32. How can I delete temporary objects that stop by mistake in the BW system?
You can run report SAP_DROP_TMPTABLES to remove temporary objects. (See 449891).
33. How can I solve performance problems with temporary /BI0/06... SID tables?
In older Support Packages, temporary SID tables with the /BI0/060... naming convention do not have primary keys. With a BW Query, this can cause the database optimizer to calculate an unfavorable table sequence.
For more information, see 759936.
34. Is it useful to check whether indexes are missing after the transfer from the fact table to the dimension table?
No, it actually makes no sense to carry out a more detailed check because the corresponding indexes are already created here. This means that you do not have to check the join conditions of fact tables to the dimension tables in the WHERE clause more closely.
35. Where do indexes usually go missing in the BW environment with MaxDB?
Indexes are usually missing from master data tables if a selective column that can significantly restrict the result set was qualified in the WHERE clause.
Since there is no relation between dimension tables, it can be useful to create a suitable multiple index on the fact table if two restrictions on different dimension tables were specified in the WHERE clause.
Get More Questions and Answers with Explanation related to SAP MaxDB at SAP BW Forum.