Wednesday 20 June 2012

Merge Dimensions in Webi


Merge Dimension
This is a feature in Web Intelligence which allows linking of two data providers(or 2 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






Query 2







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.


























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.















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.












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:










So its showing data as left outer join .
Now I will check the option”Extended Merge Dimension” from document properties.













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













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.

17 comments:

  1. Ok.. can you pls let us know How do u link different data providers in a single report ??

    ReplyDelete
  2. Thanks....a lot... now I got the clarity of Merge Dimensions..
    Pretty explanation.

    Keep poting more...

    ReplyDelete
  3. Crystal clear!! great job!

    ReplyDelete
  4. Thanks a lot....

    ReplyDelete
  5. liked the way it was explained..


    ReplyDelete
  6. SOOOOO Helpful, did not know it was an outer join! Thought it was an intersection the whole time!

    ReplyDelete
  7. if You are are getting same result after using merge dimension and extended merge dimension, then whats use of extended merge dimension?

    ReplyDelete
  8. super explanation..Thanks a ton brother..

    ReplyDelete
  9. thanks for the explanation

    ReplyDelete
  10. I Need Material of Combained query of WEBI

    ReplyDelete
  11. Can you please put the practical difference of using Full outer join using 'Extended Mege Dimension' and 'Merge ID' column?
    If they are doing the same job what is the exact difference between them?

    ReplyDelete
    Replies
    1. Hi,
      I have summarised the same in the last section.
      let me take another example:
      Lets say you have dimensions M1 and M2, and you merged tham say M3.
      M1 has values 1,2,3 and M2 has values 3,4,5.
      lets say you brought revenue also like R1 from query 1 with M1 and R2 from query 2 with M2.
      In the block when you select M1,R1, it will give you 1,2,3 values and there revenue.
      If you select M2,R2 it will give 3,4,5 and revenue.
      If I select M3 than data will be 1,2,3,4,5
      Note:- extended merge dimension option is not selected yet.


      Now if ou remember when you used M1 it just shows you 1,2,3.
      Now check the option "extended merge dimension", you will see all results of merge are shown for this column i.e. 1,2,3,4,5.
      Eihe of the column M1 or M2 will give you merged results which earlier you were getting either from M1 or M2 based on the object you brought in the block.

      Hope it clarifies your doubt.


      Gaurav

      Delete
  12. Excellent Blog. Thanks for the effort !!

    ReplyDelete
  13. how to trouble shoot if i have a oracle data source and my report everything is correct but my user is not getting correct data of ressult.....

    ReplyDelete
    Replies
    1. Hi Ramesh

      You need to narrow down the issue by breaking the report into multiple smaller ones.
      That would be best way to troubleshoot any kind of issue.

      Thanks
      Gaurav

      Delete
  14. Hi ,

    When I use the merge, am getting #MULTIVALUE error in the second query. How to resolve this?

    ReplyDelete