Friday, September 22, 2017

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.

Monday, September 18, 2017

Communication failure while loading data to Open Hub

I came across a following error while using Open Hub functionality in SAP BW. An DTP that is used to load data from BW InfoProvider to the Open Hub it-self was failed and showing following errors:


Error: COMMUNICATION_FAILURE with function RSB_API_OHS_3RDPARTY_NOTIFY and target system . Message No. RSBO523

Error when opening an RFC connection (CPIC-CALL: 'ThSAPOCMINIT', communication rc: CM_ALLOCATE_FAILURE_RETRY (cmRc=2)). Message No. RSBO899

Error while updating to target (type Open Hub Destination) Message No. RSBK241

Operation could not be carried out for Message No. RS_EXCEPTION120
Exception CX_RS_FAILED logged Message No. RSBK228

There wasn’t any ABAP dump generated in BW system so it was obvious (also as per errors) that issue is in target external system that is picking up the data from Open Hub. I had a look into the FM mentioned in the error – RSB_API_OHS_3RDPARTY_NOTIFY as well. The source code of the FM looked strange to me.

FUNCTION rsb_api_ohs_3rdparty_notify.
*"----------------------------------------------------------------------
*"*"Lokale Schnittstelle:
*"  IMPORTING
*"     VALUE(OHDEST) TYPE  RSOHDEST OPTIONAL
*"     VALUE(REQUESTID) TYPE  RSBREQUIDOUT OPTIONAL
*"     VALUE(NUMB_OF_PACKETS) TYPE  I OPTIONAL
*"     VALUE(DBTABNAME) TYPE  RSBTABNAME OPTIONAL
*"     VALUE(DBRECORDS) TYPE  SYTABIX OPTIONAL
*"     VALUE(TIMESTAMP) TYPE  RSBTIMESTAMP OPTIONAL
*"     VALUE(REQUEST_GUID) TYPE  RSREQUID OPTIONAL
*"  EXPORTING
*"     VALUE(RETURN) TYPE  BAPIRET2
*"  TABLES
*"      PARAMETERS STRUCTURE  BAPI6107PA OPTIONAL
*"  EXCEPTIONS
*"      COMMUNICATION_FAILURE
*"      SYSTEM_FAILURE
*"----------------------------------------------------------------------

* must be implemented in the 3rd party system

ENDFUNCTION.

What that “must be implemented in 3rd ..” means? After some more investigation it was clear. The FM is called by 3rd party system once extraction for the OH is done. The FM basically just sends back to called system information on how many data were extracted. All this is sent over in parameters of the FM – Req ID, no of records etc. To just transmit this basic info there is no need to put any additional code.

The FM is part of API available for connect a third-party tool to the BW. There are other API’s FM available too:
RSB_API_OHS_DEST_SETPARAMS
RSB_API_OHS_3RDPARTY_NOTIFY
RSB_API_OHS_REQUEST_SETSTATUS
RSB_API_OHS_DEST_GETLIST
RSB_API_OHS_DEST_GETDETAIL
RSB_API_OHS_DEST_READ_DATA

Now let’s came back to the error itself. This is basically connection error between the BW and target extremal system. Once connection between the two was re-established the load was successful.

More information:
1585460 - Error when running DTP to 3rd party Open Hub Destination

Friday, September 1, 2017

How to find existing workbooks in BW system

Here are few options how to find workbook in the SAP BW system.


1. Via BEx Analyzer: in menu of Analyzer called Open -> Open workbook. This give only list of workbooks that user is authorized to authorized to access to. Either via menu role, favorites etc.
  

2. Via Analyzer Office for MS Excel: in menu Analysis -> Open Workbook -> it can be opened either from BusinessObjects platform or from SAP NetWeaver platform. Here again only access to the workbooks that user is authorized to access. Just these workbooks are not those created in BEx Analyzer but either migrated to AfO or newly created in AfO.



3. Via RSA1 – Metadata Repository: in t-code RSA1 -> Modeling -> Metadata Repository all the workbooks from all users available are listed. While drilling into it all details like what queries are used in the WB, who created it, when, last change and many others also can be found.



4. Via ABAP – FM RRMX_WORKBOOK_LIST_GET. If you need to deal with available workbooks in your custom ABAP program you can use this FM to get list of the existing workbook. Input parameters are just 2:
I_SORT                          1           “comes by default
I_ALL                           Y            “needs to be supplied

Output is a table E_LIST which has all information about the workbooks like:
WORKBOOKID - GUID of the workbook      
TITLE                      - Long description
FILESIZE                 - workbook file size in bytes
QUERYCOUT             - no of queries used in the workbook      
TYPE                      - Indicator: InfoCatalog type
TSTPNM                  - Last changed by user
TIMESTMP               - Last changed on date and time in timestamp format


5. t-code RRMXP – program RRMX_START_EXCEL, here you can specify workbook technical ID and once it is entered it opens BEx Analyzer with particular workbooks.
 







Thursday, August 31, 2017

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

Wednesday, August 23, 2017

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:
652856 - Loading hierarchies with process chains

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

Thursday, August 17, 2017

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.

Tuesday, August 15, 2017

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.

Friday, August 11, 2017

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? 


Wednesday, August 9, 2017

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



Sunday, August 6, 2017

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:
1963326 - BEx Analyzer: Global Default Workbook can be changed by enduser without having authorization