MS SQL Server OLAP - Analysis Services 2005: MDX and Dimension Attributes/Member Properties

Author

Analysis Services 2005: MDX and Dimension Attributes/Member Properties

Stuart

2005-09-23, 3:23 am

Analysis Services 2005: MDX and Dimension Attributes/Member Properties

I am having problems getting Member properties using MDX in reporting
services.
I am using Adventureworks for my example here.

I have this MDX here which returns most of the results that I need:

SELECT
NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS,
NON EMPTY { ([Product].[Product Categories].[Product Name].ALLMEMBERS *

[Geography].[Geography].[Postal Code].ALLMEMBERS *
[Promotion].[Promotions].[Promotion Category].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( { [Geography].[Geography].[City].&[Alexandria]&[NSW] } )
ON COLUMNS
FROM (
SELECT ( { [Date].[Fiscal].[Date].&[1] } ) ON COLUMNS
FROM [Adventure Works]
)
)
WHERE ( [Date].[Fiscal].[Date].&[1] )

However I now want to display the Color for the product (Product has a
number of Dimension attributes\propertie
s, one of them being Color).

I have tried 2 approaches to getting the information that I need:
1) Calculated Members

With
Member [Measures].[Color] as
'[Product].[Product Categories].CurrentMember.Properties("Color")'
SELECT
NON EMPTY { [Measures].[Internet Order Count],[Measures].[Color] } ON
COLUMNS,
NON EMPTY { ([Product].[Product Categories].[Product Name].ALLMEMBERS *

[Geography].[Geography].[Postal Code].ALLMEMBERS *
[Promotion].[Promotions].[Promotion Category].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( { [Geography].[Geography].[City].&[Alexandria]&[NSW] } )
ON COLUMNS
FROM (
SELECT ( { [Date].[Fiscal].[Date].&[1] } ) ON COLUMNS
FROM [Adventure Works]
)
)
WHERE ( [Date].[Fiscal].[Date].&[1] )

2) Dimension Proprties

SELECT
NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS,
NON EMPTY {[Product].[Product].[Product].Members}
DIMENSION PROPERTIES [Product].[Product].[Subcategory],
[Product].[Product].[Color], MEMBER_CAPTION, MEMBER_UNIQUE_NAME on
rows,
NON EMPTY { ([Geography].[Geography].[Postal Code].ALLMEMBERS *
[Promotion].[Promotions].[Promotion Category].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON pages
FROM (
SELECT ( { [Geography].[Geography].[City].&[Alexandria]&[NSW] } )
ON COLUMNS
FROM (
SELECT ( { [Date].[Fiscal].[Date].&[1] } ) ON COLUMNS
FROM [Adventure Works]
)
)
WHERE ( [Date].[Fiscal].[Date].&[1] )

However both of the above approaches do not quite work, as they return
large datasets. In the 1st approach the Calculated Member is never
null, hence the non empty flag on the axis does not reduce the dataset
size. In the 2nd approach joining the rows on the pages also returns a
large dataset, most of which has empty Measure fields.

This MDX is being used in a Reporting Services report, and so the first
axis can only contain the Measures Dimension.

Does anyone have some ideas on how I can write this query to get the
result I need (ie get the color property)?

Deepak Puri

2005-09-23, 8:24 pm

The Calculated Measure approach should work, if you set it to be empty
when the measure of interest is empty:[color=darkred
]
With
Member [Measures].[Color] as
iif(IsEmpty([Measures].[Internet Order Count]), NULL,
[Product].[Product Categories].CurrentMember.Properties("Color"))
SELECT
NON EMPTY { [Measures].[Internet Order Count],[Measures].[Color] } ON
COLUMNS,
NON EMPTY { ([Product].[Product Categories].[Product Name].ALLMEMBERS *

[Geography].[Geography].[Postal Code].ALLMEMBERS *
[Promotion].[Promotions].[Promotion Category].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( { [Geography].[Geography].[City].&[Alexandria]&[NSW] } )
ON COLUMNS
FROM (
SELECT ( { [Date].[Fiscal].[Date].&[1] } ) ON COLUMNS
FROM [Adventure Works]
)
)
WHERE ( [Date].[Fiscal].[Date].&[1] )[color=darkred]

But it seems simpler to use the Color attribute:[color=dar
kred]
SELECT
NON EMPTY { [Measures].[Internet Order Count] } ON
COLUMNS,
NON EMPTY { ([Product].[Product Categories].[Product Name].ALLMEMBERS *
[Product].[Color].[Color].Members *
[Geography].[Geography].[Postal Code].ALLMEMBERS *
[Promotion].[Promotions].[Promotion Category].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( { [Geography].[Geography].[City].&[Alexandria]&[NSW] } )
ON COLUMNS
FROM (
SELECT ( { [Date].[Fiscal].[Date].&[1] } ) ON COLUMNS
FROM [Adventure Works]
)
)
WHERE ( [Date].[Fiscal].[Date].&[1] )[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Stuart

2005-09-25, 8:23 pm

Deepak

Thanks a lot for your help, that worked perfectly.

Thanks

Stuart

Sponsored Links