SSAS FAQ Design FAQ How in SSAS 2005 do you create what is used to be "Member Properties" in Analysis Services 2000
How in SSAS 2005 do you create what is used to be "Member Properties" in Analysis Services 2000
User Rating: / 2
PoorBest 
Written by Vidas Matelis   
Sunday, 22 April 2007 15:18

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.

  1. Edit dimension Product and add another attribute Color.
  2. 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.
  3. 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

 

 

 

< Prev   Next >
 
Strategy Companion