Skip to Content

Merge Dimension

  • This is a feature in Web Intelligence which allows linking of two or more data providers( queries).
  • Basically we merge objects from two or more queries in one webi report, so that we can use them in same block.
  • Mandatory condition to merge objects is to have same data type.

For eg.  See below tables , coming from two different queries in webi report.

Query 1

/wp-content/uploads/2013/12/img1_336582.jpg

Query 2

img2 (1).jpg

Now if you  try to use all these objects in same block , you will get data sync error.(As webi will not be able to find any relation between these queries)

I will now merge  empid and cust id.

/wp-content/uploads/2013/12/img3_336613.jpg/wp-content/uploads/2013/12/img4_336626.jpg


Merge Id is showing all rows coming from emp id and cust id and their corresponding revenue and salary column values.

This is now showing results as Full outer join.

But if I involve any one non-merged dimension in this block, the results will start showing dimension values pertaining to that query only.

/wp-content/uploads/2013/12/img5_336627.jpg/wp-content/uploads/2013/12/img6_336628.jpg

So basically it is working as Left or Right Outer join when we try to use any dimension in the block which is not merged.

But we will not be able to use both emp name and cust name in same block as these are not merged.

We will get data sync error if we try to use them in same block.

Similarly if you don’t use merge id column and use emp id or cust id along with revenue and salary, that will work as Left or Right outer join and not as Full outer join.

/wp-content/uploads/2013/12/img7_336629.jpg

So this explains when merge dimension works as Full outer join and when it works as left or Right outer join.

There is also one option which makes the functionality of left or right outer join to work as full outer join. i.e. Extended merge dimension. (note:- but this works only when unmerged dimension is not selected in the block)

See for below example.

When I used cust id revenue and salary, the block look like this:

/wp-content/uploads/2013/12/img8_336630.jpg

So its showing data as left outer join .

Now I will check the option”Extended Merge Dimension” from document properties.

/wp-content/uploads/2013/12/img9_336631.jpg

Now it will work as full outer join as shown in screenshot:

/wp-content/uploads/2013/12/img10_336632.jpg

So this option makes the query to give full outer join result set even if you have selected specific and not merged object in the block.

Note: this option wont work if you use non-merged dimension in  the block. For eg. If you use cust name in the above block it wont work as full outer join.

To report this post you need to login first.

22 Comments

You must be Logged on to comment or reply to a post.

  1. Leslie Haltbakk

    I am using WebI xi coming from a Crystal Reports background.  can you tell me where I can find the Avoid duplicate rows aggregation and show rows with empty dimension values?

    I am trying to join two tables: Tickets and Work Orders.

    Tickets don’t always have work orders and Work orders don’t always have tickets.  Tickets may have multiple work orders.  I’m getting the dreaded #multivalue.My goal is to get a count of incidents and a count of work orders in one report.  Maybe I’m going about it all wrong.

    Should I have two queries, one for Incidents and one for Work Orders? How would I combine the tallies in a report?  I need to include additional fields such  as department, etc.

    Many thanks!

    (0) 
    1. Sahil Khurana

      Hi Leslie,

       

       

      Yes, to achieve functionality which you require, You must have to include two data providers or I can say two different queries. One for Tickets and another one for Orders. On the basis of common dimension, merge both the data providers as explained by Gaurav. It will return result as you require.

      And to include additional field say “Department” in same block, you will have to make a new variable with the type of Detail. In this specify merge dimension in place of “Associated dimension” and Department Name in place of “Formula” and parse it. After that, you will able to include that variable in same block, which will return you corresponding department name.

      Please refer attached file for the same./wp-content/uploads/2014/02/webi_381359.jpg

      (0) 
  2. Leslie Haltbakk

    Thank you so much Gaurav.  I almost understand it.

    I have one query for tickets and one for work orders

    I can add them into a report separately and get the correct answers:

    tx1.PNG

    (0) 
  3. Leslie Haltbakk

    After I merge, I cannot add in work orders in the first table above – it just ignores me.

    The first table has just the incident (ticket) #, the second table has the work order and the ticket, if there is one related to the work order.

    I merged ticket from both tables.

    If I create a variable for Work Order and a variable for Incident it only gives me part of the data

    tx2.PNG

    Can you tell me where I’m going wrong? Thanks!

    (0) 
    1. Vaibhav Singh Rathore

      Hi,

      I have been trying to merge the dimensions of common data type from different queries, it is working fine but only with the merged dimension and the comparising dimension from either of the query. Not able to add new dimension because it is giving Incompatible Object.Means it is allowing me to only add one object with the common object.

       

      Example: i have taken Emp_Firstname and Job_Title from Employee and Job tables in Query 1

                     in the second query i have taken Emp_Firstname and Showroom_Name from Employeeand Showroom tables in Query 2.

                     I have merged the Emp_Firstname.

      And when i am trying to drag Emp_Firstname along with Showroom_Name and Job_Title , it is giving incompatible object cannot drop.

      But when I only take Emp_Firstname and either Job_Title or Showroom_Name it works fine.

       

      Why cant I take all the three objects in the report?

      Please give suggestions

      Regards

      (0) 
      1. Gaurav Saxena Post author

        Hi Vaibhav,

         

        Merge dimension is a different concept , it is not same like a join.

        If you are having unmerged dimension objects from different queries, it will not allow you to select them in same block.

         

        But there is a work around.

        From your example ,create a detail variable in the report and select showroom name object in it. and in the associated dimension property select Emp_first name.

        Now use this variable with other three objects i.e.

        Emp_Firstname , Job_Title and the variable.


        See if it works for you.


        Thanks

        Gaurav

        (0) 
    1. Leslie Haltbakk

      I re-read Gaurav’s excellent document one more time and found my problem.  I needed to merge tickets to work orders.  Works perfectly.  Thanks for the excellent solution!

      (0) 
    1. Vaibhav Singh Rathore

      Hi,

      I have been trying to merge the dimensions of common data type from different queries, it is working fine but only with the merged dimension and the comparising dimension from either of the query. Not able to add new dimension because it is giving Incompatible Object.Means it is allowing me to only add one object with the common object.

       

      Example: i have taken Emp_Firstname and Job_Title from Employee and Job tables in Query 1

                     in the second query i have taken Emp_Firstname and Showroom_Name from Employeeand Showroom tables in Query 2.

                     I have merged the Emp_Firstname.

      And when i am trying to drag Emp_Firstname along with Showroom_Name and Job_Title , it is giving incompatible object cannot drop.

      But when I only take Emp_Firstname and either Job_Title or Showroom_Name it works fine.

       

      Why cant I take all the three objects in the report?

      Please give suggestions

      Regards

      (0) 
  4. Balaji Kati

    hi, I am wondering for an explanation for my problem. pls help me.

    my problem looks some what similar, but feels totally different.

     

    Issue: I have 3 data providers in my report and first 2 data providers have common fields and so applied merged dimensions concept. report is working fine so far.

     

    My 3rd data provider is just a query fetching date from a different table in the same data warehouse. and this date field is used in the report footer.. not placed in the main report.

     

    but it is showing the error #multivalue in the footer

     

    by default the option “extend merged dimension values” is checked. if i uncheck this option, i am not getting this error..

     

    i am not understanding why my report filter is giving this error when that option is checked? what is happening behind the scenes? with out knowing the reason, i just cant uncheck the option for which i feel it may effect the main report results..

    pls help me understand this working..

     

    Thanks in advance,

    Balaji

    (0) 
  5. Vaibhav Singh Rathore

    Hi,

    I have been trying to merge the dimensions of common data type from different queries, it is working fine but only with the merged dimension and the comparising dimension from either of the query. Not able to add new dimension because it is giving Incompatible Object.Means it is allowing me to only add one object with the common object.

     

    Example: i have taken Emp_Firstname and Job_Title from Employee and Job tables in Query 1

                   in the second query i have taken Emp_Firstname and Showroom_Name from Employeeand Showroom tables in Query 2.

                   I have merged the Emp_Firstname.

    And when i am trying to drag Emp_Firstname along with Showroom_Name and Job_Title , it is giving incompatible object cannot drop.

    But when I only take Emp_Firstname and either Job_Title or Showroom_Name it works fine.

     

    Why cant I take all the three objects in the report?

    Please give suggestions

    Regards

    (0) 

Leave a Reply