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 account).

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.

Example: 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.

Hi Marc,

Thanks for your reply. You bring up some good points, which are addressed in more detail in the first link in my post "Many-to-Many Dimensions in Analysis Services 2005". As it says, "The sum for all amounts is not the sum of the individual amounts for each customer—that would double-count the data that John and Jane share. Instead, the total amount is the sum of all transactions". So that answers your point about doubling the totals. It also goes into detail on how to model it in SSAS, so I'm confident the model above is correct. Please look over that link and let me know if you disagree. Again, thanks for your input.

I have come up with one of the scenario where you have different attribute on a table and have many to many relationship. Lets take above example and add one more columns sex in customer dimension and then add related attribute in cusotmer dimension of SSAS. After processing the cube browse the cube based on sex and check the fact count or amount, it will give you improper result. It can be very well handle in SQL Database using inner join but how can we handle this scenario in SSAS?