How to find existing workbooks in BW system

Here are few options how to find workbook in the SAP BW system.

1. Via BEx Analyzer: in menu of Analyzer called Open -> Open workbook. This give only list of workbooks that user is authorized to authorized to access to. Either via menu role, favorites etc.

  

2. Via Analyzer Office for MS Excel: in menu Analysis -> Open Workbook -> it can be opened either from BusinessObjects platform or from SAP NetWeaver platform. Here again only access to the workbooks that user is authorized to access. Just these workbooks are not those created in BEx Analyzer but either migrated to AfO or newly created in AfO.

3. Via RSA1 – Metadata Repository: in t-code RSA1 -> Modeling -> Metadata Repository all the workbooks from all users available are listed. While drilling into it all details like what queries are used in the WB, who created it, when, last change and many others also can be found.

4. Via ABAP – FM RRMX_WORKBOOK_LIST_GET. If you need to deal with available workbooks in your custom ABAP program you can use this FM to get list of the existing workbook. Input parameters are just 2:

I_SORT                          1           “comes by default

I_ALL                           Y            “needs to be supplied

Output is a table E_LIST which has all information about the workbooks like:

WORKBOOKID - GUID of the workbook      

TITLE                      - Long description

FILESIZE                 - workbook file size in bytes

QUERYCOUT             - no of queries used in the workbook      

TYPE                      - Indicator: InfoCatalog type

TSTPNM                  - Last changed by user

TIMESTMP               - Last changed on date and time in timestamp format

 

5. t-code RRMXP – program RRMX_START_EXCEL, here you can specify workbook technical ID and once it is entered it opens BEx Analyzer with particular workbooks.

 

Administration of BW workbooks

By term a workbook we refer to MS Excel file that contains a worksheet(s) having SAP BW queries inserted in. Basically when the workbook is stored it physically contains data pulled from SAP BW system via BEx Query. The workbooks can be either stored on the SAP BW backend or locally in user’s workstation.

In case the users are using the SAP BW system to store their workbooks after same time there can be a huge number of the workbooks pilling up on the server. The BW administrator therefore shall take care about it and do maintenance of the workbooks. There is couple of tools (ABAP reports) provided by SAP to support this task. Below I briefly introduce them.

I wrote similar post about the workbook deletion earlier here. This one is more general with regards to the workbook maintenance.

Report RSR_WORKBOOK_ADMINISTRATION (t-code RSRWBADMIN) – it takes care about mass deletion of the BW workbooks from the system. Multiple criteria are available on its selection screen. There are selection fields like Workbook Version/ ID/Owner, Last Changed by, Last Changed on, Workbook Size (Kb), Number of Changes, Number of Reads, Workbook Extension, Original System etc. Once the workbooks are found according the selection then either one or many can be deleted from report’s output screen. This report is dedicated to version 7.30 and 7.40 of the SAP BW.

Report RSR_WORKBOOK_ADMIN (t-code RSRWBADM) – same as above but dedicated to versions 7.00 and 7.11 of the SAP BW.

Report COMPONENT_REORG (t-code RSZDELETE) – generic t-code which also is performing a deletion of BW BEx queries. It utilizes FM RRMX_WORKBOOKS_DELETE  to perform deletion. It can be used as explained in my other post: Deletion of BEx workbooks

Report RRMX_WORKBOOKS_UNTOUCHED_PURGE - delete unreferenced (orphaned) workbooks. These are the workbooks that are not referenced to any role, there are no in any user’s favorites or there are no in any InfoCatalog. Report has also an option to specify on selection screen so called “Minimum age (in days)” however this field/variable is later not used in the code so the field has no meaning. Tested on version SAP_BW 740     SP09.

Report RRMX_WORKBOOKS_GARBAGE_COLLECT - identifies and removes orphaned workbooks. The report has no other option available on selection screen apart of checkbox “Delete workbooks found”. Basically it does the exactly the same as *_PURGE report

FM RSBB_WHERE_USED_LIST – provides a where used list of a workbook in activity groups.

Report RSWB_ROLES_REORG - Reorganization of the workbook and role storage. It checks the assignment of the workbooks to roles and the workbooks to favorites in both directions and it deletes assignments of non-existent IOs.

Deletion of BEx workbooks

Sometimes during cleanup or maintenance activities there are many obsolete workbooks found in the BW systems. Even in case just one orphaned workbooks is found that was linked to query which was just test query such workbook needs to be deleted. In best case we ask user who created the workbook to delete. But in case when user left organization long time back someone else needs to step in and delete the workbook.

The question is how to perform it. We can do it in following ways:

1. Function module RRMX_WORKBOOKS_DELETE run it in tcode SE37 and provide following input parameters:

I_WORKBOOKID                 

I_OBJVERS                       A

I_PERSONALIZED_WORKBOOK         R

Notice that there are no any messages neither return code returned by the FM. In case deletion is successful there are no output parameters at all.

2. T-code RSZDELETE: In this case also query on which the workbook is originated is deleted. On selection screen provide report technical ID and thick off “Workbooks” check box.

On next screen select DELETE checkbox for corresponding query:

Once it is finished following confirmation popup is displayed:

How to determine what BEx query is used in BW workbook

Workbooks are integral part of SAP BW. They are used very much within users as they are very helpful. Basically they allow having several worksheets having run there different BEx queries. Even several BEx queries can be placed in single workbook.

Setup of such a workbook is fairly easy. The Bex query (or queries) is just inserted into MS Excel while connected to BW backend system either via Business Explorer Analyzer or Analysis office.

The workbook can be saved in BEx favorites in backend system in user's favorites or in user's roles.

Usual question while dealing with the workbook is to get know what are Bex query or queries contained in there. Here either we recognize this directly as we are connected to backend via Analyzer or Analysis office. Second option is to lookup this relation in BW tables.

Table RSRWORKBOOK contains information on 'Where-used list' for reports in workbooks. Here we put the workbook ID into field WORKBOOKID. From this table we get a list of BEx queries in column GENUNIID. With this information we go to table RSRREPDIR where we put it to column GENUNIID. Finally in column COMPID we get BEx query technical name(s).

Refresh Function for Individual Query in BEx Analyzer

Sometimes BW power users are maintaining huge workbooks with many queries. Kind of their “personal dashboards” with important data out of BW systems. The problem with these workbooks is that sometimes it takes very long time to get the workbook refreshed. To solve this; users were looking to kind of functionality where they could decide which query contained in the workbook should be refreshed.

Finally this function is available there. It is called "The Single Query refresh" functionality of the BexAnalyzer. It allows refreshing individual queries if there are several queries used in the workbook. Before once user clicked on refresh of one query; it refreshed it but also other queries were refreshed.

To implement this we need:

1. In BW server: enable parameter ANA_SINGLEDPREFRESH with value X in transaction RS_FRONTEND_INIT.

2. In the workbooks: on General tab of the Workbook in Settings dialog, a flag "Allow Refresh Function for Individual Queries" must be checked. Once this is done there in context menu of an item that belongs to the query a query it has one more option called "Refresh This Query".