Skip to Content

OQR.png

Applies to:

SAP BI 3.x and 7.x

Summary

Often in BI Migration projects or in Application support, it is very necessary to check or to know where certain Info Objects (Character, Key figure) or navigation attributes has been used in Queries and /or workbooks. So this “OQR tool” comes very handy and provides all such information at a single click of button.

Author(s): Anil Raparthi

Company: Bayer Business Services. (Hong Kong)

Created on: May 30, 2012

Author Bio

Anil.png

Author Bio

Anil Raparthi is a Senior BI consultant currently working with Bayer Business Services (Hong Kong) which is an internal IT service provider for Bayer AG. He has more than 8 years of core BI experience which was archived while working with many multinational clients across the world. 

He is also expertise in various BI technologies apart from SAP and also experienced in emerging technologies like SAP Enterprise Widgets, SAP Mobility and SAP HANA.

In the past, he has also contributed several other knowledge documents on SDN.

Table of Contents

As-is Situation

Introduction

Scope of ‘OQR Tool’

OQR Selection Screen

Working with OQR Tool

     • InfoObject (Characteristics & Key figure) –>Query/Workbook “where use list”

     • Navigational Attribute –> Query/Workbook “where use list”

OQR source code

As-is Situation

Often in BI migration projects or regular application support, it is very necessary to know where a particular InfoObject (Characteristic InfoObject or a Keyfigure InfoObject) or a particular Navigation attribute has been used in the reporting layer (i.e. Queries and Workbooks)

Unfortunately at the moment we do not have any standard application or tool on which can provide such useful information on SAP BI systems when required.

Introduction

This below tool is called as ‘OQR’ which means Object Query Reference and this can provide the list of all the Queries, Query Data target and also the list of all  workbooks where these queries are assigned, in just a single click.

Scope of ‘OQR Tool’

• Provide InfoObject –> Query and Workbook ‘where use list’

• Provide Navigational Attributes –> Query and Workbook ‘where use list’

• Fast retrieval of the information

• Usage can be restricted if the program is assigned a Transaction code and then in to the roles

• Simple and User friendly

OQR Selection Screen

/wp-content/uploads/2012/05/1_107014.png

Working with OQR Tool:

   • InfoObject (Characteristics & Key figure) –> Query/Workbook “where use list”

          For eg: 0CUSTOMER

/wp-content/uploads/2012/05/2_107015.png

Output

/wp-content/uploads/2012/05/3_107017.png

    • Navigational Attribute –> Query/Workbook “where use list”

         For eg: 0CUSTOMER__0CITY

/wp-content/uploads/2012/05/4_107018.png

Output

/wp-content/uploads/2012/05/5_107019.png

Note:
OQR Tool is designed to show Queries and Workbooks which are built on Cubes and Multiproviders only. It cannot be used for DSOs or InfoSets based queries/workbooks.

OQR Source Code:

*&———————————————————————*

*& Report  Z_OBJ_QUERY_REF_TOOL

*&

*&———————————————————————*

*& Created by: Anil Raparthi||Date: 20 Apr 2012||CRT: xxxxx

*&———————————————————————*

REPORT  Z_OBJ_QUERY_REF_TOOL

   LINE-SIZE 170

   LINE-COUNT 58.

* —– Database Tables ————————————————

TABLES: RSZSELECT,         ” Selection properties of an element

        RSZELTXREF,        ” Directory of query elements

        RSRREPDIR,         ” Directory of all reports

        RSZELTTXT,         ” Text of reporting components

        RSZRANGE,          ” Selection Specs for an element

        RSZGLOBV,          ” Global variables in reporting

        RSZCOMPDIR,        ” Directory of reporting componenets

        RSRWORKBOOK,       ” Where-used list for reports in workbooks

        RSRWBINDEX,        ” Excel workbooks

        RSRWBINDEXT,       ” Excel workbook titles

        RSDIOBJ,           ” Directory of InfoObjects

        RSDIOBJT,          ” Text for InfoObjects

        RSDCUBET,          ” Text for InfoCubes

        RSDATRNAV,         ” Navigation Attributes

        RSDATRNAVT.        ” Navigation Attributes Text

*

* —– Internal Tables ————————————————

DATA: BEGIN OF TAB1 OCCURS 0,

        INFOOBJECT_NAME   LIKE RSDIOBJT-TXTLG,

        INFOOBJECT        LIKE RSDIOBJ-IOBJNM,

        INFOCUBE_NAME     LIKE RSDCUBET-TXTLG,

        INFOCUBE          LIKE RSRREPDIR-INFOCUBE,

        QUERY_NAME        LIKE RSZELTTXT-TXTLG,

        QUERY_TECH_NAME   LIKE RSRREPDIR-COMPID,

        WORKBOOK          LIKE RSRWBINDEXT-TITLE,

      END OF TAB1.

*

* —– Internal Tables ————————————————

DATA: BEGIN OF TAB2 OCCURS 0,

        CHARACTERISTIC    LIKE RSDATRNAV-CHANM,

        NAV_ATTR_NAME     LIKE RSDATRNAVT-TXTLG,

        ATTRIBUTE         LIKE RSDIOBJ-IOBJNM,

        NAV_ATTRIBUTE     LIKE RSDATRNAV-ATRNAVNM,

        INFOCUBE_NAME     LIKE RSDCUBET-TXTLG,

        INFOCUBE          LIKE RSRREPDIR-INFOCUBE,

        QUERY_NAME        LIKE RSZELTTXT-TXTLG,

        QUERY_TECH_NAME   LIKE RSRREPDIR-COMPID,

        WORKBOOK          LIKE RSRWBINDEXT-TITLE,

      END OF TAB2.

*

* —– Data Definitions ———————————————–

DATA: H_TEXT(170)       TYPE C.

DATA: H_INFOCUBE(60)    TYPE C.

DATA: H_QUERY(30)       TYPE C.

DATA: H_workbook(60)    TYPE C.

DATA: VAR1              TYPE C.

*

* —– Parameters and Select-options ———————————-

SELECTION-SCREEN BEGIN OF BLOCK BLK1

  WITH FRAME TITLE TITLE1.

*

PARAMETERS: P_INFOBJ   radiobutton group g1 DEFAULT ‘X’, ” Info Object Listing

            P_NAVATT  radiobutton group g1.             ” Nav Attr Listing

SELECT-OPTIONS: S_VERSN  FOR RSRREPDIR-OBJVERS      ” Version

                  OBLIGATORY NO-EXTENSION NO INTERVALS DEFAULT ‘A’,

                S_IOBJNM FOR RSRREPDIR-INFOCUBE     ” InfoObject

                  OBLIGATORY NO INTERVALS,

                S_ATTR   FOR RSDATRNAV-ATTRINM     ” Attribute

                  NO INTERVALS. ” DEFAULT ‘*’.

SELECTION-SCREEN END OF BLOCK BLK1.

*

SELECTION-SCREEN BEGIN OF BLOCK BLK2

  WITH FRAME TITLE TITLE2.

*

PARAMETERS: P_CUBE   AS CHECKBOX TYPE C  DEFAULT ‘X’,  ” Display Cube Information

            P_QRY    AS CHECKBOX TYPE C  DEFAULT ‘X’,  ” Display Query Information

            P_WB     AS CHECKBOX TYPE C  DEFAULT ‘X’.  ” Display Workbook Information

SELECTION-SCREEN END OF BLOCK BLK2.

*

SELECTION-SCREEN BEGIN OF BLOCK BLK3

  WITH FRAME.

SELECTION-SCREEN COMMENT 1(9)    COMMENT1.

SELECTION-SCREEN COMMENT /1(79)  COMMENT2.

SELECTION-SCREEN COMMENT /1(79)  COMMENT4.

SELECTION-SCREEN COMMENT /1(79)  COMMENT3.

SELECTION-SCREEN END OF BLOCK BLK3.

* —– Initialization ————————————————-

INITIALIZATION.

  TITLE1 = ‘Report Selections’.

  COMMENT1 = ‘NOTE:’.

  COMMENT2 = ‘InfoObject/Qry Ref: Please provide InfoObject Tech-name.’.

  COMMENT3 = ‘Generic searches may take a while to run.’.

  COMMENT4 = ‘Navi Attr/Qry Ref: Please provide InfoObject and Attr Tech-name.’.

*

* —– At START-OF-SELECTION ———————————————-

START-OF-SELECTION.

VAR1 = ‘A’.

IF P_INFOBJ = ‘X’.

* —– Main Processing Info Obj ——————————————–

  PERFORM F010_EXTRACT_INFOOBJECT.

ENDIF.

IF P_NAVATT = ‘X’.

* —– Main Processing for NAV ATTR —————————————–

  PERFORM F0010_EXTRACT_NAVATT.

ENDIF.

*

END-OF-SELECTION.

*

* —– INFO OBJECT————————————————————

IF P_INFOBJ = ‘X’.

  SORT TAB1

    BY INFOOBJECT

       INFOCUBE

       QUERY_TECH_NAME.

*

  FORMAT: COLOR 2 ON, INTENSIFIED ON.

  WRITE: AT /6  ‘Query Technical Name’,

         AT 38  ‘Query Description’,

         AT 100 ‘Workbook’.

  WRITE: / SY-ULINE(170).

  FORMAT: COLOR OFF.

  IF VAR1 EQ ‘A’.

  WRITE: AT /38  ‘Sorry! NO Information Available’.

  ENDIF.

*

  LOOP AT TAB1.

    AT NEW INFOOBJECT.

      CONCATENATE ‘InfoObject:’ TAB1-INFOOBJECT ‘-‘

                  TAB1-INFOOBJECT_NAME

                    INTO H_TEXT

                    SEPARATED BY SPACE.

      WRITE AT / H_TEXT.

    ENDAT.

IF P_CUBE = ‘X’.

    AT NEW INFOCUBE.

      CONCATENATE ‘InfoCube:’ TAB1-INFOCUBE ‘-‘

                  TAB1-INFOCUBE_NAME

                    INTO H_TEXT

                    SEPARATED BY SPACE.

      WRITE /.

      WRITE AT /3 H_TEXT.

    ENDAT.

ENDIF.

    IF TAB1-QUERY_TECH_NAME <> H_QUERY.

      IF P_QRY = ‘X’.

        WRITE: AT /6  TAB1-QUERY_TECH_NAME,

               AT 38  TAB1-QUERY_NAME.

      ENDIF.

      IF P_WB = ‘X’ and P_QRY = ‘X’.

        WRITE: AT 100 TAB1-WORKBOOK.

      ENDIF.

      IF P_WB = ‘X’ and P_QRY = ”.

        WRITE: AT /100 TAB1-WORKBOOK.

      ENDIF.

          MOVE TAB1-QUERY_TECH_NAME TO H_QUERY.

      MOVE TAB1-WORKBOOK        TO H_WORKBOOK.

    ELSE.

      IF TAB1-WORKBOOK <> H_WORKBOOK.

        IF P_WB = ‘X’.

          WRITE: AT /100 TAB1-WORKBOOK.

        ENDIF.

        MOVE TAB1-WORKBOOK TO H_WORKBOOK.

      ENDIF.

    ENDIF.

    AT END OF INFOOBJECT.

      WRITE: / SY-ULINE(170).

    ENDAT.

ENDLOOP.

ENDIF.

*

* —– NAVI ATTR —————————————————————

IF P_NAVATT = ‘X’.

  SORT TAB2

    BY NAV_ATTRIBUTE

       INFOCUBE

       QUERY_TECH_NAME.

*

  FORMAT: COLOR 2 ON, INTENSIFIED ON.

  WRITE: AT /6  ‘Query Technical Name’,

         AT 38  ‘Query Description’,

         AT 100 ‘Workbook’.

  WRITE: / SY-ULINE(170).

  FORMAT: COLOR OFF.

  IF VAR1 EQ ‘A’.

  WRITE: AT /38  ‘Sorry! NO Information Available’.

  ENDIF.

*

  LOOP AT TAB2.

    AT NEW NAV_ATTRIBUTE.

      CONCATENATE ‘Navigational Attribute:’ TAB2-NAV_ATTRIBUTE ‘-‘

                  TAB2-NAV_ATTR_NAME

                    INTO H_TEXT

                    SEPARATED BY SPACE.

      WRITE AT / H_TEXT.

    ENDAT.

IF P_CUBE = ‘X’.

    AT NEW INFOCUBE.

      CONCATENATE ‘InfoCube:’ TAB2-INFOCUBE ‘-‘

                  TAB2-INFOCUBE_NAME

                    INTO H_TEXT

                    SEPARATED BY SPACE.

      WRITE /.

      WRITE AT /3 H_TEXT.

    ENDAT.

ENDIF.

    IF TAB2-QUERY_TECH_NAME <> H_QUERY.

      IF P_QRY = ‘X’.

       WRITE: AT /6  TAB2-QUERY_TECH_NAME,

              AT 38  TAB2-QUERY_NAME.

      ENDIF.

      IF P_WB = ‘X’ and P_QRY = ‘X’.

       WRITE: AT 100 TAB2-WORKBOOK.

      ENDIF.

      IF P_WB = ‘X’ and P_QRY = ”.

       WRITE: AT /100 TAB2-WORKBOOK.

      ENDIF.

      MOVE TAB2-QUERY_TECH_NAME TO H_QUERY.

      MOVE TAB2-WORKBOOK        TO H_WORKBOOK.

    ELSE.

      IF TAB2-WORKBOOK <> H_WORKBOOK.

        IF P_WB = ‘X’.

          WRITE: AT /100 TAB2-WORKBOOK.

        ENDIF.

        MOVE TAB2-WORKBOOK TO H_WORKBOOK.

      ENDIF.

    ENDIF.

    AT END OF NAV_ATTRIBUTE.

      WRITE: / SY-ULINE(170).

    ENDAT.

  ENDLOOP.

  ENDIF.

*———————————————————————*

*       FORM F010_EXTRACT_INFOOBJECT                                  *

*———————————————————————*

FORM F010_EXTRACT_INFOOBJECT.

  SELECT * FROM RSDIOBJ

    WHERE IOBJNM  IN S_IOBJNM

      AND OBJVERS IN S_VERSN.

    SELECT SINGLE * FROM RSDIOBJT

      WHERE LANGU   = SY-LANGU

        AND IOBJNM  = RSDIOBJ-IOBJNM

        AND OBJVERS = RSDIOBJ-OBJVERS.

    CASE RSDIOBJ-IOBJTP.

      WHEN ‘CHA’.

        PERFORM F020_CHARACTERISTICS_FIND.

        PERFORM F035_CHECK_QUERY_SELECTIONS.

      WHEN ‘KYF’.

        PERFORM F030_KEY_FIGURE_FIND.

    ENDCASE.

  ENDSELECT.

ENDFORM.

*———————————————————————*

*       FORM F020_CHARACTERISTICS_FIND                                *

*———————————————————————*

FORM F020_CHARACTERISTICS_FIND.

  SELECT * FROM RSZSELECT

    WHERE IOBJNM  = RSDIOBJ-IOBJNM

      AND OBJVERS = RSDIOBJ-OBJVERS.

    SELECT * FROM RSZELTXREF

      WHERE TELTUID = RSZSELECT-ELTUID

        AND OBJVERS = RSDIOBJ-OBJVERS.

      SELECT * FROM RSRREPDIR

        WHERE COMPUID = RSZELTXREF-SELTUID

          AND OBJVERS = RSDIOBJ-OBJVERS.

        PERFORM F040_GET_QUERY_DETAILS.

      ENDSELECT.

    ENDSELECT.

  ENDSELECT.

ENDFORM.

*———————————————————————*

*       FORM F030_KEY_FIGURE_FIND                                     *

*———————————————————————*

FORM F030_KEY_FIGURE_FIND.

  SELECT * FROM RSZRANGE

    WHERE LOW     = RSDIOBJ-IOBJNM

      AND OBJVERS = RSDIOBJ-OBJVERS.

    SELECT * FROM RSZELTXREF

      WHERE TELTUID = RSZRANGE-ELTUID

        AND OBJVERS = RSDIOBJ-OBJVERS.

      SELECT * FROM RSZELTXREF

        WHERE TELTUID = RSZELTXREF-SELTUID

          AND OBJVERS = RSDIOBJ-OBJVERS.

        SELECT * FROM RSRREPDIR

          WHERE COMPUID = RSZELTXREF-SELTUID

            AND OBJVERS = RSDIOBJ-OBJVERS.

          PERFORM F040_GET_QUERY_DETAILS.

        ENDSELECT.

      ENDSELECT.

    ENDSELECT.

  ENDSELECT.

ENDFORM.

*———————————————————————*

*       FORM F035_Check_Query_Selections                              *

*———————————————————————*

FORM F035_CHECK_QUERY_SELECTIONS.

  SELECT * FROM RSZRANGE   ” Only need to see if IOBJ occurs once

    WHERE IOBJNM  = RSDIOBJ-IOBJNM

      AND OBJVERS = RSDIOBJ-OBJVERS

      AND ENUM    = 1.

    SELECT * FROM RSZELTXREF

      WHERE TELTUID = RSZRANGE-ELTUID

        AND OBJVERS = RSDIOBJ-OBJVERS.

      SELECT * FROM RSZELTXREF

        WHERE TELTUID = RSZELTXREF-SELTUID

          AND OBJVERS = RSDIOBJ-OBJVERS.

        SELECT * FROM RSRREPDIR

          WHERE COMPUID = RSZELTXREF-SELTUID

            AND OBJVERS = RSDIOBJ-OBJVERS.

          PERFORM F040_GET_QUERY_DETAILS.

        ENDSELECT.

      ENDSELECT.

    ENDSELECT.

  ENDSELECT.

ENDFORM.

*———————————————————————*

*       FORM F040_GET_QUERY_DETAILS                                   *

*———————————————————————*

FORM F040_GET_QUERY_DETAILS.

  SELECT SINGLE * FROM RSZELTTXT

    WHERE ELTUID  = RSRREPDIR-COMPUID

      AND OBJVERS = RSDIOBJ-OBJVERS

      AND LANGU   = SY-LANGU.

  SELECT SINGLE * FROM RSDCUBET

    WHERE LANGU    = SY-LANGU

      AND INFOCUBE = RSRREPDIR-INFOCUBE

      AND OBJVERS  = RSDIOBJ-OBJVERS.

  CLEAR TAB1.

  MOVE RSDIOBJ-IOBJNM     TO TAB1-INFOOBJECT.

  MOVE RSDIOBJT-TXTLG     TO TAB1-INFOOBJECT_NAME.

  MOVE RSRREPDIR-COMPID   TO TAB1-QUERY_TECH_NAME.

  MOVE RSZELTTXT-TXTLG    TO TAB1-QUERY_NAME.

  MOVE RSRREPDIR-INFOCUBE TO TAB1-INFOCUBE.

  MOVE RSDCUBET-TXTLG     TO TAB1-INFOCUBE_NAME.

  MOVE ‘Query not assigned to workbook’ TO TAB1-WORKBOOK.

VAR1 = ‘X’.

  SELECT * FROM RSRWORKBOOK

    WHERE GENUNIID = RSRREPDIR-GENUNIID

      AND OBJVERS  = RSDIOBJ-OBJVERS.

    SELECT SINGLE * FROM RSRWBINDEXT

      WHERE LANGU      = SY-LANGU

        AND WORKBOOKID = RSRWORKBOOK-WORKBOOKID

        AND OBJVERS    = RSDIOBJ-OBJVERS.

    MOVE RSRWBINDEXT-TITLE  TO TAB1-WORKBOOK.

  ENDSELECT.

  APPEND TAB1.

ENDFORM.

*———————————————————————*

*       FORM F0010_EXTRACT_NAVATT                                     *

*———————————————————————*

FORM F0010_EXTRACT_NAVATT.

  SELECT * FROM RSDATRNAV

    WHERE CHANM   IN S_IOBJNM

      AND OBJVERS IN S_VERSN

      AND ATTRINM IN S_ATTR.

    SELECT SINGLE * FROM RSDATRNAVT

      WHERE CHANM   = RSDATRNAV-CHANM

        AND OBJVERS = RSDATRNAV-OBJVERS

        AND ATTRINM = RSDATRNAV-ATTRINM

        AND LANGU   = SY-LANGU.

    SELECT * FROM RSZSELECT

      WHERE IOBJNM  = RSDATRNAV-ATRNAVNM

        AND OBJVERS = RSDATRNAV-OBJVERS.

      SELECT * FROM RSZELTXREF

        WHERE TELTUID = RSZSELECT-ELTUID

          AND OBJVERS = RSDATRNAV-OBJVERS.

        SELECT * FROM RSRREPDIR

          WHERE COMPUID = RSZELTXREF-SELTUID

            AND OBJVERS = RSDATRNAV-OBJVERS.

          PERFORM F0040_GET_QUERY_DETAILS.

        ENDSELECT.

      ENDSELECT.

    ENDSELECT.

    PERFORM F0035_CHECK_QUERY_SELECTIONS.

  ENDSELECT.

ENDFORM.

*———————————————————————*

*       FORM F0035_CHECK_QUERY_SELECTIONS                              *

*———————————————————————*

FORM F0035_CHECK_QUERY_SELECTIONS.

  SELECT * FROM RSZRANGE   ” Only need to see if ATRNAV occurs once

    WHERE IOBJNM  = RSDATRNAV-ATRNAVNM

      AND OBJVERS = RSDATRNAV-OBJVERS

      AND ENUM    = 1.

    SELECT * FROM RSZELTXREF

      WHERE TELTUID = RSZRANGE-ELTUID

        AND OBJVERS = RSDATRNAV-OBJVERS.

      SELECT * FROM RSZELTXREF

        WHERE TELTUID = RSZELTXREF-SELTUID

          AND OBJVERS = RSDATRNAV-OBJVERS.

        SELECT * FROM RSRREPDIR

          WHERE COMPUID = RSZELTXREF-SELTUID

            AND OBJVERS = RSDATRNAV-OBJVERS.

          PERFORM F0040_GET_QUERY_DETAILS.

        ENDSELECT.

      ENDSELECT.

    ENDSELECT.

  ENDSELECT.

ENDFORM.

*———————————————————————*

*       FORM F0040_GET_QUERY_DETAILS                                   *

*———————————————————————*

FORM F0040_GET_QUERY_DETAILS.

  SELECT SINGLE * FROM RSZELTTXT

    WHERE ELTUID  = RSRREPDIR-COMPUID

      AND OBJVERS = RSDATRNAV-OBJVERS

      AND LANGU   = SY-LANGU.

  SELECT SINGLE * FROM RSDCUBET

    WHERE LANGU    = SY-LANGU

      AND INFOCUBE = RSRREPDIR-INFOCUBE

      AND OBJVERS  = RSDATRNAV-OBJVERS.

  CLEAR TAB2.

  MOVE RSDATRNAV-CHANM    TO TAB2-CHARACTERISTIC.

  MOVE RSDATRNAV-ATRNAVNM TO TAB2-NAV_ATTRIBUTE.

  MOVE RSDATRNAV-ATTRINM  TO TAB2-ATTRIBUTE.

  MOVE RSDATRNAVT-TXTLG   TO TAB2-NAV_ATTR_NAME.

  MOVE RSRREPDIR-COMPID   TO TAB2-QUERY_TECH_NAME.

  MOVE RSZELTTXT-TXTLG    TO TAB2-QUERY_NAME.

  MOVE RSRREPDIR-INFOCUBE TO TAB2-INFOCUBE.

  MOVE RSDCUBET-TXTLG     TO TAB2-INFOCUBE_NAME.

  MOVE ‘Query not assigned to workbook’ TO TAB2-WORKBOOK.

VAR1 = ‘X’.

  SELECT * FROM RSRWORKBOOK

    WHERE GENUNIID = RSRREPDIR-GENUNIID

      AND OBJVERS  = RSDATRNAV-OBJVERS.

    SELECT SINGLE * FROM RSRWBINDEXT

      WHERE LANGU      = SY-LANGU

        AND WORKBOOKID = RSRWORKBOOK-WORKBOOKID

        AND OBJVERS    = RSDATRNAV-OBJVERS.

    MOVE RSRWBINDEXT-TITLE  TO TAB2-WORKBOOK.

  ENDSELECT.

  APPEND TAB2.

ENDFORM.

*————————–END OF CODE———————————–*

Related Content

ZLISTCUBE: an Alternate to LISTCUBE

Custom Process Types “Remote Trigger” and “End Time”

Tracking Real-Time Loads with a Bex Query

How to Track the status of all the Historical Requests in a Data Target

How to Send a Data Load Status Mail from SAP BI Using “Quick Stats”

Sent mail to Anil Raparthi

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply