DTP reads different data than LISTCUBE

I came across a situation when DTP I created was pulling different data set comparing to t-code LISTCUBE. I wanted to validate data that the DTP was pulling as source for my custom ABAP login in BW transformation. To do this I opened t-code LISTCUBE and entered a same selection as my DTP’s filter was using. To my surprise, I got different data sets in the DTP and in the LISTCUBE. I started to debug the load and found out even more odd thing. In one of master data characteristics used in the source of the DTP, the data was not populated in none of the records pulled by the DTP. Whereas in the LISTCUBE the same characteristics the data was populated.

First thing that I tried was to run some tests for the affected characteristics in t-code RSRV. In particular, it was test called “Compare size of P tables/Q tables with X tables/Y tables” which can be found under All Elementary Tests -> Master Data. Error that was detected by this test was following:

15:00:06 o'clock on 17.03.2020: Start test run for user XXX

Compare sizes of P or Q and X or Y tables for characteristic RZZZ

Characteristic RZZZ: Consistency check between P and X, Q and Y tables

Characteristic RZZZ: Tables /BIC/PRZZZ, /BIC/XRZZZ are not consistent: 50.004 deviation(s)

15:00:06 on 17.03.2020: Test run for user XXX completed

From the error, it was obvious that I have inconsistencies between P (Master Data Table) and X (Attribute SID Table) table of master data characteristics (RZZZ). In fact, in the P table, there were records but there were no records in X table of the characteristics. I tried to use “Correct Error” function available in the RSTV t-code however; it was not possible to repair the error.  

Second thing I tired was ABAP program RSDMD_CHECKPRG_ALL. There are couple of tests available in this program. Most of them are available as well in the RSRV. As this program has also REPAIR function, I tried to apply it. 

However, I was again unsuccessful with it. The program when it ran on foreground dumped and while running on background there was following error:   

Message text                                                                                            

Error in BW:

Characteristic RZZZ: Checking all SID values in the X and Y table

Characteristic RZZZ: Checking table /BIC/XRZZZ

Characteristic RZZZ: Checking consistency between tables /BIC/XRZZZ and /BIC/PRZZZ

Characteristic RZZZ: Errors found during this test

Characteristic RZZZ: 1.403.304 values from table /BIC/PRZZZ do not exist in table /BIC/XRZZZ

Characteristic RZZZ: Following data records have errors (max 50 displayed):

             Characteristic RZZZ: Checking SID values for characteristic/attribute RZZZ

Question remained. How to fill X table - attribute SID table of the IO? In the t-code RSD1 – Characteristics maintenance there is one option. It is available via menu Extras ->Database Table -> Fill Attribute SID Table. While running this the X table was populated and tests in the RSRV or in the RSDMD_CHECKPRG_ALL report were positive w/o any issues. The functionality “Fill Attribute SID Table” available in RSD1 is also available via FM RSDRX_FILL_INITIAL_XYTABLE. Once all corrected my DTP was puling same data set as the LISTCUBE and data values in specific characteristics were not missing any more in the load.

20:35:34 o'clock on 17.03.2020: Start test run for user XXX

Compare sizes of P or Q and X or Y tables for characteristic RZZZ

Characteristic RZZZ: Consistency check between P and X, Q and Y tables

Characteristic RZZZ: No errors found in this test

20:35:34 on 17.03.2020: Test run for user XXX completed

20:36:41 o'clock on 17.03.2020: Start test run for user XXX

Compare sizes of P or Q and X or Y tables for characteristic RZZZ

Characteristic RZZZ: Consistency check between P and X, Q and Y tables

Characteristic RZZZ: No errors found in this test

Compare characteristic values in SID/P/and Q tables for characteristic RZZZ

Characteristic RZZZ: Check for all values existing in P, Q and SID tables

Characteristic RZZZ: No errors found in this test

20:36:41 on 17.03.2020: Test run for user XXX completed

DBSQL_SQL_ERROR error while writing to BW InfoProviders

I recently faced a strange error while working on simple BW transformation. There was an end routine in place. I had a few arithmetic operations within the end routine. In particular I was multiplying key figure by big number (10mil) – so called I was scaling up the key figure. The routine was syntactically correct. In addition, the transformation did not indicate any problems it was possible to activate it – so no problem in design time at all. On other had while in run time my load failed.

There were messages like following in the load’s monitor:

Runtime error while executing rule -> see long text

Update terminated in accordance with error handling setting

Overflow converting from '1.08647e+14'

An exception was raised

Error in substep

Data package processing terminated

There was also ABAP dump present, which was generated for the failed load in case target infoprovider was info cube.

Category               Installation Errors

Runtime Errors         DBSQL_SQL_ERROR

Except.                CX_SY_OPEN_SQL_DB

 Short Text

     SQL error "SQL code: 0" occurred while accessing table "/BIC/FZZZZ_ZZZ".

 What happened?

     Database error text: "SQL message: "

An exception has occurred which is explained in more detail below. The

exception is assigned to class 'CX_SY_OPEN_SQL_DB' and was not caught

 procedure

"WRITE_ICFACT" "(FORM)", nor was it propagated by a RAISING clause.

Return value of the database layer: "SQL dbsl rc: 99"

>>>>>   INSERT (l_facttab) FROM TABLE g_t_bia.

In case target infoprovider was DSO object the ABAP dump looked like below:

Category               Installation Errors

Runtime Errors         DBSQL_SQL_ERROR

Except.                CX_SY_OPEN_SQL_DB

 Short Text

     SQL error "SQL code: 0" occurred while accessing table "/BIC/AZZZZ_ZZZ00".

 What happened?

     Database error text: "SQL message: "

An exception has occurred which is explained in more detail below. The

exception is assigned to class 'CX_SY_OPEN_SQL_DB' and was not caught in

procedure

"INSERT_ODS" "(FORM)", nor was it propagated by a RAISING clause.

Return value of the database layer: "SQL dbsl rc: 99"

>>>>>   INSERT      /bic/azzzz_zzz00

I tried to comment out my arithmetic operations in the code of the transformation and the load succeeded. It was obvious that there is overflow going on while computing my arithmetic operation I was wondering why in the design time there is no error. My target variable of multiplying was type of DEC 21 Decimal 7 (e.g. IO /CPMB/SDATA in BPC) and that was not able to fit the result of computation that was bigger that DEC 21. When I changed my target variable to type of DEC 31 (e.g. IO /CPMB/LSDATA in BPC) it all worked fine.

In case there is no arithmetic operations in your code there might most likely issue with regarding parallelization. Some processing that are running in parallel are locking the objects (e.g. target infoprovider) that causes the error like this. In such a cases repair of DB object via t-code SE14 may help. Similarly, cube indexes rebuild via ABAP program SAP_INFOCUBE_INDEXES_REPAIR or in t-code RSRV may help. Another solution can be reduce maximum size of the data packages. All these things mentioned above are valid in case the BW is not on HANA DB.

Cube load failure call subroutine FILL_SID_CACHE

Some loads to BW cubes may fail with below error that is related to subroutine FILL_SID_CACHE.

An exception with the type CX_SY_DYN_CALL_ILLEGAL_TYPE occurred, but was neither handled locally, nor declared in a RAISING

The attempt to call the subroutine FILL_SID_CACHE in the program &PROGRAM& failed due to a type error involving parameter number

An exception with the type CX_SY_DYN_CALL_ILLEGAL_TYPE occurred, but was neither handled locally, nor declared in a RAISING

Error while updating to target XXXX_XXX (type INFOCUBE)

Data package 1 / 17.02.2020 20:40:29 / Status 'Processed with Errors'

There is SAP note 1972401, which describes this error.  Regeneration of data loads to the cube of the program happened automatically but call of the form routine went to the older version of the program. Therefore, it failed. Issue is most likely caused two possible reason. One can be buffering problem. Second can be that a loading process was already connected to the old version of the generated program.

As workaround, one can try to reactivate the corresponding DTP or involved TRFN and repeat the load. If it does not help use program RSDD_RSTMPLWIDTP_GEN_ALL with both check box ticked off for the BW cube that is affected.

More information:

1972401 - DTP failed due to Exception CX_SY_DYN_CALL_ILLEGAL_TYPE

2172882 - DTP execution failed with CX_SY_DYN_CALL_ILLEGAL_TYPE and FILL_SID_CACHE

DTP can’t run via process chain

In event that particular DTP wasn’t finished and while it was in progress there was a BW system shout down (regardless planned or not) going on such the DTP may have issues while running it after the systems was restored back.

After the restart process chain that contains the DTP reports errors like these:

Object DTP DTP_xxxx not found                         Message No. RS_EXCEPTION111

Object INSTANTIATE_TEMPLATE  not found          Message No. RS_EXCEPTION111

Error in BW:                                                  Message No. RS_EXCEPTION105

Exception CX_RS_PROGRAM_ERROR logged         Message No. RSBK228

Could not repair last request (  )                       Message No. RSBK074

First error is very strange as the DTP is in the system. It is normally visible in RSA1/RSDTP t-codes. When you check the tables like RSBKDTP – the DTP entry has regular Active record there. Reactivation of the DTP doesn’t help either. Corresponding transformation is in active state too. Its check doesn’t report any issues. Entry in table RSTRAN is active for the transformation too. Reactivation of the transformation doesn’t help as well.

What needs to be done is just run the DTP standalone outside of PC. During such an attempt the BW system will ask to delete/repair previous run of the DTP that caused red load request in target. Once you confirm it shall be repaired – system asks whether it shall be done synchronously/asynchronously. After any of the two options is confirmed the DTP is successfully executed and data loaded.

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 3^rd ..” means? After some more investigation it was clear. The FM is called by 3^rd 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

Online docu Third-Party Tools As Destinations

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

Hierarchy data export - job terminated in source system error

I encounter below issue while exporting hierarchy data from InfoObject to other systems. I had enabled my IO with settings “Characteristics is Export DS” available on Master Data/Texts tab of the IO maintenance screen. This means that characteristic is used as export data source. So its attributes, texts, and hierarchies of the IO can be extracted into other BW systems via Data Mart Interface.

Afterwards I created export DS via doing right click on the IO and choosing menu Additional Functions -> Create Export DataSource. Notice that the export DS for hierarchy are still working in 3.x version fashion. Also the version of such a DS is always only modified and the DS is self is Emulated. Same settings you can observe in t-code RSDS.

I replicated DS in my target system. Once I tried to load the data via InfoPackage there was a following error:

Job terminated in source system -> Request set to red.

There was an ABAP dump associated with the cancelled job. It was saying about termination in ABAP report SAPLRSSG, runtime error was type of SYNTAX_ERROR and error description was like follows:

The current ABAP program "SAPLRSSG" had to be terminated because it has come across a statement that unfortunately cannot be executed.

In include "GP00733D5CMRY7YE3D93SUE852V", in line 34 of program

 "GP00733D5CMRY7YE3D93SUE852", the following syntax errors have occurred:

Field "/BIC/C5DU8_IOname is unknown. It is neither in one of the specified tables nor defined by a "DATA" statement.

It was obvious that data element as described wasn’t present in the system and it even wasn’t supposed to be in there.

Actually issue was within method for loading of data. This information for particular DS is stored in table ROOSGEN - Generated Objects for OLTP Source. In my case it was set to T that means transactional RFC but once I corrected it into I (Idoc) my upload worked.

There are few forum posts about this issue in SDN like here and here but solution is not provided within the posts. I hope this blog will be useful for people who face same issue.