InfoSol We're passionate about inspired business intelligence
One of the often overlooked additions given to us in the 4.2 SP3 release was the ability to merge data on variables, not just dimensions. For many years we have lived with these restrictions in Web Intelligence:
1.) You could only merge on dimensions, not on variables.
2.) The data format between the two data sets had to match – for example, February 7 2019 would not merge with a 2/7/2019 and Joe Smith would not merge with JOE SMITH.
Now we have the ability to modify the format of an existing dimension so that we can then merge the resulting data with data from another data provider. This article will explain that process.
In the examples below, we want to merge data from two different universes, one which contains Order revenue data and the other contains Tax Amount paid, for 2 years, by Year and Quarter:
We have two dimensions which we can use for merging in these data sets – Year and Quarter.
The Year objects are held as Number type in both universes, which we can see by hovering over the objects in the Available objects list:
Since they are both Numbers, there will not be any issues with merging these dimensions. After selecting Order Year and Calendar Year with your control key, right click and select “Merge” to merge these dimensions.
If we now create a new table with our Merged year object, along with the Revenue and Tax Amt objects, our table will look like this:
However, we want to view the table displayed by Quarter as well, and when we add Order Quarter to the table, this is how the data will display:
Notice how the values for Tax Amt are repeating for each quarter and are showing the year total amount on each line. This is because there is no way for this data to display properly, because there is not a correlating merged object for the Tax Amt to know how to display. In SQL terms, it means there is a join missing. To help the two data sets know how to display together, we have to merge the Quarter dimensions. However, the data type for Order Quarter is Text … which will not merge with the data in Calendar Quarter, which is a Number. We can overcome this limitation by creating a variable so that the data in both sets will match.
In this case, we can create the following formula to get the Calendar Quarter data to match Order Quarter:
=”Quarter “+[Calendar Quarter]
Name the formula as “Tax Qtr” to complete the variable. By appending text in front of the number, the new variable will have the data type of Text, which is just what we want.
Now we are ready to merge Order Quarter and Tax Qtr. Select both objects using the control key, right click and select Merge.
Replace the Order Quarter in the new table with our new merged Quarter dimension and we will get the proper display of our data:
Note: By default, Web Intelligence will name the Merged dimension by the same name as the first selected dimension. However, the name of the merged object can be changed easily by right clicking on the merged dimension and giving it a new name. I recommend using a different name to help clarify that the table is using the merged object rather than the universe/query object.
Having the ability to create a variable and reformat how data is displayed is a great help when needing to merge similar data with different data types. May this tidbit help with your future data analysis by bringing data sources together using the Merge function with variables.
I have now WEBI 4.3 and I cannot merge dimensions and variable. Do you know how can this be done in this version?
Hello Mihai, Yes, you can definitely still merge dimensions & variables in Webi 4.3. The important thing to check is that they both are the same data type (string with string, number with number, date with date).
If you are using Excel as your data source for one of the dimension you want to merge, you may need to change the data type before merging. And the location for making that change is different in 4.3. Previously, this was handled in the query panel, but now it is handled in the report. First, select the object from the Document objects, then, on the Properties Panel, select the icon that looks like a clip-board. That is where you can change the data type on your non-universe data source objects. Once you make the change, refresh your report, then you should be able to merge dimensions, even if they are variables.
I know how to merge dimensions from BW queries and Excel, also to change the data type for Excel.
For test purposes, I have created a variable being equal with one dimension from the query.
I can merge the original dimension with The appropriate Excel column, but I cannot do this with the variable.
I am very sad about
Hi Roxanne,
I came across this post on merged dimensions and I was hoping I can post my question as well. I have 3 queries that I am merging objects on the report. The values on the merged dimensions are identical, but one of the queries is causing a 2nd row for the same values. The data type is a string for the dimensions in all the queries.
Any tips? Thank You.
Sangita
Hi Sangita,
It is a bit difficult to troubleshoot this without seeing your data, but here are some ideas of things you can check:
If you are getting multiple rows when adding the 3rd query, this seems to be showing that the data is at a different granularity level. You might try to display all the objects in a table and see which other granular dimension is making it repeat, perhaps adding the dimensions one at a time.
The only other things that comes to mind is that sometimes there are universe objects like “Quantity” which are set as a measure without any aggregation. This could also cause the extra rows (but could likely also be over come with aggregate functions).
If you are on version 4.3 sp3 or later, then you might also try joining the data in Data Mode rather than merging, and trying out some of the other join options to see if this gives you the results you are looking for.
Best regards,
Roxanne