Skene Intelligence*

Lately, i’ve been involved in a number of projects where there is a requirement to analyse and report on SAP BW data, using external tools other than the Business Explorer (BEx) suite native to SAP BW. While some of these analytical reporting tools are non-SAP, such as Microsoft Reporting Services, I’m seeing more and more projects where SAP Business Objects is being used to report on SAP BW data.

Indeed from an on the ground (or in the trenches) consultant’s point of view, it seems that more and more SAP BW customers are choosing, or at least investigating, adding the Business Objects toolset to their system landscape. The momentum is growing and I believe this is particularly noticeable since the release of the SAP BusinessObjects Platform BI 4.0 (SBOP), with its improved and tighter integration with SAP BW through the Business Intelligence Consumer Services (BICS) interface. The BICS interface has streamlined connectivity options to SAP BW, broken down some of the more complex integration points that previously existed in the 3.x version, and allowed most of the SBOP tools to directly access BW data through SAP BW BEx queries. The case for SAP BW only shops to report and analyse SAP BW data through SBOP BI 4.0 is becoming more compelling.

See Jonathan Haun’s ‘Why is SAP Business Objects 4.0 a better BI platform for SAP BW?’ great blog entry for a more comprehensive summary of the improved SAP BW connectivity of SBOP BI 4.0 vs. XI 3.1.

The prevelance of the BICS/BEx connectivity combination to SAP BW data from SBOP is illustrated in the below SAP slide for the Web Intelligence tool:

In terms of connectivity, direct connectivity of the SBOP Dashboards tool to BW data through BICS/BEx queries is currently possible. However, the Dashboard itself must be published to a SAP BW BI-Java instance, such as on an Enterprise Portal, to leverage the BI-Java BICS interface to SAP BW data. This connectivity regime, remains an exception compared to the other tools, as they can leverage BICS connectivity directly from the SBOP platform, without going through a BI-Java instance.

This will be resolved in the much awaited SBOP Feature Pack 3 release however, whereby Dashboards will be able to directly connect to SAP BW BEx queries through BICS whilst being hosted on SBOP. See Tammy Powlas’ excellent ASUG webcast summary on planned Feature pack 3 content and don’t miss the links to each tool at the bottom of the page. SAP has also provided a comprehensive What’s new in SAP BusinessObjects Business Intelligence Suite 4.0 Feature Package 3 guide, that provides an overview of the features and enhancements that have been added to the SAP SBOP suite since the SP2 release.

The Challenge

The challenge to a guy like me in a SAP BW / SBOP integration project, is that the SAP BW BEx queries are not accessible to external tools by default. To enable external access to BEx query data, each query needs to be opened up in the BEx Query Designer client tool and the ‘Allow External Access to this Query’ flag needs to be set. This is shown in the screenshot below:

Note that the flag is under the section 'Release for OLE DB for OLAP'. However, OLE DB (Object-Link Enablement for Data Bases for OLAP) isn't necessarily used for all external tools. As mentioned, for BusinessObjects platform connectivity, direct access is achieved via the BICS interface, and not OLE DB. Hence the same flag is used for several purposes, whether it be BICS or OLE DB connectivity. The key takeaway is the external access flag must be set to enable BEx query data connectivity to all external tools, i.e. SAP BusinessObjects Platform, IBM Cognos, Microsoft SQL Server Reporting Services (SSRS), etc.

This manual process of opening up each query and setting the flag is pretty cumbersome when multiple queries are required to be accessed in external tools. This scenario is particularly apparent when SBOP is being introduced to a mature SAP BW landscape, in which a significant investment has been made over time in developing many BEx queries. Assuming I want to access quite a lot (if not all) of those queries in SBOP or another external tool, now I need to go into each one in the BEx query designer and set the flag accordingly… there must be a better way.

The Solution

There is a better way of doing this… the answer is programatically. I came across a blog post on SCN by Suhas Karnik titled RSZELTPROP: The Query Element Parameters Table. This post detailed a method of setting the external access flag directly in the SAP BW table RSZELTPROP through determining the query element technical ID in the table RSZELTDIR. This process would alleviate the need to open up the BEx query designer and manually set the flag for each query – instead, you could just set the flag directly in the SAP BW table.

Whilst the method works and is great, I need a program that:

• 1. Can do mass updates to multiple queries at a time based on a selection parameter;
• 2. Can both set and remove the flag.
• 3. Avoids the need to manually enter table maintenance in SAP BW and perform updates;
• 4. Don’t want to have to manually map the query name to a nonsensical technical ID, then go into another table and set a flag against a technical ID.

With this is mind, I created my own program.

The Program

This is a relatively simple ABAP program that updates the external access flag for a single BEx query or multiple queries through a wildcard * query name input. Depending on the options, the external access flag can either be set or removed and a Simulation program run can be made which will show you the expected results, but won’t update any of the underlying tables. A text output is produced to notify the user what external flag updates have been made.

1. Main Screen

The main screen of the program is shown below:

Input

1. Technical BEx Query Name: Enter the BEx query technical name, i.e. ZBW_SIMPLE_QUERY_001. The wildcard * is accepted as an input, e.g. ZBW*. Furthermore F4 matchcode functionality is available to navigate through the InfoArea hierarchy structure to your query.

Options

1. Set External Flag: When this checkbox is set, the external access flag for the specified query is set, i.e. turned on. When the checkbox is not set, the external access flag is removed, i.e. turned off. The Set External Flag checkbox is set by default, when the program is opened.

2. Simulate: When this checkbox is set, the program is run in a simulation mode, in which the external access flag(s) for the queries specified are NOT changed and no table updates are made. This is a useful feature which can test and illustrate the expected results, prior to committing changes to the underlying table.

2. Sample Outputs

1. Query input: 0TCT_MCWS* with Set External Flag checkbox set.

Result:

2. Query input: 0TCT_MCWS* with Set External Flag checkbox NOT set.

Result:

3. Technical Details

Please find my code below, along with accompanying text symbols and selection texts for you to paste into the program.

Feel free to use and modify as you see fit. I hope you get great value out of it in automating the often mundane procedure of setting and removing the external access flag in the BEx query designer. I sure have!

Cheers,
Campbell

---

Text Symbols

Symbol	Text	dLen	mLen
1	(Wildcard * accepted)	21	21
2	Options	7	7
3	Set External Access Flag (Blank will remove)	44	44

Selection Texts

Name	Text
P_BEX_FL	Set External Flag
P_QUERY	Technical BEx Query Name
P_SIM_FL	Simulate

ABAP Program Code

*&---------------------------------------------------------------------*
*& Report  ZBW_QUERY_EXTERNAL_UPDATE
*&---------------------------------------------------------------------*
*&
*& Title: Update BEx Queries for External Access
*& Created by: C.Skene on 14/03/2012
*& Version: 0.2
*&
*&  Purpose:
*&  This program updates the external access flag for a single BEx query or
*&  multiple queries through a wildcard * query name input. The flag can either
*&  be set or removed, depending on input parameters.  When the external flag for
*&  a BEx query is set, the BEx query data can be read from external non-BW systems,
*&  such as Business Objects.
*&
*&  Use:
*&  1. Enter a BEx query technical name into the parameter.
*&     The wildcard * is accepted, e.g. 'ZBW*'; or
*&  2. Use the match code navigation to navigate to your query and
*&     double-click to select it.
*&
*&---------------------------------------------------------------------*
*& Version Control:
*& 0.1 - 14/03/2012 (CSKENE) Initial implementation
*& 0.2 - 28/03/2012 (CSKENE) Updated to include option to remove external
*&                           access flag, and simulation mode, allowing a
*&                           user to see results before performing the
*&                           table update.
*&
*&---------------------------------------------------------------------*

REPORT  ZBW_QUERY_EXTERNAL_UPDATE.

*----------------------------------------------------------------------*
* Declarations                                                         *
*----------------------------------------------------------------------*

TYPES: BEGIN OF ty_queries,
          mapname type rszeltdir-mapname,         " query name
          eltuid  type rszeltprop-eltuid,         " technical query ID
          rfcsupport type rszeltprop-rfcsupport,  " external flag
       END OF ty_queries.

DATA: gs_queries TYPE ty_queries,
      gt_queries TYPE STANDARD TABLE OF ty_queries,
      l_eltuid type rszeltprop-eltuid,
      l_ext_fl type rszeltprop-rfcsupport,
      l_sim_fl type c,
      l_techname type rszeltdir-mapname.

FIELD-SYMBOLS:  TYPE ty_queries.

*----------------------------------------------------------------------*
* Selection-Screen                                                     *
*----------------------------------------------------------------------*

SELECTION-SCREEN BEGIN OF BLOCK 1.
  PARAMETERS: p_query TYPE RSZELTDIR-MAPNAME OBLIGATORY.

  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 1(21) TEXT-001.
  SELECTION-SCREEN END OF LINE.

    SELECTION-SCREEN SKIP 1.
SELECTION-SCREEN END OF BLOCK 1.

SELECTION-SCREEN BEGIN OF BLOCK 2 WITH FRAME TITLE TEXT-002.

  SELECTION-SCREEN BEGIN OF LINE.
    PARAMETERS: p_bex_fl AS CHECKBOX DEFAULT 'X'.
    SELECTION-SCREEN COMMENT 2(44) TEXT-003 FOR FIELD p_bex_fl.
  SELECTION-SCREEN END OF LINE.

  PARAMETERS: p_sim_fl AS CHECKBOX DEFAULT ''.

SELECTION-SCREEN END OF BLOCK 2.

START-OF-SELECTION.

*----------------------------------------------------------------------*
* Parameter preparation                                                *
*----------------------------------------------------------------------*

* Replace asterix with % for select statement
  l_techname = p_query.
  REPLACE ALL OCCURENCES OF '*' IN l_techname WITH '%' IN CHARACTER MODE.

* Set BEx flag according to parameter p_bex_fl
  l_ext_fl = p_bex_fl.

  l_sim_fl = p_sim_fl.

  IF l_sim_fl = 'X'.
    WRITE: / 'SIMULATION MODE'.
    ULINE.
  ENDIF.

  WRITE: / 'Searching for BEx Query: ', p_query.
  SKIP 1.

*----------------------------------------------------------------------*
* Find Queries based on input                                          *
*----------------------------------------------------------------------*

* The table RSZELTDIR holds the details of all query elements.
* Query Technical Names are contained in the MAPNAME field with
* active versions given by 'A' in the OBJVERS field and DEFTP being
* 'REP' for Report.
* Retrieve the Technical Query ID, ELTUID for further operations.

  SELECT a~MAPNAME b~ELTUID b~RFCSUPPORT
    INTO TABLE gt_queries
    FROM RSZELTDIR AS a
    INNER JOIN RSZELTPROP AS b
    ON a~ELTUID = b~ELTUID
    WHERE a~MAPNAME LIKE l_techname " Query tech name
    AND a~OBJVERS EQ 'A'  " Active
    AND a~DEFTP EQ 'REP'    " Query and not a CKF or other element
    AND b~OBJVERS EQ 'A'.    " Active

* Query found
  IF sy-subrc EQ 0.

*----------------------------------------------------------------------*
* Loop on results: Set external access flag or report back otherwise   *
*----------------------------------------------------------------------*

    LOOP AT gt_queries ASSIGNING .

      CASE -rfcsupport.

        WHEN ''.

          IF l_ext_fl = 'X'.
* Set flag
            IF l_sim_fl NE 'X'. " Not a simulation
              UPDATE RSZELTPROP SET RFCSUPPORT = l_ext_fl
                WHERE ELTUID EQ -ELTUID
                AND OBJVERS EQ 'A'.
            ENDIF.

            WRITE: / 'Query: ', -mapname, 'Object ID', -eltuid, ' set for external access'.

          ELSE.
* External access all ready removed
            WRITE: / 'Query: ', -mapname, 'Object ID', -eltuid, ' external access all ready removed'.
          ENDIF.

        WHEN 'X'.

          IF l_ext_fl = 'X'.
* All ready active
            WRITE: / 'Query: ', -mapname, 'Object ID', -eltuid, ' all ready active'.
          ELSE.

            IF l_sim_fl NE 'X'. " Not a simulation
* Remove external access, set flag to blank
              UPDATE RSZELTPROP SET RFCSUPPORT = l_ext_fl
                WHERE ELTUID EQ -ELTUID
                AND OBJVERS EQ 'A'.
            ENDIF.
            WRITE: / 'Query: ', -mapname, 'Object ID', -eltuid, ' external access removed'.
          ENDIF.

        WHEN OTHERS.
* Not found
          WRITE: / 'Query: ', -mapname, ' not found'.

      ENDCASE.

    ENDLOOP.

* Query not found
  ELSE.
    WRITE: / 'Query: ', p_query, ' not found'.

  ENDIF.

* Clean-up
  REFRESH gt_queries.
  CLEAR: l_techname, l_ext_fl, l_sim_fl.