UDM Many-to-many Relations and "AND" queries
Recently I got an interesting question about querying UDM many-to-many relationships. For example, in my book I demonstrated how you can implement an UDM model (Bank cube) that has a many-to-many relationship between bank customers and their accounts. That’s because a customer could have more than one account and an account can belong to more than one customer (a joint account).
But what if you want to find only the joint accounts that are owned by any two customers? The following query returns the accounts owned by Bob and Alice:
SELECT
NON EMPTY {[Measures].[Balance]} ON COLUMNS,
Intersect
(
Exists([Account].[Account Number].[Account Number],
[Customer].[Full Name].&[Bob], “Customer Account”),
Exists([Account].[Account Number].[Account Number], [Customer].[Full Name].&[
) ON ROWS
FROM Bank
The trick is to evaluate the sets over the hybrid “Customer Account” measure group which is actually a dimension table but plays a role of a measure group. The Exists function returns the accounts that the given customer owns. The Intersect function returns the subset of the customer sets.
