Thursday, November 3, 2016

Further checking of broken BEx queries 3 – performance

With regards to BEx query performance it is beneficial to know where is a bottleneck during the query execution. This is especially useful while investigating cases where one user is complaining about the query whereas for other it runs okay.

Wihtin t-code RSRT there is a quick way how to display statistics information about date of query runtime. You can switch this on after hitting “Execute + Debug” button.

RSRT -> enter query -> Hit "Execute + Debug" button -> new popup -> check "Display Statistics Data" -> 

After the query output there is a screen called "Statistics Data for Query Performance" displayed with 2-3* tabs (see below) having information on how much time the query execution took at different processing levels. More information about how to read the screen can be found here.

- Front-end/Calculation Layer
- Aggregation Layer
- Aggregation Layer Cluster Info
* - no of tabs may vary depending on BW system release

Intermediate result of APD process

APD or Analysis Process Designer is tool within classic BW used to model analysis processes.  The process itself has a purpose of data calculation and data transformations for an analytic application in the BW. The processes build in the tool can read and merge data from data sources in the BW system, different transformations can be connected, etc. The result data can be stored into the DSO objects in the BW.

Now while I mention saving of the results of the ADP process… Actually one can see the results w/o running whole APD leveraging of functionality of “Intermediate Results”. This is available on right click perfumed on particular process (node). The intermediate results are then stored into temporary transparent table with naming convention like: /BIC/000APR e.g. /BIC/000APR00002. The table can be viewed e.g. in t-code SE11.

Now there is handy icon appearing next to the process (node) indicating that it has the intermediate results data stored. 

In case you do not need to the intermediate results have in the table you can delete it via function called “Clean Up temporary table” available under menu Environment in t-code RSANWB.

Monday, October 31, 2016


Assertion in software development is technique used to detect and track a state in program which should not occur. In computer programming it is so called programming predicate that developer assumes that is always true. The predicate is Boolean valued function which returns a true–false expression.

Information about the predicate can be placed into comment of the program code. However it can be also directly included in the code. While the code is compiled or the compiled program runs there are checks for these predicates. These do not serve as treatment of unexpected or err nous states like user input checks. Instead it is about the detection of the status which is not supposed to happen.

Assertion or so called checkpoints define points in a program, at which the state of the program can be tested during program execution. Checkpoints are either conditional or unconditional. Conditional checkpoints are defined by ASSERT, unconditional checkpoints by BREAK-POINT or LOG-POINT. Needless to say that the checkpoint of all types are non-operational statement used just for test purposes.

Activation/deactivation of checkpoints outside of the program can be done by assigning them to a checkpoint group in t-code SAAB.

How the checkpoints or assertion is supported in ABAP programming language? There are a couple of ABAP statements for this purpose.

ASSERT statement - it is a checkpoint. It is used to verify specific assumptions about the status of a program at a specific point. The ASSERT statement is instantly recognizable for example when analysing ABAP dump.

LOG-POINT statement - is a checkpoint which creates a log entry when an active logpoint is reached. The logpoint must be assigned to a checkpoint group. The statement LOG-POINT was introduces to replace wrong usage of the ASSERT with a condition that is always wrong for simply writing log entries.

BREAK-POINT statement - is placed in the ABAP program. When it is reached in foreground/dialog processing it branches to the ABAP Debugger. If the breakpoint is set by user in ABAP editor it has limited validity - until user logs off. In case the breakpoint is hard-coded with the statement BREAK-POINT it is considered as unconditional checkpoint with an unlimited validity which is either always active or activated by assigning it to a checkpoint group.

More information:

See example of LOG-POINT statement usage:
Below see a simple log entry created by LOG-POINT statement.

Friday, September 23, 2016

Generation limits of ABAP programs

While ABAP programmers do coding of report in ABAP before it runs it needs to be activated and generated. The activation basically means that all syntax checks takes place and active version of the report is stored. Then during generating phase a load version is created. The load version is actually the one which is executed when the report runs.

You may have already observed generating phase e.g. in case if you run new t-code which wasn’t run by anyone in the system yet there is a Compiling … message shown in system bar of SAP GUI. This happens usually after upgrades when some part of SAP standard code was overwritten by upgrade. Then ABAP code that was upgraded needs to be re-generated. To speed up this process and prevent users to see this message a job can be scheduled via t-code SGEN.

However there are some limits into which the ABAP report can run into during the generation. There are limits with regards to no of global variables, generic global field symbols, Components (of structures etc.), literals, classes, interfaces, events, methods, local Data and many more. Usually no custom ABAP reports are that big that could reach these limits. However really “large” ABAPs like generated ones e.g. from SAP BW transformations which may have even 10k lines and more these may run into these limits.

To evaluate whether some report doesn’t reach into the limits you can use standard ABAP report called BC_ABAP_GEN_LIMITS.

Thursday, September 22, 2016

Pushing DTP execution to HANA DB

After migration to HANA DB one can observe a new setting on DTP screen. To be precise it is on header part of the DTP and it is called “SAP HANA execution” What it means is that in case it is checked then the DTP is executed directly in HANA DB. If it is NOT checked then the DTP is executed on ABAP AS and not directly in HANA DB. That means that the execution is not pushed to DB and there is no potential performance gains. So it simple runs like in BW on any other DB.

Naturally there are some conditions that must be met to enable this. Transformation associated with the DTP must not have any with ABAP routines because these can’t be executed by HANA but just by ABAP AS. Therefore if you have any ABAP routines that jus manipulates with the result of field like below one:


Just replace it with Rule Type Formula like:


Now imagine we got rid of all ABAP routines in the transformation and we would like to enable the “SAP HANA execution” flag. However attempt to do so gets us an error messages like:

HANA Execution: "No extraction using API" not supported
HANA Execution: Error handling not supported

To solve 1st error we need to enable another flag on the DTP. It is located at Extraction tab of the DTP and it is called “No Extraction Using API” (TREX_EXT_AGGREGATE). What it means is that even the DTP execution can be pushed to HANA it would be a case and it still will be executed at ABAP AS level. Obliviously we would like to push the DTP to HANA. So in case we switch to EDIT mode of the DTP and we attempt to thick off the settings. 

The second error message is related to error handling. This setting is available on Update tab of the DTP. And we need to set it to position “Cancel request, Do not Track records, No update”.
Once we hit “Check availability” button located next to first flag “SAP HANA Execution” (see 1st picture above) then finally the DTP will be executed by HANA as we got info message “DTP compatible with 'SAP HANA Execution'. You may notice I briefly touched this topic at my other post called “Changes in BW system after HANA migration”.

Saturday, September 17, 2016

BEx QD: Hide Repeated Key Values

One of global settings valid for all BEx query is related to repeated values. It determines whether identical key values are to be displayed more than once in the query. The setting is called and it is located on Query properties (accessible on right pane of Query Designer):

The settings usually always worked. Reason why I’m saying “usually always” is that some of new front end tools like Analysis for Office (AfO) do not consider the settings. I came to know this by trying it out in AfO. I was wondering why it doesn’t work in AfO whereas it worked while running the same query in BEx Query Analyzer. Then I ran into following SAP Note: 2299020 - Analysis Office does not evaluate Hide Repeated Key Values from Query definition.

It clearly states that “the design rule for newer clients like Analysis Office has been that visualization settings for the table (UI) should not be part of the BEx Query definition.” It is also documented on

Query Analyzer: Hide Repeated Key Values -> You can specify whether identical key values are to be displayed more than once in the query. The Hide Repeated Key Values setting is active by default, so that only the first key value is displayed for each characteristic and additional, identical key values are hidden.

Analysis for Office: Repeat Members -> The setting made in BEx Query Designer to hide/show repeated key values is not evaluated in Analysis. To define the display for repeated members only this setting in the design panel is evaluated.

To employ the setting in case of AfO there is a checkbox called “Repeat Members” on tab Components -> select particular DataSource -> Crosstab x. 

SAP BW/4HANA (B4H) – what is it?

Soon after SAP introduced their HANA optimized ERP system (S/4HANA – 4th generation ERP system, 18 months back) another main product of SAP is emerging on HANA platform. It is SAP BW/4HANA (or B4H, BW4 or just B4) launched on Sep 7th 2016. Important information is that it is a new products and it is not successor of SAP BW at all. It is based on code fork (unlike BWonHANA (BoH) which code line is still shared with rest of supported DBs) of Any DB SAP BW and it will become primary development platform at SAP. This can indicate that in future there will only be new versions of B4H delivered. All other Any DB SAP BW will not follow current BW 7.5 version and these versions will not be maintenance mode.

According SAP there is following 4 corner stones of the new product:

1. Simplicity – only 4 basic types of objects (advanced DSO, Composite Provider, IO, Open ODS view) enhancement to already introduced “Field based modeling instead of InfoObjects based one”.

2. Openness – B4H can consume data from any source and data can be visualized by any major BI front end tool. Moreover any HANA table/view can be incorporated into B4H data model w/o data replication. SQL or native SQL data approach known in classic SAP BW scenarios is still possible.

3. Modern UIs – it shifts more towards Eclipse based tool – HANA Studio from all major use case perspectives like modeling/administration/user. Use of SAP GUI shall be more less obsolete for B4H. More parts of monitoring functions are SAP Fiori / UI5 based.

4. Performance – Calculations in newly developed data warehouse models shall be pushed down to database. This will bring speed to classic BW models that are being calculated on level of ABAP AS. Push down approach shall ensure enough “horse power” to deal with big data (e.g. via SAP VORA a bridge between classic RDBMS and distributed processing on a file system (hadoop).

More information:
Online docu SAP BW/4HANA 1.0
What is B4HANA –
What is B4HANA - Thomas Zurek blog
Why B4HANA - Thomas Zurek blog
BW/4HANA FAQ - John Appleby blog
Making the leap to SAP BW/4HANA - John Appleby blog
SAP Influence page for B4H
SAP Support system component: BW-B4H

BoH: Obsolete processes in process chains

While running SAP BW on HANA database there are couple of processes within process chains that were used before and are not needed anymore. Reason is that HANA has some of these functions already build on DB level and BW doesn’t need to be worry about them. A list of these obsolete processes is here:

Index related:
Build Index
Delete Index
Aggregate or BWA related:
Initial Filling of New Aggregates
Adjust Time-Dependent Aggregates
Rolling Up Filled Aggregates/BWA Indexes
DB statistics related:
Construct Database Statistics
Update Explorer Properties of BW Objects

So what actually the SAP BW system does when it runs into the process that is obsolete for HANA? There is simple check if the HANA is the DB of particular system. The check itself is very similar to the checks that I introduced in my other blog: How to check whether your ABAP NetWeaver is HANA DB based? It is just a check of attribute n_is_hdb_system which is initialized in constructor of class CL_RS_UTILITIES (cl_rs_utilities=>n_is_hdb_system = rs_c_true). While the check return value true then messages like below are displayed:

·         Variant &1 skipped (not required for SAP HANA database)
·         Deleting the index is not necessary/useful for HANA DB systems
·         Building an index is not necessary/useful for HANA DB systems
·         No roll-up required for HANA DB systems

Then finally what we see in process chain monitor is nice log like following:

Useful links:

Wednesday, August 31, 2016

How to find out what triggered run of APD process?

Sometimes we are wondering how come that some Analysis Process Designer (APD) process ran. Usually user can execute it manually from tcode RSA1 or RSANWB. There is a possibility to see a log or so called Monitor for APD but it doesn’t tell who and how it was started. What is possible to get out of the APD monitor is job name (BI_PROCESS_ANPR) and job number, etc.

Therefore one option that one can turn to is a possibility that it was triggered by process chain (PC). To look up the list of PC that contain a step of APD execution a table RSPCCHAIN can be used. Following restriction shall be used. Into field TYPE = ANPR and VARIANTE = “name of APD process”. By this selection one can get a list of PC where particular APD is used.

Monday, August 1, 2016

RSREQREDUCE - Reduction of Requests in InfoProv

Within version 74 of BW a new tcode was introduced. It is called RSREQREDUCE and it helps in housekeeping of data load requests. While using it a BW administrator can identify and reduce potentially large number of requests in InfoProvider. In case the InfoProvider has many requests in there it slows down the processing of such InfoProvider.

The functionality of Reduction of Requests is available while running the above mentioned tcode or via RSA1 -> Administration -> Housekeeping Tasks -> Request Housekeeping or via data request monitor. There an RED icon is displayed. By clicking on it it get ot the tcode:


From its main screen the BW admin can run the request reduction itself by specifying parameters. I find the tool is quite handy. It is intuitive and it reveals the objects that have many request there in very straight forward way.

Monday, July 18, 2016

Check Time Zone of SAP NetWeaver system

One more post regarding time zone of SAP NetWeaver system. It is for actual checking of the time zone of the system. To simply find out what is the time zone of the system there is a report: TZONECHECK - Check Time Zone Data for Consistency. By entering client and language on selection screen the real time zone of the system is displayed.

Wednesday, July 13, 2016

RSM37 - Display of process chains with particular scheduling state

Recently I needed to list all process chains that will be executed in next 24 hours in SAP BW system. I looked to SAP basis tables like TBTCO (Job Status Overview Table) and TBTCP (Background Job Step Overview). But I didn’t succeed. Even custom ABAP report (like advised here) didn’t help me.
Then I found out that actually this requirement is covered by standard BW tcode. It is t-code RSM37. The tcode gives a jobs and corresponding process chains list with its program variants.

On selection screen as Job Name we can enter BI_PROCESS_TRIGGER because it is most common job name for the PC. The name like this comes as default and usually it is not common to change this name. Then by specifying all statuses of the job (like planned, released, ready, active, finished and cancelled) and by giving time interval we get a list which we are interested in. 

In the output screen just filter on column Selection for value equals to CHAIN and list of the PC is here. In mind it is very easy to use and useful t-code.

Example of the RSM37 tcode output:

Sunday, July 10, 2016

SAP Application Value (SAV)

Recently there is a lot of noise between SAP customers running their SAP systems on Oracle. In case they obtain their Oracle license through SAP they will pay more for the Oracle license. The thing is that SAP renegotiated their agreement with the Oracle and as per new deal a cost of buying Oracle runtime licenses will be increased from 15 percent of SAV to 19 percent.

In regards to this usually there a term called “SAP application value (SAV)” mentioned. Reason is that the SAP is the basis for calculating database license fees for databases licensed through SAP. What is the SAP then? The SAV is defined as a sum of all named user licenses and all package licenses, excluding certain items defined in the SAP price list

In case of Oracle SAP sells Oracle license is an Application Specific Full Use (ASFU) or also called as Oracle runtime or OEM license. The license only license allows connect SAP software only to the Oracle DB with exception are 3rd party database admin tools. Oracle Enterprise Edition of the DB is needed for most of SAP apps.

The SAP price list is only known to their customers and partners however closer look to documents available like “SAP List of Prices and Conditions SAP Software and Support” shed at least some light into the issue. There are many types of licenses of SAP software itself. There are options like Package Licenses, Names Users Licenses, etc.

Now back to database cost issue. The price of the database license is calculated as a percentage of the SAP Application Value (SAV). So whatever amount is customer paying for SAP software (the SAV) then percentage of is payed to database vendor.

As Oracle DB is now 19% of the SAV and SAP HANA DB is 15% perhaps for some customers this can be a reason for migration to HANA DB. Perhaps ORACLE will do something about this…

More information:
581312 - Oracle database: Licensing restrictions
740897 - Info about the scope of the Oracle license; Required Oracle options
592393 - FAQ: Oracle
Oracle licensing via SAP

Thursday, July 7, 2016

Transport Connection -> Object Changeability

Some of BW objects can be created and also changed directly in production systems. This is a case of most common objects like InfoPackages or DTP but also BEx Queries. Particular BW object can be setup in way that it can be changeable even if the BW system is set to cannot be changed (via tcode SE06).

A function available in RSA1 -> Transport Connection (or directly tcode RSOR) where the setting can be customized. 

I’m not going to discuss the possible settings in here as this is pretty much known to BW folks. However I just want to point to table where the settings are stored. It happened to me recently that I needed to check the Object Changeability but I wasn’t authorized to do so. BTW user has to have an authorization to access SETTINGS wit activity 23 = maintain this is checked by FM RSSB_AUTHORITY_ADMWB_OBJECT.

While debugging I realized that settings are stored in table RSTRACTIVE – “BW CTO Status - Content Can Not Be Changed”. The data from the table are retrieved by FM RSO_MAINTAIN_OBJ_CHANGEABLE.

Wednesday, June 8, 2016

Changes in BW system after HANA migration

This is just yet another blog about BWonHANA (BoH). In particular it is about changes in the BW system that was brought to HANA DB offers. Basically I go through things that are visible once the BW system is migrated to HANA DB.

Let’s go via t-code RSA1’s left pane sections:

Modeling -> “SAP HANA Analysis Process” there is a new section called or HAP. The HAP enables to run SAP HANA native functions (e.g. AFL functions like PAL or L or R scripts or SQL procedure) directly on BW’s InfoProvider. The data can be processed as data set as opposite to classic BW transformation. Each HAP process has source (it can be most of most BW InfoProviders) then function and finally target (Analytic index, DSO, DB tab, or another HAP process).

Modeling -> InfoProvider -> new objects: Open DSO views and Composite Providers. Within this part of the RSA1 -> Modeling -> InfoProvider section there is a possibility to create and maintain these new objects:

Modeling -> InfoProvider -> cubes: Star scheme of classic BW’s infocubes was reorganized to just 2 tables: fact and 1 dimension table. There is only package dimension which has its own dimension table available. The SIDs of all other characteristics are stored directly in the fact table.

Moreover following are HANA related things that can be spotted in infocube’s settings screen:

External SAP HANA view: flag that tells an external SAP HANA view is generated for the infocube. The external HANA view can be used by native HANA apps as interface to BW models and data.

Auditable: if this flag is set then an audit dimension is added into the cube. Later it can be used to cover audit reporting to e.g. see who and when did a change into planning data in case of planning cube.

Also there are few less tabs on Manage screen of infocube (Performance and Rollup tabs are gone):

Before HANA:


Also in case cube is converted to HANA-Optimized cube there is a new icon indicating it on right side of the cube:

Modeling -> InfoProvider -> DSO: here is new flag call “External SAP HANA View”. See below on IO part for details of this flag.

All DSOs that are not HANA optimized (no X flag in RSDODSO-IMOFL) are having a little square icon next to the icon of the DSO. This is similar way of “marking” obsolete objects as it was in BW 7.x while marking 3.x related objects like DS, IS, etc…

Modeling -> PC: There are few new processes related to Hana within process chain’s processes. Especially when it comes to Execution of the HAP processes.

Modeling -> TRFN: in the menu Extras of Transformation maintenance screen there is a possibility to display generated HANA transformation similarly as it is possible to display generated ABAP program.

Modeling -> DTP: On the DTP maintenance screen here are following new things. First there is a checkbox which indicates whether the DTP shall be executed in HANA. In this case all data is processed directly by the database no ABAP app server is involved. This mode of execution is called SAP HANA Processing mode.

In addition to the checkbox there is a button called “Check Availability”. This button helps to determine whether the DTP can be pushed to the HANA DB for execution. There are few prerequisites that must be met in order to have the possibility to execute the DTP in HANA. See here for details on these prerequisites.

Modeling -> InfoObjects: Here at IO maintenance screen there is couple of new things as well. First there are field SAP HANA Package and SAP HANA View. These serve as source of data for the IO (e.g. MD). By this the data from the IO will be taken from SAP HAN View specified here. The Package field says where in which HANA package the view is stored.

Moreover there are also following checkboxes. They are following ones: “External SAP HANA View for MD” and “External SAP HANA View for MD InfoProvider”. These two basically serves for purposes of generating SAP HANA Views for InfoObjects. Having the view generated enables consumption of BW data in the HANA. This supports so called mixed scenarios when BW data and HANA data is merged together in HANA with HANA tools. See more details about the flags here. Of course there are some restrictions. For details on the restrictions see here.

Administration: Also here some new objects and features can be spotted.

On general Admin view there is an External SAP HANA View part. It can be used to view the Views.

On Monitors view there is a monitor screen available for the HAP processes. It servers for monitoring of the HAP runs.

On Current Setting part a settings related to External SAP HANA Views.

Transport Connection obviously also here few new objects were added in order to support operations with them. See below comparison between how the Transport Connection screens look like in BW 74 system on any DB and on HANA DB.

That’s all for now! As a disclaimer I need to say that there might be many more ways how to observe the new exciting things that are coming to BW system while running on HANA DB.