How to determine query tech name for BEx variable processing (in user exit/BADI)

While implementing a logic in user exit based BEx/BW variables a requirement of query technical name determination may appear. Thus, I tried to research on this topic a little bit. Fortunately, there is a structure called I_S_RKB1D, which has a description Query Reporting Attribute. The structure has a lot of useful information. Among others (see below) there is an also COMPID information that has the query technical name. The structure is available since a call of standard FM RRS_REPORT_INIT_START.

The structure is still available in memory at the point where the custom logic serving for purposes of specific variable is called.

Columns available in the structure:

COMPUID UUID in compressed form

INFOCUBE InfoCube

GENUNIID Internal display of the report identifier

COMPTYPE Type of a reporting component

COMPID Name (ID) of a reporting component

RFCSUPPORT Report supports access using OLAP API

OBJSTAT Object Status

SMODE Special reports

READMODE Read Mode

NOAGGRFL Flag: Do not use aggregates for this query

IDVERS Dictionary version (internal vers.) of report in RSIX

DVERS Dictionary version (current Release) of report in RSIX

RNAME ABAP Program Name

AUTHOR Name of author

GENTIME Query Generation Time (Cache Invalidation)

LASTUSER Last Changed By

MODTIME Last Change to Definition

REPTIME Last Change of Query Runtime Object

GEN1TIME Query Gen Time (no cache invalidation needed)

PACKFL Process Key Figure with High Precision

PACKFL_DEF Boolean

NOSPBUF SP Cannot be Permanently Buffered

CACHEMODE Query Cache Mode

CACHEUSAGEMODE Cache Usage Mode

KIDSEL Use Selection of Structure Elements

FAGGR_LATE Calculation of Commutative Formulas After Aggregation

OPT_OCCURS Boolean

OPT_OCCURS_TIME UTC Time Stamp in Short Form (YYYYMMDDhhmmss)

OPT_OCCURS_MODE Optimization Mode

OPT_OCCURS_PER Optimization Periods

SUPPRMESS Suppress Messages

CACHEUSEREXIT Indicator: Cache Use Despite Virtual Characteristics/KF

NOPARALLEL No Parallel Processing

GENPROT Generation Protocol

INPUTABLE Make All Basic Indicators Ready for Input

PARTITIONMODE Type of SP Grouping for Delta Caching

DELTACACHE Update Cache Objects in Delta Process

ACTUALDATA Data Integrity

OLAPSTAT OLAP Statistic Properties of the Query

OLAPSTATDETLEV OLAP Statistics Detail Level

ADDNLS Should the Cold Store storage be read too?

NOHPA Obsolete Field (Do Not Use BWA Index)

READMODE_DEFT Boolean

ACTUALDATA_DEF Boolean

CACHEMODE_DEF Boolean

DELTACACHE_DEF Boolean

PARTITION_DEF Boolean

DIS_MODE Disaggregation on Totals

INFOPROV_SUPPORT Query is used as InfoProvider

NOPARALLEL_DEF Boolean

KIDSEL_DEF Boolean

ADDNLS_DEF Boolean

NOHPA_DEF Boolean

CHUSEREXIT_DEF Boolean

TREXOPS Operations in SAP HANA

TREXOPS_DEF Boolean

MATERIALIZEQUERYLAYER Materialize Intermediate Query Result

MATERIALIZEQUERYLAYER_DEF Boolean

IS_PLANQUERY Boolean

DATA_INTEGRITY_SCENARIO Data Integrity Profile

DI_SCENARIO_DEF Boolean

READ_CHILD_KIDS Also Read the Child Members with Hierarchy Structure Node

READ_CHILD_KIDS_DEF Boolean

OLAPEFFORT OLAP Effort for Query

QMODI Q Mode Implementation (relational browse)

DOCUMENT_SUPPORT Document Support

DEFAULT_DOC_APPL_ID Default Document Application ID

PROPVERS Version of Properties concerning Support Package

MOSTRECENT Read according to newest, possibly not active structure

VARIANT ABAP: Name of variant (without program name)

PMODE Mode in which a query is executed (online, batch)

TMSTMP_ROLLUP UTC Time Stamp in Short Form (YYYYMMDDhhmmss)

TMSTMP_ROLLUP UTC Time Stamp in Short Form (YYYYMMDDhhmmss)

TMSTMP_START TS for Start of RRI_REPORT_IMPORT_AND_CHECK

ODBO Using ODBO to Execute Query

EXPLAIN_QUERY This Is A Explain Query

NO_RTPERSISTENCE Does the query have a runtime persistency

USE_NLS Boolean

ONLY_SIMPLE_CU Boolean

MANDT_DEP Object is client-specific

More than one query with the same technical name?

In BW systems it is not a rare case when there can be multiple BW objects with same technical name. This is especially related to BEx Queries (plus other query components like (REP, CKF, RKF, STR, VAR and SOB can be similarly effected too). Normally one would expect that the technical name is unique identifier of the query. However it is not the case. The technical name (COMPID in table RSZCOMPDIR and others) is not unique similarly as a description of the BEx query is not unique. It just represents language-independent name for the query element. This is in contrary to other objects in SAP systems where the technical name is really unique.

Technically it is not a problem to have duplicate technical names because other field (COMPUID) is used to uniquely distinguish between them. But it may cause problems while using tools like BEx where user needs to specify the query name in OPEN/SAVE dialogs. Only one query will be processed in these tools – whichever is found first in the database.

There can be several cases which may lead to situation causing having more than one query with the same technical name. Some of them can be:

Transports: When one query is imported from system A to system B. Technical name is the same in both systems. Later the query is deleted in the system A and recreated with same name. In case deletion of the query wasn’t transported to system B there will be 2 queries with the same technical name in the B system once recreated query gets transported to system B.

Several dev/test systems used to import objects to one prod systems: of query with the same technical mane is created in both source systems and they are imported to target system the query in there will have the same technical name.

It is very advisable to prevent into running issues like this. However when they occurs there are some possibilities how to deal with this situation.

1. Situation first needs to be analyzed e.g. by ABAP report ANALYZE_RSZ_TABLES.

2. Query can be copied to completely new name with help of t-code RSZC and then re-transported. Query with old tech name shall be deleted (e.g. t-code RSZDELETE).

3. If query (or it parts) needs to be preserved a ABAP report RENAME_DUPLICATE_ELEMENTS can be used to rename the query and/or its components. 

4. While transporting the query a parameter of RSADMIN table called QDEF_NO_DUPLICATE can be defined in order to prevent a situation when query component with the same technical name and different UID that already exit in the target systems is written.

More information:

How to look up COMPID for BEx Query based on GENUNIID ?

Strange entries in table RSRREPDIR

907025 - Duplicated technical names (COMPID) for query components

1551586 - Duplicated technical names of query components

5541024 - Duplicate query names in the BW system (BW 3.x related Note)

1765828 - Unique technical names for SAP Business Content queries

2061998 - Deletion of a query during transport does not delete an entry in RSRREPDIR

Seldom BEx query error “NODE_TO_NODEDR - (AXI) redesign query” while running in APD process

I encounter following error in my BEx query that didn’t run in APD. In my case it is very seldom error I didn’t experienced it very often. Basically the query doesn’t run and it produces following errors.

System error in program CL_RSDRC_SELDR_SRVS and form NODE_TO_NODEDR-01-            Message No. BRAIN299

This internal error is a targeted termination since the program has an incorrect status.

Error while getting data (AXI); redesign query               Message No. RSCRM027

The scenario in my case was that the query was part of APD process. When I ran the query standalone it worked just fine. Initially I assumed that it is caused by huge query output. However while disobeying limits on query result set it worked well in t-code RSRT. So it wasn’t about the large data set. There are few SAP Notes (2432897, 2098434, 2265753, 2146267 or 1505587) related to error but in my system based on BW 74 SP09 it turned out that all were already implemented. After more digging into SAP ONE Support Site I found a Note: 2265753 - MDX statement terminates with system error in program CL_RSDRC_SELDR_SRVS and form NODE_TO_NODEDR-01- which turned out to be the one that solved this situation. From the Note description it seems that the issue is related to calculation of members member used in the where-clause (slicer).

BEx Analyzer features – Properties for characteristics: Cumulate Values

Here I’m starting couple of posts about not very known features of BEx Analyzer. One of them is called “Cumulate Values”. It is available in context menu (right click) on characteristic’s cell while BEx report is opened in BEx Analyzer tool.

What it does is following. It cumulates a value of KF for specific characteristics shown in the report. This works without considering what aggregation type is defined for particular KF.

Here’s example of report’s output. There is KF called “Total time” displayed in the report. Characteristics Calendar Day is displayed as well in the columns of the report. Without “Cumulate Values” feature enabled report looks like following:

To enable the feature a right click is performed on the characteristics Calendar Day and menu item Properties is chosen. On next pop-up windows the feature is enabled.

This is hoe the report looks like when it is ON. Value of the KF in first column stays the same as the feature wouldn’t be enabled. But value of 2^nd and other subsequent columns are cumulating values of all predecessors’ columns:

Online documentation:

Properties of characteristics

BEx Query where used program

An ABAP program I’m writing about originated in one requirement I had today. I needed to evaluate where a bunch of BEx queries are being used. Place where particular BEx query can be used comprises of: workbooks, web templates, query views, broadcast setting, BW transformation etc.

As I had bunch of the queries to evaluate I wrote simple ABAP report which extracted “where used” information for me. Here I’m posting a fragment of my program which does the thing. A list of where used objects are provided in internal table lt_where_used.

REPORT zmm_bex_query_where_used.
 
PARAMETERS: p_compid TYPE rszcompid,
            p_obv    TYPE rszhelp-objvers OBLIGATORY DEFAULT 'A'.
 
DATA:  lv_compuid         TYPE rsz_uid,
       lt_where_used_info TYPE rzd1_t_where_used_info.
 
SELECT SINGLE compuid FROM rsrrepdir INTO lv_compuid WHERE objvers = 'ACT' AND compid EQ p_compid.
 
CALL FUNCTION 'RSZ_DB_COMP_WHERE_USED'
  EXPORTING
    i_compuid           = lv_compuid
    i_objvers           = p_obv
  IMPORTING
    e_t_where_used_info = lt_where_used_info.

Source code available at: github.com/softy12/BEX_QUERY_WHERE_USED

How to look up COMPID for BEx Query based on GENUNIID ?

While dealing with BEx query there may several scenarios occurred based on which we do not have real technical name of BEx query but we have some other identificator of the query. In very common case we have so called technical ID of the query. This is the ID that is visible in BEx Query Designer. BEx developer gives this ID while query is being created. The query technical name is stored as field COMPID (Name (ID) of a reporting component) in table RSRREPDIR. The field COMPID is 25 characters long.

Other unique indentificators which are stored in the same table are:

COMPUID     CHAR  25      UUID in compressed form 

GENUNIID    CHAR  25      Internal display of the report identifier

The COMPUID is unique identificator. This is very important to be noted as one query can have same technical name (COMPID) but COMPUID must be always unique. The COMPUID as well as GENUNIID serve as links between RSRREPDIR table and other BW tables which are related to BEx query.

Sometimes it may happed that we know only GENUNIID value for particular query. Based on this we want to look up COMPID for the query. Following function module can be used for this:

CONVERSION_EXIT_GENID_INPUT- Conversion Exit: InfoCube/Compid -> Genuniid

Imagine we have query with following record in the table RSRREPDIR:

By using mentioned FM we can lookup GENUNIID of the query by providing the COMPID of the query. Of course one can say that we can directly see the GENUNIID in table RSRREPDIR by providing COMPID value so no need to go for the FM. The SE11 for the RSRREPDIR is faster. However if it needs to be done programmatically perhaps call of the FM from ABAP can serve the purpose better.

Another option is to go to SE11 and on selection screen to enter value of GENUNIID:

Once it is entered by ENTER key we get COMPID as the GENUNIID is converted (by the same FM) to COMPID:

However there are cases when we only know the GENUNIID and we need to lookup COMPID. Here we use FM: CONVERSION_EXIT_GENID_OUTPUT

How to recognize between 3.x and 7.x BEx query?

SAP BW in its versions starting with 7.0 introduced many changes to BEx queries.  This resulted in a situation that there are two types of queries. First there are “old” or 3.x based queries. These were created with BEx Query Designer 3.x tool. On other hand we have “new” or 7.x based queries created in BEx Query Designer 7.x tool.

As a side note sometimes in SAP documentation they refer to 3.x BEx as “Business Explorer 3.5” or “BI BEx Suite 3” or “NW 2004” and to 7.x as “NW 2004s” or “BI BEx Suite 7” or “BEx 7”.

SAP advised to perform 3.x based queries migration to 7.x fashion right after the 7.x version popped up. However customers were neglecting it. The fact is that even in SAP BW 7.3 the 3.x based queries still run without problems. In version SAP BW 7.4 the BEx tools (Query Designer and Analyzer) of version 3.x are not available but Analytic Engine of BW 7.4 can still execute these queries.

Even we still can run the 3.x queries in BW 7.4 it is highly advisable to migrate it finally at this point of time. The BEx queries have gotten many new features in BEx 7.x. Also new BEx Query Designer of 7.x was redesigned so therefore it makes no sense to keep the query in old fashion.

Now how to recognize what queries are in the SAP BW system which the 3.x based? Version of the BEx tool that was used to edit particular BEx Query elements is captured in table RSZCOMPDIR. The table has column called VERSION which hold information about version of the reporting component. And exactly this is the field that tells us either it is query of 3.x or 7.x. As per value of the column; if it is higher than 100 it means that 7.x query if it is less than 100 means it is 3.x.

In case you want to perform the migration of old queries you might be interested to read further. The migration is quite easy in fact. All that needs to be done is to open the BEx 3.x query in BEx 7.0 Query Designer and save it. Once you hit SAVE button on the toolbar the query is migrated. It means you can open it only in 7.x BEx Query Designer and it can’t be opened in BEx 3.x Query Designer again. On such an attempt you will get a following message:

This component was edited with a more recent version of the Editor. You also have to use the more recent version to edit further. Further processing not possible. Update your front end'.

There is one more thing. If you open the old query in new BEx Query Designer the system performs automatic backup of the query. It stores the backup of the query and its component which was originally created in 3.x version of BW. The backup components can be recognized by value “B” of column OBJVER of table RSZCOMPDIR. Unless the query is saved in the BEx Query Designer 7.x it still can be opened in old BEx QD even it has backup version. There is an ABAP report (COMPONENT_RESTORE) provided which can be used to convert migrated query to 7.x back to 3.x version. Needless to say this is not foreseen to be used too much; of course. Also notice that once restore is done then all modifications done with QD 7.x are lost.

There is a possibility to prevent creation of the backup version of the query. It can be achieved by implementing following parameter in the table RSADMIN and setting its value to X: QDEF_NO_3X_BACKUP

Useful information:

Overview of BW 3.x to 7.x migration possibilities per BW objects

1932461 - SAP BW 3.5 BEx tools / BW 3.5 frontend add-ons are incompatible with BW7.4

1807522 - BEx 3.5 Objects Migration to BEx 7.0

1097674 - NW2004s: Administration of backup object version

949330 - NW04s: Query backup and restore

1410878 - Maintenance for BW 3.5 front-end add-ons

Changes to query properties in BW 73 vs 74

While working on BW 74 upgrade I notices there are many changes in BW in many areas. I’m not writing today about some significant new features (new objects types like Advanced DataStore Object; CompositeProvider etc). Just want to mention few remarks on how BW changes over different releases. Particularly in area of Query Properties in tcode RSRT.

BW73:

BW74:

A new checked was added: Calculation of Commutative Formulas After Aggregation.

Dropdown menu of Operation in BWA/HANA was renamed. Option 3 which was called as Standard is called Optimized Access now.

BW73:

BW74:

These changes may look cosmetics thought. However options in Operation in BWA/HANA settings are not. As per the settings in this particular field an query processing is set within BWA or HANA. So far (in 73) standard settings was option 3 = Standard. However having majority of the queries marked as 3 can cause problems in BWA/HANA. These problems can be linked to issues of memory consumptions or data correctness. Therefore as per SAP Note (1790426 - Processing MultiProviders in BWA) the 3 is not default option anymore (e.g. in 74). New default is option 2 = Individual access per InfoProv.

Few more BEx messages 6

Well one more for today on BEx and its error topic. One is related to WAD or Web Application Designer. If WAD is not properly installed on your machine or if there is an conenctity issue fto Backend BW server you may encounter following message:

RFC-Call RSWAD_TEMPLATE_SERVICES failed (SUBRC=0) EB_ERROR

 

Other error message is related BEx Analyzer. In case you running the report producing large query result which is not even be able to be handled by your MS Excel but not even by BW backend you may encounter following. This may also be related to your network connection between local machine and BW backend. So check also this option.

More posts on that topic:

• librfc32dll troubles
• BEx Connectivity Issue
• Not possible to change queries in BEx Query Designer?
• ERROR text in cell of BEx reports
• Error when opening BEx Query Analyzer under SAP GUI 7.10
• Few more BEx Messages 2
• Few more BEx Messages 3
• Few more BEx Messages 4
• Few more BEx Messages 5