Saturday, February 25, 2012

Attributes, Properties; Dimensions, Levels, and Hierarchies, oh my!

I have a most basic question: how to correctly access properties (attributes) of dimension members that are at a different level. Here's a simple query from AdventureWorks to illustrate:

Code Snippet

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

[Measures].[Internet Sales Amount]

on 0

from [Adventure Works]

This query works, but the city property is left out of the results (postal code is provided). How can I get City, State-Province, Country, etc, as properties in a query like this? (As the relationships are all correctly defined, and the levels for these attributes/properties are less restrictive than the query level, I'm totally missing why this doesn't work in either the form given -- but do I have the name perhaps wrong?)

Seperately, I have read (somewhere) that property access is to be discouraged in SSAS05, instead prefering direct attribute access via UDM; is there a better way to write these sorts of queries, say including City, etc in the output grid area?

Thanks as always!

To somewhat answer my own query, I have found that:

Code Snippet

with member [measures].[cc] as [customer].[city].MemberValue + ', ' + [customer].[state-province].MemberValue

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

{[measures].[cc], [Measures].[Internet Sales Amount]}

on 0

from [Adventure Works]

properly returns the customer "city, state" in the output grid area. But I am still interested in how to obtain the city and state-province instead as properties of the customer members on axis(1).....

Thanks again!

No comments:

Post a Comment