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
| 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
Working with OQR Tool:
• InfoObject (Characteristics & Key figure) --> Query/Workbook “where use list”
For eg: 0CUSTOMER
Output
• Navigational Attribute --> Query/Workbook “where use list”
For eg: 0CUSTOMER__0CITY
Output
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"
- SAP Managed Tags:
