Tips for Merging Dimensions

I’ve noticed lately that there seems to be a lot of confusion about merging dimensions in Web Intelligence. I’ve spent a fair amount of time on BOB answering questions about this topic. So, perhaps it’s time to put together a comprehensive document covering this topic. After all, merging dimensions is one of the most powerful features of Web Intelligence.

Tip 1: Adding “Incompatible” dimensions to the block

Have you ever noticed that, sometimes when you try to add a dimension to a block that includes merged dimensions, you sometimes get an “Incompatible object” error? Let me explain why this happens, and then we’ll look at ideas on how to fix it.

Dimension objects and Detail objects have a fundamental difference: Dimension objects usually represent a different level of granularity. For example, State and City might be two dimension objects in your universe. City is a lower level of granularity than State. So, when you add City to an existing block that already includes State, the measure objects will be aggregated at a lower level of granularity.

Detail objects, on the other hand, typically do not represent a lower level of granularity when used with their related dimension. For example, if I have Sales Revenue broken down by Customer in a block, and then add the Eye Color detail object, Sales Revenue will not be aggregated at a lower level. It will stay at the level of Customer. This is how detail objects work.

So, if I have two queries merged on State, and try to display another dimension, that is not merged, such as State Capitol, Web Intelligence doesn’t know how to aggregate the measures at the lower level of State Capitol, since that dimension doesn’t exist as a merged dimension. Of course, you and I both know that each State only has one State Capitol, so it’s not really a lower level of granularity. But Web Intelligence doesn’t know that. So we have to tell it.

The way we tell is as follows: Create a detail variable. In this case, maybe we call it Capitol. Make it a detail of the State merged dimension. The formula for this variable is:

=[State Capitol]

We can then add the variable to the block, as Web Intelligence sees it as a detail of State, rather than a different level of granularity. Note that the detail variable must be a detail of a merged dimension. Otherwise, you still won’t be able to add it to the block.

Tip 2: Auto-Merge dimensions only works within a universe

Web Intelligence has a feature called “Auto-merge dimensions”. It’s in the document properties, and is turned on by default. However, not all dimensions will automatically merge with this feature. So let’s clear up the confusion and make it crystal clear when this feature works.

If you have two queries, from the same universe, that include the exact same dimension objects, those dimension objects will automatically merge. This is the only time when dimension objects automatically merge.

Here’s an example of a merge that will not happen automatically. Let’s say you have an object called Address, in a class called Vendor, and you have another object called Address, in a class called Customer. These two objects have the same name, and are from the same universe. Will they automatically merge? No. Web Intelligence is smart enough to know that they are not the same object. Of course, in this case, you probably won’t want them to merge. But if you do, you will need to manually merge them.

Tip 3: Values displayed depend on which object is used

Sometimes, the values between two merged dimensions don’t completely match. For example, you may have a list of product numbers from query 1, and a list of product numbers from query 2, and perhaps some of the product numbers in query 1 don’t show up in query 2. That’s OK. But which list of product numbers will appear on the report? Well, that depends on which Product Number object you use.

If you display the Product Number object from Query 1, you will see all the Product Numbers from Query 1. If you display the Product Number object from Query 2, you will see all the Product Numbers from Query 2. However, if you merge the two Product Number objects, and display the merged dimension, you will get all product numbers from both queries. For those of you familiar with SQL, this is the equivalent of a Full Outer Join.

Tip 4: “Extend merged dimension values” has a similar effect of using the merged dimension

In the document properties, you will find a property called “Extend merged dimension values”. This a fairly useless feature, as it has a similar effect to using the merged dimension. Therefore, I never use this feature. I just follow the rules in Tip 3, above, to determine which values will be displayed.

Tip 5: There are rules to merging dimensions
  • Only dimensions defined in the universe can be merged. You cannot merge using variables.
  • Objects must have the same data type. You cannot merge a number with a string, even if the values match.
  • Any number of queries can be merged. There is no limit.
  • Any number of dimension objects can be merged between two queries. Again, no limit.
  • Values are case-sensitive. So, if the values are the same, but of different case, they will not match. They will be shown as different values.
  • Watch out for trailing blanks. Even if the values look exactly the same, they won’t match if one has a trailing blank, and the other one doesn’t.
Conclusion

Merging dimensions is the only way to combine data from different data sources in the report. Therefore, it’s a very powerful feature, especially if you understand how it works, and how to make it work. If you’re trying to get your merged dimensions to work, and they just won’t cooperate, read through the tips above, and you’re likely to find the solution. If you have other tips, feel free to comment below.

Thanks for reading. 🙂

Advertisements

83 Responses to Tips for Merging Dimensions

  1. KFonMurphi says:

    Hi,

    About “Extend merged dimension values” (tip 4): it’s useful to get the same behaviour as in Deski, where you don’t know really why the ProductNumber form Query2 shows values from Query1 sometimes 🙂 (well it depends from the measures.)

    It’s checked in Webi documents created from Deski documents by the Report Conversion Tool.

    It’s as shame that you cannot use variable: that would be so useful for working with very different universes. It’s even worse now that Webi checks the type: in Deski, you could merge Year as varchar and year as Number. With Webi you can’t!

    • Bill Avery says:

      Funny you mention Deski functionality, as I am just now being exposed to it after many years of only Webi/Universe. I am sort of incredulous that all Deski functionality has not been incorporated into Webi by now. And now they say they are simply doing away with it. Doesn’t make any sense to me.

      • Thanks for the comment, Bill. I think of it this way: They used to have two different applications, one for the desktop, and one for the web, that were designed to do basically the same thing, but were completely incompatible with each other. Now they have Web Intelligence Rich Client, and Web Intelligence Web Client, and they are both compatible with each other. In this way, I think it makes sense. Hopefully, they will eventually get all the missing functionality into Web Intelligence.

    • Diwa Chamarti says:

      “Extend merged dimension values” –
      This is very useful if you are merging on multiple dimensions and the uniqueness of detailed objects is determined by multiple merged dimensions. If you do not set the Document property to Extend Merged DIm values, you get #multivalue

  2. Dave Rathbun says:

    Hi, Michael, I wrote a blog post myself some time back about the extend merged dimensions feature that shows what it does (and does not) do.

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

    • Thanks, Dave. I like your blog post. It confirms what I’m saying about the uselessness of that feature. It does the same as using the Merged Dimension, but doesn’t give as much control.

      • Bill Avery says:

        Glad to hear this from the professionals! I always wondered why anyone would use that “feature”.

  3. Great post – a great one stop shop for merged dimensions.

  4. Lars Barasinski says:

    Hi Michael,

    Thanks for the article.

    When merging dimensions and a corresponding detail (in Deski) everything works fine when using either the dimension or the detail information in the report. If I do the same in Webi I get duplication of rows when using the detail, but the dimension works fine. Am I missing something here or is this a limitation of Webi?

    Lars Barasinski

    • Hi, Lars;
      Without knowing the details of your situation, I have found that most such issues can be resolved by making sure that the detail is a detail of a merged dimension. If not, you can get unexpected results. Merging dimensions in Webi works much differently than linking dimensions in Deski. Personally, I prefer the way it works in Webi.

  5. Raj says:

    Thanks for the article. “Auto-Merge dimensions only works within a universe” helped me think of the solution to my problem in a different angle.

  6. Roland says:

    Michael

    Once again thank you for your insight. This blog prevented me from posting a question on BOB. This is one of the quesitons that I know the answer to but hoped there was a work around… In this case, I guess not. Now, if they would enhance the product to allow merging on variables, my issue would be solved!

    ■Only dimensions defined in the universe can be merged. You cannot merge using variables.

  7. Shiji says:

    It is very clear explanation of merged concept.

  8. Leticia Locatelli says:

    Dear Michael,
    Many thanks for this post, it’s very clear.
    I wonder if you have done some testing merging different types of objects. I am working on a BO 4.0 proof of concept and according to SAP if should be possible to merge a hierarchy object with a Dimension (help link here: file:///C:/Program%20Files/SAP%20BusinessObjects/SAP%20BusinessObjects%20Enterprise%20XI%204.0/Web%20Content/Enterprise_XI40/help/en/html/topic821.html#concept_6B5F79D4A5A64E75B523018847CC063C_821).

    I am trying to merge a hierarchy member (based on the new BICS connector) with a non-hierarchical dimension coming from an Excel file. I have not been able to merge them so far. I am looking for how-to papers to do this but all the posts bring me to your blog :). Yo I decided to give it a shot and ask you.

    Many thanks in advance for your time.
    Leticia

  9. Jagpreet says:

    Hi Michael,

    I guess, merging in Webi is same as the “link” option we had in desktop intelligence. I want to know, if there is a scenario that I am writing 2 queries from the same universe and have common dimensions, let’s say Year, State and Product, and I want the queries to link/merge on State and Product but not on Year, can i do that? By default, Webi merges all three dimensions, but i want to un-merge Year. Is there a way to do that. Please advise.

    Jagpreet

  10. Cherry says:

    Hi all,

    Could you please expain a little bit about two universes that need to merge dimension? And this dimension as a key dimension.

  11. Jon Fortner says:

    Michael, I used two Character Dimensions that have the same type of data from two different universes. I was trying to get the table to show blanks for cells that didn’t have a match in the other query on Product names. I think this should have worked but didn’t (would only show me the combined list of products even if I used the non-merged dimensions in the table) and may be related to a size/type definition in the table where one is a VARCHAR and the other is like Char(30) or one is trimmed the other is not. I haven’t tried it yet, but an rtrim function on the object defined as Char(30) might help them get in sync. Your thoughts?

    • Hi, Jon;

      If they are both Character strings, then it doesn’t matter if one is VARCHAR and the other is CHAR(30). They can still merge, and the values are the same. If you want to see all values from Query 1, and only values from Query 2 where there is a match on Products, then merge the two queries on Products, but only use the Products object from Query 1.

      • Jon Fortner says:

        That was the issue, it didn’t matter which combinations of objects I used from the Merged Dimension or from the specific query, it always combined the list of products from both Query 1 and 2, even when I used [Query 1].[Products] in column 1 and [Query 2].[Products] in column 2 of a table. Q1 had 4 products, Q2 had 7 and I need a table showing the gaps. It should have worked but didn’t. I’ll do some more testing and if I determine the fix, I’ll post it.

      • Kari-Anne says:

        Hi,
        Thank you for a informative post, Tip 1 have been of great help. But I’m still struggling with the same problem that Jon Fortner describes: I need a table showing the gaps. Is there a solution to this?

      • Kari-Anne, sorry for the slow response. What gaps do you want to show?

      • Mike says:

        I’m having a similar issue as Jon and Kari-Anne.

        The “gaps” would be what I want to show.
        Q1.obj1 has a set of values (A,B,C,D,E,F,G).
        Q2.obj1 has a set of values (B,D,G,H,I).
        I need to only show the values from Q1 that are not returned in Q2 (A,C,E,F).
        Display Q1.obj1 where Q1.obj1 Q2.obj1

        If the Obj1 dimensions are not merged I cannot relate Q1.obj1 to Q2.obj1 without getting incompatible object error in my variable.

        Merging the dimensions creates a single list of values (A,B,C,D,E,F,G,H,I). Union Set

        We need what is known as the relative complement is set mathematics
        https://en.wikipedia.org/wiki/Set_(mathematics)

        Thanks,
        Mike

      • Perhaps a Minus query would be better in your case. Have you tried that?

      • Mike says:

        Thanks for the reply.

        I actually solved it by filtering on an “object from another query”.

        Q1.Object (Universe query) not in list Q2.Object (Spreadsheet query)

        Maybe that is what you meant by minus query.

        Mike

  12. Noel Scheaffer says:

    This a very well-written and informative post. This directly addressed and provided a solution to a problem I was wrestling with today.

    Thanks!

  13. Don says:

    I have this perception that you should only merge on objects that represent key data. In other words merge only on objects that you might use at the database or universe level to link tables. However, Webi auto merges all dimension objects that meet the rules in Step 5 above regardless of whether they seem correct or not. Are there rules for when two objects should be unmerged (not merged)? Sorry if I missed the answer in the article above.

    • Don, Webi only automatically merges when the exact same dimension object appears in multiple queries. The merge happens based on the CUID of the object, not the name. So, if two different objects with the same name, appear in two queries, they will not be merged automatically. Does that help?

  14. Richard says:

    I have a Business Objects Server configured with two seperate ODBC connections (they point to two seperate databases; one Test and the other Production) and two seperate universes (they are alike in every way, but they point to the different ODBC connections to pull in different data). However, I configured a report while connected to the Test universe and when I tried pointing it to the Production universe (Edit Query>Properties>Universe), it deleted all of my merged dimensions. It in essence, breaks the report of most of it’s functionality (especially the sectioning that relied so heavily on the merged dimensions). There is no reason why the merges should delete themselves, and there are other reports I’ve performed the same task with and their merges remain intact. Is there an option under Document Properties that I’m missing here to stop the merged dimensions from being deleted?

    • Hi. Richard;

      I haven’t seen this happen. I can see it happen with a Deski document, but not Webi. Deski had issues with changing universes. Webi handles that change much better. However, perhaps I can make another suggestion: Instead of having two identical universes, pointing to two different databases, you could have one universe, pointing to the production universe. Then create a restriction set in that universe that includes a connection to the test database. Set a group of Testing user IDs in the system, and apply the restriction set to them. So, when they use that universe, it connects to the test database.

  15. Morgan says:

    Michael, thanks for the great article, very helpful. I’m having a hard time with something & am wondering if you can lend me a hand. It’s a little complicated to explain, but here goes – I basically have 4 queries. #1 brings back brings back balances by branch #. #2 links branches to line of business. #1 is from a 3rd party database that we don’t have control over, and #2 is our own grouping of the branches…. #3 is from a separate universe, similar to #1, and #4 is again segmenting them in to markets.

    So, I was able to merge #1 & #2 by using your technique of creating a detail variable and have it summarized to a Line of Business and a Total. Same deal with #3 & #4. I can get two separate tables showing the line of business and total. Now I need to merge the two merged queries on that line of business to get the grand totals. That make sense? Any ideas? Seems like I should be able to specify in a formula that I want the total from A plus the total from B where the market in A matches the market in B. I tried doing a 3rd merge to get to this, but it’s not working and I’m running out of ideas… Seems like it should be simple – I have the data in two separate tables and just need to combine them, joining on one of the values… Am I missing something? Thanks very much, I appreciate it!!

    • Morgan,

      You’re right, it is difficult to explain. It’s difficult for me to quite understand the whole situation. Have you tried posting the question BOB? If you do, let me know, and I’ll try to help there.

  16. Crystal says:

    In response to Tip No. 3 Query 1 has actual data by section and Query 2 has plan data by section. I want to merge the data in the two queries based on section, however, I want to see all sections on both Query 1 and Query 2. Is this possible?

  17. swati says:

    Thanks for this great artical on merge dimension.

  18. Reddy says:

    Hi Michael,
    It is really great article on ‘merge’ option.
    Info which you mentioned in Tip3 is like left, right and full outer joins. but I am looking for equi join… Could you please let me know how we can display data if i want to see data similer to equi join.
    Thanks,

    • Thanks, Reddy. I’m glad you enjoyed it. Unfortunately, there is no built in feature for an equi-join. If you search BOB, you will find some tips for doing that with a variable.

  19. Bob says:

    I’m struggling with totals on a merged dimension value in Webi – when I apply an analysis filter, the totals on my merged data from the secondary query are not updated, while the totals on the main query are – I presume this is because the field I am filtering on is on the primary query – any suggestions?

    • Bob, I think you are correct. Can you apply the filter on the merged dimension? You should get the expected results that way.

      • Bob says:

        Yes, that would work. unfortunately I need to filter on a field thats only on one of the queries in the merged dimension – sorry, should have made that clearer. I guess I have to live with it..

  20. Brad says:

    I don’t leave comments very often, but wanted to say “thanks”. This article provided answers I could not find anywhere else. I mean anywhere. I can usually figure out most things, but I am finding Rich Client does not play nice with Excel.

  21. guru says:

    We are trying to merge an essbase universe dimension with a relational universe dimension and it does not seem to be working.. The merge happens, but when adding the merged dimension to the report, we get #SYNTAX. Do you know if this is possible or not?

  22. Surya says:

    Thanks Michael..for your detailed Explanation on Merged dimensions

  23. Ranjith says:

    Hi Michael,
    We have Query1 with Objects: State, Category, Quantity;
    Query2 with objects: State, revenue. We have merged both queries on State.
    Created block with State, Quantity, Revenue and creating input control on category, But Revenue is not influencing if you change the category.? Could you please suggest me here.. I am expecting Revenue also should change accordingly when you change ‘Category’.

    Tried by creating detail variable for Category and then created input control on it, but no luck… we have relationship for State to Category is 1:N.

    Thank you in advance,

    • Hi, Ranjith;

      Revenue cannot be broken down by category, since you don’t have revenue and category in a query together. If you can add Category to Query2, and then merge on Category, then your input control will work fine.

  24. Teresa says:

    Very interested post. Thanks

  25. Caralie Ringholz says:

    This is my first time trying to use a merged dimension and am not sure if it’s even the correct thing to do.

    I have two queries:
    ‘Query 1’ selects data from a donation universe (Donations made to our Co.) based on several section criteria. The field to GL is a ‘GL Reference Nbr’ stored in the Contribution tables (Contributions hit the GL).

    ‘Query 2’ selects data from the GL Transaction Detail table in the Finance universe based on that same GL Reference Nbr (where GL Reference Nbr = [Query 1].[Gl Reference Nbr]. Each contribution can affect multiple accounts on the GL side. The only link between the 2 queries is the GL Reference Nbr which is not a key to either table.

    The selection for both queries is working correctly.
    I need a report of the contribution information selected in ‘Query 1’ along with all accounts affected from ‘Query 2’, including account number, and debit / credit amts.

    When using Merge Dimensions on the GL Reference Nbr I get a total debits and total credits for each contribution disregarding accounts nbrs. I cannot drop in the GL Account Nbr and I am not getting the individual debit / credit amounts.

    I can’t see how to attach a document. If I could I would show you the details.

    • Hi, Caralie. Create a detail variable with =[GL Account Nbr] as the formula. Make it a detail of GL Reference Nbr. You should then be able to add that variable to the block, and get what you need.

      • Caralie Ringholz says:

        Thanks for the help, I’ve never used a detail variable.

        However I must have missed something because it still didn’t work. I kept the Merged Dimension on the Transaction Reference No from both universes. I then created the Detail variable v_GL Account No with the Associated dimension = GL Transaction Ref No.(from the Finance Universe).
        I then moved the merged GL Ref No into the report (along with all the fields from Query 1 . Then the Detail Variable and Debit / Credit Amounts from Query 2 . I am still getting the Total Debit / Credit Amount and the Detail Variable is #Multivalue.

      • Caralie, associate the detail with the Merged dimension for GL Reference Nbr., not the one from either query. Also, make sure that you use the Merged dimension in the block.

      • Caralie Ringholz says:

        Thanks Michael. I tried your suggestion and am still having problems. Here’s the details.

        Query 1 Advancement Universe
        2 Records (Two contribution transactions for the same contribution ID, one a Matching Gift and one an outright Gift. Many other fields are also included. Each transaction has a debit and credit resulting in 4 GL detail transactions):
        Both Records: [Contribution ID] = 154806
        [Contribution GL Reference No] = DN154806
        1st Record: [Contribution Donor ID] = D1
        [Contribution Match Credit Amt] = $700
        [Contribution Hard Credit Amt] = $0
        2nd Record: : [Contribution Donor ID] = D2
        [Contribution Match Credit Amt] = $0
        [Contribution Hard Credit Amt] = $700
        Query 2 Finance Universe
        4 Records
        All 4 Records: [GL Transaction Ref No] = DN154806
        1st Record: [GL Account Nbr] = A1
        [GL Transaction Debit Amount] = $700
        [GL Transaction Credit Amount] = $0
        2nd Record: [GL Account Nbr] = A2
        [GL Transaction Debit Amount] = $0
        [GL Transaction Credit Amount] = $700
        3rd Record: [GL Account Nbr] = A3
        [GL Transaction Debit Amount] = $700
        [GL Transaction Credit Amount] = $0
        2nd Record: [GL Account Nbr] = A4
        [GL Transaction Debit Amount] = $0
        [GL Transaction Credit Amount] = $700
        REPORT (Many fields displayed from Query 1)
        Merge Dimension – [v_Merged Ref No]
        Query 1 – [Contribution GL Reference No]
        Query 2- [GL Transaction Ref No]
        Detail Variable – [v_Dtl GL Acct No]
        Formula: = [GL Account Nbr]
        Associated Dimension: [v_Merged Ref No]
        Insert into Contribution Report:
        [v_Merged Ref No]
        [v_Dtl GL Acct No]
        [GL Transaction Debit Amount]
        [GL Transaction Credit Amount]
        Report Result:
        2 Rows – both with [v_Merged Ref No] = DN154806
        1st Row: [Contribution Donor ID] = D1
        [v_Dtl GL Acct No] = #Multivalue
        [GL Transaction Debit Amount] = $1400
        [GL Transaction Credit Amount] = $1400
        2nd Row: [Contribution Donor ID] = D2
        [v_Dtl GL Acct No] = #Multivalue
        [GL Transaction Debit Amount] = $1400
        [GL Transaction Credit Amount] = $1400
        Expected Report Result:
        4 Rows – all with [v_Merged Ref No] = DN154806
        1st Row: [Contribution Donor ID] = D1
        [v_Dtl GL Acct No] = A1
        [GL Transaction Debit Amount] = $700
        [GL Transaction Credit Amount] = $0
        2nd Row: [Contribution Donor ID] = D1
        [v_Dtl GL Acct No] = A2
        [GL Transaction Debit Amount] = $0
        [GL Transaction Credit Amount] = $700
        3rd Row: [Contribution Donor ID] = D2
        [v_Dtl GL Acct No] = A3
        [GL Transaction Debit Amount] = $700
        [GL Transaction Credit Amount] = $0
        4th Row: [Contribution Donor ID] = D2
        [v_Dtl GL Acct No] = A4
        [GL Transaction Debit Amount] = $0
        [GL Transaction Credit Amount] = $700

  26. Caralie Ringholz says:

    I found another field that can be merged – Query 1 – [Contribution Donor ID], Query 2 – [GL Transaction Person or Vendor IDr ]. This is the Donor the contributed. (I already had Query 1 – [Contribution Donor ID]. but had to add Query 2 – [GL Transaction Person or Vendor IDr ].

    Merge Dimension – [v_Merged Donor ID]
    Query 1 – [Contribution ID]]
    Query 2- [GL Transaction Person or Vendor IDr ]
    Detail Variable – [v_Dtl Donor ID]
    Formula: = [GL Transaction Person or Vendor IDr ]
    Associated Dimension: [v_Merged Ref No]
    Added [v_Merged Donor ID] to BLock
    Still 2 rows in report, each with [v_Merged Ref No]=DN154806 and [v_Dtl GL Acct No] = #Multivalue
    1st row – [v_Merged Donor ID] = D1
    [GL Transaction Debit Amount] = $0
    [GL Transaction Credit Amount] = $0
    2nd row – [v_Merged Donor ID] = D2
    [GL Transaction Debit Amount] = $1400
    [GL Transaction Credit Amount] = $1400
    I tried changing Detail Variable [v_Dtl GL Acct No] to Associated Dimension [v_Merged Donor ID] with the same results.

    • Caralie Ringholz says:

      After further testing and filtering:
      I have eliminated the match transactions and am now down to a single contribution from Query 1 and 2 account numbers for each contribution, one with a debit and one with a credit.

      Merge Dimensions:
      [v_Merged Ref No]
      Query 1 [Contribution GL Reference No]
      Query 2 [GL Transaction Person or Vendor IDr ]
      [v_Merged Donor ID]
      Query 1 [Contribution Donor ID]
      Query 2 [GL Transaction Person or Vendor IDr ]
      Detail Variable:
      [v_Dtl GL Acct No]
      Formula: [GL Transaction GL Account Number] Query 2
      Associated Dimension: Tried all these combinations:

      Associated Dimension: [v_Merged Ref No]
      Associated Dimension: [v_Merged Donor ID]
      Associated Dimension: Query 1 [Contribution Donor ID]
      Associated Dimension: Query 1 [Contribution GL Reference No]
      Associated Dimension: Query 2 [GL Transaction Person or Vendor IDr ]
      Associated Dimension: Query 2 [GL Transaction Ref No]

      All variations of [v_Dtl GL Acct No] Returns #Multivalue. There should be 2 Account Numbers returned, one with a debit amt and one with a credit amt

      Any ideas how to solve the #multivalue issue?

      Thanks!

  27. dorai says:

    Hi ,

    Rit now we are showing data at territory level and We have an requirement to show at rep level.means who are all reps comes under that praticular territory and has to corresponding rep level sales.We dont have fact data rep level.
    I have created two data provider for terrioey sales and one more data provider for bringing rep information.

    I have created rep detail object on territory as associate dimension object.here problem is same rep assigned two differnt terrotories.whne i tried to use detail varible I am also getting Multi value errors.please let me know how to reslove the isse.

    • Hello, dorai. You can’t resolve this. If you don’t have fact data at the rep level, you can’t create a report that displays data at that level. You can only merge two queries to the lowest level of granularity that is common between the two queries.

  28. Jody Tucker says:

    Hi Michael… We’ve spoken before, but it’s been quite some time.

    I use merged dimensions a lot, but one thing has always perplexed me… What are the rules about which non-merged dimensions can be included in a report object and which have to be converted to a Detail? I can’t seem to find the pattern. Sometimes it seems I can drag on any old dimension from any query and it’s fine, but other times it forces me to do the Detail. Do you know what the rule of thumb is?

    • Hey, Jody. It’s great to hear from you. The rule of thumb is that you can add any dimensions to the block that are merged. So if you have 5 merged dimension, and 5 unmerged dimensions, you can add any of the 5 that are merged, but the others will have to be made into details using variables.

      • Jody Tucker says:

        See… that’s the thing. You don’t always have to convert the un-merged dimensions to Details…. and I just don’t know why…

  29. It’s consistent with me. It always works the same. Maybe the software makes special exceptions for you. 🙂

  30. Kan says:

    Hi Michael,

    This is a great post. Thank you for sharing!

    I have a question on Tip 1. When you say “Web Intelligence doesn’t know how to aggregate the measures at the lower level of State Capitol, since that dimension doesn’t exist as a merged dimension. “, it means merged dimensions should go at the lowest level/granularity. But will it know if I want the aggregation happens at higher level?

    For example, I have
    Query 1: State, City, Sales Revenue;
    Query 2: City, Gross Sales.
    I merged two City dimensions together, so the report will look like:
    State, City(merged), Sales Revenue, Gross Sales

    I created a break on State, and summed up Sales Revenue, however, the subtotal of sales revenue for individual state shows the grand total. So i guess WebI doesn’t understand the relationship? Should I use detail dimension in this case too?

    Thank you in advance for your help!
    Kan

    • Kan, Since you don’t have Gross Sales at the state level in a query, Webi won’t be able to roll it up to state. You will need to add State to your second query.

      • Kan says:

        What if my second query/universe don’t have the State data? Any idea how to do a workaround?

        Thanks

  31. There’s no workaround. You’ll need to get the State added to that universe.

  32. Aaron says:

    Thank you Micheal it is veray useful.
    but about the granularity. cisty should have higher level of granularity compare with state, since granularity is about the level of detail

  33. Jon says:

    Michael,

    I am trying to implement a detail variable to add a dimension from a 2nd query to my report, but am having issues. I have made the detail variable’s associated dimension the merged dimension between the queries, but the column containing the detail variable returns blank results. Have I missed something simple here?

  34. Lyla Yan says:

    Hi Sir, we encounter one issue regarding tip3. we used 3 merge dimemsions in our report, master category, category, sub category, and show value based on quey1. but after we add a drill through hyperlink on category and subcategory, the value show in report is not based on quey1 again. please help

  35. Kirk says:

    Thank you very much! Your article finally shed light on the concept of creating Detail objects that other articles have not. That made all the difference in allowing me to solve my problem.

  36. Veutin says:

    HI Michael,

    I am reading your post today as I am trying to find a solution for two queries that I have merged and which are not behaving as an outer join.

    Let’s say that I have
    – Query 1 with: SO | Order Date | Item
    – Query 2 with: PO | Order Date | Item | Delivery Date

    I have linked the two queries on Item and I would like to show for each SO all the related PO but when I create my table with SO as a dimension and PO as a detail of Item it seems that BO assigns PO’s to SO’s randomly and does not really behave like an outer.
    I would have expected the SO to be repeated as many times as I have PO, e.g. if I have for item A SO1 + SO2, and PO1, PO2, PO3, I would expect BO to return
    SO1 | Item A | P01
    SO1 | Item A | P03
    SO1 | Item A | P03
    SO2 | Item A | P01
    SO2 | Item A | P02
    SO2 | Item A | P03
    But it does not behave like this. Do you have any ideas how to resolve this ?

    Thanks in advance for your feedback-

    David

  37. kristaldoyle says:

    Hi Michael, thank you so much for the informative post regarding merged dimensions.

    In tip 5 you mention that any number of dimensions can be merged and there is no limit. I am wondering however if you have suggestions on performance. I have a report with 6 queries and merging dimensions according to our hierarchy. Then we want to use this dimension in a drill so that users can drill through the hierarchy for results sets. I’ve noticed HUGE performance impact in that it will take nearly 40 seconds to render the page once you complete a drill from level 1 to level 2 (for example).

    without drill the page will render in 4 seconds. Do you have any insight on why the drill functionality on merged dimension would be such an impact to performance?

  38. Grace says:

    Hi Michael,

    I have a question about merging. I have a report with Queries 1, 2 and 3 in which I have merged dimensions including ‘Family’ and ‘Sub-family’. I’m trying to add Queries 4 and 5 to the same report (which have a different sales reporting time frame) and merge the ‘Family’ and ‘Sub-family’ dimensions only between 4 and 5, not with 1, 2 or 3, even though they have the same dimension name. When i try to do this, however, I get an error message telling me that they are already linked. Is there a way to merge the dimensions in Queries 4 and 5 separate from 1, 2 and 3?
    Otherwise, I’m going to have to make a new report for this data, though it would make more sense in the current report.

    Many thanks,
    Grace.

    • Hi, Grace. I have to admit that I haven’t tried doing what you’re doing. Can you merge the dimensions from queries 4 and 5, and give the merged dimension a different name, like ‘Fam’ and ‘SubFam’?

      • Grace says:

        Thanks for replying.
        It won’t let me merge them in the first place, since I’ve already merged the same dimensions in queries 1,2 and 3. I’m not able to merge variables am I?
        The issue is that the merged dimensions from queries 1, 2 and 3 have query filters applied, but I don’t want any filters on those dimensions in queries 4 and 5.

        Cheers.

  39. Diane Spargo says:

    Love your blogs and I find myself looking for them often. I had this come up today, I don’t think I have ever had anyone ask me to do this. Hmm. They have three data providers, each one uses a different universe. They want to be able to merge all three, however there isn’t anything in common between all three. There is a dimension in common between the first two, let’s just call it DIMA. Then, there is a separate dimension from the second and the third one, let’s call it DIMB. In order to display the data from all three data providers in one report, do they need to have a common dimension across all three? That is what I am guessing as I haven’t been able to make this work correctly. Thanks in advance.

    • Hi, Diane;

      Yes, you are correct. The only way to match the granularity between all three data providers, there must be at least one common dimension between them all.

      BTW, I’ve done quite a bit of work for your company. What a great place to work!

  40. Diane Spargo says:

    Thank you for a quick response. I love my job. I actually met and had drinks with you at the national conference in Anaheim a few years back. Nice to chat with you again. Have a great day.

  41. Pramod Kumar Jonna says:

    We are trying to perform Left join between query1 and query2 using Merge dimension functionality. We are displaying the merged dimension column from first query instead of merged dimension itself but it is considered as inner join instead of left outer join.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: