Extend merged dimension values behavior

[Susan]

It seems that the purpose of checking this box is to effect an outer join. My question is: is this only for cases where the merged dimension is the original one from one or the other data provider, but not the new dimension created from the merge? It seems that if I bring in the new merged dimension (which doesn’t exist in DeskI), it also gives me an outer join. Just wanted to doublecheck my understanding that if I used the new merged dimension, I don’t ever need to check the box. Hope I am making sense. Thanks.

[jooken]

Hey Susan!
Where do i find the checkbox Extend merged dimension? Are you using XI 3.1?

[Susan]

On any report tab, right click and select Document Properties. You will see the box there.

[Bobber]

Hi Susan,

That is a good question, as I never have really understood what that "Extend Merged Dimensions" checkbox means. I have seen it given as a solution to problems, but never explained!

[jooken]

THX Susan!!

[BO_Stuffed]

My Understanding:-

When we use normal merge dimensions, we are affecting left outer join.

But when we check this "Extend Merge Dimension Value", actually we are using FULL outer join.

I would love if someone corrects me.

Cheers.

[Captspeed]

My understanding of the "Extend merged Dimension values" is:

If you do NOT check/select the "Extend merged dimension", then it is basically doing an "inner-join" (1-to-1) of the selected Data Providers Fields.

If you DO check the "Extend merged dimension", then it will be doing an "Outer-Join" (1-to-many) of the selected Data Provider Fields (depending on which DP you selected to be the "Source Dimension").

IF anyone understands it differently, please explain.

[Dave Rathbun]

It gets a bit more complex than that. Extend merged dimensions doesn't do anything if you pick the merged dimension result. It only changes the behavior if you use one of the specific-sourced dimension values.

Suppose I have two data providers with a shared dimension "D". From the first data provider I get D1, from the second provider I get D2. The merged result is D. If I use D in my block, the setting does not do anything. If I use D1 I get a left join, if I use D2 I get a right join. (Subject to the order of my data providers, of course.) With Extend merged... turned on I get a full outer join instead.

I wrote all of this up as a blog post and published it just a few minutes ago.

http://www.dagira.com/2010/06/19/what-does-extend-merged-dimensions-really-do/

[BO_Stuffed]

Dave,

Thanks for writing this blog for us. It really answers some of the intricacies of merging.

However, leave aside extended property, if we only use merge dimension (no right and left dimension), then it will be inner join. Right?

Note:- The link you have put does not take to the right blog but we can surely find the matter you are trying to point us to.

Cheers.

[Dave Rathbun]

Fixed the link, thanks. I originally scheduled it for the 20th but then decided to go ahead and release it right away. The link I had copied was based on the later schedule.

As to the question:

[Keerthana]

Hi Dave,

Thank you for the detailed explanation on this topic. It really helped to get the concept right.

Thank you
Keerthana