Metadata Reporting - Restricted Key Figures

[kohesco]

Hi,

While creating a lot of new reports based on a lot of new restricted key figures.  I created an "audit report" to achieve a transparent and reportable view on our meta-data.

The goal of the report is to show which filters are used to calculate the BO-measures.  In short, an overview of the used key-figures per bex-query.

Result:

In the report you can select the desired query and when you click on the restricted key figure, the used filters on the dimensions appear.

How to do this

The whole picture of the setup is quiet easy

BW - datasource pulling data from a view, based upon the query-tables, transferring data to the virtual cube and reporting enabled via a Infoprovider.

The whole flow is set as "direct access", so we retrieve real-time data.  This is set via the virtual cube, where you activate the direct access.  When you have done this you can set your BW-datasource as direct acces as well on the extraction-tab.

Creation of view:

Table rsrepdir contains all the reports (ID/Name/Techn.Name).  The ID is used to link all the different query-info-tables with one another.

Table rszeltxrref contains the used elements within a bex query.

Table rszeltxt contains the text of the used elements.

Table rszrange contains the filters used upon the restricted key figures.

In our view we only use the first three tables.  Because you have to use a special logic to retrieve the data.

The buildup of a bexquery is very structured.

The Bex query holds an ID= COMPUID.

WHen you use the COMPUID as a filter in rszeltxrref you will obtain ID's for the used global variables, the filters and the sheet.

The sheet represents the actual report.  Within the sheet, you'll find the columns/rows/free chars.

All that information is stored in that same table.

Via a cunning play betweet ID's and types you can retrieve the desired data.

Used ID's : SELTUID and TELTUID

Types: LAYTP

In my view I retrieve the sheet info (laytp=sht) of each query build upon one test-infoprovider="c_fi_m03".

I added the text as well in my extraction (open for discussion )

The transformation is set as an "expert routine", so I can manipulate the data easier.

In the code we loop over our datasource and fetch each time other data from the same table via a different filter on the layouttype.

First time, the layouttype is set as : COL, to retrieve the ID's of elements from the columns.

Second time, the layouttype is set as: NIL, to retrieve the ID's of restricted keyfigures set in the columns.

Now, we have detected which restricted keyfigures are used.

In table rszrange we can look them up.  But note ! restriced keyfigures used in the query have a different ID as the stored restricted keyfigures.

So first we retrieve the ID's of the stored restricted key figures and the we can fetch their filters/Definitions.

Now we have finally all information.

The virtual provider can be set-up as you wish.

My rsrt output ...

Adding texts to the info-objects from table rsdiobjt will deliver you a nice reportable dataset of your querydesigner metadata.