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

Sequence of loading data in SAP BW

While loading data into SAP BW systems one aspect of it comes to the picture. It is the one related to sequence of data loading. Normally in general the data loading sequence shall be like following. Master data loads come first followed by transactional data loads.

Loading sequence within the master data (MD) shall be as follows. First to load MD attributes then texts and finally hierarchies. But the attributes and texts can be loaded in parallel. Due to more complex operation while loading of the hierarchies are going on I suggest to put the hierarchy loads as last step of MD loads.

Now on topic of loading MD before transaction data (TD). All MD shall be loaded before TD is loaded. If it is not the case the TD loads triggers insertion of MD rather empty master data entry. What is meant by insertion here is that SID needs to be generated for the characteristic attributes, and new records have to be inserted in the MD tables. 

This can cause performance issues as the load can take twice as much due to inserting the MD during load of TD. Also if there are parallel loads in place there may locks appear in SID table update. Another issue that can happen is while there are master data lookup in the transformation of the TD. If the MD is not present then there is nothing to be looked up and as a result there is a data quality issue.

More information:

BW 74: Performance Tips for Processing Master Data

652856 - Loading hierarchies with process chains

1597364 - FAQ: BW-BCT: Extraction performance in source system

Not possible to trigger PC - Status F has already been reported to instance…

Under certain circumstances BW system is not able to trigger execution of Process Chain (PC). In the monitoring of the PC (e.g. RSPC1) there is no log that PC ran despite the fact that it was schedule to run. While checking corresponding job that was supposed to kick it off there is following messages displayed:

1. Start process TRIGGER in run 5AWPM9VCLJIWE4ARYHETSP4EA of chain

2. NO_MORE_SPACE: Can not create MTE: no more space: no more MESSAGE_CONTAINER slots available

3. Status F has already been reported to instance 5AXLA3PBRY95MBO9I9159UXKY, variant , in run 5AWPM9VCLJIWE4ARYHETSP4EA

Issue is caused by fact that Monitoring Tree Elements (MTEs) has not enough space to be created. Therefore whole activity is stopped and not performed.  The MTEs (or MTE classes) are objects within SAP Monitoring Framework (component BC-CCM-MON). In general MTEs are part of CCMS. The CCMS is Centralized Computing Center Management System. It is SAP component that provides a range of monitors for monitoring the SAP systems. It is used to evaluate behavior of the SAP processing environment. The MTEs of CCMS They can be reviewed see t-code RZ20 for SAP Basis CCMS or in t-code BWCCMS for CCMS of BW.

The MTE class is rule-based node in monitor definition. MTE classes contain information on general properties and method assignments for monitoring objects and attributes.

Issue of not running PC can be resolved by below activities:

1. System profile parameter called alert/MONI_SEGM_SIZE shall by increased. This can be done in t-code RZ11.

2. Changing setup of MTEs related to PCs: T-code RZ21 -> Methods -> Method definitions -> Display Overview. Open items RSPC_CCMS_AGENT and subsequently RSPC_CCMS_STARTUP by double click on it. On the "Parameters" tab, set param value to a lower value for: DAYS_TO_KEEP_LOGS and LOGS_TO_BE_KEPT.

Administration of BW workbooks

By term a workbook we refer to MS Excel file that contains a worksheet(s) having SAP BW queries inserted in. Basically when the workbook is stored it physically contains data pulled from SAP BW system via BEx Query. The workbooks can be either stored on the SAP BW backend or locally in user’s workstation.

In case the users are using the SAP BW system to store their workbooks after same time there can be a huge number of the workbooks pilling up on the server. The BW administrator therefore shall take care about it and do maintenance of the workbooks. There is couple of tools (ABAP reports) provided by SAP to support this task. Below I briefly introduce them.

I wrote similar post about the workbook deletion earlier here. This one is more general with regards to the workbook maintenance.

Report RSR_WORKBOOK_ADMINISTRATION (t-code RSRWBADMIN) – it takes care about mass deletion of the BW workbooks from the system. Multiple criteria are available on its selection screen. There are selection fields like Workbook Version/ ID/Owner, Last Changed by, Last Changed on, Workbook Size (Kb), Number of Changes, Number of Reads, Workbook Extension, Original System etc. Once the workbooks are found according the selection then either one or many can be deleted from report’s output screen. This report is dedicated to version 7.30 and 7.40 of the SAP BW.

Report RSR_WORKBOOK_ADMIN (t-code RSRWBADM) – same as above but dedicated to versions 7.00 and 7.11 of the SAP BW.

Report COMPONENT_REORG (t-code RSZDELETE) – generic t-code which also is performing a deletion of BW BEx queries. It utilizes FM RRMX_WORKBOOKS_DELETE  to perform deletion. It can be used as explained in my other post: Deletion of BEx workbooks

Report RRMX_WORKBOOKS_UNTOUCHED_PURGE - delete unreferenced (orphaned) workbooks. These are the workbooks that are not referenced to any role, there are no in any user’s favorites or there are no in any InfoCatalog. Report has also an option to specify on selection screen so called “Minimum age (in days)” however this field/variable is later not used in the code so the field has no meaning. Tested on version SAP_BW 740     SP09.

Report RRMX_WORKBOOKS_GARBAGE_COLLECT - identifies and removes orphaned workbooks. The report has no other option available on selection screen apart of checkbox “Delete workbooks found”. Basically it does the exactly the same as *_PURGE report

FM RSBB_WHERE_USED_LIST – provides a where used list of a workbook in activity groups.

Report RSWB_ROLES_REORG - Reorganization of the workbook and role storage. It checks the assignment of the workbooks to roles and the workbooks to favorites in both directions and it deletes assignments of non-existent IOs.

Error displayed in strange way while opening query in BEx QD

I came across a strange error in BEx Query Designer (QD) recently. While my user wasn’t authorized to edit the query I got below message from the message class R9 – msg no 108:

You do not have authorization for &1

While the error was shown in the QD it was displayed as follows, just plain msg ID and number. It was obvious that my user couldn’t change the query. But why regular description associated with the text ID and number is not displayed it is a mystery for me… Any thoughts? 

Determining what version of MS Excel was used to create BEx workbook

There are few of limitations when it comes to usage of BEx Query Designer within particular version of MS Excel. Mostly they are related to the fact that workbooks created in one version of MS Excel may do not work in other version. E.g. the workbooks created with MS Excel 2007 having format xlsx, xlsm or xlsb can’t be opened with Excel 2010. For more details about similar issues refer to Notes:

1498919 - Known Limitations of BExAnalyzer with Excel 2010

1175947 - Known Limitations of Analyzer with Excel 2007

Therefore fundamental question in issues like user is complaining that the workbook that worked in one version of the Excel doesn’t work in other is to check in what version of the Excel the workbook was created.

One way to find out that would be to have a look to extension of the Excel file. Up to version 2017 the Excel used only proprietary binary format – extension XLS. Afterwards it also uses Office Open XML as its primary file format – it has extension XLSM (or XLSX). These files are just a ZIP files with folder structure and several XML files. One of the XML files has in information about the version which was used to produce the file. The XLSM file just needs to be temporarily renamed to ZIP and in the folder called docProps a file called app.xml has to be displayed. In the XML element called the version is stored.

Now how to match the number from file (14 in this case) with the Excel version. Here below table (from wikipedia) becomes handy. In our case 14 means Excel 2010:

Excel 3          3.0    

Excel 4          4.0    

Excel 5          5.0    

Excel 95        7.0    

Excel 97        8.0    

Excel 2000     9.0    

Excel 2002     10.0

Excel 2003     11.0

Excel 2007     12.0

Excel 2010     14.0

Excel 2013     15.0

Excel 2016     16.0

In case one would try to use e.g. BEx workbook created in MS 2013 (version 15 would be stored in the app.xml file) in Excel of version 2010 (version 14) most likely it would not be possible as there would be libraries used by macros written in the Visual Basic incompatible between different versions of MS Excels. Attempt to open the Excel file would be ending up with errors like:

Error in loading DLL

or

Workbook corrupted error

The issue of missing or incompatible libraries can be spotted in editor of Microsoft Visual Basic for Application. Where in menu called Tools -> References a missing library would be highlighted like below:

More information:

1581104 - Workbooks created in Excel2007 cannot be opened in Excel2010

Setting up a global template for BW workbooks

Templates used by BW (BEx) reports can be customized in BW system. There can be either global template for all users in the system or each user can have different template assigned. All this customizing is to be done in table RSRWBTEMPLATE – “Assignment Excel workbooks as personal templates”. I thought that I write some information about the table as it wasn’t much known to me until now.

If user has particular template customized there is an entry for such a user. Field TEMPLATEUSER contains user ID and the field WORKBOOKID contains the workbook ID.

All other users that do not have an entry in the table are using template that is stored in the table row that has blank TEMPLATEUSER field but there is a value in the filed WORKBOOKID. So this is how global default workbook is set up in the table.

Now how to actually customize the table? There is no maintenance view generated for the table either there is no entry in the SPRO t-code. All the customizing is actually done via BEx Analyzer. Here it is available in menu on BEx toolbar: BEx Analyzer -> Global Settings -> Select “Default Workbook” tab. In this popup there are following buttons available:

·         Use Current – user can set as global template current template that is opined in BEx

·         Use Default – if user has specific workbook setup this will overwrite it with default one

·         Use SAP Standard – this removes specific workbook set for user and SAP default one will be used. The SAP default workbook cannot be adjusted or modified.

There is also a checkbox called “Global Default Workbook” available on the same tab. By ticking off it the selected workbook as the default workbook will be used for all users.

One may ask if there is any difference between BW 3.x and 7.x with regards to the customizing of default workbook ID. BW 3.x there were only 2 fields in the table: TEMPLATEUSER and WORKBOOKID. In BW 7.x a third field was added: LEAST_BEX_VERSIO which tells about version of BEx. If the value in there is 70 it is obvious that the systems run BW 7.x.

The table values can be also manipulated by custom ABAP program. See as an example this ghist by JouryJonkergouw.

Also as per SAP Note 1963326 there is a bug that allows user w/o proper authorization to change the Global Default Workbook (template) used in BEx Analyzer. The Note explicitly says that it is happening in SAP GUI 7.3 but as per my testing also SAP GUI 74 is effected as well.

More information:

Online docu

SDN wiki

Setting the global workbook template in SAP NW 2004s BI or BW 3x

1963326 - BEx Analyzer: Global Default Workbook can be changed by enduser without having authorization

Minimal ABAP code to send out an email from ABAP NetWeaver system

Yesterday I blogged about minimal ABAP code needed to display data from internal table in ALV grid. Today I post similar “minimal ABAP code“ thing just this time related to sending an emails from SAP NetWeaver ABAP stack based systems.

I came up with below to example program demonstrating minimal ABAP code for sending email:

Minimal version:

REPORT zmm_email_send_01.

DATA: lt_recepeints TYPE TABLE OF somlreci1,

      ls_recepeints TYPE somlreci1,

      ls_docdata    TYPE sodocchgi1,

      lt_bodydata   TYPE TABLE OF solisti1,

      ls_bodydata   TYPE solisti1.

SELECT * FROM usr02 INTO TABLE @DATA(lt_users).

LOOP AT lt_users INTO DATA(ls_users).

  ls_bodydata-line = ls_users-bname.

  APPEND ls_bodydata TO lt_bodydata.

ENDLOOP.

ls_recepeints-receiver = 'x.x@domain.com'.

ls_recepeints-rec_type = 'U'.

ls_recepeints-express = 'X'.

APPEND ls_recepeints TO lt_recepeints.

CALL FUNCTION 'SO_NEW_DOCUMENT_SEND_API1'

  EXPORTING

    document_type  = 'RAW'

    put_in_outbox  = 'X'

  TABLES

    object_content = lt_bodydata

    receivers      = lt_recepeints.

COMMIT WORK.

Regular version (with subject etc):

REPORT zmm_email_send_01.

DATA: lt_recepeints TYPE TABLE OF somlreci1,

      ls_recepeints TYPE somlreci1,

      ls_docdata    TYPE sodocchgi1,

      lt_bodydata   TYPE TABLE OF solisti1,

      ls_bodydata   TYPE solisti1.

SELECT * FROM usr02 INTO TABLE @DATA(lt_users).

LOOP AT lt_users INTO DATA(ls_users).

  ls_bodydata-line = ls_users-bname.

  APPEND ls_bodydata TO lt_bodydata.

ENDLOOP.

ls_docdata-obj_name   = 'Email'.

ls_docdata-obj_descr  = 'Email from' && sy-sysid. “subject

ls_recepeints-receiver = 'x.x@domain.com'.

ls_recepeints-rec_type = 'U'.

ls_recepeints-express = 'X'.

APPEND ls_recepeints TO lt_recepeints.

CALL FUNCTION 'SO_NEW_DOCUMENT_SEND_API1'

  EXPORTING

    document_data  = ls_docdata

    document_type  = 'RAW'

    put_in_outbox  = 'X'

  TABLES

    object_content = lt_bodydata

    receivers      = lt_recepeints.

COMMIT WORK.

Source code of the example can be found here: gsofty12/MINIMAL_EMAIL_SENT