I’ve noticed lately that there seems to be a lot of confusion about merging dimensions in Web Intelligence. I’ve spent a fair amount of time on BOB answering questions about this topic. So, perhaps it’s time to put together a comprehensive document covering this topic. After all, merging dimensions is one of the most powerful features of Web Intelligence.
Tip 1: Adding “Incompatible” dimensions to the block
Have you ever noticed that, sometimes when you try to add a dimension to a block that includes merged dimensions, you sometimes get an “Incompatible object” error? Let me explain why this happens, and then we’ll look at ideas on how to fix it.Dimension objects and Detail objects have a fundamental difference: Dimension objects usually represent a different level of granularity. For example, State and City might be two dimension objects in your universe. City is a lower level of granularity than State. So, when you add City to an existing block that already includes State, the measure objects will be aggregated at a lower level of granularity.
Detail objects, on the other hand, typically do not represent a lower level of granularity when used with their related dimension. For example, if I have Sales Revenue broken down by Customer in a block, and then add the Eye Color detail object, Sales Revenue will not be aggregated at a lower level. It will stay at the level of Customer. This is how detail objects work.
So, if I have two queries merged on State, and try to display another dimension, that is not merged, such as State Capitol, Web Intelligence doesn’t know how to aggregate the measures at the lower level of State Capitol, since that dimension doesn’t exist as a merged dimension. Of course, you and I both know that each State only has one State Capitol, so it’s not really a lower level of granularity. But Web Intelligence doesn’t know that. So we have to tell it.
The way we tell is as follows: Create a detail variable. In this case, maybe we call it Capitol. Make it a detail of the State merged dimension. The formula for this variable is:
=[State Capitol]
We can then add the variable to the block, as Web Intelligence sees it as a detail of State, rather than a different level of granularity. Note that the detail variable must be a detail of a merged dimension. Otherwise, you still won’t be able to add it to the block.
Tip 2: Auto-Merge dimensions only works within a universe
If you have two queries, from the same universe, that include the exact same dimension objects, those dimension objects will automatically merge. This is the only time when dimension objects automatically merge.
Here’s an example of a merge that will not happen automatically. Let’s say you have an object called Address, in a class called Vendor, and you have another object called Address, in a class called Customer. These two objects have the same name, and are from the same universe. Will they automatically merge? No. Web Intelligence is smart enough to know that they are not the same object. Of course, in this case, you probably won’t want them to merge. But if you do, you will need to manually merge them.
Tip 3: Values displayed depend on which object is used
If you display the Product Number object from Query 1, you will see all the Product Numbers from Query 1. If you display the Product Number object from Query 2, you will see all the Product Numbers from Query 2. However, if you merge the two Product Number objects, and display the merged dimension, you will get all product numbers from both queries. For those of you familiar with SQL, this is the equivalent of a Full Outer Join.
Tip 4: “Extend merged dimension values” has a similar effect of using the merged dimension
Tip 5: There are rules to merging dimensions
- Only dimensions defined in the universe can be merged. You cannot merge using variables.
- Objects must have the same data type. You cannot merge a number with a string, even if the values match.
- Any number of queries can be merged. There is no limit.
- Any number of dimension objects can be merged between two queries. Again, no limit.
- Values are case-sensitive. So, if the values are the same, but of different case, they will not match. They will be shown as different values.
- Watch out for trailing blanks. Even if the values look exactly the same, they won’t match if one has a trailing blank, and the other one doesn’t.
Conclusion
Merging dimensions is the only way to combine data from different data sources in the report. Therefore, it’s a very powerful feature, especially if you understand how it works, and how to make it work. If you’re trying to get your merged dimensions to work, and they just won’t cooperate, read through the tips above, and you’re likely to find the solution. If you have other tips, feel free to comment below.Thanks for reading. :-)
