Program to find personal data in BW/BEx report objects

Within BW reports (formerly BEx reports), there can be a personal information in form of SAP user name or email stored. There are multiple BW report objects that can store such an information. It can be query element (ELEM), query view (QVIW), workbook (XLBW), web template (BTMP), enterprise report (ERPT), broadcast settings (BRSE), bookmark (ABAP, JAVA runtime or BICS), web item (BITM), Query Variants (RSRT) and so on.

Situation where one particular user that is used in objects like above needs to be replaced with a new one. This can be a case when original user is leaving an organization. Similarly if user email got changed (e.g. in case of marriage). How to find what are all the objects where the user ID/email is stored?

SAP provides an ABAP program to do that. It is called RS_FIND_USER_INFO and it serves for purposes of finding the user information across different BW/BEx objects.

On its selection screen a user needs to be specified whose information is search for. On top of that, program can run in three modes:

1. Display what are particular BW/BEx table where the information was found and how many entrees are there in those tables. See picture below.

2. Replacement of user in OWNER field – OWNER is a user who created the particular BW/BEx object. In this mode of the program run the OWNER value can be replaced.

 3. Replacement of user in other field – Programs replaces the user name in other fields such as AUTHOR, TSTPNM, LASTUSER, CHANGED_BY etc.

One more option on the selection screens is a checkbox for deletion of a records in the tables that carries the user record.

Example of the program’s output:

The program 1st appeared in BW 7.4 SP20 and is present also in BW4/HANA systems.

 

More info:

2603432 - Report RS_FIND_USER_INFO

2642676 - NW 7.50 - BEx 7.x Java runtime – deletion of data

Suppressing messages generated by BEx Queries

Update on 04JUL2022:

In case of BW4 systems there is no button called Messages available in a toolbar of the RSRT or in RSRT1 t-codes. However, same functionality is still available over the menu Environment -> Messages.

Original post:

BW (formerly BEx) queries are normally raising many messages either information, warning or errors. Sometimes some of them may confuse end users. Therefore, it might be useful to get them suppressed. There is a nice way how to do it via t-code RSRT. The RSRT serves as the query monitor. The BW queries can be tested, checked, and managed. Many technical things can be setup for the queries like regeneration of the queries, checks or change the query properties, displaying technical information, monitor the query cache etc.

One of the RSRT features is to suppress messages of the BEx queries. There is a button called Messages available in the RSRT. Thru a screen available on that button one can determine which messages for the chosen query are not to be shown to the user in the front end.

On the left-hand part of screen area, there are listed message categories via them one can navigate between the messages. The catalogers includes the messages related to: Generation, Hierarchy, Variables, Key Figures, Calculate As, Other, Planning, Data Integrity etc. Further, on right hand side of the screen area there are particular messages listed. Each of them can by suppressed by ticking off corresponding check box. Moreover, on bottom part of the screen area a long text of the particular message that is highlighted is displayed.

If you need more sophisticated solution to suppress the messages, you may consider implementing exit/enhancement into the FM RRMS_MESSAGE_HANDLING that is handling all the messages for the BEx queries. Using this approach an customer specific logic related to the messages processing can be implemented or customer messages can be added.

In addition, in some specific cases you may need to call FM like RRMS_MESSAGE_OFF_RESET which enables the message display by resetting the suppression of the messages. This might be needed in case the messages are not popping up. There is also FM RRMS_MESSAGE_OFF available which suppressing the messages completely.

More information:

How to suppress messages

2007609 - Display Message in Variable Customer Exit doesn't Work for a BW Query

BEx QD error: Hierarchy does not exist for InfoObject

BEx Query Designer may show a below error related to in consistent data in SAP BW hierarchy tables.

037(BRAIN) "The hierarchy &2 &3 &4 does not exist for InfoObject &1"

Issue typically lies within table RSRHIEDIR_OLAP. There might be an inconsistent entries in the table exit. Those entries can be fixed with a help of ABAP program RRHI_CORRECT_OSID_KTAB. 

In addition, depending on particular BW version root cause can be completely different. In case there is a hierarchy node variable that is used in the BEx Query and which is bound into hierarchy that does exist in development BW system but it doesn’t exit in target BW system (Q or P) the error may appear. Tricky part here is that simple re-assignment of the variable definition from non existing to existing hierarchy doesn’t help. Only way that solves the issue is to deletion of such hierarchy node variable and its recreation again from the scratch.

More information:

1900360 - BRAIN037 during query execution using a hierarchy with THJ

2177061 - Runtime Error RAISE_EXCEPTION in CL_RRHI_VIRT_HIER_SINGLETON - exception HIERARCHY_NOT_FOUND

2754938 - Enhancement of hierarchy correction report (rsrhiedir_olap)

2433852 - How to repair inconsistent Hierarchies

Locks set in BW system by BEx tools

When BW query is opened in BEx Query Designer similarly to when the BW query runs in BEx Query Analyzer there are a locks set in the BW system. There is a reason for that.

Each query is represented by generated ABAP report GP* that executes data selection from BW server to the query. We can compare the generated ABAP report as runtime processing of this BEx query. The lock is set while the ABAP report is generated.

Locks by BEx QD:

Lock by query run:

Normally the locks are not observed by regular BW user runing the query. However in the case that several users are running the same query there can be a message shown like following:

Object requested is currently locked by user (MC601, MC 601)

Every change to the query triggers it regeneration. More complex the query is more time it requires to regenerate. You can manually regenerate the query in t-code RSRT or via ABAP report RSR_GEN_DIRECT_ALL_QUERIES.

More information:

wiki

Different version of BEx query in runtime vs design time?

After query changes done in BEx Query Designer it may happen then these changes are not reflected in the frontend tools like BEx Analyzer or Analysis for Office. Buffering of BW tables is to be blamed for these types of issues. In particular it is important that table RSRREPDIR - Directory of all reports is not buffered. In some BEx versions the table was setup in way that it was buffered. In case there are more application servers in the BW system landscape then the other server which wasn’t used by the BEx Query Designer is reading the query definition from buffer which is not up2date.

Either the table is or is not buffered it can be checked in tcode SE11 -> Technical Settings -> Buffering. Correct setting here is “Buffering not allowed”.

 

Similarly in case of running BI frontend tool like BEx Analyzer or Analysis for Office on server with more than one application server there can be different results provided by the frontend tool. Again this can be due to table buffering.

More information:

958250 - Query displayed according to obsolete query definition

964390 - Different application Server shows different query result.

Trace files for BEx tools

Frontend (BEx) tools of SAP BW are supporting recording of traces. Such traces can be useful for providing detail information for SAP support team in case creating incident via SAP Support Site.

How to enable generation of BEx tools trace files? 

In case of the “BEx tools” like Bex Analyzer, Bex Query Designer or BEx Report Designer there is an menu "BEx/Tools" -> "Global Settings" -> “Trace” tab, where set trace level to shall be set to "Verbose" value. Trace files are stored in folder X:\Users\user_name\AppData\Local\Temp\

Example of the trace file name:

bextrace_qd_20171128_1127.txt

bextrace_wad_20170424_0226.txt

bextrace_rd_20170424_2246.txt

In case of Analysis Office for MS Excel/PowerPoint the trece function can be enable via Analysis menu -> "Settings" -> Support tab. Trace files are stored in folder X:\Users\user_name\AppData\Local\Temp\Sap\Cof\

 

Example of the trace file name:

AO_Trace_9284.glf

More information:

948489 - Customer Message requirements for BEx Report Designer

948487 - Customer Message requirements for Web Application Designer

585643 - Extended BW Component Frontend DLL-Usage Analysis

BEx Query Designer, query filter: Characteristics Restriction vs Default Values

In the BEx Query Designer there are two areas where characteristics can be restricted (filtered). What are differences between them?

As example this is simple BEx query having characteristics called “BI Appl. Obj. Type” placed at Filter->“Characteristic restriction” area and “InfoProvider Type” at “Default Values” area.

If a characteristic restricted in the “Default Values” area -> users can override the variable value or values at runtime. In the query runtime more values can be pulled into the restriction.

In case the characteristic restricted in “Characteristic restriction” area -> in the query runtime only values which were placed to the filter during query design time can be further restricted but no new values can be added.

Update 20-SEP-2021:

As reporting part of BW moved over the years to Analysis for Office, the pictures in this post became obsolete. Thus, I place new ones here about how the in the Analysis for Office we can remove values from “Default Values” filter. As well as how to setup default values filter in SAP Studio BW Query.

Here’s BW Query in SAP Studio:

Here’s Analysis for Office:  

Issue while determine text for fiscal period related IOs

Some time ago I spotted strange behavior in BEx query related to text of fiscal period IOs. In case of 0FISCPER when I entered value of 01.2012 on the query selection screen the text associated with it appeared as March 2012. Similarly for other months there were texts that didn’t correspond to the entered months at all. Also in query output where values of all variables used in query were shown the text again didn’t match the entered fiscal period.

In case of the 0FISCPER the texts are taken from standard SAP table T009C - Period names. The table has also field Fiscal Year Variant (PERIV) specified as a key field. From that fact it is obvious that to get proper value of text for particular fiscal period the Fiscal Year Variant must be present in the query. If the key of Fiscal Year Variant is not present in the SELECT statement that used by query to retrieve the text then first entry that fulfils the SELECT is taken and most likely it is wrong.

Actually in case of my query where the texts of fiscal period didn’t work was missing 0FISCVARNT IO in the query. Once I added the 0FISCVARNT into the query global filter (restricting it to value of K4 in my case) issue was fixed. The query properly determined the text of fiscal period used in the query.

Was BEx query changed directly in production system?

Sometime s it may become useful to check whether BEx query wasn’t changes directly in production system. Some eager user may just did it perhaps by accident? Also BW admin may need to do it to fix hot production issue when it was really urgent.

I’m not going to discuss here what are all pitfalls of doing changes to the BEx queries directly in production systems. Just to mention few: systems in landscape are out of sync, duplicates query elements (GUIDs      ) when using same name for query (or its elements) when is recreated in development and transported to the production, etc.

Anyhow query changed directly in the production system can be seen in many BW systems. Basically how to find out whether particular query was changed directly in the production? This information is stored in table RSZCOMPDIR. It stores data about query components so called - directory of reporting components. Here an field CHANGED_WITH is available. It can have below values for particular query:

VALUE                    MEANING

                 BEx Query Designer

1                           Web Item for Ad-Hoc Query Design

2                           Design Planning Selection Object

C                           Activate SAP Content

F                           Function RSZ_DB_ELT_COPY_INTO_OBJVERS

R                           Program COMPONENT_RESTORE

T                            Transport

N                           Transient element, requires processing (not persisted)

3                           Java Query Designer (Eclipse based BW Modeling Tools - BWMT)

Whenever you get T value of the field for the query you know it is just fine. The query was transported. In case there is a blank value it is obvious that the query was changed directly in that system.

While I’m writing about the RSZCOMPDIR table there are couple more of useful fields in there:

OWNER         User who created the query

TSTPNM        Last changed by user

LASTUSED     Time Stamp of when a component (queries and filters) was executed as a part of corresponding query object

TSTPDAT       Date when the component was changed on

TSTPTIM        Time when the component was changed at

CREATED       Time Stamp when the component was created

TIMESTMP      Time Stamp the component was changed at

More information:

1760688 - Update of the LASTUSED timestamp for query components

2020974 - Facts about the "Last Changed Time" for a BW Query