How in SSAS 2005 do you create what is used to be "Member Properties" in Analysis Services 2000
Q: How in SSAS 2005 do you create what is used to be "Member Properties" in Analysis Services 2000?
A: Lets say you want to create member property "Color" for dimension Product attribute SKU.
- Edit dimension Product and add another attribute Color.
- Change attribute relationship between SKU and Color. By default all
attributes are related to dimension key attribute. You should delete that
default relationship and instead create relationship between SKU and Color
by dragin "Color" attribute into area "<new attribute relationship>" just
below SKU attribute. - As attribute Color will not be used for querying, you should change its
property "AttributeHierarchyEnabled" to False.
Now attribute "Color" will not appear in dimension "Product" attribute list, but you will be able to show it as property in many client tools.
In SSAS 2005 when you create relationship between attributes, you kind of defining attribute properties. When [State-Province] attribute is related to [Country] attribute in Geography dimension, you can write a query using [State-Province] attribute and return it with [Country] attribute as a dimension property. It depends on your front-end application how returned member property is showed.
Here is a query on Adventure Works datamart:
SELECT NON EMPTY { [Measures].[Reseller Order Count] } ON COLUMNS
, NON EMPTY { ([Geography].[State-Province].[State-Province].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, [Geography].[State-Province].[Country] ON ROWS
FROM [Adventure Works]
When you execute this query in Microsoft SQL Server Management Studio, results will look like this:
State-Province Reseller Order Count
Alabama 20
Alberta 75
Arizona 65
Bayern 15
...
Wisconsin 24
Wyoming 31
In Microsoft SQL Server Management Studio you have to click on province name to see properties for that state.
The same query in Reporting Services will return following:
State-Province Country Reseller Order Count
Alabama United States 20
Alberta Canada 75
Arizona United States 65
Bayern Germany 15
...
Wisconsin United States 24
Wyoming United States 31
Another way to access related attributes is by using Properties function. Here is example:
WITH MEMBER [Measures].[Geography Country] AS [Geography].[State-Province].CurrentMember.Properties("Country")
SELECT NON EMPTY { [Measures].[Geography Country], [Measures].[Reseller Order Count] } ON COLUMNS
, NON EMPTY { ([Geography].[State-Province].[State-Province].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, [Geography].[State-Province].[Country] ON ROWS
FROM [Adventure Works]
In the result from query above country will be
State-Province Geography Country Reseller Order Count
Alabama United States 20
Alberta Canada 75
Arizona United States 65
Bayern Germany 15
...
Wyoming United States 31