Dependency analysis on HANA calculation views

Just a small tipp… in order to analyze dependencies of calculation views you can use the following table:

SELECT * from OBJECT_DEPENDENCIES WHERE (dependent_object_name like '%IOLE%STATUS%' or base_object_name like '%IOLE%STATUS%' ) and dependent_schema_name = '_SYS_BIC'

Starting AfO from ABAP

Just a small snipped: AfO can be started via transaction RAAOE.

Directly triggered from ABAP works this way:

SUBMIT rs_ao_launcher
WITH p_t_doc = abap_false " to remove the default value
WITH p_t_qu = abap_true
WITH p_query = 'MYQUERNAME'.

Opening Analysis for Office query via shortcut launchfile

When executing the transaction RAAOE I noticed that BW is generating a temporary file to jump directly into a specific query in AfO.

It is an XML file which is then executed by the AfO. The cryptical hash from the generated file does (I guess) contains a temporary hash key for the session. Leaving this aside it will prompt for username and password.

 

<?xml version="1.0" encoding="utf-8"?><AnalysisLauncher><Connection type="NW"><System>FL1</System><Language>EN</Language><Server>myserver.mydomain.tld</Server><SystemNo>00</SystemNo><Client>100</Client><SNCName></SNCName><SNCQuality></SNCQuality><SNCPartnerName></SNCPartnerName></Connection><User><Name>TEST_USER.3</Name></User><Open type="Query"><Name>ZQUERYNAME</Name></Open></AnalysisLauncher>

The filename ending is open_query.sapaox

After executing the shortcut file, the opener deletes automatically the file.

 

Process chain kill

With one the last updates of SAP BW 7.5 I recognized the behaviour that a chain is in status yellow just because one old request was not fully progressed after e.g. an outage. The following snipped stops the yellow chain runs just by checking the status of the log entry of the chain run.

REPORT z_chainkill.

DATA: lt_chain TYPE STANDARD TABLE OF rspclogchain.

DATA lv_e_message TYPE so_text255.

PARAMETERS: pchain TYPE rspc_chain.

SELECT * FROM rspclogchain
INTO CORRESPONDING FIELDS OF TABLE lt_chain
WHERE chain_id = pchain AND analyzed = '' AND analyzed_status = 'A'.

LOOP AT lt_chain ASSIGNING FIELD-SYMBOL(<c>).

CALL FUNCTION 'RSPC_API_CHAIN_GET_STATUS'
EXPORTING
i_chain = <c>-chain_id
i_logid = <c>-log_id
IMPORTING
e_message = lv_e_message
.
WRITE: / lv_e_message.
ENDLOOP.

Filter out special characters through SQL/AMDP

With this example AMDP script special characters gets sorted out using regular expressions:

outTab = SELECT "/BIC/A", "/BIC/B",
REPLACE_REGEXPR('([^[:print:]|^[\x{00C0}-\x{017F}]|[#])' IN "/BIC/C" WITH '' OCCURRENCE ALL ) AS "/BIC/C",
"/BIC/D", "RECORD", "SQL__PROCEDURE__SOURCE__RECORD" FROM :inTab;

The expression filters everything non printable or hash or everything which is not in the unicode character range 00C0-017F ( https://unicode-table.com/en/ ) for the InfoObject C in this case.

Alpha conversion in HANA SQL

To do a alpha conversion directly in AMDP script the following expression can be used:

SELECT ..., CASE WHEN ( LENGTH(LTRIM(hanaview."CHARACTERISTIC",' 0123456789')) = 0 and LTRIM(hanaview."CHARACTERISTIC",' ') <> '' )
THEN LPAD( hanaview."CHARACTERISTIC",5,'0' ) ELSE hanaview."CHARACTERISTIC" END as "/BIC/CHARACTERISTIC", ... FROM ...

This statement checks if the string is not empty and only numeric. If so, it fills up to in this example a length of 5 characters with 0.

I did not found a standard method to replicate users. But SAP provides mighty BADI function modules for authorizations – with them (if you have an RFC destination) it is possible to replicate users quite easy:

report z_usr_repl.

types:
begin of struc_usr,
user_src type bapibname-bapibname,
user_tgt type bapibname-bapibname,
passwd type string,
txt type string,
type type bapi_mtype,
id type symsgid,
number type symsgno,
message type bapi_msg,
log_no type balognr,
log_msg_no type balmnr,
message_v1 type symsgv,
message_v2 type symsgv,
message_v3 type symsgv,
message_v4 type symsgv,
end of struc_usr
.
data: lv_username_new type bapibname-bapibname,
lv_username_src type bapibname-bapibname,
lt_fields type slis_t_fieldcat_alv,
ls_fields like line of lt_fields,
lt_usr type standard table of struc_usr,
lt_usr2 type standard table of struc_usr,
ls_usr like line of lt_usr,
lv_return type standard table of bapiret2,
ls_logondata type bapilogond,
ls_defaults type bapidefaul,
ls_address type bapiaddr3,
lv_company type bapiuscomp,
lv_islocked type bapislockd,
lt_parameter type standard table of bapiparam,
activitygroups type standard table of bapiagr,
lt_addtel type standard table of bapiadtel,
lt_addsmtp type standard table of bapiadsmtp,
lv_password type bapipwd,
lv_lock_locally type bapiuslockx-bapiuslock,
lv_generated_password type bapipwd.

select-options: susr for ls_usr-user_src default 'MYUSER'.

loop at susr assigning field-symbol().
lv_username_new = 'MYUSER_T'.
lv_username_src = -low.
ls_usr-user_src = lv_username_src.
ls_usr-user_tgt = lv_username_new.

call function 'BAPI_USER_GET_DETAIL' " DESTINATION 'SYSCLNT100'
exporting
username = lv_username_src
* CACHE_RESULTS = 'X'
importing
logondata = ls_logondata
defaults = ls_defaults
address = ls_address
company = lv_company
* SNC = SNC
* REF_USER = REF_USER
* ALIAS = ALIAS
* UCLASS = UCLASS
* LASTMODIFIED = LASTMODIFIED
islocked = lv_islocked
* IDENTITY = IDENTITY
* ADMINDATA = ADMINDATA
* description = description
tables
parameter = lt_parameter
* PROFILES = PROFILES
activitygroups = activitygroups
return = lv_return
addtel = lt_addtel
* ADDFAX = ADDFAX
* ADDTTX = ADDTTX
* ADDTLX = ADDTLX
addsmtp = lt_addsmtp
* ADDRML = ADDRML
* ADDX400 = ADDX400
* ADDRFC = ADDRFC
* ADDPRT = ADDPRT
* ADDSSF = ADDSSF
* ADDURI = ADDURI
* ADDPAG = ADDPAG
* ADDCOMREM = ADDCOMREM
* PARAMETER1 = PARAMETER1
* GROUPS = GROUPS
* UCLASSSYS = UCLASSSYS
* EXTIDHEAD = EXTIDHEAD
* EXTIDPART = EXTIDPART
* SYSTEMS = SYSTEMS
.

move-corresponding lv_return to lt_usr2 keeping target lines .

if lv_return is initial.

clear lv_return.
call function 'BAPI_USER_DELETE'
exporting
username = lv_username_new
tables
return = lv_return.

move-corresponding lv_return to lt_usr2 keeping target lines .
clear lv_return.

lv_password-bapipwd = 'tt'.
lv_lock_locally = lv_islocked-local_lock.

call function 'BAPI_USER_CREATE1'
exporting
username = lv_username_new
logondata = ls_logondata
password = lv_password
defaults = ls_defaults
address = ls_address
company = lv_company
lock_locally = lv_lock_locally
generate_pwd = 'X'
importing
generated_password = lv_generated_password
tables
parameter = lt_parameter
return = lv_return
addtel = lt_addtel
addsmtp = lt_addsmtp.

move-corresponding lv_return to lt_usr2 keeping target lines .
clear lv_return.

call function 'BAPI_USER_ACTGROUPS_ASSIGN'
exporting
username = lv_username_new
tables
activitygroups = activitygroups
return = lv_return.

move-corresponding lv_return to lt_usr2 keeping target lines.
clear lv_return.

ls_usr-txt = 'created user'.
ls_usr-passwd = lv_generated_password.

else.
ls_usr-txt = ' Error when reading user in source'.
endif.
append ls_usr to lt_usr.
endloop.

append lines of lt_usr2 to lt_usr.

*--------------------------------------------------------------------
* Display
*--------------------------------------------------------------------

ls_fields-col_pos = 1.
ls_fields-fieldname = 'user_src'.
ls_fields-seltext_l = 'user_src'.
ls_fields-outputlen = 10.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'user_tgt'.
ls_fields-seltext_l = 'user_tgt'.
ls_fields-outputlen = 10.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'PASSWD'.
ls_fields-seltext_l = 'PASSWD'.
ls_fields-outputlen = 28.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'TXT'.
ls_fields-seltext_l = 'TXT'.
ls_fields-outputlen = 38.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'message'.
ls_fields-seltext_l = 'message'.
ls_fields-outputlen = 20.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'message_v1'.
ls_fields-seltext_l = 'message_v1'.
ls_fields-outputlen = 20.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'message_v2'.
ls_fields-seltext_l = 'message_v2'.
ls_fields-outputlen = 20.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'message_v3'.
ls_fields-seltext_l = 'message_v3'.
ls_fields-outputlen = 20.
append ls_fields to lt_fields.

ls_fields-col_pos = ls_fields-col_pos + 1.
ls_fields-fieldname = 'message_v4'.
ls_fields-seltext_l = 'message_v4'.
ls_fields-outputlen = 20.
append ls_fields to lt_fields.

* call ALV
call function 'REUSE_ALV_GRID_DISPLAY'
exporting
i_grid_title = ''
it_fieldcat = lt_fields
tables
t_outtab = lt_usr
exceptions
program_error = 1
others = 2.

if sy-subrc  0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
endif.

Create ER diagrams from SAP table using draw.io

I recently wanted to create a diagram from SAP tables. SD11 seems to be to complicated, VISIO Standard does not alow that – so I thought different and checked draw.io – which is browser based and free: https://about.draw.io/

In draw.io is the possibility to create a table based on the SQL create statement. In SAP I don’t know / found an elegant method to get the statement without database access – so I created a small abap program:

*&---------------------------------------------------------------------*
*& Report  Z_TABLE
*&
*&---------------------------------------------------------------------*
*& generate CREATE table statements
*&
*&---------------------------------------------------------------------*
report zy2bw_table.

data: lt_tabs type standard table of dd02l,
      lt_tab  type standard table of dd03m,
      lv_tab  type tabname.

select-options pt for lv_tab default 'TVARV'.

select tabname from dd02l
  into corresponding fields of table lt_tabs
  where tabname in pt.

loop at lt_tabs assigning field-symbol(<tabs>).
  clear lt_tab.

  select tabname fieldname position keyflag
    from dd03m
    into corresponding fields of table lt_tab
    where tabname = <tabs>-tabname and ddlanguage = 'E' order by position.

  loop at lt_tab assigning field-symbol(<t>).
    if <t>-position = 1.
      write: / `CREATE TABLE `, <t>-tabname.
      write: / `( `.
    endif.
    write: / <t>-fieldname.
    if <t>-keyflag = 'X'.
      write: ` PRIMARY KEY`.
    endif.
    write: `,`.
  endloop.
  write: / `); `.
endloop.

Executing this program you select one or more tables:

And the programs creates the statements based on the dictionary tables:

These statements you can then copy & paste to draw.io:

Outcome is then a table:

My sniped can be if needed of course extended – also the types are available in the SAP table dd03m.

Opening SAP transactions via PHP

With PHP and a specific generated text file it is possible to open SAP transactions.

The following describes a small PHP page which can be called with parameters to opens transactions. The PHP does not have .php, it uses .sap (through .htaccess) :

/sap.sap?cmd=SM69&param=&system=DX3&clnt=100

In the above example it opens transaction SM69 on the system DX3 / Client 100.

Same works also with function modules – as an example:

/sap.sap?cmd=SE37&param=RS38L-NAME=SXPG_CALL_SYSTEM&system=DX3&clnt=100

So how does it work? You can just have a look, the .sap file must only deliver the parameters in a specific format:

<?php
header('Content-type: application/sap');
?>[System]
Name=<?php
echo $_REQUEST["sys"]."\n";
?>
Client=<?php
if (is_null($_REQUEST["clnt"])) {
echo "100\n";
} else {
echo $_REQUEST["clnt"]."\n";
}
?>
[User]
Name=MYUSERNAME
Language=E
[Function]
Command=<?php
echo $_REQUEST["cmd"]." ".$_REQUEST["param"]."\n";
?>
Title=
Type=Transaction

KeeFox … multiple logons for the same website

A small tipp… I use the KeeFox add-on in combination with the Firefox browser and Keepass to get automatically the corresponding credentials per website.

If there are multiple logons for the same website it takes one as default. To influence which one will be taken as default it is enough to adjust the first character in the title of the multiple entries in Keepass, the first one according the alphabetical order will be taken.