Saturday, October 21, 2017

Transformation code in INCLUDE: how to perform where-used?

Here let’s have following scenario. ABAP logic for Transformation (TRFN) is encapsulated in ABAP INCLUDE and it needs to be changed. Before a change is done it is beneficial to check what are all TRFN where the ABAP include is used. In case there are several TRFN and change is not supposed to be done for all of them an issues can came.

Normally as the INCLUDE it would be regular ABAP one there is a Where-Used functionality available in t-code like SE38/SE80. However it doesn’t work as the code of TFRN is stored in generated report (GP*).


What actually works in case of the GP* reports is Syntax Check functionality available as well as in the SE38/SE80. Via the Check the list of the all TRFN is available.

APD: all Key figures equal zero after ABAP routine

Working with APD process with ABAP routine I noticed that all key figures were equal to zero. The APD in question had regular BEx query as data source. After then an ABAP routine followed. Code in the routine wasn’t manipulating the key figures. Therefore it was a strange to me why the key figures were all equal to zero.


Currency or unit fields are not a standalone fields as they depend on a value field.  So if just a key figure is mapped in the ABAP routine it won’t transfer real numbers so they all appear as zero. Therefore the key figures associated with the currencies or/and units have to be assigned into field list on “Source Flds” tab in the APD process’s ABAP routine.


Friday, October 20, 2017

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.


Thursday, October 5, 2017

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).


Friday, September 29, 2017

How to get notification whether Process Chain ran?

There are some possibilities how to monitor runs of process chain in SAP BW system. It can also be achieved from web. However all these options are only informing about the PC status where it actually started to run at least partially. Means at least it reached to processing of the first process within the PC. From that very first process an message can be set up to inform user whether it ran successfully or it failed.

Now what about the PC didn’t run? How can user be informed that it for whatever reason didn’t run? There are not many options how to achieve this as far as I know. I created small ABAP custom program that can run as a job and will be checking the PC (name of the PC given on selection screen) whether it ran in last X days (X also provided on selection screen). Only coding that is missing is part that will send email. Check my other stuff on github for that.

Code available at my github: gist.github.com/softy12

Pattern Selection for DTP with source as DataSource (PSA)

It is not very known fact. At least it was in my case. I was a bit surprised when I realized that my DTP created on top DataSource which was extracting data from it via PSA didn’t pull any data. My case was that I had a filter on DTP where pattern selection was used.

Precisely speaking I had a wildcard used for selection like *something*. Once I ran it no data came. I tried to put precise value instead of the wildcards and it worked. I turned to SAP Support portal and I found a SAP Notes explain this behavior.

Actually this feature is not available in some of BW system. Depending on version of the BW it is only available in higher SP packages. TO be able to use this feature BW needs to be upgraded to these specific versions of the SP – see below.


There is one setting available in DataSource that is influencing behavior of particular field within the DataSource. It is called “Selection Options” and is present on tab Fields of DS maintenance screen. It basically drives what selection options can be processed for the DataSource field. Its values are stored in table RSDSSEGFD-SELOPTS (table has an info on DataSource - Segment Fields). There can be following values for the field set:

0        Undefined Selection Options
1        Selection EQ Permitted
2        Selection BT Permitted
3        Selection EQ, BT Permitted
7        Selection EQ, BT, CP Permitted


More information:
- For BW 7.00 and 7.01 see Note: 2509844 –SP37: Pattern Selection not possible for DTP with source as DataSource (PSA)

- For BW 7.30 and 7.40 see Note: 2150323 - 730SP14: Pattern Selection not possible for DTP with source as DataSource (PSA)

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?