The hidden secret of Constant Selection…

September 19, 2006 | 1,516 Views |

Prakash Darji

Retagging required

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

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.

Ronald Silberstein September 19, 2006 at 7:14 pm

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

like (0)
1. Meena Kabra March 17, 2010 at 12:25 pm
  
  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.
  
  like (0)
  1. Meena Kabra May 10, 2010 at 7:40 am
    
    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.
    
    like (0)
    1. Meena Kabra May 10, 2010 at 7:42 am
      
      I put the query with the constant selection on CHAR2 in the RKF for KF1.
      
      like (0)
Pankaj Gupta September 27, 2006 at 8:52 am

I have used this a lot and works great, But never compared this with Infoset or as Left outer join.

This is excellent example.

Pankaj

like (0)
Jehangir Shah September 28, 2006 at 2:56 pm

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

like (0)
1. Prakash Darji Post authorSeptember 28, 2006 at 5:59 pm
  
  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…
  
  like (0)
Dirk Herzog October 4, 2006 at 9:53 pm

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

like (0)
Tomos Evans September 20, 2007 at 6:51 am

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

like (0)
1. Prakash Darji Post authorSeptember 20, 2007 at 12:58 pm
  
  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
  
  like (0)
  1. BW BI Competence Center January 10, 2008 at 4:24 am
    
    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 !
    
    like (0)
Michael Eidler November 15, 2007 at 12:17 am

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

like (0)
1. Prakash Darji Post authorNovember 15, 2007 at 6:15 am
  
  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.
  
  like (0)
2. Samuele Balzani June 15, 2009 at 6:01 am
  
  Hello, have you resolved your issue?
  I’ve a similar problem, thanks in advance.
  
  like (0)
Samuele Balzani June 15, 2009 at 6:19 am

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

like (0)
1. Prakash Darji Post authorJune 15, 2009 at 10:00 am
  
  Yes, this scenario will work if your constant selection is configured correctly.
  
  like (0)
  1. Samuele Balzani June 15, 2009 at 11:07 pm
    
    OK, then we try to find an OSS note because after upgrade to Support Package 19 this scenario doesn’t work.
    
    like (0)
2. Samuele Balzani June 16, 2009 at 1:31 am
  
  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.
  
  like (0)
  1. Samuele Balzani June 16, 2009 at 1:41 am
    
    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.
    
    like (0)
    1. Prakash Darji Post authorJune 16, 2009 at 6:11 am
      
      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?
      
      like (0)
Alok Kashyap July 27, 2011 at 2:41 am

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

like (0)