This looks to be inappropriately modeled.
It looks like you have applied Third Normal Form rules to an OLAP database. Analysis Services is not built to support 3NF databases (although it can be convinced to do so).
When creating OLAP databases, all joins should terminate on the fact table(s). The only exception to this rule are snowflakes, where dimension hierarchies are modelled using multiple dimension tables.
In your example:
So a single transaction in our joint
account means that transaction is
associated with many customers (my
wife and I), and each customer (me)
can be associated with many
transactions (all my transactions for
my individual account and my joint
This will not aggregate properly at the Customer level (although it may aggregate properly at the Account level). Every deposit will be counted twice (one for you, one for your wife) and ever debit will be counted twice when going across the dimension Customers. When your balance is something other than 0, it will be doubled.
Action: You deposit $100 into your joint account.
Result: You have a balance of $100, your wife has a balance of $100, for a total of $200.
Action: Your wife writes a check for $50, which is presented and honored.
Result: You have a balance of $50, your wife has a balance of $50, for a total of $100.
This can be made to work using the materials you referenced above, but the initial sub-optimal design will cause huge amounts of work, headaches and performance issues down the road.
commented on Nov 25 2011 9:33AM