Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.