Not able to do left outer join

[Former Member]

Hi,

I have an infoset having two infocubes. Now I want to make left outer join but was not successful.

I read blogs regarding the issue and found out that it is not possible with two cubes to perform left outer join.

My question is, Is there any way to perform the same by changing any settings or so ??

Regards,

Sumit Roy

[yasemin_kilinc]

Hi Sumit,

You can get the same result by adding characteristic restrictions in your query design.

Suppose you have CUBE1 and CUBE2 having a common field such as CHAR1 which you want to have left outer join.

You create your infoset.

Create a query on CUBE1 add only char1 to the query (view as key only). (say cube1_query)

Create a query on your infoset.

Add CHAR1 (which you wanted to make left outer join) to characteristic restrictions pane

Create a variable with replacement path. Use cube1_query as using results from query). Use this variable to add restriction to char1.

Now you will get char1 values from cube1 only, so that it would behave as if you have made the left outer join.

hope it gives an idea.

Regards

Yasemin...

[Former Member]

Hi Yasemin,

       Thanks for your kind help.

But I don't think this would get me the same result as left outer join would have given.

The cube in which I would perform left outer join have char1, char2 and keyf1.

If I follow your procedure then I think I wont get char2 and keyf1 values for the char1 which is not in infoset. It would be unassigned, I suppose.

Regards,

Sumit

[Former Member]

Hi Yasemin,

I tried the way you told. But the output was not as it would have been for left outer join. Please help.

Regards,

Sumit

[yasemin_kilinc]

Hi Sumit,

Can we go with an example. Suppose we have infocube1

Char1  Char2   Kyf1

abc     123          500

abd     158          1000

And we have infocube2

char1     char3     Kyf2

abc          1a5     300

abe          j30     200

if we could make a left outer join we would see the result as:

char1     char2     char3     kyf1     kyf2

abc          123     1a5          500     300

abd          158     #             1000     0

Right?

When we have the infoset, the result would be:

char1     char2     char3     kyf1     kyf2

abc          123     1a5          500     300

abd          158     #             1000     0

abe          #          j30          0          200

Right?

When we have a query on ınfocube1 to see the distinct values for char1 we will get:

abc

abd

And when we use this query as replacement path variable in the infoset query, the infoset results will be restricted as what we would see in making a left outer join. Please try to explain the bahvior of your data so that I can help with that.

Regards

Yasemin...