InfoSol We're passionate about inspired business intelligence
Related Articles
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