Welcome back ForceMerge() AKA MultiCube()!
I have always been a big fan of the MultiCube() function that is available in Desktop Intelligence. I can still remember the first time I learned about the function. A colleague of mine at that time was struggling with a report with multiple data providers he had built. He was really annoyed, because he couldn’t get the measures to aggregate properly. Which is exactly the purpose of this function, so when he found out about the (proper) use of the function he was really excited he could make it work after all.
Unfortunately the same functionality wasn’t available in Web Intelligence until the introduction of XI 3.0. Why is beyond me, because having the ability to merge data providers on the document level and not being able to use the MultiCube function was a bit of nuisance. It is now, however, even though they gave the function a different name: ForceMerge.
To me the help text and especially the example that come with the MultiCube() function is much more helpful than the one that comes with the ForceMerge() function. The ForceMerge functions description states:
Includes synchronized dimensions in measure calculations when the dimensions are not in the measure’s calculation context
While the Desktop Intelligence description isn’t much better, the example is. So what I am going to try and do is recreate the example given in Desktop Intelligence in a Web Intelligence document. We’ll make use of the Island Resort Marketing Universe.
You have two queries. Query 1 returns city and country of origin of the customer.
Query 2 returns revenue per city.
Data Provider Results
Auto-merge dimensions is ticked by default, so the two city objects are automatically merged.
The results are as follows.
And combining the results is no problem either (there are obviously no customers living in certain cities).
The issue becomes evident when I remove the City object from the block. Keep in mind that the two data providers are merged by City. When that object is no longer present in the block, the measures should roll up (if set properly in the Universe and not disabled by the relevant property in the document).
That doesn’t seem right, right?
ForceMerge() to the rescue
Looking again at the description of the help text for ForceMerge we can see that this clearly is a situation where the dimension City definitely is not part of the measure’s calcuation context (because we deleted it from the block). So what do we need to do? Right, add the function to the measure.
=ForceMerge([Revenue])
And look what automagically happens to the results.
It seems about right, since the clients from the countries that don’t display any revenue are there in the database but never spent a dime in any of the resorts. Wondering why the block behaves as if an outer join is used? So am I, but perhaps that’s something for some other time.
In short
ForceMerge() AKA MultiCube() function) changes the behaviour of the, in this case Web Intelligence, report engine. Even though the merged dimension is no longer in the block, they are still taken into account by the measures that have the function wrapped around them.
Unfortunately it won’t fix every problem you’ll encounter, but it’s definitely worth taking a look at when you are forced to use several Universes and to combine the data to display in your document.
Attention
The help file also says:
- ForceMerge returns #MULTIVALUE if applied to a smart measure because the grouping set necessary to calculate the smart measure does not exist.