How to properly design process chains

There are sometimes doubts regarding how to set up processes within the process chains. Process chain is structural and logical grouping of processes , jobs or steps involved within loading of data into BW. Proper design of chain has effects on its performance and consequently on performance of whole BW system. Therefore kind of best practice I can recommend following sequence for designing process chains:

a.    Delete Index

b.    Data Transfer Process / Execute InfoPackage – optimally in parallel

c.    Generate Index

d.    Construct Database Statistics

e.    Compress cube

f.     Delete PSA (aged data requests)

g.    Delete Change Log (aged data requests)

Here’s quite obsolete picture from SAP presentation material depicting loading process in BW 3.0.

Grey rating for Earlywatch Alert report

Sometimes it is common situation that you receive the latest Earlywatch Alert (EWA) report from SAP and some of checks included have grey ratings. Usually there are suppose to be just three types of ratings:

Green – no major problems with systems observed, hurray :-)

Yellow – improvements to the system were detected

Red – severe problems were detected, you should take corrective actions immediately

Then what is going on while grey traffic lights are delivered? Simply this means that automated system that was generated this EWA report had no enough data to analyze the system. E.g. situation occurred in your system that it wasn’t recognized, connection into system was down, system as itself was down etc. There is an SAP Note discussing such situations here:

Note 762696 - Grey rating for Earlywatch Alert

Where to see code for Business Content SAP exit variable?

SAP exit variable are type of BEx variables processed during BEx Query runtime by replacement path predefined in SAP exit. SAP exit is like customer exit it is just delivered by standard SAP BW Business Content. Comparison to customer exit is right since there is also ABAP code which is in particular performing the replacement of variable value.

This might seems to be tricky for first look: Where to see ABAP code for BEx variables a type of SAP exit? Actually again comparing to user exits variable it is stored in ABAP repository but in include just in function modules:

In our case for variable 0P_SYSID there is the function module called RSVAREXIT_0P_SYSID where all the variable logic is coded:

update - 27-09-2012-

Older business content variables (e.g. 0CWD) are coded in FM RREX_VARIABLE_EXIT. 

Difference between DB and BW statistics

Terms like database (DB) and BW statistics are often referred in SAP documentation, Notes or even in SDN forums. We always hear that it is important to maintain statistics in order to achieve good performance of SAP systems. Especially in BW area performance topic is very important. Here it is often discussed that we must keep the DB statistics all the time for SAP BW. Then we hear that BW statistics mull be keep up to date as well. You can get easily confused with mixing a meaning of those two terms.

So basically what DB statistics is all about? Underlying database systems are using sophisticated algorithms to determine “a path” how to execute SQL statements. In detail a path is comprised of type of DB access (index access, full table scan, join types, memory requirements, table stats, index stat, column stat, histogram, partitioning, parallelism, clustering, etc.) how particular SQL statement is performed. In order to determine it DB engine is evaluating DB statistics which is basically about evaluation of how many I/O DB operations would take to perform DB statement with different DB access. The DB access with the fewest I/O DB operations is then executed to fulfill SQL statement. DB statistics is depended on type of used DB platform. E.g. in ORACLE there is a component of DB engine called Cost-Based SQL optimizer (CBO). Therefore sometime we call this type of statistics as an Optimizer statistics. This tool is in charge of SQL statement execution governing. This component is evaluating DB statistics. On other DB platform such a component is commonly called Query optimizer.

Now the questions would be how can we build DB stat? DB statistics can be build from BW perspectives within process chains via process called “Construct Database Statistics”. From basis point of view most common is to use BRTOOLs to build this statistics in case of ORACLE DB. Another option is to use transaction DB20 or report RSANAORA.

For more information about DB statistics see SAP Note 588668 - FAQ: Database statistics.

What about other term - BW statistics? It is a part of technical Business Content which helps to evaluate performance of SAP BW system. There are two main areas of performance evaluation: OLAP processor and Warehouse Management (WM).

Within OLAP processor gives an information how BW reporting is performing. A way how statistics data is collected If is like following. Every navigational step within the reporting that requests data from the database is saved in transparent table in BW backend (tables RSDDSTATHEADER, RSDDSTATINFO, RSDDSTATEVDATA). This includes characteristics, navigation attributes and hierarchies were involved. Here an event concept is used. Event is uniquely identifying each step in the process. From DB tables statistics data are loaded into BW statistics InfoCubes via technical process chains.

In case of WM statistics or Data Load statistics it is determining performance of loading, transforming the data and all the others staging operations. In principle system is gathering the data about process chains, DTPs and InfoPackages. Here e.g. time of execution is stored along other data load statistics data. Table where data is written is RSDDSTATWHM. WM statistics is customized via transaction RSDDSTAT.

BW statistics is a part of BI Administration Cockpit. See 934848 - Collective note: (FAQ) BI Administration Cockpit, for BW 3.x see: 309955 - BW statistics - Questions, answers and errors.

Notice that BW statistics was redesigned in BW version 7.x and it does use different tables comparing to BW 3.x.

More information:

588668 - FAQ: Database statistics

555030 - Deactivating BW-initiated DB statistics

1013912 - FAQ: Oracle BW performance