RE: Pushing the boundaries with Dynamic Dimension Security
- From: Koan B <koanb@xxxxxxxxxxxxxxxxx>
- Date: Fri, 20 May 2005 02:52:50 -0700
"=?Utf-8?B?U1FMIE1jT0xBUA==?="
<SQLMcOLAP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:C1477D62-08F3-440B-9E80-E5AFDC040D0F@xxxxxxxxxxxxx:
> 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)"
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?
> 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
Hopefully this makes my original question a little clearer.
Cheers,
Koan
> "Koan B" wrote:
>
>> 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
>>
>
.
- Follow-Ups:
- RE: Pushing the boundaries with Dynamic Dimension Security
- From: SQL McOLAP
- RE: Pushing the boundaries with Dynamic Dimension Security
- References:
- Pushing the boundaries with Dynamic Dimension Security
- From: Koan B
- RE: Pushing the boundaries with Dynamic Dimension Security
- From: SQL McOLAP
- Pushing the boundaries with Dynamic Dimension Security
- Prev by Date: repository problem (DSO)
- Next by Date: IIF AND
- Previous by thread: RE: Pushing the boundaries with Dynamic Dimension Security
- Next by thread: RE: Pushing the boundaries with Dynamic Dimension Security
- Index(es):
Relevant Pages
|
