Many-to-many relationship works sometimes; produces unexpected (null) values other times

[LawnMowerHater]

Warning: I suspect it'll take a hotshot SSAS guru to sort this out.

As noted in the title of this question, my basic problem is that I'm
getting unexpected (null) values. I get them when constraining a
query by an attribute of a dimension that has a many-to-many
relationship with a particular measure group that I'm aggregating
measures for. These (null) values can be made to go away by executing
the query differently.

Below I give full examples of everything, and outline all the
relationships and table/dimension/measure group structures.

The basic lay of the land is that I have four tables in the DSV (two
dimensions and two measure groups in SSAS):

Security - Lists what people (UserNames) can access what
Accounts; identical to Security Fact
Security Fact - Identical to Security, but implemented as a
measure group
Account - Lists general-ledger account numbers; grain is
one row per account
Transaction Fact - Lists transactions; grain is one row per account
per fiscal year per transaction number (each transaction has an
account number)

Transaction numbers (aka counter numbers) are unique only for a given
fiscal year and account. So the granularity attributes for the
Transaction Fact table are Account + Fiscal Year + Counter number).

Here are some sample rows in the tables.

First we have the Security dimension, which is paired with an
identical fact table/measure group:

UserName AccountNumber
jdoe 10000011305801
jdoe 10000011305802
rroe 10000011305801
rroe 10000011305802
rroe 10000011305803

Each row in the underlying DSV source defines a permission. E.g., row
1 above says that jdoe may access account number 10000011305801.

The Security Fact measure group is actually identical to the Security
Dimension (so in reality it's based on the same data in the DSV). It
contains one measure, and that's a simple count (SCount). I normally
wouldn't care about SCount, because the only reason for the measure
group is to provide a way to implement the many-to-many with the
Transaction Fact measure (more on which below). But issuing MDX
queries that tell me the value of SCount help me know if everything is
OK. More on this below.

Next we have the Account table (granularity attribute is the
AccountNumber):

AccountNumber Fund Source Dept Object
10000011305801 10 0000 1130 5801
10000011305802 10 0000 1130 5802
10000011305803 10 0000 1130 5803
10000011305804 10 0000 1130 5804
Etc.

Finally, we have the Transaction Fact measure group mentioned above.
The data that underlies is in the DSV looks like this:

AccountNumber FiscalYear Counter Amount
10000011305801 2010 1 60.00
10000011305801 2010 2 63.40
10000011305801 2010 3 5.75
10000011305801 2010 4 55.98
10000011305802 2010 1 1233.47
10000011305802 2010 2 1024.97
Etc.

The granularity attributes here are AccountNumber, FiscalYear, and
Counter. All three together constitute the primary key of the
underlying relational table.

Now here's the thing. I thought this arrangement would provide a
great basis for dynamic dimension security, because we have (in the
Security dimension) a set of permissions. Those permissions are
associated with a measure group, Security Fact, which acts as an
intermediary for a many-to-many relationship (via the Account
dimension) with the Transaction Fact measure group. Basically a given
user can access one or more accounts, and a single account may be
accessed by zero or more users. It's a classic many-to-many.

Here comes the fun part.

I have set up all these relationships in SSAS. And when I run MDX
queries I seem to get correct answers when I'm asking questions that
are answered via the Security Fact measure group.

For example, I can run:

select
[Measures].[SCount] on COLUMNS,
CrossJoin({[Security].[UserName].[ADS\rgoerwit]}, {[Security].
[AccountNumber].members}) on ROWS
from [GLTransactionDetail]

This query give me a count (SCount) of how many permissions (user +
account number) apply to each account. In this case I'm restricting
the permission counts to those applying to me (rgoerwit). Here is
what I get back:

ads\rgoerwit All 3
ads\rgoerwit 10000011305800 1
ads\rgoerwit 10000011305801 1
ads\rgoerwit 10000011305802 1

This all looks exactly right, and it *is* right. I have one row in
the Security dimension for each account I can access, and there are
three of them.

I can also tie into the Account dimension as well by changing
[Security].[AccountNumber].members above to [Account].
[AccountNumber].members. Here's what I get when I do that (excluding
nulls):

select
[Measures].[SCount] on COLUMNS,
CrossJoin({[Security].[UserName].[ADS\rgoerwit]}, {[Account].
[AccountNumber].members}) on ROWS
from
[GLTransactionDetail]

ads\rgoerwit All 3
ads\rgoerwit 10000011305800 1
ads\rgoerwit 10000011305801 1
ads\rgoerwit 10000011305802 1

Same result - as expected. So far so good.

But we're still only using the Security Fact measure group (where
SCount lives).

When, however, I ask for [Measures].[Count] (in the Transaction Fact
measure group) instead of [Measures].[SCount] (in the Security Fact
measure group), here is what I get:

select
[Measures].[Count] on COLUMNS,
CrossJoin({[Security].[UserName].[ADS\rgoerwit]}, {[Account].
[AccountNumber].members}) on ROWS
from
[GLTransactionDetail]

ads\rgoerwit All 4
ads\rgoerwit 10000011305801 (null)
ads\rgoerwit 10000011305802 4
ads\rgoerwit 10000011305807 (null)

Note the nulls. What are they doing there? I expect a count in all
cases. The 4 above is the count of the number of transactions posted
against the 10000011305802 account. I know that transactions have
been posted to the other accounts. I can see it if I pose a
relational query against the underlying tables in Access.

Now my MDX isn't particularly sophisticated, so granted, I may just
have done something dumb. To test this, I went and set this up in
Excel. What I did was to create a pivot table in which I had the
UserNames on the column labels, then the account numbers. And the
Count was summed in the center.

Same results. Well, actually the two accounts with (null) values
above were simply missing from the pivot table. But it's the same
idea.

Weirdly, I can actually get the missing counts if I execute the query
differently. My MDX is not great, but I played around and did things
like this:

select
[Measures].[Count] on COLUMNS,
Exists([Account].[AccountNumber].[10000011305801],
[Security].[UserName].[ads\rgoerwit],
"Security Fact") on ROWS
from
[GLTransactionDetail]

And what I get back from the above query is the correct answer: 493.
That is, I get 493 - the total number of transactions posted to
account 10000011305801 (a value that, in the query before this last
one, came out as (null)).

So there you have it: A weird problem.

Anybody have any idea what is going on here? Why don't I see all the
numbers when I execute a select using a cross join?

[Marco Russo]

It's possible that the issue is the way you defined M2M relationships.
Please take a look at this paper I wrote: http://www.sqlbi.com/manytomany.aspx
You should take a look at the cascading M2M scenario and you have to
be sure to "fill" all the gray boxes in dimension relationship grid
(it's better described in the paper).
Let me know if you already applied this to your cube or not.
You might write me directly at marco (dot) russo (at) sqlbi.com in
case I don't answer you promptly (I don't follow newsgroup regularly).

Marco Russo
http://www.sqlbi.com
http://sqlblog.com/blogs/marco_russo