Merge Dimensions – Including incompatible unmerged dimensions from 2 queries into one block

[Former Member]

Concept: Linking of two queries to combine their data.

Requirement: The dimension on which we are merging the data should be of same data type in the queries.

Note: We can only merge a dimension to a dimension. Merging can’t happen between Variables or Measures. Just Dimensions!

Reference: You can have a clear picture of merge dimension on this blog also- Merge Dimensions and Extended Merge

Well, first of all, to all the newbies out there in WEBI (I am also one of them though), let me give an idea of what Merge Dimension is. For the SQL lovers, we can say that it is much similar to creating a join between two tables. It is similar to that but not exactly like that.

Merge Dimension is a concept in which you are able to combine data from 2 different queries into a single WEBI report. Say you can combine data from Query1 with the data from 2nd query. Below images will explain it well.

Data from Query 1:

Data from Query 2:

Say we want to merge the two Queries on ORDER so that the output data should be like:

This is an easy case where we can simply merge “Order” from both the queries and drag with the other fields into a new block. But if we want to have unmerged dimensions as well, from both the queries (SQL guys can imagine it like a full outer join) then there is some pain involved.

Say you want data where you want to know which all orders were received on which date, like:

Here we have dimensions from both the queries. Let us now understand how to achieve this.

In this case, what I have done is, I have created a new detail object named dateRec_dtl. I associated this object to the merged dimension “Order”. The formula for this object would be equal to the object from the other query. Note that order, item and quantity in above screenshot are from one query and dateRec_dtl is the detail object used to bring the dateRec from the other query. Refer the screenshot below:

Now you can drag this object along with the other query objects and it wouldn’t give “Incompatible” error.

While working on this, I came up with a situation where I was getting #Multivalue error.  I added another row into the data in query 1 to get this error in my example. The data in query 1 will be like:

Coming back to #Multivalue error, it comes out like:

After wondering how to solve this #MULTIVALUE issue, I came up with a workaround. Right click on the table, click on table properties and check the “Allow duplicate row aggregation.”

On doing this I was able to get the correct data and it looked like this now:

I hope this article help in understanding merge dimension concepts and helping you merge unmerged dimensions also from 2 queries. Sorry if the example was poor or if you think I didn't use much screenshots.