Introduction
After running a BW-system for several years, there will be many InfoProviders where you are not sure if they are really being used by BEx Queries. But every time you have to modify or would like to replace the InfoProvider, you will have to edit the Multiprovider in which the Infoprovider is included.
A good option would be to remove the (unused) Infoprovider from the Multiprovider (where it won’t be called at query runtime to read data).
It would take a lot of time to check every query (based on the Multiprovider in which the Infoprovider is included). Therefore, it would be better to check the BW metadata tables via an ABAP program.
Checking Multiprovider and their queries
In order to determine the usage of a part provider in a query, we will have to
- get the mapped key figures of the Infoprovider within the Multiprovider
- check if these keyfigures are used in BEx queries/key figures (and no other PartProvider is filtered there)
- find out if the filter at 0INFOPROV (in restricted key figure) is set to the PartProvider (“Fitting IP-Sel.?”=Y)
Instructions for use:
- You can use single values, intervals or ranges (e.g. T*) on the selection screen; cubes and DSOs can also be used simultaneously.
Selection-Screen: Example with three InfoProvider (to check for usage)
The result is split into the
- Usage of every Infoprovider within a Multiprovider
- “Keyf.incl.?” (=Key figure included?): set to X if one key figure of the PartProvider is used in the shown query/structure/key figure (“Mapname”)
- “IP-Sel.?”(=Infoprovider selection existing?): X will be shown in this column when the global BEx element (“Mapname”) includes a selection of “0INFOPROV”
- “Fitting IP-Sel.?” (=Fitting 0INFOPROV Selection?): In addition to “IP-Sel.?”, this field will be marked if the 0INFOPROV selection includes the shown Part cube/Provider (e.g. 0INFOPROV=TCHC001)
- When there are no queries or other global BEx elements based on the resp. Multiprovider, there will be no entries at the details (e.g. Multiprovider: TCGMZ001)
- At the chapter “==> LIST OF RESULTS:”, the aggregation of the detailed results from before will be displayed. The PartProvider which are not used in the respective Multiprovider (Used?=N) can be removed from the Multiprovider (from a technical perspective).
- List of Providers without any Multiprovider inclusion
- Are these Providers still relevant or can they be deleted?
Results: Example with three InfoProvider (to check for usage)
Summary
With the two lists (“LIST OF RESULTS:” / “CUBES WITHOUT MULTIPROVIDER INCLUSION”) you know which Infoprovider is not used by BEX elements within the resp. Multiprovider or that this Infoprovider is not used by any Multiprovider at all.
Please note that the program is only fitting for InfoCubes/DSOs within a Multiprovider and that it is not used for aggregation levels, planning functions or Composite Providers.
CODING
*&---------------------------------------------------------------------*
*& Report ZBI_BEX_PARTCUBES_UNUSED
*&---------------------------------------------------------------------*
*& Created by/on: C. Heinrich - (11.02.2016)
*& Targets:
*& A) check usage of Cubes/DSO, included in Multiprovider, in queries
*& and global BEx-elements (e.g. restricted key-figures)
*& B) also show Provider, which are not included in any Multiprovider
*& Additions:
*& -> beside (all types of) Cubes, also DSOs are supported
*&---------------------------------------------------------------------*
REPORT ZBI_BEX_PARTCUBES_UNUSED LINE-SIZE 160 NO STANDARD PAGE HEADING.
TABLES: RSDCUBET.
**** Variables, internal tables
DATA: BEGIN OF ls_mpro, " List of MultiProvider
INFOCUBE TYPE RSINFOPROV,
PARTCUBE TYPE RSINFOPROV,
USED(1) TYPE C,
END OF ls_mpro,
lt_mpro LIKE STANDARD TABLE OF ls_mpro.
DATA: BEGIN OF ls_keyf, " List of found key-figures
INFOCUBE TYPE RSINFOPROV,
PARTCUBE TYPE RSINFOPROV,
IOBJNM TYPE RSIOBJNM,
END OF ls_keyf,
lt_keyf LIKE HASHED TABLE OF ls_keyf
WITH UNIQUE KEY INFOCUBE IOBJNM PARTCUBE.
DATA: BEGIN OF ls_compic, " List of queries/elements to MultiProvider
COMPUID TYPE SYSUUID_25,
MAPNAME TYPE RSZCOMPID,
INFOCUBE TYPE RSINFOPROV,
PARTCUBE TYPE RSINFOPROV,
FLAG_KEYF(1) TYPE C, " key-f. of part-cube included(=X)?
FLAG_SEL_IC(1) TYPE C, " selection at 0INFOPROV exists? (Y/N)
FLAG_PARTCUBE(1) TYPE C, " selection at corret part-cube (=Y)?
END OF ls_compic,
lt_compic LIKE STANDARD TABLE OF ls_compic.
DATA: lv_used(1) TYPE c.
*** Field-symbols:
FIELD-SYMBOLS: <fs_mpro> LIKE ls_mpro,
<fs_compic> LIKE ls_compic,
<fs_keyf> LIKE ls_keyf.
**** A) Selection-screen
SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: S_CUBE FOR RSDCUBET-INFOCUBE OBLIGATORY.
SELECTION-SCREEN: END OF BLOCK b1.
*** B) Checking usage of Partprovider(s):
START-OF-SELECTION.
" --> getting list of Multiprovider, including the Cube(s):
SELECT RSDCUBEMULTI~INFOCUBE RSDCUBEMULTI~PARTCUBE
FROM RSDCUBEMULTI
INTO CORRESPONDING FIELDS OF TABLE lt_mpro
WHERE RSDCUBEMULTI~OBJVERS = 'A'
AND RSDCUBEMULTI~PARTCUBE IN S_CUBE.
SKIP 1. FORMAT INTENSIFIED ON.
WRITE: / '==> CHECK PER MULTI-PROVIDER AND GLOBAL BEX-ELEMENT:'.
FORMAT INTENSIFIED OFF.
LOOP AT lt_mpro ASSIGNING <fs_mpro>.
" Header: Multiprovider / Part-Cube
WRITE: / 'Multiprovider: ', <fs_mpro>-INFOCUBE,
50 'Partprov.: ', <fs_mpro>-PARTCUBE.
" --> get key-figures of part-cube which are included at the Multiprovider(s):
SELECT RSDICMULTIIOBJ~INFOCUBE RSDICMULTIIOBJ~IOBJNM
RSDICMULTIIOBJ~PARTCUBE
FROM RSDICMULTIIOBJ INNER JOIN RSDIOBJ
ON RSDICMULTIIOBJ~PARTIOBJ = RSDIOBJ~IOBJNM
AND RSDICMULTIIOBJ~OBJVERS = RSDIOBJ~OBJVERS
INTO CORRESPONDING FIELDS OF TABLE lt_keyf
WHERE RSDICMULTIIOBJ~INFOCUBE = <fs_mpro>-INFOCUBE
AND RSDICMULTIIOBJ~PARTCUBE = <fs_mpro>-PARTCUBE
AND RSDICMULTIIOBJ~OBJVERS = 'A'
AND RSDIOBJ~IOBJTP = 'KYF'.
*** --> now get queries and other BEx-elements similar to restricted key-figures
*** and structures, containing the key-figure(s) of the Partprovider(s):
SELECT COMPUID MAPNAME INFOCUBE
FROM RSZCOMPIC INNER JOIN RSZELTDIR
ON RSZCOMPIC~COMPUID = RSZELTDIR~ELTUID
AND RSZCOMPIC~OBJVERS = RSZELTDIR~OBJVERS
INTO CORRESPONDING FIELDS OF ls_compic
WHERE RSZCOMPIC~INFOCUBE = <fs_mpro>-INFOCUBE
AND RSZCOMPIC~OBJVERS = 'A'.
ls_compic-PARTCUBE = <fs_mpro>-PARTCUBE.
APPEND ls_compic TO lt_compic.
ENDSELECT.
" every BEx-component should be checked downwards (recursively),
" if one of the relevant key-figures is used and the Infoprovider (0INFOPROV)-
" selection could cover also the part-cube(s):
WRITE: / 'MultiProvider', 25 'Part-Cube', 40 'Mapname',
85 'Keyf.incl?', 100 'IP-Sel.?', 110 'Fitting IP-Sel.?'.
WRITE: / '------------------------------------------------------------' &
'-------------------------------------------------------------' &
'-----'.
lv_used = 'N'. " Start with "is used"=no
" check every query/key-figure for usage
LOOP AT lt_compic ASSIGNING <fs_compic>.
PERFORM CHECK_BEX_ELEMENT_RECURSIVE USING <fs_compic>-COMPUID
CHANGING <fs_compic>.
IF <fs_compic>-FLAG_KEYF = 'X' AND ( <fs_compic>-FLAG_PARTCUBE = 'Y'
OR <fs_compic>-FLAG_SEL_IC <> 'Y' ).
lv_used = 'Y'. " is used=yes
ENDIF.
WRITE: / <fs_compic>-INFOCUBE, 25 <fs_compic>-PARTCUBE,
40 <fs_compic>-MAPNAME, 85 <fs_compic>-FLAG_KEYF,
100 <fs_compic>-FLAG_SEL_IC, 110 <fs_compic>-FLAG_PARTCUBE.
ENDLOOP.
<fs_mpro>-USED = lv_used.
SKIP.
ENDLOOP.
*** C) Log at end of run:
SKIP 3.
ULINE.
" C1. List of partprovider(s) usage
FORMAT INTENSIFIED ON. WRITE: / '==> LIST OF RESULTS: '. FORMAT INTENSIFIED OFF.
WRITE: / 'Multiprovider', 30 'Infocube', 50 'Used?'.
WRITE: / '---------------------------------------------------------'.
LOOP AT lt_mpro ASSIGNING <fs_mpro>.
WRITE: / <fs_mpro>-INFOCUBE, 30 <fs_mpro>-PARTCUBE,
50 <fs_mpro>-USED.
ENDLOOP.
SKIP 3.
" C2. List of not-anywhere used part-cubes
WRITE: / '--------------------------------------------------------------------------------'.
FORMAT INTENSIFIED ON. WRITE: / '==> CUBE´S WITHOUT MULTIPROVIDER-INCLUSION: '.
FORMAT INTENSIFIED OFF.
PERFORM MISSING_CUBES.
*************************************************************************************
* Form CHECK_BEX_ELEMENT_RECURSIVE
* Key-figure and selection to 0INFOPROV will be checked by every BEx-element down-
* wards (form in recursive mode)
*************************************************************************************
FORM CHECK_BEX_ELEMENT_RECURSIVE USING I_ELTUID TYPE SYSUUID_25
CHANGING C_COMPIC LIKE LS_COMPIC.
DATA: BEGIN OF ls_range, " entry at RSZRANGE
IOBJNM TYPE RSIOBJNM,
SIGN TYPE RALDB_SIGN,
OPT TYPE RSZ_OPERATOR,
LOW TYPE RSCHAVL,
HIGH TYPE RSCHAVL,
END OF ls_range.
DATA: lv_eltuid TYPE SYSUUID_25.
" Selection of Partprovider (at 0INFOPROV) or relevant key-figure present?
" e.g. RSZRANGE-IOBJNM = '1KYFNM' plus RSZRANGE-LOW = 'MyKeyfigure'
" or RSZRANGE-IOBJNM = '0INFOPROV' plus RSZANGE-LOW = 'MyCube'
SELECT IOBJNM SIGN OPT LOW HIGH FROM RSZRANGE
INTO CORRESPONDING FIELDS OF ls_range
WHERE ELTUID = I_ELTUID
AND OBJVERS = 'A'
AND IOBJNM IN ('1KYFNM', '0INFOPROV').
IF ls_range-IOBJNM = '1KYFNM'.
READ TABLE lt_keyf ASSIGNING <fs_keyf>
WITH KEY INFOCUBE = C_COMPIC-INFOCUBE
IOBJNM = ls_range-LOW
PARTCUBE = C_COMPIC-PARTCUBE.
IF sy-subrc = 0. " Key-figure of Part-Provider used at query!
C_COMPIC-FLAG_KEYF = 'X'.
ENDIF.
ELSEIF ls_range-IOBJNM = '0INFOPROV'. " Selection at Infoprovider exists
C_COMPIC-FLAG_SEL_IC = 'Y'.
IF ls_range-SIGN = 'I' AND ls_range-OPT = 'EQ'. " Single value
IF ls_range-LOW = C_COMPIC-PARTCUBE. " Selection to fitting part-cube exists
C_COMPIC-FLAG_PARTCUBE = 'Y'.
ENDIF.
ELSEIF ls_range-SIGN = 'I' AND ls_range-OPT = 'BT'. " Interval
IF ls_range-LOW <> ''
AND C_COMPIC-PARTCUBE BETWEEN ls_range-LOW AND ls_range-HIGH.
C_COMPIC-FLAG_PARTCUBE = 'Y'.
ENDIF.
ENDIF.
ENDIF.
ENDSELECT.
" further check sub-elements of current BEx-element:
SELECT TELTUID FROM RSZELTXREF INTO lv_eltuid
WHERE SELTUID = I_ELTUID
AND OBJVERS = 'A'.
PERFORM CHECK_BEX_ELEMENT_RECURSIVE USING lv_eltuid
CHANGING C_COMPIC.
ENDSELECT.
ENDFORM.
*************************************************************************************
* Form MISSING_CUBES
* Some part-cube(s) not found in any Multiprovider? To this case, there should also
* a list be generated and shown
*************************************************************************************
FORM MISSING_CUBES.
DATA: BEGIN OF ls_cube,
INFOCUBE TYPE RSINFOCUBE, " Cube-Name
CUBETYPE TYPE RSCUBETYPE, " Cube-/DSO-type
TXTLG TYPE RSTXTLG, " Infoprovider-Text
END OF ls_cube,
lt_cube LIKE STANDARD TABLE OF ls_cube.
FIELD-SYMBOLS: <fs_cube> LIKE ls_cube.
" first get all cube(s) to selection:
SELECT RSDCUBE~INFOCUBE RSDCUBE~CUBETYPE RSDCUBET~TXTLG
FROM RSDCUBE INNER JOIN RSDCUBET
ON RSDCUBE~INFOCUBE = RSDCUBET~INFOCUBE
AND RSDCUBE~OBJVERS = RSDCUBET~OBJVERS
INTO CORRESPONDING FIELDS OF TABLE lt_cube
WHERE RSDCUBE~INFOCUBE IN S_CUBE
AND RSDCUBE~OBJVERS = 'A'
AND RSDCUBE~CUBETYPE <> 'M' " MPro not relevant
AND RSDCUBET~LANGU = sy-langu.
" also, add DSO to the selection:
SELECT RSDODSO~ODSOBJECT AS INFOCUBE RSDODSO~ODSOTYPE AS CUBETYPE
RSDODSOT~TXTLG AS TXTLG
FROM RSDODSO INNER JOIN RSDODSOT
ON RSDODSO~ODSOBJECT = RSDODSOT~ODSOBJECT
AND RSDODSO~OBJVERS = RSDODSOT~OBJVERS
APPENDING CORRESPONDING FIELDS OF TABLE lt_cube
WHERE RSDODSO~ODSOBJECT IN S_CUBE
AND RSDODSO~OBJVERS = 'A'
AND RSDODSOT~LANGU = sy-langu.
" now check cube(s) at multiprovider assignments:
LOOP AT LT_CUBE ASSIGNING <fs_cube>.
READ TABLE lt_mpro TRANSPORTING NO FIELDS
WITH KEY PARTCUBE = <fs_cube>-INFOCUBE.
IF sy-subrc = 0. " Cube is included in one multi-provider, no entry necessary
<fs_cube>-INFOCUBE = 'DELETE'.
ELSE.
CASE <fs_cube>-CUBETYPE.
WHEN '' OR ' ' OR 'T' OR 'W'. "=DSO
WRITE: / 'DSO ', <fs_cube>-INFOCUBE, 18 '/', <fs_cube>-TXTLG, 80 ' not included in any Multiprovider!'.
WHEN OTHERS.
WRITE: / 'Cube ', <fs_cube>-INFOCUBE, 18 '/', <fs_cube>-TXTLG, 80 ' not included in any Multiprovider!'.
ENDCASE.
ENDIF.
ENDLOOP.
DELETE LT_CUBE WHERE INFOCUBE = 'DELETE'.
ENDFORM.
- SAP Managed Tags:
