Constant selection on multiprovider with several infoproviders

[Former Member]

Hello,

We need to join five different kinds of data. First we planned using infoset, but as infosets don't accept LEFT OUTER JOIN with more than two tables we had to find another solution. 'Constant selection' should do the trick. But it's not stright forward.

To limit the problem I try the following:

Cube A (PO item) has the characteristics Sales document and Sales documet item, and the KF PO Order Qty.

Cube B (SO item) has the characteristics Sales document, Sales documet item and Sales Office, and the KF Sales Price.

Cube C (billing) has the characteristics Sales document, Sales documet item and Billing type, and the KF Billing Quantity.

All cubes have lots of other characteristics and key figures.

Initially I get this result from my query:

Sales document  SD item  Billing type  Sales Office  PO Order Qty  Billing Quantity  Sales Price
206699          10       ZF2           #                           3
                         #             3010                                          1226
                                       #             3

I want all the related key figures to appear in the same row, so we use constant selection to join the data. This is done according to documentation and other information found on constant selection:

[http://help.sap.com/saphelp_nw70/helpdata/en/e7/5f983c1a356858e10000000a114084/frameset.htm]

[https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/4478] [original link is broken] [original link is broken] [original link is broken];

On KF PO Order Qty we add constant selection on Billing type and Sales Office. On Billing Quantity we add constant selection on Sales Office. On Sales Price we add constant selection on Billing type. The result is not as expected:

Sales document  SD item  Billing type  Sales Office  PO Order Qty  Billing Quantity  Sales Price
206699          10       ZF2           #             3             3
                         #             3010          3                               1226
                                       #             3

If I simplify the query, joining only PO and Billing, removing Sales Office and Sales Price, with constant selection on Billing type for PO Order, then I get the expected result (i.e. on a single row):

Sales document  SD item  Billing type  PO Order Qty  Billing Quantity
206699          10       ZF2           3             3

Actually, it seems from the result with all three cubes that PO and Billing are joined as are PO and SO, but only separately! I therefore try another simplification. Only SO and Billing, with constant selection on Sales Office for KF Billing Quantity and on Billing type for Sales Price. I get the following result:

Sales document  SD item  Billing type  Sales Office  Billing Quantity  Sales Price
206699          10       ZF2           #             3
                         #             3010                            1226

Just as if there were no constant selection at all! What is wrong? This last problem seems to be the same as described in thread , but I find no solution to the problem there.

Please help!

Points will be assigned to everyone which can help me solve the issue.

Best regards,

Christoffer Owe

[Former Member]

Hi Christoffer,

Have you got the solution for Multiprovider constant selection?I am also facing similar issue.

Could you please provide me the solution/any document that solves the multiprovider constant selection issue.

Thanks,

Selva

[Former Member]

Hello Selva,

Unfortunately there is no good solution. BW is simply not able to do what we want it to do. I thought Constant Selection could be used for joining characteristics. It can't. It can only join key figures. It's possible, using Constant Selection, to include in a report characteristics which are common from two cubes and add charactertics which only exist in one of the cubes. You can't add unique characteritics from both cubes. And we discovered that since our client wants to restrict data on all kinds of fields, the constant selection scenario would not work at all.

The problem can easily be solved with Business Objects, but we didn't wanted to introduce this software to the client now.

We are still working on the issue, but the solution we are working on are based on different InfoSets or cubes for each query, limiting the number of involved infoproviders as much as possible. When only two invoproviders are involved, InfoSets is the best solution as left outer join works with two infoproviders. If three or more infoproviders are involved, we have in some cases loaded transaction data as masterdata. This is a really bad solution, but it works, and we see no alternative. The join between a cube and its masterdata is a left outer join - exactly what we want.

I really wish SAP could include an option for joining data during data load. You can join key figures from two different infoproviders when you load data into a DSO. If an option could be added to join characteristics as well could be added (instead of just overwriting all characteristic values as happens now), the problem would be solved.

Best wishes,

Christoffer

[Former Member]

In your restricted key figures, make sure all characterstics you want to join on are there, irregardless of whether they are filtered. THen flag the ones you want to ignore for join as constant... If it doesn't work as expected, I would log a customer support message.

[Former Member]

Thank you for your answer.

What do you mean by "In your restricted key figures, make sure all characterstics you want to join on are there"? Do you mean I should add the characteristics 'Sales document' and 'SD item' to the key figure selections? I see no reason to do that as long as I don't want to do any further restrictions on them and there should be no constant selection on the characteristics on which I want to join. Just to be sure I tried adding them, but with no effect. Have I misunderstood you?

Best regards,

Christoffer Owe

[Former Member]

I would log a message.

[Former Member]

Yes, I did that yesterday. I will update the thread when I know the end of the story.

Best regards,

Christoffer Owe

[Former Member]

Try to restrict the infoprovider and select it as "constant selection" and "display values not posted".

it is working for me.

[Former Member]

I have found a old presentation with an example using "Display values not posting" and from the explanation it sounds like this could solve my problem.

But do you know where I can find it? I don't see it in the context menu in the Query Designer of BW 7.0.

Best regards,

Christoffer

[Former Member]

I used it only in 3.X query designer. couldnt find it in the new query designer....

[Former Member]

hi,

i am interested by this issue, have you get a solution?

Thanks.

[Former Member]

Hello,

We have still not found a solution. SAP could not help us. They just claim my queries works as designed, and after checking again documentation and examples, I must give them right. It is possible to join key figures, but not characteristics, using "Constant Selection". Using "Display values not posted" could possibly have solved the problem, but seems to have been removed from QueryDesigner 7.0.

Possibly the problem can be solved using Business Objects or Crystal Reports. I'm not really familiar with these products, and it's not an option to install these now.

For now, our client will have to live with one row of data for each cube instead of a single row with data from all cubes. They are surprised and dissatisfied that BW seems to be unable to solve such a problem.

Best regards,

Christoffer

[Former Member]

I gave up with constant selection and used infosets on the cubes instead