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.