The problem is a "popular" one. You have 2 facts with different levels of aggregation. It's often Orders fact and Order Lines fact (where there are Order Total in the Orders fact and Lines values in Lines Fact that might not the same due to shipping or general discount). In my case this is Fines fact and Payments fact. In this case they sums often don't match since they might be only partial payment of the fine.
We can't solve it as the regular OBIEE "2 facts with common dimensions" solution since we have specific non-aggregate columns for each fact (Fine code, fine date, payment number, payment date...)
I blogged about the solution of the problem on the Analysis level here. The customer insisted on natural solution in the repository model. This post is about the modeling solution.
Initially I was sure I'll find a solution in the extended Sample Sales available here. There is a subject area "X5 - OLTP (Fusion Order)". As always, I was sure would be perfect. This time they didn't cover it yet, and I had the default, wrong results:
In the example, the first order total was multiplied by the number of lines, so we have 102 instead of 51, as desired.
My first attempt was to divide by the number of lines in the model. I didn't mind seeing the relevant portion of the total fine in each line, as long as the total was correct. It was almost perfect, but the default aggregation of the column wasn't sum, so it forced the user to change it manually.
Lets start from the begging.
I have 4 tables: Fines, Payments, Dates and People.
I'll duplicate (alias) the Fines and Payment so we will have fact and dimension for each.
I'll also duplicate Dates, so we will have Fine Date and Payment Date.
In the Business Model I created a unified Fact of Fines an Payments:
For each of the duplicates of the facts: Fine Details and Fine Payment details I created a simple level based dimension of totals and details only:
Now comes the tricky part. For FINE_SUM I defined the Aggregation to be based on dimensions, with SUM for the Fines Dimension and AVG for Others.
The result is working fine.
Please note, the order here is critical. That is the difference between Sum(AVG(xxxx)) and AVG(SUM(xxxx)).