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.

Infos about SAP Notes

You can subscribe to SAP Notes to receive notifications when they are updated

See:
http://sapport.blogspot.be/2009/06/subscription-to-updates-of-sap-notes.html

http://sapport.blogspot.be/2011/02/receiving-sap-notes-updates.html

Where can I maintain those note subscriptions?
If you are regular customer (with valid license of SAP products) you can request the list of notes you subscribed via customer message under the user component xx-ser-sapsmp-usr.

In case your OSS ID is same as mine (certified consultant w/o license to SAP software, you can request SAP directly to open the customer message on behalf of you.

Program listing the workbooks and queries in a BEx Role

To build a list of BEx Workbooks and BEx Queries in a role use table AGR_HIER

keys = AGR_NAME + OBJECT_ID

  • AGR_NAME = role
  • OBJECT_ID  = ID fo the object in the role (1 is the root folder)
  • PARENT_ID = ID of the parent folder (1 for objects that are in the root folder)
  • FOLDER = X if it is a folder
  • TEXT = Either Role menu descripiption or report description
  • REPORT = RRMX for WBK / SAP_BW_QUERY for query
  • SAP_GUID = WBK ID or Query TECH NAME

The following program lists the Role / Workbook ID / Workbook name and all the queries that are in the workbook. Additionally it will list any replacement path variable that uses the result of another query as input.

Download – ZBW_WB_PROPERTIES – 17.4 kB – 380   downloads

BEx Analyzer – Default Workbook

Via the “Global Settings” option in the BEx Analyzer add-on menu you can define the Workbook ID of the Default Workbook. The default workbook is the workbook into which queries are opened. By default, the “SAP Default Workbook” is used, but this can be changed to a customized Default Workbook.

References

How to identify the Workbook ID of the SAP Default Workbook?

Go to table RSRWBINDEXT

Search a record with
LANGU = EN OBJVERS
OBJVERS = D
TITLE = “SAP Default Workbook”

FindSAPDefaultWbk1FindSAPDefaultWbk2

How to check which Default Workbook is being used by a user

Check table RSRWBTEMPLATE

See following SCN Post Setting the Global Workbook Template in SAP NetWeaver 2004s BI or BW 3.x

How to open the SAP Default Workbook to make a copy => TCD RRMXP