cancel
Showing results for 
Search instead for 
Did you mean: 

BEx metadata tables - how to list Query/Chars/KFs

Former Member
0 Kudos

Greetings All

My goal is to create listing of BEx queries and their characteristics, key figures, and identify(flag) any Restricted Characteristics. Have studied several threads describing BEx metadata tables; obtained helpful information from advice by Edwin Harpino (thanks!).

To this point, am able to list queries and their Characteristics.

However (not being a programmer), in my attempt to interpret ABAP code to identify tables/columns involved and how to join up the tables, I am unable to list KFs and flag which characteristics are Restricted Chars.  Probably misinterpreted ABAP and messed up. 

Simply, I need info an ERD would supply for the 2 remaining items with which I struggle.

So, for the 2 items below, please supply the BEx metadata tables involved, how the fields joined, and which table/columns involved:

  1. listing all Key Figures (IOBJNM and TXTLG) for each BEx query
  2. identify which characteristics are a Restricted Characteristic

Thanks for you help.

Regards, Rick Merrill

Atmos Energy

Dallas, TX

Accepted Solutions (0)

Answers (5)

Answers (5)

ccc_ccc
Active Contributor
0 Kudos

Hi Richard,

Please use below FM , it will give really lot information if you pass any one of below

GENUNIID,COMPID and COMPUID.

For this may need ABAP program to pull reports from   rsrrepdir  and pass to below FM and get output.

RSZ_X_COMPONENT_GET

Thank you,

Nanda

Former Member
0 Kudos

Hi Richard,

Did you ever succeed in achieving your objective.  I am trying to do something similar which will allow the end user documentation to be produced authomatically

Colin

Former Member
0 Kudos

SVU, Kamal Mehta, et al: I have already perused and studied the links you supplied.  Am also aware of RSRTQ, which is not a practical approach because I need to get BEx characteristics/KFs/char restrictions for 140+ BEx queries each month - NOT by running that query 140+ times/month!

I require an approach involving a query I build on the BEx metadata tables.  I am having specifc problems with the ERD associated with the tables and what I have studied in many SCN posts.

Answers to specific questions are not in the form of supplying links for SCN enteries that I have already researched.  Please, please, will someone PLEASE answer my specific questions #1. and #2  listed above.  Thanks to anyone who has sufficient knowledge to yield specific answers to the two questions as stated. NO post links - answers!

Regards, Rick

Former Member
0 Kudos

Hi Richard,

Do you intend to get all the BASE Chars and KFs under each Restricted KF (RKF)?

Things will become a little bit tricky if the RKF has nested RKF (or CKF), as each nested RKF will have its own char restriction and KF.

Based on the statement given by you, if you removed the line "and iobjnm = '1KYFNM'." then you will be able to get the KF (can be a CKF, another RKF or base KF) together with the restriction values (field LOW) by each Char (field IOBJNM).

  select low into lv_iobjnm
  from rszrange where eltuid = rszeltxref-teltuid and objvers = 'A'
  and iobjnm = '1KYFNM'.

If you want to drill down the nested RKF (if there is any) in order to get the base KF, then you have to use table alias to join the same table (i.e. RSZRANGE) in order to achieve this. For example, rszrange-low = rszrange(alias)-eltuid. Depends on the depth of the RKF, you may have to do the same look up a few times to get to the bottom.

Regards
Mak

Former Member
0 Kudos

No Mak, no need for info on RKFs.  Removing restriction iobjnm = '1KYFNM' helped, thanks.  But Mak, here is what I need :

Please supply the tables involved, how the tables are joined so, for all BEx queries, I can

  1. list all Key Figures (IOBJNM and TXTLG)
  2. identify which characteristics are a Restricted Characteristic
  3. the BEx query technical name & description associated with all characteristics and key figures in 1. and 2. above.

Please supply concise answers to the 3 questions above - no links to other threads.  Concise answers.  Thanks.

Best Regards, Rick

Former Member
0 Kudos

Hi Rick,

I am afraid I might misunderstand your requirements. Two questions

1.list all Key Figures (IOBJNM and TXTLG)

If your Query has three columns (i.e. RKF1, CKF2 & KF3) as below, where RKF1 is derived from CKF1 and KF1, and CKF2 is derived from KF2.

a) RKF1 <- CKF1 <- KF1
b) CKF2 <- KF2
c) KF3

Are you trying to get RKF1, CKF2 & KF3, or KF1, KF2, KF3?


2.identify which characteristics are a Restricted Characteristic

Do you mean those Characteristics being restricted in each RKF? Or those Characteristics that being filtered (either by fixed value or variable) found in the Filter section?

Best regards

Mak

Former Member
0 Kudos

Mak, sorry for insufficient clarity, sir.  Am very impressed with your desire to answer what the user seeks.  Hope the wording below doesn't wear you out:

  1. list all Key Figures (IOBJNM and TXTLG) - I meant to state I desire to list all KFs, CKFs, and RKFs as listed in InfoProvider panel of BEx Query Designer (see example snapshot, below).   Mak, I require each row of the output to contain(per example screenshot):
    1. BEx query technical name (e.g. [ZFC_MC10_BI166_S101] )
    2. BEx query description (e.g. "Disconnects CO NARUC (BI-166)" )
    3. the KF / CKF / RKF technical name (e.g. [0FC_BETRH] )
    4. the KF / CKF / RKF description (e.g. "Amt in Loc. Currency")
    5. each KF/CKF/RKF simply listed on a separate row
    6. So, Mak, what I need is list of which table(s) and columns (and how to select the rows in tables) I will use to list KF/CKF/RKFs in the BEx query.  I assume there will be a separate combination of tables/columns/selection criteria/joins for each KF, CKF and RKF (please list each separate combination per KF/CKF/RKF).

2. identify which characteristics are a Restricted Characteristic - Similar to 1.5 above, all characteristics are to be listed on separate rows.  Now, for said characteristic row output, I need to identify which are Characteristic Restrictions listed in Characteristic Restrictions panel of BEx Query Designer(see example snapshot, below).  For each each output row, I simply would "flag" each Characteristic used as a Characteristic Restriction.   Here is what I need for output on Characteristics:

    1. BEx query technical name (e.g. [ZFC_MC10_BI166_S101] )
    2. BEx query description (e.g. "Disconnects CO NARUC (BI-166)" )
    3. the Characteristic technical name (e.g. [0FCACTDETID] )
    4. the Characteristic description (e.g. "ADID")
    5. each Characteristic shall be simply listed on their own separate row
    6. list of which table(s) and columns (and how to select the rows in tables) used to create items 2.1 - 2.5, above.
    7. for each Characteristic (item 2.3) used as Characteristic Restriction(via fixed value /variable), I need to get flag the output rows stated in 2.6, above.  So, Mak, all I need is list of which table(s) and columns (and how to select the row in those tables) used to identify Characteristic Restrictions, and how to join said tables to the tables listed in item 2.6, above.

Mak, I hope this provides sufficient clarity. If not, I again apologize; please feel free to demand it!  Again, thanks so much for your desire to help.

Best Regards,

Rick / Atmos Energy

KamalMehta
Advisor
Advisor
0 Kudos
RamanKorrapati
Active Contributor
0 Kudos

Hi Richard,

pls check the below discussed thread. may helps you

http://scn.sap.com/thread/3261297

Thanks

Former Member
0 Kudos

RK, thanks for your reply, but I already perused that link several days ago.  It provided insufficient info to resolve my particular issues [FYI: RSA1/Metadata Repository is unavailable; will take more time than I have to wait for its availability].

Therefore, please continue to see if someone can provide level of detail required for my two questions (listed again below along with more info, hopefully describing the detail I need):

1.  listing all Key Figures (IOBJNM and TXTLG) for each BEx query - my understanding: key figures are stored in RSZRANGE (joined to RSZELTXREF) per thread   http://scn.sap.com/thread/80254  (Edwin Harpino Nov 15, 2005 11:04 AM)

*keyfigures

  select low into lv_iobjnm

  from rszrange where eltuid = rszeltxref-teltuid and objvers = 'A'

  and iobjnm = '1KYFNM'.

    it_result-iobjnm = lv_iobjnm.

    append it_result.

  endselect.

 

diagram of how I joined RSZELTXREF, RSZRANGE as referenced above (also joined RSZELTTXT to get TXTLG KF description):

the joins returned data result that appears reasonable:

BUT when I attempt to join RSZELTDIR / RSRREPDIR (BEx query descriptions per thread 80254), nothing is returned:

(no results)

2.  identify which characteristics are a Restricted Characteristic - need detail on how to identify the table(s) containing restricted characteristics, how to identify them in the table(s), and how to join involved table(s) to RSZELTDIR / RSRREPDIR to fetch associated BEx query descriptions.

Thanks again for any help. 

Regards, Rick Merrill

Atmos Energy

former_member202718
Active Contributor
0 Kudos

Hi Richard,

Give a try to T code RSRTQ !! This gives Query Based information but for one Query.

Also Try this link.

http://scn.sap.com/thread/2034742

Tables

http://wiki.sdn.sap.com/wiki/display/BI/Tables+in+SAP+BW+which+give+information+related+to+Queries,+...

rgds

SVU