Category Archives: SAP BW

SAP BW

ABAP Program to do mass deletion of Process Chains

The following small program allows you to do a mass deletion of process chains.

I tried to remove all popup messages that occur during PC deletion, but there is stil some problem with that and you have to press OK at each process chain.

You can download the code of the programhere in Saplink format.
Download – PROG_ZBW_RSPC_MASS_DELETE.slnk – 3.3 kB – 408   downloads

Modify an existing SAP BW Source System connection

Technique for switching ECC source systems in SAP BW Environment without affecting the
data modeling objects in the BW environment. By data modeling objects, we mean transformations, update rules and transfer rules getting inactive or getting deleted. Once the source system is switched, data reload needs to happen to have the initializations and deltas to come from the new source system. Also we have to ensure that the data from old source system( both transactional and master ) needs to be deleted in the BW system before reloading from the data from new source system to ensure consistency in the data from new system.

The following  SCN Article describes the procedure

Step by Step on Changing ECC Source Systems without Affecting Data Modeling Objects in SAP BW

 

See also 110849 – “Error during insert in port table” (create source system) if the error occurs during the RSA1 / Source System / Restore step

SAP REFX Conditions – BW Conditions DSO and One Time Conditions

Data from the standard REFX exctractor 0REFX_1 can be loaded into a DSO with keys related to the RE Object (Object ID, Company Code, Building, Rental Space…) the condition Type and a validity date (ex:0DATEFROM). The condition amounts are provided with cumulative Key Figures (see 0COND_CHG) in relation to time.

This works fine for normal condition type  that can have a start date, an end date and in some cases 1 or more dates where the value of the condition changes.

Ex:

Condition start 01.01.2013 value 100. Condition change on 1.1.2014 value 80. Condition end on 31.21.2014. This results in 3 0REFX_1 records as follow:
VALUEVALIDFROM = 01.01.2013 COND_CHG = 100
VALUEVALIDFROM = 01.01.2014 COND_CHG = -20
VALUEVALIDFROM = 31.12.2014 COND_CHG = -80

There is however one problem with this configuration when a contract contains multiple one-time conditions related to the same condition type whose respective start and end dates are adjacent. 

Ex:

One-Time Condition start 01.01.2013 value 100. Condition ends on 31.12..2013.
Second One-Time  Condition start 01.01.2014 value 80. Condition ends on 31.12..2014.

This results in 4 0REFX_1 records as follow:
VALUEVALIDFROM = 01.01.2013 COND_CHG = 100
VALUEVALIDFROM = 01.01.2014 COND_CHG = -100
VALUEVALIDFROM = 01.01.2014 COND_CHG = 80
VALUEVALIDFROM = 01.01.2015 COND_CHG = -80

You see that here there are 2 records on the 1.1.2014.  If the transformation rule is set to overwrite, one record will be lost.

What can be done in BW: Make sure that 2 distinct 0REFX_1 records with similar keys do not have the same valid from date. to do this. Locate  possible cases by identifiying the one time condition record and then shift the valid from date by 1 day in the past (and make sure no more overlaps exists any more).

However it is not easy to identify one time conditions in the standard extractor 0REFX_1

To do this you need field UNIQUECOND. This field is available in the extract structure REIS_CONDITION_TRAN and the FM that is executed by the extractor will fill this field. However the field is set to not visible in ROOSFIELD, thus it is not visible in the extraction. A field in an extract structure is set to invisible when field ROOSFIELD-SELECTION equals ‘A’. This can be changed directly in the table by creating and running a small ABAP program. This change is not transportable, you will need to do this in acceptance and production too, and then transport the extractor after having removed the ‘Hide’ flag in RSA6 for the now visible UNIQUECOND field.

*&---------------------------------------------------------------------*
*& Report  ZOREFX_1_ROOSFIELD_UNIQUECOND
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  zorefx_1_roosfield_uniquecond.
DATA: ls_roosfield TYPE roosfield.

SELECT SINGLE * FROM roosfield
  INTO ls_roosfield
  WHERE oltpsource = '0REFX_1'
    AND objvers = 'A'
    AND field = 'UNIQUECOND'.

IF sy-subrc = 0.
  ls_roosfield-selection = ''.
  MODIFY roosfield FROM ls_roosfield.
  COMMIT WORK.
ENDIF.

See also note 931669 – BW conditions: Validity of one-time conditions

Technical Content – BI Statistics

Reference documents

No SID found for value ‘yyyymmddhhmm60’ of characterstic 0TCTTIMSTMP – 0TCT_DS21 – 0TCT_DS22

  • Apply manual changes to the update rules as per Note: 1713932
    • Goto RSA1. Navigate to infoprovider 0TCT_C22 and double click on the transfer rules
      for datasource 0TCT_DS22
    • Change to edit mode
    • Choose the field 0TCTTIMSTMP in the transfer rules and click on assignment button and
      select the radio button ‘Routine’ and click on ‘Create’ button.
    • Enter the transfer routine name as ‘Long timestamp -> short timestamp’.
    • Select the field ‘0TCTSTRTTST’ and enter.
    • Under FORM COMPUTE_TCTTIMSTMP, Please mention the below sentence RESULT = TRUNC(
      TRAN_STRUCTURE-TSTMP_START ).
    • Save the routine.
Do this for each BW Stats update rule activated in the system that fails after the upgrade.

http://scn.sap.com/community/data-warehousing/netweaver-bw/blog/2013/07/12/sap-bw-73-upgrade-issues-and-solutions

Process Chain Statistics Extractor – 0TCT_IS21

0TCT_IS21  is a “Technical Content” extractor that will retrieve statistics from the Process Chainws execution logs. These logs are stored in the following SAP Tables:

  • RSPCPROCESSLOG
  • RSPCLOGCHAIN

The 0TCT_IS21 extractor is a FM based extractor calling the function module RSDDK_BIW_GET_DATA.

The FM is a ‘multi purpose’ function that is used by several TCT extractors. Based on the Data Source it will call the appropriate data extraction subroutine; the one for 0TCT_IS21 is  “PERFORM get_fact_ds21”.

Analysing the  extractor in my system ( I am working on SAP BW 7.30 -SAPKW73008), I noticed the following errors in the extractor itself and in the Transfer Rules / Update Rules to cube 0TCT_C21:

  • Inconsistent conversion of start / end timestamps to start / end date and times
  • Calculation of the overall duration of the process chain is incorrect
  • Calculation of the number of steps in is incorrect

 

Inconsistent conversion of start / end timestamps to start / end date and times

The PC Log Tables provide start and end timestamps. The transfer rule from Data Source 0TCT_DS21 to Infocube 0TCT_C21 derives the start and end time via abap conversion routines. The proble is that start time is converted to the local time zone while end time is converted to UTC.

Code of the start time routine:

FORM COMPUTE_TCTSTRTTIM
  USING    RECORD_NO LIKE SY-TABIX
           TRAN_STRUCTURE TYPE TRANSFER_STRUCTURE
           G_S_MINFO TYPE RSSM_S_MINFO
  CHANGING RESULT TYPE /BI0/OITCTSTRTTIM
           G_T_ERRORLOG TYPE rssm_t_errorlog_int
           RETURNCODE LIKE SY-SUBRC
           ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel datapackage
*$*$ begin of routine - insert your code only below this line        *-*
* DATA: l_s_errorlog TYPE rssm_s_errorlog_int.
  DATA: l_start_time TYPE t.

  CONVERT TIME STAMP TRAN_STRUCTURE-starttimestamp TIME ZONE sy-zonlo
          INTO TIME l_start_time.

  RESULT = l_start_time.
* returncode <> 0 means skip this record
  RETURNCODE = 0.
* abort <> 0 means skip whole data package !!!
  ABORT = 0.
*$*$ end of routine - insert your code only before this line         *-*
ENDFORM.

Code of the end time routine:

FORM COMPUTE_TCTENDTIM
  USING    RECORD_NO LIKE SY-TABIX
           TRAN_STRUCTURE TYPE TRANSFER_STRUCTURE
           G_S_MINFO TYPE RSSM_S_MINFO
  CHANGING RESULT TYPE /BI0/OITCTENDTIM
           G_T_ERRORLOG TYPE rssm_t_errorlog_int
           RETURNCODE LIKE SY-SUBRC
           ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel datapackage
*$*$ begin of routine - insert your code only below this line        *-*
* DATA: l_s_errorlog TYPE rssm_s_errorlog_int.
  DATA: l_end_time TYPE t,
        l_tzone TYPE sy-zonlo.

  l_tzone = 'UTC'.  " This needs to be changed to sy-zonlo.
  CONVERT TIME STAMP TRAN_STRUCTURE-endtimestamp TIME ZONE l_tzone
          INTO TIME l_end_time.

  RESULT = l_end_time.
* returncode <> 0 means skip this record
  RETURNCODE = 0.
* abort <> 0 means skip whole data package !!!
  ABORT = 0.
*$*$ end of routine - insert your code only before this line         *-*
ENDFORM.

The transfer rule routines converts the timestamps in similar ways for Start Time (0TCTSTRTTIM) , End Time (0TCTENDTIM), Start Time as Key Figure (0TCTSTIMEK) and Time (0TIME) and also for the dates 0CALDAY, 0TCTSTRTDAT, 0TCTENDDAT.

So if you want to have consistency between all times and dates, you should check all the routines for the conversions of the following elements and replace ‘UTC’ by sy-zonlo on each of them (7 routines in total).

Calculation of the number of steps in is incorrect

The characteristic 0TCTSTAUIK (“Frequency”) counts the number of occurrences of steps in process chains. For individual records inthe 0TCT_C21 cube it should always be 1. For the record summarizing the PC execution (the one with 0TCTPRCSTYP = “#’), it should be the sum of the steps in the process chain. However the 0TCT_DS21 extractor performs the calculation incorrectly and returns a negative number equal to 1 minus the number of steps.

There could be several ways to correct this (apart from asking SAP to do the correction…) One way is by creating an user exit that recalculates the field in the extractor, or by correcting the value in the start routine of the update rule.

Calculation of the overall duration of the process chain is incorrect

Same as above. Characteristic 0TCTDURATION is incorrect for the record summarizing the whole PC execution.  Same solutions as above.

RSDMD_DEL_BACKGROUND and Transactional Infocubes

Program RSDMD_DEL_BACKGROUND is used to delete MD in BW. This program can be included in a PC to regularly clean up the “unused” master data entries of a characteristic. By unused we mean those entries whose SID is not referenced in any InfoProvider.

If you schedule such deletions on a characteristic that is used in a Transactional Infocube you might experience errors when there is an open (tellow) request in the Transactional Infocube.

Note “Note 1258548 – Master data cannot be deleted” expalis this.

One solution to this is probably to switch to load mode before running the deletion.

 

APD – Limitations on BEx Query as source

Additional infos:

 

OBIEE Reporting on SAP BW – BEx Report Key Date

OBIEE Reporting on SAP BW runs via MDX. SAP states that ‘The term time dependency does not exist in Microsoft’s MDX specification”.  This statement comes from the “Mapping Metadata” page of the “Open Analysis Interfaces”  pages for SAP BW.

Time Dependency in MDX

The term time dependency does not exist in Microsoft’s MDX specification. According to this specification, the same hierarchy, and therefore the same key date, has to be used in both MDX and in function BAPI_MDPROVIDER_GET_MEMBERS. Because the current date is always used when you call BAPI_MDPROVIDER_GET_MEMBERS, BW hierarchies with time-dependent names or time-dependent structures are also evaluated with the current key date. Key date variables are also ignored in MDX.

However, you can set a date other than the current date for the current session using function BAPI_MDPROVIDER_SET_KEY_DATE. For consistency reasons, the query key date of all subsequent MDX executions are also replaced by this date.

You use BAPI_MDPROVIDER_GET_KEY_DATE  to get the value that you set using BAPI_MDPROVIDER_SET_KEY_DATE. If no value has been set, this function returns the current date.

You can use these two functions like you use other BAPIs delivered by SAP. Enter the date in the SAP-internal format YYYYMMDD.

Here are some simple situations:
Case 1. Static Key Date value in Query Definition.
InfoObject 0EMPLOYEE with Time Dependent Master Data 0COSTCENTER.
Employee A with Costcenter A in 2013 and Costcenter B in 2014
BEx Query 1
Rows = Employee, Display Attribute Costcenter of Employee, Cot Center
Columns = Number of Records
Filter on Employee A
Key Date = 1/12/2013
BEx Query 2
Same as BEx Query 1 but Key Date set to 1/2/2014

In OBIEE Create 2 Subject Areas for the 2 BEx Queries
Analysis on the subject areas will return the correct Costcenter relative to the Key Date.

Case 2. BEx Ready for Input Single Value Mandatory Variable for Key Date.
BEx Query 3
Same as BEX Query 1 above, but Key Date set to the BEx Variable.
In RPD (OBI Administration the Key Date Variable is visible as Cube Variable)
However when running an OBIEE Analysis an error is returned <>, as stated in the SAP Help.