BWA Utilities – Part 2

Author(s): Anil Kumar Puranam and Peesu Sudhakar

Company: Deloitte

Created on: 24th December, 2014

 

Authors Bio:

Sudhakar Reddy Peesu is a PMP certified SAP Technology Manager in Deloitte Consulting. He has more than 16 years of experience leading SAP BW on HANA, BW 3.x/7.x/BPC/ABAP implementations, custom applications, and developing IT strategies covering all phases of the SDLC using onsite/offshore delivery model. He has strong communication, project management, and organization skills. His industry project experience includes Banking, Consumer/Products, and Public Sector.

 

Anil Kumar Puranam is working as a Senior BW/BI Developer in Deloitte consulting. He has more than 10 years of SAP BW/BI/BO experience. He has worked on various support/implementation projects while working in consulting companies like Deloitte, IBM India, and TCS

 

.

Concept for Enable/Disable the BWA Delta Index for InfoProviders

 

Normally, when the transactional data is loaded on certain frequent basis with delta capability, the InfoCube data is rolled up to the BWA indices for BWA activated cubes. And this is added to the main delta index file/table location. In this process the TREX standard program runs the BWA delta optimization process, during which it may take more than normal time to complete the optimization when the index size is huge (in high millions) and growing and this also lead to high system resource utilization (mainly in BWA server). During this time any reports executed by the reporting users may receive TREX InfoCube lock message and data is not available for reporting. In such cases, the BWA Delta Index will help to avoid the report interruptions. The delta index is a separate index (we may call as temporary index) that TREX creates in addition to the main index for the same InfoCube; the main index and its delta index are internal separate index locations and both location data is read for reporting as if it maintain a single index. We have the option to pick and choose the InfoCubes (which have huge volume and more frequently loaded throughout the day) to ENABLE the delta index. When we enable the delta index, means the additional data loads for that particular cube will be to the new (delta) index location so the optimization process will be faster as we will have fewer records. The delta index records will MERGE to the master index at frequent interval during the quiet time for reporting (after normal business hours) at which point in time the delta records are appended to the master and optimizes the index.

Identify the InfoProviders to Build the BWA Delta Indexes (Standard Method)

 

The BWA Delta Index for a InfoCube is very useful for cubes holding huge volume of records and get the data more frequently (say, every one hour or two hour interval) as delta. Here the Delta Index helps to avoid the additional time taken for optimization process of main index with each roll up of the delta load.

SAP provided the option to run standard transaction code to determine proposals from the BWA statistics data. The resultant 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 Info Cube are switched on.

 

• Use RSRV transaction code and option ‘Propose Delta-Index for Indexes’ to get the information about the need of Delta Index.

Identify the InfoProviders to Build the BWA Delta Indexes (Using the ABAP Program)

 

Utilized the standard function modules and developed a custom ABAP program ‘YBW_SYSCHK_BWA_PROP_DELTA_INDX’ with email alert capability to get the proposal of Delta Index (attached the program code below)

 

The custom program selection screen –

                                                                               

 

Note: In case you want to schedule the program to run automatically at the regular intervals, make sure Broadcasting settings are enabled in the system to send the report to the corporate outlook inbox.

Steps to Enable/Disable the BWA Delta Index for Info Providers (Standard Method)

  1. Go to transaction code ‘RSDDB’

2. Select ‘Goto’ menu -> choose BWA Monitor as shown below

 

 

 

3. Select ‘BW Accelerator’->Index Settings->Set Delta Index

 

 

 

4. Select the check box ‘Delta Index’ column for the identified F-table of the InfoCubes.

 

 

 

Steps to Enable/Disable the Delta Index for InfoProviders (Using the ABAP Program)

We may have requirement or a need to enable and disable delta Index capability dynamically depending on certain circumstances, such as:

 

• The Cube data may need to drop and reload to resolve/fix the data inconsistency issues – here we need to disable the delta index before BWA initial fill and enable the delta indexing
• We may have more granular partition of cubes, say, quarterly/monthly and the requirement is to activate BWA for latest two quarters or months, in which case we need to dynamically disable the delta for older partitions and enable for the partitions holding the latest periods.
• Cases where we have requirement to run delta more frequently for certain days in a month and later only once a day is good for reporting; here we may want to disable outside the window of frequent data loads to optimize the system resource utilization

 

In these scenarios, we can develop and use a custom ABAP program to dynamically enable and disable the delta indexes. Note that, we need to make sure we have completed the MERGE of Delta Index data to Main Index before disabling the Delta Index.

 

ABAP Program ‘YBW_DELTA_INDEX_SET_RESET_BWA’ is developed to the customer needs. Here we have selection screen option to select Enable or Disable the Delta Index by passing the desired InfoCube technical names. We need to mention the F table of the cube, since the BWA is built with the F-table name (for example, /BIC/FZIT_C101 is the fact table name for InfoCube ZIT_C101).

Selection screen for the Program:

 

Merge the BWA Delta Indexes to the Main BWA Index of the InfoCube

We have 2 ways to do the Delta index Merging into main BWA :

• Using RSRV Transaction code: BI Accelerator-> BI Accelerator Performance Checks-> Size of Delta Index elementary test-> choose ‘Correct Error to access repair mode’ and execute a MERGE for the indexes
• Using ABAP Program: Run standard ABAP program ‘RSDDTREX_DELTAINDEX_MERGE’ – Select the InfoCube(s) and check the “Edit All Delta Indexes’ checkbox before running the program. Successful completion of the program means the delta BWA index is written (merged) to main index and reset/clear the delta index.

Note:

1. The Delta Index need to be enabled after initial fill
2. Enable delta indexing, when the main delta index reaching to 100,000 to 1,000,000 documents or 500 MB (benchmark numbers and this not a hard rule)
3. Data in the Main Index and Delta Index should be merged at regular intervals (maybe end of each day, nightly) in the repair mode to avoid the delta index reaching more than 10% of main index

Enable and disable the BWA for reports

We may come across a situation where the BWA index for a cube or cubes got corrupted and need time to diagnose the issue to roll up the delta data loads to the BWA. During this time and to minimize the interruption for reporting, we can disable/deactivate the cube reading from BWA and go to the fact table of the cube. We can go to the BWA monitor to disable a cube or cubes or use the custom program ‘YBW_BWA_SWITCH’ developed to switch the BWA availability for a specific cubes. This program will help us to switch for huge number of cubes for a MultiProvider. If there are any corruptions or issues with BWA, and if BWA needs to be switch off/on temporarily based on MultiProvider, so the query can continue to work by fetching data from database instead of BWA, the program ‘ZBW_BWA_SWITCH’ can be used.

 

Masterdata Demon Job (BI_BIA_NEWMD_INDEX):

When transaction data (first time loading or delta) is rolled into BWA, new Masterdata brought in through these transaction loads, get loaded to BWA through Masterdata demon job. The Masterdata demon job is scheduled to run frequently in the system with the name ‘BI_BIA_NEWMD_INDEX’. Default frequency of this MD demon job is once every 3 minutes. Between the two Masterdata demon jobs, new masterdata is brought in through rollup process, it gets stored in table RSDDTREXNEWSID. The next MD demon job moves these new MD values to BWA and then removes entries from table RSDDTREXNEWSID.

 

Also when rollup process is started, it completes only when these new masterdata brought in are get rolled into BWA through MD demon jobs. If there are many new masterdata is brought in, then MD demon job could take longer to finish. While MD demon job runs, BWA rollup process waits for certain time based on BWA Setting. If MD demon job runs longer than this configured wait time, rollup process fails.

• For more info on MD demon job refer sap note 1974787
• The Masterdata demon job need to be scheduled to run on frequent basis in order to transaction data rollup job to run and write to the cube indexes

 

Additional important BWA Parameters

 

Here are additional important BWA Parameters that we will come across often – in fact the below details are available on BWA help but at different places. Here we are consolidating important parameters for easy access. Though it provides info on changing the standard settings but the optimal setting can be arrived in discussion with the BWA server (BASIS) administrators.

 

1.     BATCHPARA

Used for number of parallel batch processing in the initial BWA indexing. We can increase the value from default value # 3 for a big cube indexing, but it dependents on BWA Configuration and also increasing too much can lead to Stability issues. The optimal setting can be arrived in discussion with the BWA server administrators

2.     NUMPROC

Used for number of parallel batch processing using aRFC dialog processes in the initial BWA indexing. We can increase the value from default value #5 for a big cube indexing, but it dependents on BWA configuration and also increasing too much can lead to Stability issues. We can specify different value for this parameter to other number than default value while indexing a Cube. The optimal setting can be arrived in discussion with the BWA server administrators.

 

If no more dialog processes are available when the new dialog processes start system-wide (or within the RFC group), the system still waits for 20 x 5 seconds before the process terminates with the message “RESOURCE_FAILURE: 19” (Note 961403).

 

The maximum number of background processes required is calculated from (the number of BIA index processes started simultaneously) * BATCHPARA. The maximum number of dialog processes is calculated from (the number of BIA index processes started simultaneously * BATCHPARA) * NUMPROC. However, the number of dialog processes actually required is generally lower because the processes are rolled out for the RFC for the BIA.

 

3.     PKGSIZE

The package size in bytes for internal tables, during indexing, using aRFCs. We can increase the value from default value (100,000,000) for a big cube indexing but it dependents on BWA configuration and also increasing too much can lead to stability issues. The optimal setting can be arrived in discussion with the BWA server administrators

4.     SUBPKGSIZE

Package Size (Rows) for EXPORT TO BUFFER, during indexing, using aRFCs.We can increase the value from default value (20000) for a big cube indexing, but it dependents on BWA Configuration and also increasing too much can lead to Stability issues.

5.     PARASIZETHRESHOLD

Number of lines as of when a table is divided up into batch processes. The default value is 1 million lines; for smaller tables it is not usually worth the effort to divide up the tables and start various batch processes. As it is shown in the Part 1, we can split the Initial fill of BWA into Multiple blades.

6.     SPLIT_TYPE

During initial indexing of an InfoCube, the two fact tables (E and F table) are divided into ‘n’ disjunction parts once a certain size is reached. The ‘n’ is the value of the parameter for the batch variant. The split mode determines how these blocks are formed. We can specify different value for this other than default value while indexing a Cube.

7.     MD_JOBFREQUENCY

Frequency (seconds) of the BIA master data delta daemon. The BIA master data delta daemon checks whether the new BIA-relevant master data was uploaded to the system. If yes, this data is indexed automatically.

8.     DEMON_APPSERVER

You can specify an application server for the BWA master data daemon job (BI_BIA_NEWMD_INDEX) on which you want to execute the job.

 

Important note: This bypasses the automatic work distribution and we therefore recommend it for exceptions only. To generate a list of possible application servers, exerun the function module TH_SERVER_LIST with services = 08, sysservice = 00, active_server = 1, subsystem_aware = 1. The table LIST contains the possible names in the NAME column.

9.     FLOAT

Before Support Package Stack 8, a FLOAT point number is saved in the BIA server as an actual FLOAT point number, while it is considered as a “DOUBLE” number in ABAP (that is an 8-byte-figure). This storage method is used to optimize the memory consumption in the BIA server but it increases rounding differences.

 

As of Support Package Stack 8, you can specify in the BI system whether you want to save key figure of the “FLOAT” type in the BIA server as an actual FLOAT point number or as double numbers (with correspondingly higher memory consumption and a minor decrease in performance).
You can make this setting only for all BIA indexes together. Note that if you change this setting, you must delete BIA indexes for InfoCubes with FLOAT key figures and then recreate them with the new setting. The setting is displayed in the BIA monitor and you can only change it there. The default setting is the existing setting, that is, the actual FLOAT in the BIA server.

 

10.   BIALOCKWAIT:

If you restart the BI accelerator (BIA) server in the BIA monitor, the server is not available for a short time. Processes that index data in the BIA terminate and you have to start them again. When you import Support Package Stack 12, before each call of the BIA for the indexing processes (initial filling, roll up, change run, delete request), the system checks whether the lock for restarting the BIA server is set. If there is a lock, the system waits until the lock has been released and then continues indexing. Only if the specified wait time is exceeded, does a termination occur. The default wait time is two minutes. If you want the system to wait longer, execute the RSDDTREX_ADMIN_MAINTAIN program with object = BIALOCKWAIT, value = n (n integer value in minutes) and INSERT = X. If you enter “-1”, the system does not wait, rather a termination occurs immediately if there is a lock.

11.   DBFALLBACK_TIME

By default, you must wait 30 minutes until a query tries to read data from the BIA again – unless the table entry was changed manually. You want to change this default time. You can do so with this program enhancement. Execute the program RSDDTREX_ADMIN_MAINTAIN with the following values: Object = DBFALLBACK_TIME, Value = , INSERT/UPDATE = X.

 

The new optimization has been in place from 7.3. So When BWA is down and a query is executed, we insert an entry into table RSDDTREXHPAFAIL. Just after insertion, a batch job is triggered (with name, BI_BIA_DBFALLBACK_CHECK) which does a ping to BWA every 5 seconds to check if BWA is up. If so, iw deletes the entry from table RSDDTREXHPAFAIL and the job stops. If the BWA is down for more time, then the batch job runs for a maximum of 1 hour and then stops. The batch job also stops when the entry in table RSDDTREXHPAFAIL is deleted because of reaching the ‘DBFALLBACK_TIME’.

 

‘DBFALLBACK_TIME’ is still in place, which can help you to control the Fallback, in case you know how long you expect the BWA to be down.

 

12.   NOBIANOQUERY

No query executions if SAP HANA/BWA index not available, if the value for this parameter is ‘X’. If it is blank Query execution goes to Data base level. Query execution goes to Data Base level if the value is Blank, even for all 3 below cases:

1.     Certain users should read the data for queries from the database (InfoCube, aggregate) and not from the BI accelerator BIA

2.     If a BIA index has the status “inactive” (it cannot be used for queries), the data should NOT be read from the database, rather the system should only issue a corresponding message to inform the user

3.     3. If a navigation step on the BIA server terminates because the data volume that is read is too large (standard value = 3.000.000 records), the system should not terminate, rather it should issue a corresponding error message.1.

 

 

13.   QUERY_ERROR_ALERT

It provides notification Type with Database Fallback.

• • Value = ‘CCMS’: Instead of an e-mail, the message arrives in the CCMS only,
  • Value = ‘BOTH’: An e-mail is sent and the message arrives in the CCMS,
  • Otherwise: Only an e-mail is sent.

 

14    FLATFACTOR

Default value (20%) for large relationships. The size (number of lines) of the fact tables are compared with the size of the dimension or SID table (for line items). If the relationship exceeds the limit value, a partition attribute is created and this ensures that the JOIN performs well when it is executed locally. If two tables are largecompared to the fact table, the fact-dimension schema is de-normalized and a wider fact index is created in the BIA instead. This type of fact index is called “flat”.

 

15.   DONT_IGNORE_BIA_OVERLOAD

 

If we load too much data into SAP NetWeaver BI Accelerator (BIA) or if the user load is so high that the temporary memory consumption becomes too large, this may affect the stability of the BIA server and lead to queries terminating. By default, this message is only a warning message in all processes that index large volumes of data in the BIA. You can turn this warning message into an error message by specifying certain parameters. If this error occurs, all index processes are terminated. To do this, execute program RSDDTREX_ADMIN_MAINTAIN with the following parameters: Object = DONT_IGNORE_BIA_OVERLOAD, value = X and INSERT/UPDATE = X.

16.   USED_TO_TOTAL_MEM_RELATION

Ratio of “Memory consumption of loaded data” to “Available main memory”.

The index data in the filer must not exceed 50% (Default) of the available main memory. We can also find the memory consumption of the loaded data in the TREX administration tool using the BIA monitor on the tab page “Index Administration” in the column “Memory size [KB] (total)”.

17.   ALLOWED_UNLOADS_COUNT

The alert ‘BIA overloaded’ depends on memory unloads and the relation of disk usage and physical memory per blade. If the number of unloads is above a certain threshold (default value 200) you get the alert. The parameter ALLOWED_UNLOADS_COUNT is the BW parameter to control this threshold.

18.   COMP_SIZE_TLD

 

Limit value for the difference in size between fact tables and fact indexes. The default is 5%. If the limit value is exceeded, a warning is issued in the check.

 

19.   WAITTIME and WRITEINDEX

 

The master data and master data ID (SID) indexes in the BI Accelerator can be used by several BIA indexes. Rolling up data to BIA indexes from different InfoCubes and change runs may result in an attempt to write new data to the indexes simultaneously. A lock situation occurs since this is not possible on the BIA server.

 

Message RSD_TREX 041 “Loading into index for table ‘&1’ locked by competing job” and message RSD_TREX 042 “InfoCube of competing process: ‘%&1′” are then displayed in the application log.

 

You must then wait before the system tries to index this data. Only when this fails does the whole process terminate, and you have to start again. Default values are: Wait time between attempts = 30 seconds, number of attempts per index = 10. In the default case, you must wait a maximum of 10 x 30 seconds (5 minutes).

 

You can increase both values. To do this, execute the program RSDDTREX_ADMIN_MAINTAIN with the following parameters:

Object = WRITEINDEX, Value =

Object = WAITTIME, Value =

 

20.   BIA_NOTIFY_EMAIL_ADDRESS

The system sends all messages of the alert server of the BI Accelerator to a specified e-mail address. You can schedule the job by choosing “BIA Checks” in the menu of the BI Accelerator monitor.

21.   BIA_NOTIFY_EMAIL_ERROR_ONLY

If a value of EMAIL_ERROR_ONLY is assigned to this object, the e-mail is sent only if there is an error.

22.   SPLIT_ROUNDROBIN Parameter

BATCHPARA says the number of Logical blocks of data that are read and indexed in separate background processes. This parallelization is available only for the fact data (F table and E table for InfoCubes) and only during initial indexing.

 

Before the first data package is read, logical disjunction blocks of characteristic/field attributes are created using the characteristic/fields of the data source. For E fact tables, this is the partitioning characteristic or the most detailed time characteristic, for F fact tables, this is the request ID. There are two algorithms for determining the blocks. During the initial indexing in the BI accelerator server of an InfoCube, the blocks for parallel processing in batch jobs may not be optimal if the data in the fact tables is not distributed equally. For example, there is a very large request or many small ones in the F fact table, or there are posted values for many days in the E fact table, but most of these are only for a short period of time.

 

By default, the system forms intervals with the same amount of values using the block column of the table. Each interval constitutes a block and therefore a batch job for the indexing. If the data in the fact tables is not distributed equally (as described above), the blocks may not be optimal, that is, some may be large and some may be small. Parallel processing cannot handle these optimally.

 

You can continue to form blocks as described above, but you can also form blocks as follows: The different values in the block columns are distributed amongst the blocks using a “round robin” procedure. This is likely to distribute the data better in the examples described above. The procedure is a system-wide parameter. To implement it, execute program RSDDTREX_ADMIN_MAINTAIN with object = ‘SPLIT_ROUNDROBIN’, value = ‘X’ and INSERT = X.

 

Restriction: The second algorithm only works if there are less than 1000 different values in a block column. This means that large “in lists” do not have to be created during the read statement.

 

23.   ROLLUP_WAIT Parameter

 

Rollup terminates due to lock on the concerned InfoCube as other processes (say a change run) are executed on it. We have the option to set a Wait-time for a Rollup which waits for certain number of minutes. To do this execute the program (transaction SE38) SAP_RSADMIN_MAINTAIN and select the OBJECT = ROLLUP_WAIT, VALUE = and INSERT Parameters.

 

This will set the time until which the rollup waits to acquire the lock set on the InfoCube by other processes. If the lock held by the other process is released within the specified wait time, the rollup continues. If the wait time is exceeded or if wait time is not set at all the rollup terminates due to the lock.