Hi,

I'm working with dynamic dimension security with a user-defined
function, and am trying to make it "query-aware", more than just
"user-aware". Assume that the UDF to return the sets of allowed and
denied members can take an additional parameter (specifically, a
member of another dimension) and modify the result set accordingly.

This would be nice... if I could get it to work, of course! :-) But
the thing is, it seems to me like it *should* work. For example, on
Page 115 of my copy of "MDX Solutions" by George Spofford, discussing
the resolution order of the following skeleton MDX query:

WITH
MEMBER
SET
SELECT
{ axis set 0 } on COLUMNS,
{ axis set 1 } on ROWS
FROM
cube
WHERE
slicer

he states that the *first* relevant element is the FROM clause (which
makes sense), and that the *second* relevant element is the WHERE
clause (which also makes sense, to me). So, if I have a dimension
[foo] with a member [bar], and my slicer was

WHERE ([foo].[bar])

then this is resolved before the remaining sets are resolved. So if I
secure another dimension with a UDF which takes
[foo].CurrentMember.UniqueName as a parameter, as well as username,
then the allowed (and denied) members from the secured dimension can
be influenced by the selection of another.

Except that, as I say, it doesn't seem to work. Although my UDF works
correctly, and although it is being evaluated each time I execute the
MDX query (I've tested this by using a function that queries a view
on a SQL database, and changing the view definition between
executions of the MDX query, and the MDX results are exactly in step
with the view changes) it seems as if [foo].CurrentMember.UniqueName
from the originating query is never passed to the call to resolve the
dimension members, i.e. the allowed members are always returned as
if[foo].[All foo] was selected.

So, is there something I am missing to make this work as I wish, or
is it actually impossible to make it work as I've described?

Any insights appreciated!

Cheers,

Koan

I'm not sure exactly what you want for output based upon your post.

Could you re-post your question with something more like:

"Here's my input (describe the input with an example) and I pass in x,y,z
(describe your inputs as well) via (MDX, UDF writing in whichver language)
etc..."

"I'm hoping to get an MDX statment like this returned(then post the MDX you
want to get) but I'm actually getting this MDX (then post the MDX that's
incorrect)"

This way I can understand exactly what you want, how you're trying to get to
it, and what's the symptom/error you're getting.

Thanks.

- Phil


"Koan B" wrote:

"=?Utf-8?B?U1FMIE1jT0xBUA==?="
<SQLMcOLAP (AT) discussions (DOT) microsoft.com> wrote in
news:C1477D62-08F3-440B-9E80-E5AFDC040D0F (AT) microsoft (DOT) com:

A fair request, so I'm going to try and explain, in the context of
FoodMart 2000, *what* I'm trying to do, and *what* I'm expecting
(well, hoping) to see as a result. What I can't be more explicit
about is *why*; corporate IPR policy prevents me from revealing that.

It's a slightly artificial example, but here goes...

Suppose we want to be able to run queries out of the Sales cube for
those customers who live within a target radius of, say, 100 miles of
a particular warehouse. The current schema doesn't support this.
There are five member properties at the Name level of the Customers
dimension (namely Gender, Marital Status, Education, Yearly Income,
Member Card); if we added a sixth, for "nearby warehouse", that
wouldn't really help, because a customer might live within the target
radius of 0, 1 or multiple warehouses.

If, however, I had a separate resource (e.g. in RDBMS tables) which,
given a particular warehouse, would return a list of all the
customers within 100 miles of that warehouse, then I could run a
query like:

SELECT {[Measures].[Store Sales]} ON 0,
[Customers].[Name].Members ON 1
FROM [Warehouse and Sales]
WHERE [Warehouse].[All
Warehouses].[Canada].[BC].[Vancouver].[Bellmont Distributing]

and expect to see the [Store Sales] for all customers within 100
miles of [Bellmont Distributing] - except for two problems:

1) The sales data is not dimensioned by [Warehouse], so I wouldn't
see any results for [Store Sales]; and

2) I still see all the Customers; conceptually, the slice does not
restrict the specific customers which are returned on the rows.

I can get over the first problem by using ValidMeasure(), i.e.

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
[Customers].[Name].Members ON 1
FROM [Warehouse and Sales]
WHERE [Warehouse].[All
Warehouses].[Canada].[BC].[Vancouver].[Bellmont Distributing]

but I'm still seeing all of the customers, not just those that are
within 100 miles of [Bellmont Distributing]. Which is where the
notion of using dynamic dimension security comes in.

Suppose I secure the customer dimension, using a UDF which takes as
an input parameter [Warehouse].CurrentMember.UniqueName and returns
(by means of an RDBMS query) an "allowed set" consisting of those
customers within 100 miles of that warehouse and a "denied set"
consisting of those customers outside that radius. Suppose also that
if I do not explicitly use the [Warehouse] dimension in my slicer
(which would effectively resolve a value for
[Warehouse].CurrentMember.UniqueName of "[Warehouse].[All
Warehouses]") that the allowed set is [Warehouse].Members and the
denied set is {}.

Then, when I execute the second query, and according to the
resolution sequence I referred to in the original post, Analysis
Services should first determine that I want to query the [Warehouse
and Sales] cube, *then* determine that the slice I'm interested in is
[Warehouse].[All Warehouses].[Canada].[BC].[Vancouver].[Bellmont
Distributing], and *only then* resolve the sets; including querying
for the members of the [Customers] dimension. By the time it queries
for those, it knows the slice (i.e. it has resolved a value for
[Warehouse].CurrentMember.UniqueName, which the UDF should be using
to determine which members of the [Customer] dimension are allowed
for this query, and which are denied.

So, the combination of ValidMeasure() and a query-aware security UDF
would give me the result I seek. Except that the UDF does not seem to
receive the slice information, i.e. it returns the complete set of
allowed members.

So my original question remains; given that the UDF is evaluated
every time I execute a query which uses the secured customer
dimension (and which I have proved); and since a security UDF *can*
take a parameter (such as the classic username when used to return
the allowed and denied sets for the user associated with the
connection) how can I ensure that that the value of
[Warehouse].CurrentMember.UniqueName is passed to the UDF when it is
executed?

Hopefully this makes my original question a little clearer.

Cheers,

Koan

"So my original question remains; given that the UDF is evaluated
every time I execute a query which uses the secured customer
dimension (and which I have proved); and since a security UDF *can*
take a parameter (such as the classic username when used to return
the allowed and denied sets for the user associated with the
connection) how can I ensure that that the value of
[Warehouse].CurrentMember.UniqueName is passed to the UDF when it is
executed?"

The best way to be sure it's the proper member (from within the MDX querying
world) would be to create a calculated member in whichever dimension you
choose. Let's say, for example, Measures.

example:

WITH MEMBER [Measures].[MyMeasure] AS '[Warehouse].CurrentMember.UniqueName'

Then select it wherever you like, as long as it's either on an opposing
axis, or as a latter part of a crossjoin on the same axis.

example:

WITH MEMBER [Measures].[MyMeasure] AS '[Warehouse].CurrentMember.UniqueName'

select
{[warehouse].members } on rows,
{[Measures].[MyMeasure]} on columns
from warehouse


Just by looking at this simple grid, you should be able to be sure that the
proper unique member name is being used. If your calculated member grabs the
dimension.currentmember.uniquename correctly, there should be no reason why
your UDF doesn't get it correctly as well. Also, in debugging your UDF, you
should be able to debug that value and compare it back to whichever axes your
on to be sure it's correct.

This will work if you have warehouses on either of your axes, but will also
work if you have it in your slice, and NOT on either axes.

If you run the following MDX:

WITH MEMBER [Measures].[MyMeasure] AS '[Warehouse].CurrentMember.UniqueName'

select
{[warehouse].members } on rows,
{[Time].[Year].members} on columns
from warehouse
where ([Warehouse].[All Warehouses])


....again you should see the proper unique name of the warehouse current
member. If your spinning through a cellset in ADMD and echoing back the
cells values, you'll see these results as well. Again, at debug time in your
UDF development, you should be able to test it right then.

Not knowing enough about how you're passing coordinates to your UDF, I'm not
sure how much more helpful I can be. When debugging your UDF, do you see the
incorrect value being passed in?


- Phil


"Koan B" wrote:

"=?Utf-8?B?U1FMIE1jT0xBUA==?="
<SQLMcOLAP (AT) discussions (DOT) microsoft.com> wrote in
news:0D44C970-0A5C-4B4B-BBB7-EB93EE7C317F (AT) microsoft (DOT) com:

<Snip>

Hi Phil,

I really appreciate your efforts in attempting to help me here; I'm
frustrated, though, because I'm obviously not explaining clearly
enough exactly what the problem is, and where I'm experiencing it.
So, I'll try again...

I have a VB6 UDF, secured_members(warehouse_member, allowed); for any
valid warehouse_member and a value of allowed of 1, it returns the
set of customers within 100 miles of that warehouse; for the same
value of warehouse_member and a value of allowed of 0, it returns the
set of customers greater than 100 miles from that warehouse. The UDF
uses a SQL lookup to determine which customers fall within the target
radius of each warehouse.

The following queries all work, and all return the expected results:

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
StrToSet(Secure_Members([Warehouse].CurrentMember.UniqueName,1))
ON 1
FROM [Warehouse and Sales]

-- returns all customers (i.e. correctly resolves
[Warehouse].CurrentMember.UniqueName to [Warehouse].[All
Warehouses];

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
StrToSet(Secure_Members([Warehouse].CurrentMember.UniqueName,0))
ON 1
FROM [Warehouse and Sales]

-- returns no customers (i.e. correctly resolves
[Warehouse].CurrentMember.UniqueName to [Warehouse].[All
Warehouses], and determines that the denied set is therefore {};

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
StrToSet(Secure_Members([Warehouse].CurrentMember.UniqueName,1))
ON 1
FROM [Warehouse and Sales]
WHERE [Warehouse].[All
Warehouses].[Canada].[BC].[Vancouver].[Bellmont Distributing]

-- returns only those customers within 100 miles of the target
warehouse;

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
StrToSet(Secure_Members([Warehouse].CurrentMember.UniqueName,0))
ON 1
FROM [Warehouse and Sales]
WHERE [Warehouse].[All
Warehouses].[Canada].[BC].[Vancouver].[Bellmont Distributing]

-- returns only those customers greater than 100 miles of the target
warehouse.

Bottom line; the UDF works correctly, and correctly resolves
[Warehouse].CurrentMember.UniqueName.

But, if I take that UDF and use it to secure the [Customers]
dimension, i.e. set the allowed members to be
Secure_Members([Warehouse].CurrentMember.UniqueName,1) and the denied
members to be Secure_Members([Warehouse].CurrentMember.UniqueName,0),
and then rewrite the MDX queries as follows:

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
[Customers].[Name].Members ON 1
FROM [Warehouse and Sales]

-- this *should* return all the customers (because I haven't used
[Warehouse] in the slicer), and it does; and

WITH MEMBER [Measures].[Valid Store Sales] AS
'ValidMeasure([Measures].[Store Sales])'
SELECT {[Measures].[Valid Store Sales]} ON 0,
[Customers].[Name].Members ON 1
FROM [Warehouse and Sales]
WHERE [Warehouse].[All
Warehouses].[Canada].[BC].[Vancouver].[Bellmont Distributing]

-- this *should* only return those customers within 100 miles of the
target warehouse... and it *doesn't*; it returns *all* customers.

Which is where I began this thread; the members of [Customers] to be
be projected on Axis (1) aren't calculated until *after* the slice
has been established; the UDF *should* ensure that only the allowed
members are actually returned; but, sadly, it doesn't. The UDF *does*
return exactly the correct results in all circumstances, when I call
it directly in the MDX query; but it steadfastly defaults to
Secure_Members([Warehouse].[All Warehouses],1) for allowed and
Secure_Members([Warehouse].[All Warehouses],0) if it is used to
secure the [Customers] dimension

My supposition is that the context of
[Warehouse].CurrentMember.UniqueName is being lost between the MDX
query and the resolution of the allowed members of the [Customers]
dimension. Which is a right royal pain, to be blunt! So, I'm back to
my original question; I want the UDF to be query aware, not just
connection aware. Am I missing some trick / setting / parameter /
configuration to make this happen?

Feel free to jump in here any time you like, someone from Microsoft;
this is a registered MSDN newsgroup alias, after all. ;-)

Cheers,

Koan