Home / Highlights / Tech Tip: Merging on Variables in Web Intelligence

Tech Tip: Merging on Variables in Web 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.

Want more tips on Web Intelligence? Join the Let’s Speak BO Webinar series and gain access to a library with over 30 webinars on Webi.

About Roxanne

Roxanne Pittman is a Senior Business Intelligence Technical Consultant for InfoSol, providing consulting and training for BusinessObjects. She is a certified Business Objects Instructor for Business Objects, specializing in Web Intelligence, Desktop Intelligence, Dashboard Manager, Universe Design, Xcelsius Dashboards, Xcelsius Data Connectivity, and Knowledge Accelerator Customization. Roxanne has specialized in Xcelsius connected models, with extensive experience in using XML, Web Services, Live Office and InfoBurst-XDS and XDM to create dynamic dashboards. In addition to being a multi-certified instructor, Roxanne has experience working with Fortune 500 companies as a consultant in the deployment of business intelligence solutions.

Check Also

Tech Tip: DATETIME field format – Dealing with Milliseconds

Tip: Error found at 00 Trick: Truncating datetime to seconds We recently worked with a ...

Leave a Reply

Your email address will not be published. Required fields are marked *