Jun 19 2010

What Does Extend Merged Dimensions Really Do?

Categories: Multiple Data Providers,Report Techniques Dave Rathbun @ 1:19 pm

The “Extend merged dimension values” is designed to provide more flexibility in how Web Intelligence handles merged data. In Desktop Intelligence we have for years complained that there is no way to control the merge (join) process. No matter what I did I got a full outer join. In the XI versions of Web Intelligence, I now have three different ways to reference my merged values. In this post I will show those three options and then show the impact of the extend merged dimension setting.

Defining the Problem

I am going to create a couple of very simple queries from the Island Resorts universe to demonstrate this feature. Each query is specifically structured so that it is missing some data found in the other query. For example, here is my first query.

Screen shot of Web Intelligence query panel

And the data returned by this query.

Screen shot of Web Intelligence data

And here is my second query.

Screen shot of Web Intelligence query panel

And the data returned by this query.

Screen shot of Web Intelligence data

I am going to call the first query my “left” query and the second query is my “right” query for the rest of this post. I have specifically restricted my left query to 2007-2008 years of data, and my right query to 2006-2007. That means the two queries have one year (2007) in common and each query also has unique data (left has 2008 and right has 2006).

Since both queries are from the same source I do not have to do anything to merge the shared dimensions. Both the Resort and Year are automatically merged as shown here.

Merged dimensions from a Web Intelligence document

And finally here is the resulting block of data shown in my Web Intelligence report. This is the most common presentation for the data. Note that it shows data from all three years of sales results that I selected.

Merged dimensions shown in a Web Intelligence document

Using the Left Query to Drive The Results

In Desktop Intelligence (more details below) this is as far as I can go. I don’t have any options that let me control the merge process. In Web Intelligence I do. What if I want my first query (left) to drive the results? All I have to do is open up each merged dimension object and use the dimensions that come from the left query. If I do, here’s what the block looks like.

Screen shot of a Web Intelligence block showing priority given to the left side of the merged queries

What is going on here? The left query had 2007-2008 data only. I see annual data for both of those years for all three resorts. However, the 2008 rows are missing the Number of guests values. They don’t exist, because the right query only goes up to 2007. In this case I have told Web Intelligence that my “left” data is more important, and to show right data only where it exists.

Using the Right Query to Drive The Results

Next I will turn things around and use the “right” dimensions instead. Here is the block that results from that operation.

Screen shot of a Web Intelligence block showing priority given to the right side of the merged queries

Now my revenue column is sparse (has missing values) while the Number of guests column is completely populated. That is what I expect given that I am using the right dimension objects this time.

Those are the three options that I have in Web Intelligence. I can use the merged dimensions (the most typical requirement) or I can use the dimensions from a specific data provider. If I compare the three blocks (merged, left, and right) I can see exactly how Web Intelligence is working. How does Desktop Intelligence work?

Merging Data in Desktop Intelligence

In my example Web Intelligence report I have selected the Resort object in two different queries. After the automatic merging process has run I end up with three different ways to reference the Resort values on a report. Specifically I can reference Resort(left), Resort(right), or Resort. The last option is the merged result. In Desktop Intelligence I am missing that last choice. Is that a problem?

I have a screen shot that shows what the merging process looks like in Desktop Intelligence. One complaint that I have is that I have to click on each dimension object individually to see if it is linked or not. In the screen shot shown below I am only showing that the two year values are linked.

Screen shot of Desktop Intelligence merge dimension process

Next I notice that I have no way to refer to the merged result set as I do in Web Intelligence. I have to pick either the left or right value. Does it matter? It turns out that it does not, as shown here. I have selected the right version of the Year object, yet all three values are showing up.

Desktop Intelligence merge dimension results

In Desktop Intelligence the results from the left and right queries are merged together. Every value from the left query appears on the right, and every value on the right appears on the left. It is a union of the two data providers. As a result of this union operation, it doesn’t matter which side I pick. I can swap the left and right dimension values and still get the same result. It is always a full outer join.

Is it possible for me to reproduce the way Web Intelligence behaves in Desktop Intelligence? In the example I am using today, the answer is yes. However, I need to look at the measure values instead of the dimensions since I cannot alter the merged dimension behavior. If I want to show the left data, I can create a filter so that only those rows that include a Revenue value will be displayed. Here is how that filter looks.

Screen shot of Desktop Intelligence complex filter creation process

Dimension values will never be NULL in Desktop Intelligence because of the way the values are shared as a result of the merging process. If I can identify a measure, as I did in this case, I can essentially create a left or right block based on my requirements. The process is not intuitive, nor is it effective in every case.

Extend Merged Dimension Values

So now to the point of this post: what if I would like Web Intelligence to act more like Desktop Intelligence? That’s exactly what the setting “Extend merged dimension values” does for me. Remember in my description of Desktop Intelligence I said that merged dimensions result in a union, where every value from my left query appears in my right dimension and vice versa. That means that I will always see data from either side, no matter which dimension I pick. Here is one way to invoke this setting. I am right-clicking on a report tab and I see this menu.

Screen shot of context menu in a Web Intelligence document

After selecting Document Properties the following menu appears on the side of my document.

Screen shot of document properties in a Web Intelligence document

The setting is off by default. What happens if I turn it on?

Here is what my left block looked like before the setting was turned on.
Screen shot of a Web Intelligence block showing priority given to the left side of the merged queries

And here is what it looked like afterwards.
Screen shot of a Web Intelligence block showing the results of extended merged dimensions

And here is the right block.

Screen shot of a Web Intelligence block showing the results of extended merged dimensions

They look exactly the same. And that’s how Desktop Intelligence would have behaved in this situation. Each dimension has been extended to include the values from the other. My left year includes right values, and my right year includes left values. I have recreated the Desktop Intelligence behavior in Web Intelligence. I am not sure this is a good thing, but I can do it. 🙂

Conclusion

If I only use the merged dimension (meaning I use Resort instead of Resort(Left) or Resort(Right)) then this setting doesn’t do anything for me. The results will be the same with the setting on or off. But if I need to pick the specific values from either the left or right, this setting allows me to recreate the behavior found in Desktop Intelligence.

One caveat: this setting is a document setting, not a report setting. That means if I turn it on (or off) it affects the entire document. That may or may not be an issue. It all depends on what I am trying to do.

This post was written in response to a specific topic on BOB. I started to write the answer there but realized it would make much more sense with screen shots and more text.

52 Responses to “What Does Extend Merged Dimensions Really Do?”

  1. Comment by Lauren

    Thanks Dave. I have a much better understand of Merge Dimensions after reading your skillfully articulated explanation. I visit frequently and always learn so much.

  2. Comment by Beginner

    Thanks Dave. I have some extended question on this page.
    Can we compare the left and right dimention?
    For example,

    counts([Year(Right)] where (Year(Right)]=Year(Left)]))

    This maybe not a good example. However, you can understand my point.
    I’m wondering if there is a way to compare left and right query.

  3. Comment by bhaskar

    thanks for this topic dave.

  4. Comment by Ibrahim

    Thanks for the topic, Dave

  5. Comment by David Lai

    Hi Dave,
    So it seems like the “Extend merged dimension values” is used only to make the functionality match DeskI?
    Other than that I am not sure why we would want to turn that on.
    For example if I were to pick the merged dimensions (Resort, Year) not the left and right child ones. It would do a full outer join like DeskI.
    The only thing that I see “Extend merged dimension values” doing is taking away the ability to do the left/right joins when you select the child left/right joiner dimensions.

    Thanks
    David

  6. Comment by Rig

    That’s an excellent explanation. I now have a crystal clear picture of the merged dimension functionality in BO.
    I really appreciate your efforts!

    Thanks a ton Dave! 🙂

  7. Comment by Ansh

    Thanks Dave this is an awesome representation of Webi merged dimension functionality!!! 🙂

    Ansh

  8. Comment by Starter

    Good one Dave! Merge dimensions is clear now in my mind.

  9. Comment by Pawan Lodwal

    very help full..thanks for such a nice artical

  10. Comment by pritisha

    I have a question over using report level filters on data resultng from extended merge. I am trying t restrict a few values for a particular column but the filter doesnt seem to work. I wonder why it is behaving like this.

  11. Comment by Dave Rathbun

    Hi, without more details I can’t speculate. It would probably be faster to post your question on BOB as I don’t check comments here on my blog every day. 🙂

  12. Comment by Ernst

    I just stumbled across your blog – Great Job explaining the Merged Dimensions feature! I am in the process of modifying a few financial reports some else developed a year ago and your article helped greatly. Thanks.

  13. Comment by Denny

    Hi Dave,

    First, thanks for all help that you provide with all posts.
    Could you help me with a issue ? I have 3 merged (Country – Region – City) dimensions that are grouped in Universe by Hierarchy. I use these 3 dimensions in a Webi report (XI 3.1). My problem: If I chose the 3 originals (no merged) dimensions as filters, every thing is OK. The second filter (region) show me just Regions in Country selected. BUT, when I put the merged dimensions as filters, I lost the relationship betwen the filters.

    Can you help me ?

    Many thanks.

  14. Comment by edpypf

    thanks, learned from your blog

  15. Comment by Dave Rathbun

    Hi, Denny, I would have to test this out and see how it works for me. I know in the past I have tried to recreate filter issues without success, but maybe the merged dimensions in your example are causing an extra wrinkle.

  16. Comment by venkatesh

    thanks Dave, very useful and clean explanation

  17. Comment by Naveen

    Excellent post Dave..Very useful and helpful

  18. Comment by kumar

    Hi Dave, i have 2 queries (CY & PY) in a deski report. I have to use a complex global filter as not(isnull(fiscal week(CY)) , but with this filter i am not seeing sales from PY dataprovider. Any solution for this..
    thanks..

  19. Comment by Abrar

    Thanks Dave ..This is a superb reply i have ever seen yet.

  20. Comment by Dopple

    Something I have discovered in our migration from r2 to r3.

    If you have a merged dimension in r2, and you have a variable that is pointing at a specific query’s dimension rather than the merged dimension, the merged dimension is displayed.

    In r3, only the side that you are referring to in the variable is displayed and you then need to check “Extend merged dimension values” in order to get the same results.

  21. Comment by Dave Rathbun

    Thanks for the additional information.

  22. Comment by George

    Can you please address what Beginner said?

    Thanks Dave. I have some extended question on this page.
    Can we compare the left and right dimention?
    For example,

    counts([Year(Right)] where (Year(Right)]=Year(Left)]))

    This maybe not a good example. However, you can understand my point.
    I’m wondering if there is a way to compare left and right query.

  23. Comment by Dave Rathbun

    I didn’t answer it earlier because it doesn’t work that way. I wanted “Beginner” to test and come to the conclusion on their own. 🙂

    Here’s how you can test this. I created a simple document with two queries, each showing only the year. The “left” year has 1999, 2000, and 2001. The “right” year has 2001, 2002, and 2003. If I include only the “left” year on a report I see the expected 3 values. If I include only the “right” year, again I only see the expected 3 (but different) values. If I include both the “left” and “right” year I see only 2001 since that’s the result of the inner join. If I try to compare, of course the rows are the same since both are 2001.

    If I then turn on “Extend Merged Dimensions” I will see all five values. If I try to compare, all rows will match because that’s what the extend process does, it makes up data to fill in the gaps.

    Bottom line is that once you are linked you cannot compare “left” and “right” dimensions. You can compare measures, and that’s generally what I will do if I need to filter or adjust for either a simulated left outer or right outer join.

  24. Comment by Aziz

    Merci beaucoup pour ce post. Très bénéfique.

  25. Comment by Dave Rathbun

    You are quite welcome. It has been a long time since I have been able to practice my high school French… 🙂

  26. Comment by Eric Woerle

    Dave,

    I was hoping you could help me understand Merged Dimensions a little better. I have the following situation:

    Query 1 – Customer / Reason Code / Sales Document / Incoming Sales
    Query 2 – Customer / Sales Document / Invoiced Sales

    In my scenario I am working off of BW which means I can’t write any custom SQL. So I have merged my dimensions on Customer and Sales Document, but I need to Aggregate both of my Key Figures by the Reason Code. Is there a work around that I can use to get WEBI to recognize the merge on Sales Document when Breaking on Reason Code?

  27. Comment by Gaurav

    Hi Dave,

    I read your blogs and really find them intuitive and helpful. I have read the blogs related to merged dimensions but have not been able to find answer to my question.

    My problem is:
    Hello All,

    I am creating a report with two data providers in WebI. The common dimension from the left query is pulled into the report structure(making it a left join).

    I have pulled several measures from the left query and a single measure from the right query(detail query).

    When I am applying filter on an object from the left query, the values of the measure from the right query are all turning into NULL.

    Please let me know how this may be fixed..

    Regards, Gaurav

  28. Comment by Amit

    A very well written and articulated piece. Thanks very much.

  29. Comment by srichand

    Hi,
    Very good explained blog but, i checked the above mentioned example in webi report i observed that sql code is not changing when we enabled or disabled the extended merge dimension check box
    can you help me in this.

  30. Comment by Neeraj Sharma

    Hi Dave,

    Great post. You have made merged dimensions concept so easy to understand.

    Regards,
    Neeraj Sharma

  31. Comment by Dave Rathbun

    Extend merged dimensions is a report operation only, it does not do anything to the SQL code as you have observed.

  32. Comment by AV

    Hi Dave,

    Does this work only when there more than one merged dimension..
    like in my case there are two queries each in different contexts and have one dimension in common.
    query 1: table id(1,1,1);dim1(a1,a2,a3) : id-1 has three values for dim1
    query 2: table id(1,1); dim2(b1,b2) : id-1 has two values for dim2

    now when i merge id from both query(also checked the extend merged dimension option in webi 4.0) am expecting an full outer join results in the table with the combination of id(merged),dim1,dim2 but it doesn’t seem to happen and i still get incompatible error.

    is my understanding of extended merged dimension concept wrong?

    As always
    BIG FAN,
    AV

  33. Comment by Dave Rathbun

    If dimensions are not merged (dim1 and dim2 in your example) then they continue to be incompatible. The feature discussed in this post only changes the way merged dimensions work, it is not able to fix incompatibilities arising from non-merged dimension objects.

  34. Comment by Bhaskar

    Thank you for the Exceptional Post!!!!

    -Bhaskar

  35. Comment by Dave Rathbun

    Not really, because the function is designed to work on measures, not dimensions.

  36. Comment by Leslie haltbakk

    Hi, I do feel like … I am in a maze of little twisting passages, all alike…
    I am challenged with rewriting my Crystal Reports in WebI – and am stumped by what should be a very simple thing.
    I have a group of data:
    Incident #, Assigned To, Group #
    I want to join to the Groups table – which calls Group # “code”
    I merge dimensions on Group # and Code.
    I am able to bring the Code field into my report from the Groups table.
    HOWEVER, if I try to bring in any other fields, like Manager Name, Department name, it does a union, i.e.
    instead of getting:
    Incident #, Assigned To, Group #,Manager Name, Department name
    1234 fred 1 amy fixit dept

    I get
    Incident #, Assigned To, Group #,Manager Name, Department name
    1234 fred 1 amy fixit dept
    1234 fred 1 sue breakit dept
    1234 fred 1 john at a loss dept

    Where did I go wrong?

    Many thanks!!
    Leslie

  37. Comment by Shriram

    Hi Dave,

    First my thanks to you for your wonderful articles . however,I need a clarification regarding merged dimensions. You say that when the dimensions are merged, then in Deski the results are of a union. However, you also say that merged dimension results in a full join ,which is easy to understand as the values come from 2 different queries/dps . Kindly explain how should an extended merge should be rightly defined?

  38. Comment by Dave Rathbun

    Full Outer Join would be the proper term. A Union is a completely different operation, usually involved stacking rows, and it was probably an unfortunate choice of words for this post…

  39. Comment by Ravi

    Thanks Dave! This is most helpful! Pls let me whether it works in BI 4.0 later versions of Webi?

  40. Comment by Peter De Rop

    And that my friend, is a VERY important feature of WebI I was completely unaware of.

    At our company, we constantly merge dimension data from multiple sources and DeskI was great at it.. WebI simply seemed to SUCK at it. Turns out, there was a checkbox hidden somewhere that solved all our problems.. and you just showed it to me.

    I looked on your page for a Tip jar, but couldn’t find any.

    Thanks a bunch mate !

    Peter

  41. Comment by Dave Rathbun

    Peter, glad to have helped. I’ve never had a “tip jar” but at one point I did ask folks to send me some foreign coins for my sons to add to their collection. If you’re willing to do so, I will send you my address. 🙂

  42. Comment by Kiruthika

    This is a really great explanation. I have a question though.

    Report 1 – Query 1 – There are say 50,000 values. I am calling the column “Associate IDs” (Dimension is ASSOCIATE_ID). This is the total list of all associates in an organization.
    Report 2 – Query 2 – There are say 5,000 values. I am calling the column “Associate IDs” (Dimension is ASSOCIATE_ID) . This is the list of all Retired associates in an organization.

    Now how will I be able to display a column in Report 1 , say “Is this a Retired Associate ?” as Yes or No , by comparing the values from the ASSOCIATE_ID dimension , which is a merged dimension between Query 1 and Query 2 ?

    Is there a way , I can create a variable to compare Query1.ASSOCIATE_ID and Query2.ASSOCIATE_ID , and have a Right outer join , so that I can have the 50,000 Associate IDs displayed with either Yes or No , in the “Is this a Retired Associate ?” in Report 1.

    Sorry for the long comment. I would really appreciate your inputs. Thanks much in advance.

  43. Comment by Sushanta

    Hi Dave,

    First thanks for this nice blog. But I am facing a challenge while implementing dimension merge in one of my report. First let me explain my situation. I have 2 queries (Q1 and Q2),

    Q1
    Patient_Id Patient_Name Prob_Name
    1001 Robin Broken leg
    1001 Robin Fever

    Q2
    Patient_Id Lab_Name Result_Val
    1001 Lab1 21
    1001 Lab2 45

    I want record set like below:

    Patient_Name Prob_Name Lab_Name Result_Val
    Robin Broken leg lab1 21
    Robin Broken leg lab2 45
    Robin Fever lab1 21
    Robin Fever lab2 45

    I am merging those 2 queries by Patient_id. This is a case of outer join where the relationship between two query in many-to-many. I have checked ‘Extend Merged Dimension’ option from WEBI and also used detail type variable to show those values by setting Patient_id of Q1 as Associate Dimension for detail variables. I am not getting output that I needed. If the relationship is 1 to many (instead of M:M) then it is giving correct record.

    Am I doing anything wrong. Can you please help me.

    Thanks
    Sushanta Paul

  44. Comment by Dave Rathbun

    Kiruthika, this challenge is usually solved with a measure. If you have a count or any other measure from Q2 then you base your “If” logic on that. Even if the count is simply a 1 or some other value, it should work. Once the dimensions (ASSOCIATE_ID in your case) are merged then the values are shared and you don’t really know which side of the relationship the value is coming from. Having a measure fixes that.

  45. Comment by Dave Rathbun

    Sushanta, I am not sure how I would try to solve this because many-to-many relationships are generally a problem to be avoided. You don’t really have a valid detail object in this case because each patient has many potential problems and many potential lab results. Web Intelligence usually tries to prevent a Cartesian product, and that’s what you are trying to create.

    Perhaps you can try building this using a derived table?

  46. Comment by Sushanta

    Thanks Dave for your suggestion. Till date I was thinking that M:M relationship is possibel in WEBI. Anyway I will try with some different way.

    Thanks
    Sushanta

  47. Comment by Marea

    Hi Dave

    Do derived tables relate to Webi – please tell us more? I would be interested to know how we can use these with merged dimensions.

    Marea

  48. Comment by Dave Rathbun

    Hi, derived tables are a part (or at least a potential part) of a universe. By the time things get to Web Intelligence it’s just data. It really has nothing to do with merged dimensions.

  49. Comment by Reddy

    Well explaned

  50. Comment by Manik

    Thanks a lot Dave