Tag Archives: bex

Delete all locks to Planning Cube via BEx Workbook

clearlocks

In our planning application we give the central controller the possibility of deleting all locks to the Planning Infocube to prepare for some central functions (as central settlement of all WBSs, or central version copy).

Here is how we proceed.

1) Create a Custom Planning Function type. This planning function type does nothing to the data it receives, but instead removes all locks on the desired Infoprovider. See below the code for the EXECUTE Method and the Settings for the PF Type. Note that the Planning Function has 1 parameter which is the name of the Infoprovider to be unlocked.

2) Create a Planning Function on any Aggregation Level not related to the Planning Cube that you wish to unlock. (Otherwise, the unlock Function might fail due to  locking conflicts with the locks it aims to clear!!).  Assign a BEx Variable of type Char, Single Value, Mandatory initial not allowed, on 0INFOPROV to the input parameter.

3) Create a filter in such a way that it will always return at least 1 record (otherwise the Planning Functions EXECUTE method will not be called).

4) Create a Planning Sequence on the Aggregation Level above, using the filter and the planning function.

5) Call the Planning Sequence from a BEx Command Button in the BEx Workbook. The button should contain the following command sequence.
CMD      1     EXECUTE_PLANNING_SEQUENCE
PLANNING_SEQUENCE_NAME 1 <name of the Planning Sequence>
VAR_1_NAME    1  <name of variable on 0INFOPROV>
VAR_1_VALUE   1  <name of Infoprovider to unlock>

Settings for the planning function in RSPLF1

Properties Tab
- Without blocks
- Hide Column of Chars. To Be Changed
Parameter Tab
Parameter = INFOPROV / Description = INFOPROV
Parameter Type = Elementary
InfoObject = 0INFOPROV
Variables Allowed => Checked.

Code for the EXECUTE method of the planning function

method if_rsplfa_srvtype_imp_exec~execute.

*********************************************************************************
*
*               DECLARATIONS
*
*********************************************************************************

  data:
        l_r_param type ref to if_rsplfa_param_elem,
        l_infoprov type rsinfoprov.

  data:
        l_msgv type symsgv,
        log_handle type balloghndl,
        t_log_handle type bal_t_logh,
        log type bal_s_log,
        log_msg type bal_s_msg.

********************************************************************************
*
*               LOGIC
*
*********************************************************************************
  "Log some infos

  field-symbols:
        <s_data_charsel> type rsplf_s_charsel.

  log-extnumber = 'Infrabel IP Logging'.
  log-object  = 'ZIIP'.
  log-subobject = 'ZIIP'.
  log-aldate = sy-datum.
  log-altime = sy-uzeit.
  log-aluser    = sy-uname.
  log-alprog    = sy-repid.
  call function 'BAL_LOG_CREATE'
    exporting
      i_s_log      = log
    importing
      e_log_handle = log_handle
    exceptions
      others       = 1.

  " Get number of periods to be closed via parameter 'PERIODS' type FISCPER3
  l_r_param = i_r_param_set->get_param_elem( 'INFOPROV' ).
  l_r_param->get_value( importing e_value = l_infoprov ).

  concatenate 'Remove all user locks to: ' l_infoprov into l_msgv.
  log_msg-msgty     = 'I'.
  log_msg-msgid     = 'ZIIP'.
  log_msg-msgno     = '000'.
  log_msg-msgv1     = l_msgv.  " set to Planning Function
  log_msg-msgv2     = ''.  " set to value of filter
  log_msg-msgv3     = ''.  " set to planning sequence if any
  log_msg-msgv4     = ''.  " planning function step

  call function 'BAL_LOG_MSG_ADD'
    exporting
      i_log_handle     = log_handle
      i_s_msg          = log_msg
    exceptions
      log_not_found    = 1
      msg_inconsistent = 2
      log_is_full      = 3
      others           = 4.

  append log_handle to t_log_handle.
  call function 'BAL_DB_SAVE'
    exporting
      i_t_log_handle = t_log_handle.

  data: t_enq    type standard table of seqg3,
        t_enq_f  type standard table of seqg3,
        w_eng    type seqg3,
        v_result type sy-subrc,
        v_cube   type char30.

  refresh: t_enq_f,
           t_enq.

  move l_infoprov to v_cube.

* READ LOCKS
  call function 'ENQUEUE_READ'
    exporting
      gclient                     = sy-mandt
      gname                       = 'RSPLS_S_LOCK'
*   GARG                        = ' '
      guname                      = ''
*   LOCAL                       = ' '
*   FAST                        = ' '
* IMPORTING
*   NUMBER                      =
*   SUBRC                       =
    tables
      enq                         = t_enq
* EXCEPTIONS
*   COMMUNICATION_FAILURE       = 1
*   SYSTEM_FAILURE              = 2
*   OTHERS                      = 3
            .
  if sy-subrc = 0
     and t_enq[] is not initial.
* QUEUE NOT EMPTY

    if v_cube ne space.
*---ONLY FILTER ENTRIES FOR REQUESTED IP_CUBE
      loop at t_enq into w_eng.
        if w_eng-garg cs v_cube.
          append w_eng to t_enq_f.
        endif.
      endloop.
    else.
      t_enq_f[] = t_enq[].
    endif.

    if not t_enq_f[] is initial.
*---DELETE QUEUEE
      call function 'ENQUE_DELETE'
        exporting
          check_upd_requests    = 0
          suppress_syslog_entry = ' '
        importing
          subrc                 = v_result
        tables
          enq                   = t_enq_f.
    endif.
  endif.

endmethod.

BRAIN 670 – Error in BW: error creating tmp table – RS_EXCEPTION 105 error creating tmp table

SAP_DROP_TABLE_1

After a BW SP upgrade you might get the following error at query execution

EError in BW: error creating tmp table                                                                                               RS_EXCEPTION        105error creating tmp table

AAn error occurred when getting data from the processor.                                                                             BRAIN               670

I>> Row: xx Inc: IF_RSDRH_HIER_STORAGE~CREATE Prog: CL_RSDRH_DBTAB_STORAGE                                                           RS_EXCEPTION        301CL_RSDRH_DBTAB_STORAGE

This seems to be an error that occurs regularly on BW systems after an upgrade. To solve it try deleting the temporary tables with the report SAP_DROP_TMPTABLES (see SAP notes 698280 and 449891)..

You should run report SAP_DROP_TMPTABLE + Option “Temp. Hierarchy Table (02/08).

SAP_DROP_TABLE_2

Once done, execute again the BEx report in RSRT. The error message should not appear anymore.

 

 

 

 

BEx Workbook – Popup to pass values to Planning Function

passveluestofunction

Below are the information I gathered regarding techniques to allow some kind of dynamic selection of values to be passed to Planning Functions.

1) See note 1121654 – Note 1121654 – Problems transferring variables to planning func/sequence

2) Extract from a SDN Discussion:
Iin BEx workbook there is no Possibility to call an automatic pop up for variables in planning functions. The functions in BEx Workbook take the entries from cells like described in the notes. If you want to give the user more usability with pop-ups then you have to program VBA coding which writes the entry in the VBA pop up in the cell from which the planning function take the entry and then call the planning function from VBA code
1.) You have your button for the planing function and the command range for the variables. Let’s assume the variable value is in Cell D4. The name of the planning function is BUTTON_34 (you see that in the properties window of the BEX Button. You can implment the command range in the button in an extra sheet and then hide it.

2.) On the sheet with your query or cockpit (from where you want to run the planning function) you implement a second button – but not from the BEx menubar, from the VBA forms. Then you assign a VBA macro to this button.
3.) in this button you have to:
 a.) call a Msgbox for prompt asking for the value of the period
 b.) assign the value entered from the user to CELL D4 in the hidden sheet
 c.) run the planning funtion button in the hidden Sheet

A sample coding could look something like this:

Public sub Call_Funtion()

Dim Useranswer as string

Useranswer = InputBox("Please enter the period in the format MM/YYYY", "Period")

Table1.Range(D4).Value = Useranswer

Table1.Calculate

Application.Run "'" & ThisWorkbook.Name & "'!TABLE1.BUTTON_34_Click

end sub

This should do fine. If you are more experienced in VBA you can design a form with more input fields. It would look more comofortable for the user. But to explain this how to do it would be done in a different board.

See original post: https://scn.sap.com/message/7776844
If you do not have a single value variable but an interval the command range must look like this:

VAR_NAME_3                       1               MY_VAR_SO_TO
VAR_VALUE_LOW_EXT_3     1               04.2003 (Your Start value)
VAR_SIGN_3                         1               I
VAR_OPERATOR_3               1               BT
VAR_VALUE_HIGH_EXT_3     1               06.2003 (Your End value)

In the VBA code you then have to call 2 Propmts for input – one for start, one for end or you design a form if you are experienced. Then you assign the entered values to the ranges and call the buttoon via Application.run.

BEx Analyzer Messages List

When you get a message in the BEx front end and want to find the id, go to table T100, enter your language in the SPRSL field and the text or part of the text in the TEXT field.

Some BEx Messages not listed in RSRT / Messages

BRAIN 641 – Variable &1 could not be replaced

BRAIN 109 – No data was found that meets the used conditions

Concerning BRAIN 641. This message cannot be suppressed  in RSRT. See 1730446 – Variable *SAP-EXIT Variable* could not be replaced – BRAIN 641 for possible cause.

BW OLAP Cache Mode 5 – “BLOB/Cluster Enhanced” – Is the recommended cache mode

With Support Package 16, a new cache mode with an optimized architecture was delivered for BW 7.0. This mode explicitly addresses the basic problems that were connected with the existing cache modes 1-4 up to now. In particular, these include cache entries that have been lost or were not assigned and the blocks due to enqueue locks that occur under a high load.
The new mode is managed in BW 7.0x as “BLOB/Cluster Enhanced” under number 5, or in higher versions of BW, with the same number as “Query Aggregate”.
Performance is usually noticeably better for mode 5, and is therefore recommended as the standard for most applications.

See related note – Note 1026944 – New cache mode with no directory for BI 7.0

See help on OLAP Cache Monitor – http://help.sap.com/saphelp_nw70/helpdata/en/41/b987eb1443534ba78a793f4beed9d5/content.htm

See help on  BW Cahce Mode – http://help.sap.com/saphelp_nw70/helpdata/en/d9/31363dc992752de10000000a114084/content.htm

Configuration and Utilization of the  OLAP Cache to Improve the Query Response Time  http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0ebfba1-5a1a-2d10-82b5-fdddc1e5955c?quicklink=index&overridelayout=true

Limit BEx input variable F4 values via BADI

While running a SAP BW query, sometimes F4 help (Variable input help) returns a long list of unwanted
values. Users may not be interested to see all the returned values and this may confuse users or waste a lot
of time while selecting a right value from the list. There was no way to restrict the input help values before
SAP BI 7.01. With Support Package SAPKW70109 SAP has provided BADI
RSR_VARIABLE_F4_RESTRICT_BADI.

See the 2 following SCN articles on the subject:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/4009b0a8-7adc-2e10-48b3-a111c8f407af?QuickLink=index&overridelayout=true

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20ecb78c-374a-2d10-c6af-f024f19b785d?QuickLink=index&overridelayout=true&47983374649848

Also:

http://www.consolut.com/en/s/sap-ides-access/d/s/doc/YY-BW_BADI_F4