Sep 03 2009
Using ForceMerge() To Fix Unbalanced Data Providers
I have discussed the MultiCube() function a number of times in my series of Variables presentations. In certain situations it allows you to fix measure objects when you have two (or more) unbalanced data providers in a full-client document. ForceMerge() is the equivalent for Web Intelligence.
So how do they work, exactly? What do I mean by unbalanced data providers? How is it fixed with these functions? Are there any special conditions to be aware of?
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, the ForceMerge() function will return a #MULTIVALUE error if used in this situation.
Related Links
- Blog Post: What Are Unbalanced Data Providers… And Why Should I Care?
- Blog Post: “Smart” aka Database Delegated Measures
What was that “Mississippi” thing earlier?
From Wikipedia:
Children in the United States and Canada often count ‘One-Mississippi, two-Mississippi’ during informal games such as hide and seek to approximate counting by seconds.
So now you know. 🙂
Great post, now I wonder why the ForceMerge functionality is not done automatically by the report engine. NOW that would be COOL, would it not? 😉
Andreas, I am sure it’s not done automatically because it doesn’t work all the time. How would the function determine the difference between the data configuration in this post and the one in my prior post where I talked about unbalanced data providers? Unless it can tell the difference somehow, it’s not appropriate to apply the function automatically.
Dave,
Thank you for this blog and the explanation. The truth is that I did not understand you comment in the previous blog. But this one explained it all.
Very nice post. I used to create variable and then link but the ForceMerge()will reduce lot of work. Thanks for publishing this.
How to conditionally show different measures depending on drill conditions
2 data providers showing data at different grains with a hierarchy spanning both. Drill down from one data provider, then swap to the other, switching measures when appropriate. The variable below will set a flag depending on which measure to show. [Actuals] is from data provider 1 (Balances) and [GL Ref] is from data provider 2 (Transactions).
lv Show Balances or Transactions Flag
————————————-
=If(IsError(Sum([Actuals]ForceMerge([Actuals])) In ([GL Ref]));"T";"B")
Hello Dave,
First, thank’s for this blog! It’s really great… !!!
I have a Fan Trab problem and I don’t know hot to resolve it. Perhaps do you have a little time to help me… 🙂
I have 3 tables that are linked as follows: Client -< Account -< Account_Transactions
Client
======
Client_ID
Client_Name
Account
=======
Account_ID
Client_ID
Account_Type
Account_Balance (Measure Object)
Account_Transactions
====================
Transaction_ID
Account_ID
Date_transaction
Type_transaction
Amount_Transaction (Measure Object)
In my Universe, I created two contexts and one Alias for this:
Alias of table ACCOUNT : Account_Alias
Context Account_Balance :
=========================
Joins : Client.Client_ID = Account.Client_ID
Account.Account_ID = Account_Alias.Account_ID
Context Account_Transactions :
==============================
Joins : Client.Client_ID = Account.Client_ID
Account.Account_ID = Account_Transactions.Account_ID
Now, I had to do a Report in WebIntelligence (BO XI R2). In this reporte, I will show the following fields :
– client_name,
– account_ID
– Account_Balance
But only accounts that have at least one transaction of type postal payment.
(Filter on a dimension of the lowest table…)
My Univers generate two queries (because my measure object Account_Balance has his own context to avoid carthesian products…) :
query 1 (context Account_Transactions) :
SELECT CLIENT.CLIENT_NAME, ACCOUNT_ID, TYPE_TRANSACTION
FROM CLIENT, ACCOUNT, ACCOUNT_TRANSACTIONS
WHERE CLIENT.CLIENT_ID = ACCOUNT.CLIENT_ID
AND ACCOUNT.ACCOUNT_ID = ACCOUNT_TRANSACTIONS.ACCOUNT_ID
AND ACCOUNT_TRANSACTIONS.TYPE_TRANSACTION = 'postal payment'
GROUP BY CLIENT.CLIENT_NAME, ACCOUNT_ID
(The query filter (type_transaction) use a join who belongs only to the context Account_Transactions))
–> query 1 return 5 rows
query 2 (context Account_Balance) :
SELECT CLIENT.CLIENT_NAME, ACCOUNT_ID, SUM(ACCOUNT_ALIAS.ACCOUNT_BALANCE) account_balance
FROM CLIENT, ACCOUNT, ACCOUNT_ALIAS
WHERE CLIENT.CLIENT_ID = ACCOUNT.CLIENT_ID
AND ACCOUNT_ALIAS.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
GROUP BY CLIENT.CLIENT_NAME, ACCOUNT_ID
(The Account_Balance) field use a join who belongs only to the context Account_Balance)
These two queries are now synchronized by WebIntelligence.
Now, when I take the my objects in my block Report, The 100 rows returned by the second query are displayed.
It seems to be ok, but I don’t want that ! I (the end User) only want the accounts that have at least one transaction of type ‘postal payment'(the 5 lines returned by query 1).
I would like that WEBI synchronizes data and giving me back only the five rows responding to the ‘lowest’ filter… That means only the five lines of the common dimensions account_id and client_name.
For Example,
Accounts 1,2,3,4,5 have one or more postal payments –> they are returned by query 1
Accounts 1,2,3,4,5,6…98, 99, 100 are all returned by query 2
…Synchronization…
In my resultset (Microcube), I would only have the accounts 1,2,3,4,5 (account_id 1,2,3,4,5 are returned by the two queries)
Is there a way to get only these 5 rows ? Is the way I created my contexts and alias not correct? Do you have some advice for me?
And finally, thank you if you read this entire message! And sorry for my English …
Cheers,
JP
Dave,
Hats off, that is so informative. Thanks
Ravi
Dave,
really very usefull and so nice
thanks
Bala
When you used merged dimension, what is default behavior of synchronization is used?
a. Query synchronized when using same universe.
b. Query synchronized when different universe
c. Object synchronized when using same universe
d. Object synchronized when using different universe.
I am sorry to post this question like this, Can anybody please tell me which options are correct?
Hi, I saw that you posted the same question on BOB. I think I will let the community help you out on this one. 🙂
Thanks for the clear explanation, I had not properly understood that function previously. To acheive a similar result (in 6.5.1/Deski) I would have hidden the Resort column, added a Break with a Sum to Country, then done a Fold on the Break. This way is much better.
(Despite which, I still wish that Webi would allow hiding of columns, eg to allow sorting of a report on a dimension that is not displayed)
Hi,
I am chandu, This explanation is very useful to me.
Thanks & regards
Chandu
In the above example, what happens if the same resort name is in more than one country.
How will forcemerge display the data?
It won’t work at all in that case. That’s covered earlier in the post where I talk about quarter and year.
Thank you Dave for the response.
So in that case we can only display data with country, resort and guests being displayed but not just country and guests!! 🙁
I have a scenario where query1 has country and resort, query2 has country and revenue.
We would like to display the resort and revenue(merged on country)in our report. Using forcemerge is not giving us the correct results. It is splitting the revenue at random between the resorts and displaying incorrect data. (E.g. country U.S. has resorts Hawaiian and Bahamas, U.S. has revenue 1000000. when displaying resort and revenue in the report, we want it to show 1000000 for both the resorts).
You advice is greatly appreciated…..
You can’t use ForceMerge() to allocate revenue at a lower level, the information simply doesn’t exist to make it work. Resort is at a lower level than country, your two data providers are linked by country. The resort object is never going to be compatible with Revenue.
In your earlier post of unbalanced data providers you mentioned
” If you don’t have the same number of dimensions from each data provider, then they are unbalanced. ”
In the above example country, resort, revenue is from 1 data provider and Resort, No of guest is from 2nd data provider. there is 2 dimensions in 1st data provider and 1 dimension in 2nd data provider yet when you add all object sin the block they work fine? aren’t they unbalanced right now?
As mentioned before, you can have unbalanced in two directions. Unbalanced lower dimensions are not compatible. Unbalanced upwards can be fixed with the ForceMerge() function as detailed in this post.
Hi Dave,
ForceMegre function helps a lot.
But our case is quite complex.
We have a universe contain category information, such as apple, orange belongs to “fruit”, pork, beef belongs to “meat” and all these products are belongs to “food”.
The other universe contain product information, such as how many apple or orange have been sold.
In the report we want to show the sub total of product sold base on category, “fruit”, “meat”, “food”. However, we found that, as “food” contained all the records within “fruit” & “meat”, only the sub total of food can be show and the sub total of “fruit”&”meat” will leave as blank even with ForceMegre function.
It would be grateful if you can help.
Thanks
Lawton
Hello Dave,
Really impressed with your in depth knowledge!!!!
I just wanted to know , can we get by with lower degree dimensions by making them details to a dimension which can be merged??
It was wonderful reading your posts.
Thanks
Rahul
Dave, Wondering at the depth of ur BO knowledge. And this forcemerge you have explained in a clear way. Thanks. And I found this page, demonstrated in http://www.youtube.com/watch?v=EfxCO01XNhM
Hi Dave,
If forcemerge brings the dimension in context while calculating the measure,we can use forall function also rt?
then what is the difference between forcemerge and forAll /For Each ?
Regards,
Suhasini
ForAll and ForEach are context operators and have to do with input / output context only. ForceMerge() is used to ensure that data providers are linked using all available dimensions. You would have to ForceMerge() before you can ForAll or ForEach. 🙂
This blog is awesome.Thanks Dave!
This is the way to expalin things.Excellent!
Dave, you should have some option in your blog so that we can subscribe there and each time you post something, we can immediately have a look into it… 🙂
Hi Dave,
A video version of your post is available here 🙂
http://www.youtube.com/watch?v=EfxCO01XNhM
Sue them and earn a million dollars 😉
I had used Foreach instead of ForceMerge.. Does the same work, except that instead of aggregating measures it gives a #MULTIVALUE error.
I replaced
=ForceMerge([Number of guests])
with
=[Number of guests]ForEach([Resort])
can we link two dataproviders from two universes in business objects
You should be able to do that with the RSS feed. There is one for posts, and another for comments. 🙂
Yes, the data source does not matter as long as the data types and values match up.
Ah, well, I will watch it but unless they use my actual slides there probably isn’t anything to do.
Dave,, I have a scenario where I have two data providers based on two different universes, but both don’t have any measures. They’re joined by a common dimension, but we have many to many relation. So, two records have to match with another two records and display as 4 records. I tried setting the table property to use duplicate rows, but still it gives only 2 records by matching record 1 with record 1 and record 2 with record 2.
Is there anything Forcemerge can do with this?
Unfortunately I don’t believe that ForceMerge() would be able to fix a many-to-many relationship. It’s designed to address blocks that don’t include all of the linked key objects, it can’t fix relationship issues.
Hi,
The explnation of forcemerge and many other topics was useful and helpful to me.
Two questions:
1)We can use Forcemerge fucntion when we wat to include a dimension which is not part of context/block. Am I right? Please let me know if I misinterpreted…
2) How to hide a column in Web XIR3.1 dynamically?
Thanks,
rams
ForceMerge() is specifically provided to ask Webi to consider the values for a dimension that is not included in the block when projecting measures. If that’s what you mean by “include a dimension which is not part of context/block” then you are correct.
For your second question, please see my post called Drilling to Details for one idea that might help.
Dear Dave,
I see that if I create a grand total row and apply some filters to the table, the filters won’t reflect in Grand Total row even if its having forcemerge. Grand Total will always be the sum without the filters applied.
Like in your above example, if a filter is applied based on country and you create a grand total row, then grand total of ForceMerge([Number of guests]) won’t reflect the applied filter.
Is there any way to fix this?
Great work !!
Dave,
Six and a half years after your original post and you’re still solving problems! Been beating my head against a wall for two hours trying to get this issue fixed and presto, works like a champ.
Thanks