BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Extend merged dimension values behavior
2 members found this topic helpful
Goto page 1, 2  Next
 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Susan
Principal Member
Principal Member



Joined: 03 Dec 2004

Posts: 144
Location: Washington, DC



PostPosted: Mon Jun 07, 2010 3:50 pm 
Post subject: Extend merged dimension values behavior

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.
Back to top
jooken
Principal Member
Principal Member



Joined: 20 Feb 2009

Posts: 113
Location: Vienna


flag
PostPosted: Tue Jun 15, 2010 8:59 am 
Post subject: Re: Extend merged dimension values behavior

Hey Susan!
Where do i find the checkbox Extend merged dimension? Are you using XI 3.1?
Back to top
Susan
Principal Member
Principal Member



Joined: 03 Dec 2004

Posts: 144
Location: Washington, DC



PostPosted: Tue Jun 15, 2010 9:13 am 
Post subject: Re: Extend merged dimension values behavior

On any report tab, right click and select Document Properties. You will see the box there.
Back to top
Bobber
Principal Member
Principal Member



Joined: 27 Aug 2009
ASUG Icon
Posts: 126
Location: Atlanta, GA


flag
PostPosted: Tue Jun 15, 2010 9:20 am 
Post subject: Re: Extend merged dimension values behavior

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!
Back to top
jooken
Principal Member
Principal Member



Joined: 20 Feb 2009

Posts: 113
Location: Vienna


flag
PostPosted: Tue Jun 15, 2010 10:00 am 
Post subject: Re: Extend merged dimension values behavior

THX Susan!!
Back to top
BO_Stuffed
Principal Member
Principal Member



Joined: 29 Mar 2008
ASUG Icon
Posts: 495
Location: Los Angeles



PostPosted: Tue Jun 15, 2010 10:31 am 
Post subject: Re: Extend merged dimension values behavior

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.

_________________
Together - We grow.....

Business Objects Version 6 onward..
Back to top
Captspeed
Principal Member
Principal Member



Joined: 03 Oct 2006

Posts: 245
Location: Florida


flag
PostPosted: Fri Jun 18, 2010 10:27 am 
Post subject: Re: Extend merged dimension values behavior

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.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22161
Location: Dallas, Texas


flag
PostPosted: Sat Jun 19, 2010 1:22 pm 
Post subject: Re: Extend merged dimension values behavior

It gets a bit more complex than that. icon_smile.gif 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/

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution


Last edited by Dave Rathbun on Mon Jun 21, 2010 8:14 am, edited 1 time in total
Back to top
BO_Stuffed
Principal Member
Principal Member



Joined: 29 Mar 2008
ASUG Icon
Posts: 495
Location: Los Angeles



PostPosted: Mon Jun 21, 2010 3:40 am 
Post subject: Re: Extend merged dimension values behavior

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.

_________________
Together - We grow.....

Business Objects Version 6 onward..
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22161
Location: Dallas, Texas


flag
PostPosted: Mon Jun 21, 2010 8:32 am 
Post subject: Re: Extend merged dimension values behavior

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:
Quote:
However, leave aside extended property, if we only use merge dimension (no right and left dimension), then it will be inner join. Right?


The answer is no. icon_smile.gif It also does a full-outer join. Remember that this picture (from my blog post) is based on a merge of the left and right data. The left data has 2006-2007. The right data has 2007 - 2008. If it were an inner join, it would only show 2007.

Image link

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Keerthana
Senior Member
Senior Member



Joined: 11 Jan 2008

Posts: 44



PostPosted: Tue Jun 29, 2010 2:08 am 
Post subject: Re: Extend merged dimension values behavior

Hi Dave,

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

Thank you
Keerthana
Back to top
anorak
Forum Enthusiast
Forum Enthusiast



Joined: 13 Sep 2002

Posts: 1204
Location: U.K.


flag
PostPosted: Tue Jan 25, 2011 5:17 am 
Post subject: Re: Extend merged dimension values behavior

Dave Rathbun wrote:
It gets a bit more complex than that. icon_smile.gif 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/


Dave,

I your blog you say that Extend Merged Dimensions is used in Webi to emulate the outer join behaviour of Deski. From what I've found, it only does this partially.

e.g. DP1 returns 584 records, with 529 distinct values for the linked Dimension. DP2 returns 5,661 records with 545 distinct values for the linked Dimension. In the report, the Merged Dimension shows 984 distinct values, (6,132 rows if Avoid Aggregation is selected). When adding a Measure from DP2, the full outer join behaviour that you describe is displayed (i.e. 984 rows are shown). However, if I add a non-linked Dimension from DP1 the full outer join is lost, with only 529 rows showing. This does not happen in Deski, where the full outer join is retained.

However, if I select Avoid Aggregation, the full outer join is restored, with the total 6,132 rows being displayed. This is no good to me though, as I need to copy the results into Excel, and I would have to manuualy aggregate the data there.

The bottom line, therefore, is that adding a non-linked Dimension to a table destroys the full outer join in Webi, but not in Deski, and unfortunately I need this functionality.

Is my understanding correct?

I'm using Webi Rich Client in v3.1 SP3

_________________
Available for part-time contract work.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22161
Location: Dallas, Texas


flag
PostPosted: Tue Jan 25, 2011 12:44 pm 
Post subject: Re: Extend merged dimension values behavior

What happens if you make your non-linked dimension a detail object instead?

Webi is much more particular about dimensions and details than Deski was.

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
anorak
Forum Enthusiast
Forum Enthusiast



Joined: 13 Sep 2002

Posts: 1204
Location: U.K.


flag
PostPosted: Wed Jan 26, 2011 4:19 am 
Post subject: Re: Extend merged dimension values behavior

Dave Rathbun wrote:
What happens if you make your non-linked dimension a detail object instead?

Webi is much more particular about dimensions and details than Deski was.


Dave,

Yes, using a Detail object overcomes the issue. Just yet another 'workaround'.

Thanks.

_________________
Available for part-time contract work.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22161
Location: Dallas, Texas


flag
PostPosted: Wed Jan 26, 2011 11:47 am 
Post subject: Re: Extend merged dimension values behavior

I would not call it a "workaround" I would call it proper modelling. icon_smile.gif Web Intelligence is much more strict about dimension-detail relationships, which in my opinion is a good thing.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0905 seconds using 17 queries. (SQL 0.0029 Parse 0.0595 Other 0.0281)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo