ForceMerge when your data provider objects not match

Merged Data Provider Review

I recently published a blog post that described unbalanced data providers. If you want the full description, I have included a link at the end of this post. If you want the executive summary, here it is:

When you have two (or more) data providers in the same document and want to merge the data, you have to link them using dimension objects. If you don’t have the same number of dimensions from each data provider, then they are unbalanced. That can cause issues.

It took me twelve hundred words (and six pictures) to provide a detailed explanation in my earlier post.  In the sample report I used for that post I had one common dimension (Year) and one unique dimension from each side (Resort and Region). I showed how I could use Year with either measure since it was a shared (and linked) dimension.

I also showed how (and explained why) trying to use the “extra” dimensions like Region or Resort caused problems. I don’t intend to repeat everything here; please use the link at the end of this post to read the prior post if you are unclear on anything so far.

What About MultiCube()?

One of the comments I got on that post suggested that MultiCube() could be used to fix the issue. My response was accurate as far as I was concerned, but later when I read it again I realized it could also be quite confusing. Here’s what I said:

…the MultiCube() function will help if data providers are unbalanced “upwards” but not “downwards” …

After reviewing my response, I realized that it was quite likely that I was the only person that understood what I meant by that statement. While at times I do enjoy talking with myself  that’s not why I have this blog. So I am going to try to explain that a bit further.

The problem with trying to use MultiCube() or the Web Intelligence equivalent ForceMerge() to solve this specific case is that the data doesn’t support the solution. There is no hierarchy or relationship between Region and Resort. (Yes, they are both related via links to the fact table but that’s not what I mean.) Resort is a place. Region is an attribute of the customer. There is a many to many relationship between regions and resorts, and that sort of relationship is essentially worthless. If I can’t determine how to put things together, how are the numbers going to make sense? Neither of these functions can create a relationship from nothing.

Yet there are some cases where I can fix unbalanced data providers. I need to talk about the data first, then I need to create a new document with the proper structure in order to show how the ForceMerge() function works.

Hierarchical Data

Here is what I consider to be a really good example of why this is a problem. Most people think of time as a really clean hierarchy. On the surface the hierarchy is simple: Months go into quarters, and quarters go into years. Yet if I create a query with the Quarter and Revenue from the Island Resorts database here is what I get.

Take a look at the data and tell me how you would break out the quarter data into different years.

Go ahead and think a bit, I can wait.

One-Mississippi.

Two-Mississippi.

You can’t do it, can you?  You don’t have enough information. As defined, with the format “Q1″ and “Q2″ and so on, there is no indication as to which year the quarter revenues are coming from. You can’t “break down” the quarter by year with the information that you have. That is what I meant by data being unbalanced “downwards” in my comment on my prior post. I can’t break data down because the information simply isn’t there.

What I can do, however, is roll up or move “upwards” via a hierarchy. That’s where the ForceMerge() function comes into play.

Introducing the ForceMerge() Function

Here is what the help text says for the ForceMerge() function.

Forces Web Intelligence to account for synchronized dimensions in measure calculations when the synchronized dimensions do not appear in the calculation context of the measure

I will explain what this means, but first I am going to build my example. My document will have two data providers from the Island Resorts Marketing universe. My first data provider has dimension values Country and Resort and the Revenue measure. My second data provider has the dimension Resort and the measure Guests. I will link the two data providers by Resort as it is the only common dimension.

Data Provider 1

Data Provider 2

Data Provider Results

Merged Dimensions

From this point I will create a merged block using all of the measures and both dimensions. Here are the results from that experiment.

Everything is fine so far. The issue becomes evident when I remove the Resort object from the block. Remember that my two data providers are merged (synchronized) by Resort. When that object is no longer present in the block, the measures roll up. Revenue is still okay because the Country object was part of the query context of that data provider. The Guests measure fails because it is now being viewed out of context.(At the risk of becoming tedious, I will point out one last time that this issue is covered in much more detail in the first post in this series.)

I have created a problem. Next, I will explain how to fix it.

ForceMerge() In Action

All of the information I need is there in the document. It just does not show up in the block. Here once again is the help text for the ForceMerge()function:

Forces Web Intelligence to account for synchronized dimensions in measure calculations when the synchronized dimensions do not appear in the calculation context of the measure

Simply put, the function tells Web Intelligence to use all of the information from the cube, rather than being limited to what is shown in the block. I will create a new variable with this formula:

=ForceMerge([Number of guests])

I will replace my “broken” measure with my new variable and observe the results.

I haven’t changed my dimension linking. I haven’t changed my data providers. I didn’t even change the block structure, other than to replace the broken measure with my corrected calculation. If you were looking for something more complex, I’m afraid that’s all there is.  The complicated part is understanding just what the function does and when to use it, rather than how to use it. I am glad this function is now available in Web Intelligence starting with XI 3.x. In my opinion, it should have been there since merging data providers was an option.

Summary

The magic of the ForceMerge() function (or if using Desktop Intelligence the MultiCube() function) is that it changes the behavior of the report engine. Normally when dimension values are removed from a block they are taken out of context. By using one of these functions I can specify that linked dimensions still have to be considered by the measures even if those dimensions no longer appear in the block structure.

Even with this magic function in my arsenal, I cannot rely on it to fix every problem that I have. If the data can be “rolled up” by the calculation process then I am fine. If the data has to be “broken down” then I am in trouble, and these functions will not fix the issue. Each resort exists in exactly one country so I can move up the hierarchy from the resort link and the results are valid.

Special Note
If there are smart measures in a universe, then ForceMerge() will not work. In order to calculate the results correctly, smart measures (using the “database delegated” projection function) need to have the block structure in place to define the query. According to the documentation, theForceMerge() function will return a #MULTIVALUE error if used in this situation.