Hierarchical Display of Calculated and Restricted Key Figures in BEx Query Designer

Calculated (CKF) and restricted (RKF) key figures are often used in BW mainly for re-usability and maintenance reasons. However, the handling in the Query Designer is not optimal in case of huge  number of CKF or RKF. Finding a certain CKF/RKF can be tedious and very time consuming. Using the sorting might help, but typically only if  the naming supports it. The following screenshot shows the sorting in the BEx key figure section:

BLOG_RSZTREE_2

The highest number of CKF/RKF on a single MultiProvider I have seen was more than 1.400. Here also the sorting is not really helpful anymore.

A way of supporting a high(er) number and structuring of CKF/RKF is available since the following note (available as of BW 7.3 SP8) was introduced:

1648317 – Hierarchical display of RKF/CKF in Query Designer

It delivers the transaction RSZTREE, which is typically used to maintain a hierarchical structure in the backend. The note also explains how to use it. Following example demonstrate this based on an example from the business content MultiProvider 0RMA_MP01. After activating the content and opening the Query Designer the CKF section there will look like this:

BLOG_RSZTREE_3

Call now the transaction RSZTREE in the back-end and enter the InfoProvider there:
BLOG_RSZTREE_4

The CKF/RKF are displayed in the same structure from the Query Designer. Individual grouping typically starts with building up the hierarchy and then assigning the CKF/RKF to the corresponding nodes. The finalized example looks like this:

BLOG_RSZTREE_5

Re-opening the Query Designer for the corresponding InfoProvider does look like this:
BLOG_RSZTREE_1

There are a few smaller downsides. For instance the missing maintenance integration into the Query Designer or the not always intuitive behaviour of the RSZTREE transaction. Nevertheless, this functionality is pretty useful to structure CKF/RKF a bit more user friendly in the query designer.

Finally, some additional informations/thoughts which might be beneficial using this functionality:

  • The hierarchy maintained in RSZTREE is transported together with the InfoProvider.
  • The hierarchy information is stored in the table RSZCOMPTREE and RSZCOMPTREETXT.
  • The assignment of a single CKF/RKF is stored in the table RSZCOMPIC, field FOLDER. The information is always transported together with CKF/RKF.
  • If a CKF/RKF with hierarchy assignment is transported but the hierarchy information is not available in the target, the information is ignored.
  • Top nodes Calculated key figure and Restricted key figure cannot be changed