Skip to Content

The hidden secret of Constant Selection…

September 19, 2006 | 3,294 Views |
 prakash darji
Follow
Introduction

So you may ask what is constant selection. Basically, this functionality has existed since BW 3.0 and allows you to do left outer joins on underlying infocubes within queries built on multiproviders.

Scenario

Infocube 1

Plant Material Price ($/CS)
101 A 0.12
101 B 0.14
102 A 0.11
102 C 0.24
103 C 0.23
104 D 0.15

InfoCube 2

Plant Material Customer Qty (CS)
101 A Costco 100 CS
101 B Walmart 110 CS
102 A Albertsons 105 CS
103 C Sams 115 CS
104 D Food 4 Less 110 CS

Query on Multiprovider based off InfoCube 1 and InfoCube 2

Plant Material Customer Qty (CS) Price ($/CS) Value ($)
101 A Costco 100 CS 0.12 $12.00
101 B Walmart 110 CS 0.14 $15.40
102 A Albertsons 105 CS 0.11 $11.55
103 C Sams 115 CS 0.23 $26.45
104 D Food 4 Less 110 CS 0.15 $16.50

How do we do this?

Constant Selection can be specified in 2 areas: on a characteristic or on a key figure. Constant Selection on a Characteristic allows you to ignore a particular characteristic or characteristic filter in your data model.
Example above:
InfoCube 1 – Plant, Material, Price
InfoCube 2 – Plant, Material, Customer, Stock Qty
MultiProvider 1 – Plant, Material, Customer, Price, Stock Qty

Constant Selection on “Customer” in this data model allows you to report Stock Value by Plant, Material, Customer

Constant Selection on a Key Figure allows your key figure value to become independent of all filter values on all characteristics in your cube. This is used in rare cases when you want to report the sum of a particular key figure value to show the complete value of a key figure.

Setting Constant on Material
image

Constant Selection vs Infosets
  • InfoSets are very dynamic
  • InfoSets are defined in the data model
  • Constant Selection is defined in a query built on a MultiProvider
  • Constant Selection is more flexible than InfoSets as your join conditions can be specified differently per query
  • InfoSets within SAP NetWeaver 2004s allow you to join based on InfoCubes instead of just ODS. An InfoCube must be the left side of your join. With Constant Selection, you can specify any join condition on any particular characteristic
Summary

Now you should be able to work with constant selection!

To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member
    A powerful technique, a real viable alternative to InfoSets, awesome.  Thanks for bringing this little-known gem to the public’s attention.

    Best Regards –

    Ron

    (0) 
    1. Former Member
      Hi Prakash,
      I need to combine the master data in ODS coming from source1 with the data in the cube coming from source 2.
      ODS has Mat char2 char3
      Cube has Mat char4  char5 KF1
      I need a report
      Char4 Mat char2 char3 char5 KF1
      Pls let me know.

      (0) 
      1. Former Member
        I have a similar requirement.
        ODS has MAT CHAR1 CHAR2
                001  1     1
        Cube has MAT CHAR1  KF1
                 001  1     100 Kg
                 002  2     150 Kg
        I put the query with the constant selection on CHAR1 CHAR2 in the RKF for KF1.
        So Now it showing up like this
        MAT CHAR1 CHAR2 KF1
        001  1     1     100 Kg       1st rec
                   #     100 kg       2nd rec
        001  2     #     150 kg       3rd rec

        1st rec and 3rd rec are good, but donot want the 2nd rec. that repeats the RKF value and mess up the result also.
        If I put the filter on CHAR2 = #.
        It takes up the 3rd rec also. So is there something I can do just to take away the 2nd rec.

        Thanks.

        (0) 
  3. Former Member
    It is not about Constant Selection but about PERFORMANCE. Multiprovider is good to join cubes but queries will take longer time. One should always compare with any other alternative solution. Create query on each cube and get your result in workbook by using XL macros combining data in one sheet. Trust me it will be much quicker than executing query on multiprovider.

    Thanks.

    Jehangir

    (0) 
    1. Former Member
      Post author
      Every solution has it’s pros and cons.

      Performance may be better with a workbook, but you’re tied to a workbook, and dealing with drilldown and navigation with a macro can be very difficult.

      Infosets have performance challenges as well.

      Constant Selection uses OLAP for the join, so it will add some OLAP time, but in general, it is generally worth it for the join. It’s better than some alternatives and worse than others… Everything has an up and a down…

      Ideally, if performance is your primary concern, a datamart for each query is the best option, as it will be the best for performance, however, this option is not viable as you’d have too many datamarts and you pay the price in maintenance and loading.

      I’m just trying to present a solution that people should consider. It definitely has it’s place in your modeling techniques…

      (0) 
  4. Former Member
    Constant Selection is one of the tiny flags that separates a good BI consultant from a great one. There were some things where I had to use this heavily to get the needed results in a proper style, e.g. I had a hierarchy display where results and base lines were in two different InfoProviders.
    But I’m missing two points. The first one is the location of the switch (when you do a restriction on a key figure, right click on the characteristic and choose Constant Selection). The second one is the constant selection on an InfoProvider. This is VERY useful when handling with MultiProviders. It ignores all restrictions on characteristics that are not available in this InfoProvider.

    Best regards
    Dirk

    (0) 
  5. Former Member
    Hi, very useful info here, thankyou. There is still not a solution if you wish to display a Characteristic from Provider 1 next to a Characteristic from Provider 2. (That isn’t in both providers)E.g The storage location that the material is held in the plant …
    Any help greatly appreciated, Tom
    (0) 
    1. Former Member
      Post author
      Hey Tomos,

      The solution above actually shows an example of what you mention as Characteristic “Customer” is not in both cubes.

      This solution displays Characteristic from InfoProvider 1 (Material) next to the characteristic from InfoProvider 2 (Customer) which isn’t in both providers.
      Prakash

      (0) 
      1. Former Member
        Hello Tomos, Prakash,

        I think Tomos and I have the same issue: characteristic ‘A’ is available in both infoProviders, characteristic ‘B’ and KF ‘b’ is only in infoProvider 1, and characteristic ‘C’ and KF ‘c’ is only available in infoProvider 2.
        There is indeed no way (at least that I can find!)to have a line displaying
        A B C a b c

        Still, Prakash, your example is very clear and most definitely useful !

        (0) 
  6. Former Member
    Thanks Prakash for your contribution.

    I was wondering if there is a possibility to do an inner join between the underlying Info Providers of the Multi-Provider.

    I think the described example works as long as MatNr and Plant are in the drilldown. I’m looking for a solution, where I can navigate or drilldown with a characteristic which is only in one Info Provider and get the key figures of the other Info Provider. So in the described example a drill down only with customer w/o MatNr and plant.

    In my opinion this would be an inner join and can only be done with Info Sets, which don’t allow to join two basis cubes.

    Any comment would be appreciated.

    Cheers Michael

    (0) 
    1. Former Member
      Post author
      If you combine this solution with BI Conditions, you can do an inner join. The eliminations of the inner join can be handled with conditions.
      (0) 
  7. Former Member
    Hi Prakash,
    constant selection is ok also in this scenario?

    Multiprovider (with join on CHAR_A)
    CHAR_A
    CHAR_B
    CHAR_C
    CHAR_D
    KF_1
    KF_2

    Base Cube 1:
    CHAR_A
    CHAR_D
    KF_1

    Base Cube 2:
    CHAR_A
    CHAR_B
    CHAR_C
    KF_2

    CHAR_D is only available in Base Cube 1 and CHAR_B (and CHAR_C) is only available in Base Cube 2.

    We have some problems and we don’t understand if there can be a bug.
    Any suggestion?
    Thanks in advance

    (0) 
    2. Former Member
      Hi Prakash,
      I have the same problem.

      Multiprovider (with join on CHAR_A)
      CHAR_A
      CHAR_B
      CHAR_C
      CHAR_D
      KF_1
      KF_2

      Base Cube 1:
      CHAR_A
      CHAR_D
      KF_1

      Base Cube 2:
      CHAR_A
      CHAR_B
      CHAR_C
      KF_2

      CHAR_D is only available in Base Cube 1 and CHAR_B (and CHAR_C) is only available in Base Cube 2.
      I set constant selection for KF_1 on CHAR_B and CHAR_C. I also set constant selection for KF_2 on CHAR_D.
      If drlldown separately on CHAR_B, CHAR_C or CHAR_D, it works fine, but if drill on CHAR_C and CHAR_D together, I get something like:

                          
      CHAR A  CHAR_        CCHAR_D    KF_1     KF_2    
      A     31/08/2008   #             3.026PZ  3.050PZ
           #            6/06/2008     3.027 PZ     

      I would like to have all information in a row:

      CHAR A  CHAR_C         CHAR_D       KF_1      KF_2
      A     31/08/2008  16/06/2008   3.026PZ   3.050PZ

      Can you say what is wrong in my settings?

      Thanks.

      (0) 
      1. Former Member
        Sorry for bad formatting,
        I try to re-post:
        what I get is:

        CHAR A  CHAR_C      CHAR_D    KF_1     KF_2    
        A       31/08/2008  #         3.026PZ  3.050PZ
                #           6/06/2008 3.027 PZ       

        I would like to have all information in a row:

        CHAR A  CHAR_C     CHAR_D       KF_1      KF_2
        A       31/08/2008 16/06/2008   3.026PZ   3.050PZ

        Thanks.

        (0) 
        1. Former Member
          Post author
          It looks like it should be fine. Without seeing more details, I’m not sure. When you set constant selection, you have to create a local RKF, drag and drop the dimensions into the RKF (without filtering) and then right click on each dimension and set constant selection. Is that how you did it?
          (0) 
  8. Former Member
    SAP released one note this month only on ‘Constant Selection’ which tells us about pros and cons of using it and in real sense revealing lots of stuffs

    SAP note 944815

    (0) 

Leave a Reply