, 02/08/2008: Logged In: YES
Good catch. I hadn't thought of that one!
One approach to fixing this would be to simply veto native SQL generation if there is any access-control going on.
More sophisticated would be to try to convert the access-control into MDX. For example, consider
SELECT [Product].Children on Columns,
NON EMPTY [Customers].Members on Rows
where [Product] is access controlled. We could expand
NON EMPTY [Customers].Members
then expand access control on the [Product] dimension, to
This expression is no longer access-controlled, and is therefore eligible for native SQL expansion.
Certain forms of access-control would not be translatable into MDX, or the MDX might be too complicated to translate into SQL. But this is a unifying approach, and a good start.
By the way, I use the EXISTS function here. I don't know that I've used the syntax exactly right, but it's a powerful primitive that we need to start using more. Microsoft essentially replace NonEmptyCrossjoin with EXISTS from SSAS 2005 onwards, because it is more powerful and theoretically better behaved. We should be doing the same.