Setting the External Access flag in a BEx Query

BEx External Query Flag Program

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:

SAP NetWeaver BW with SAP BusinessObjects Web Intelligence 4.0

SAP BusinessObjects Web Intelligence 4.0 - Connectivity Options

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:

Setting the External Access flag in a BEx Query

Setting the External Access flag in a BEx Query

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:

Main screen - Update BEx queries for External Access

Main screen - Update BEx queries for External Access

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.

Sample query wildcard input - 0TCT_MCWS*

Sample query wildcard input - 0TCT_MCWS*

Result:

Sample result - Query input 0TCT_MWS*

Sample result - Query input 0TCT_MWS*

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

BEx External Access Flag NOT set: 0TCT_MCWS* input

BEx External Access Flag NOT set - 0TCT_MCWS* input

Result:

Sample result - Remove External Access: Query input 0TCT_MWS*

Sample result - Remove External Access: Query input 0TCT_MWS*

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

SymbolTextdLenmLen
1(Wildcard * accepted)2121
2Options77
3Set External Access Flag (Blank will remove)4444

Selection Texts

NameText
P_BEX_FLSet External Flag
P_QUERYTechnical BEx Query Name
P_SIM_FLSimulate

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: <l_s_queries> 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 <l_s_queries>.
 
      CASE <l_s_queries>-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 <l_s_queries>-ELTUID
                AND OBJVERS EQ 'A'.
            ENDIF.
 
            WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' set for external access'.
 
          ELSE.
* External access all ready removed
            WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' external access all ready removed'.
          ENDIF.
 
        WHEN 'X'.
 
          IF l_ext_fl = 'X'.
* All ready active
            WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-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 <l_s_queries>-ELTUID
                AND OBJVERS EQ 'A'.
            ENDIF.
            WRITE: / 'Query: ', <l_s_queries>-mapname, 'Object ID', <l_s_queries>-eltuid, ' external access removed'.
          ENDIF.
 
        WHEN OTHERS.
* Not found
          WRITE: / 'Query: ', <l_s_queries>-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.

9 comments

  1. Jonathan Haun · April 5, 2012

    Great posting. This is excellent information for Enterprises that need to mass update their BEx queries.

    • Cam · April 5, 2012

      Thanks Jonathan – hope you get some great value out of the tool and feel free to send me feedback and suggestions for any improvements. Cheers, Campbell

    • Areif Shaik · April 5, 2012

      Thanks, I was looking at best practices for SAP BW web queries and SBOP 4.0. this helps a lot.

      • Cam · April 5, 2012

        No problem Areif, glad you found it useful. No doubt you’ve seen that SAP are heavily investing in the ongoing development of the BusinessObjects tools and things are moving pretty quickly on the integration points (btw very excited about the GA of SP4!). However BEx queries are the go forward mechanism for exposing BW resident data to BI 4.0 through BICS and will be around for sometime to come.

  2. kaustubh · April 5, 2012

    Hi All,
    i want exposed the bex query for NON BW system can u please help for that.
    how to create the web service on bw queries & another system only read web service to access the bw data . kindly suggest.

    • Cam · April 5, 2012

      Hi kaustabh, this is a little outside the bounds of this post – there are a number of ways to achieve this depending on the scenario. I’d firstly look at what system you are using for reporting and see if there are any standard drivers provided by the vendor for querying BW data (or loading it into your system). For instance, Microsoft SSRS, MicroStrategy, IBM Cognos, Oracle, Qlikview, etc. provide standard ETL approaches and/or integration drivers.

      Failing that I’m aware that SAP BW systems running on version 7.0 and greater, provide a mechanism to generate your own bespoke function module based on a fixed query, this is then exposed in a web service and run on MDX. See How to Expose A BW Query As Web Service for further details to get started.

      There are other ways to implement this in a more flexible manner in which you pass the query name to a web service, but that will require a little more programming. I’d caution you before going down this approach as dependent on your requirements, MDX may not be performant, and there could be significant maintenance and development overheads in this approach.

      Best of luck, Cam

  3. Olivier Grandhenry · April 5, 2012

    Hi,

    Here using SAP 7.x with BO XI 4.0 SP4, updating RFCSUPPORT in table RSZELTPROP is NOT enough.

    Table RSRREPDIR needs its RFCSUPPORT field updated as well.

    Brgds,
    Olivier

    • Cam · April 5, 2012

      Hi Olivier,

      Thanks for your feedback. By not enough am I right that you’re saying that without updating RSRREPDIR, you can’t access BEx queries in SBOP BI 4.0 SP4? When I built this program we were using SBOP BI 4.0 SP2 (pre feature pack 3 days) and it worked just fine in BI 4.0. The ‘Allow external access to this query’ flag was checked for each query in the BEx query designer, and each query was able to be accessed through BICS in BI 4.0.

      I’ll also do a check in SP4 and update the program accordingly if needs be, and give you a credit in the code!

      Cheers,
      Campbell

      • Olivier Grandhenry · April 5, 2012

        Hi Campbell,

        Sorry for late reply, was getting some rest in Tuscany, Italy 🙂

        So “yes”, RSRREPDIR needs to be updated as well in order to see BEx Queries in BO 4 SP4.

        Cheers,
        Olivier