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!
Best Regards –
Ron
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.
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.
This is excellent example.
Pankaj
Thanks.
Jehangir
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…
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
Any help greatly appreciated, Tom
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
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 !
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
I’ve a similar problem, thanks in advance.
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
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.
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.
SAP note 944815