How to extract a list of attributes from a SSAS multidimensional cube without using a measure

Often consumers of a SSAS multidimensional cube will want a list of attributes from the cube without having to select a measure.   Recently, users I've been working with have wanted to do three things:

1. Get a list of attributes in an Excel pivot table to use for lookup
2. Get a list of attributes in PowerPivot for Excel to use for relationships
3. Get a list of attributes to populate a SSRS drop down pick list

It is a misunderstanding to think that you always need a measure to get a list of attributes from a MultiD cube.

Assumption: The reader of this post knows how to create a data connection in Excel to a SSAS MultiD cube, and is familiar with creating datasets in SSRS.

Get a List of Attributes in an Excel Pivot Table:
Be default, Excel will display a list of attributes when no measure is selected.  Simply create a new pivot table and place an attribute on Rows leaving Filters, Columns and Values empty.

Depending on your cube design, you may need to change the properties of the pivot table to Show items with no data on rows.  (You wouldn't think this should make a difference when there is no measure selected for the pivot, but it does.)

Get a List of Attributes in PowerPivot for Excel:
The tiny trick here is to know about the Show Empty Cells button found in the Table Import Wizard screen.  Without the Show Empty Cells button clicked, you will receive a No rows found. Click to execute the query message.

Click the Show Empty Cells button, and you will get a list of dimension attribute values without a measure.

The result is a list of dimension attribute(s) in a PowerPivot table with no measure needed.

Get a List of Attributes in SSRS
Acquiring a list of attribute values in SSRS is a bit more challenging because you need a bit of MDX, but never fear -- take my MDX given below and make it your own.

First, I strongly recommend the use of Shared Datasets in SSRS when selecting data in SSRS that could be used by more than one report.  Data sources for drop down pick lists are excellent reasons to create a shared dataset:  a list of relative dates, codes, descriptions, doctor names, procedures, products etc.

In SSRS Query Designer, copy and paste the following MDX, or your variation thereof.

 SELECT {} on 0,
[Date].[Calendar].[Date].Members ON 1
FROM [Adventure Works]

WITH MEMBER [Measures].[Level Name] as [Date].[Calendar].CurrentMember.Level.Name
     MEMBER [Measures].[Ordinal No] as [Date].[Calendar].CurrentMember.Level.Ordinal
SELECT {[Measures].[Level Name], [Measures].[Ordinal No]} on Columns
    ,NON EMPTY [Date].[Calendar].members on Rows
FROM [Adventure Works]

 SELECT  { }  on 0,
[Customer].[Education].Children on 1
FROM [Adventure Works]

The following MDX uses a [Date Filters] table created specifically for SSRS parameters.  Like [Date Calculations], it has no relationship to any measure group in the cube.  The t-SQL in the cube DSV looks like this -->
SELECT 1 as DateFilterKey, 'CurrentDateTime' as DateFilterName, Convert(varchar(25),GetDate(),120) as DateFilterValue
UNION
SELECT 2, 'CurrentDate' as DateCalculationName, Convert(varchar(25),Convert(Date,GetDate()),110)
UNION
SELECT 3, 'CurrentDayNo', Convert(varchar(2),Day(GetDate()))
UNION
SELECT 4, 'CurrentWeekNo', Convert(varchar(2),DatePart(Week, GetDate()))
UNION
SELECT 5, 'CurrentMonthNo', Convert(varchar(2),Month(GetDate()))
UNION
SELECT 6, 'CurrentYearMonthNo', Convert(char(6),Convert(int,Convert(char(4),Year(GetDate())) + RIGHT(RTrim('00' + Convert(char(2),Month(GetDate()))),2)))
UNION
SELECT 7, 'PriorDay1', Convert(varchar(25),Convert(DateTime,Convert(varchar(8),GetDate() -1,112),1),110)
UNION
SELECT 8, 'PriorWeekNo', Convert(varchar(2),DatePart(Week, GetDate() - 7))
UNION
SELECT 9, 'PriorMonthNo', Convert(varchar(2),DatePart(mm,dateadd(day,(day(GetDate())-1)*-1,dateadd(month,-1,GetDate()))))
UNION
SELECT 10, 'SameDayLastYear', Convert(varchar(25),
        CASE WHEN Right(Convert(char(8),GetDate(),112),4) = '0229'
          THEN GetDate() - 366
          ELSE GetDate() - 365
        END,110)
UNION
SELECT 11, 'None', '01-01-2054'
Find a more extensive list of t-SQL data calculations here --> http://www.delorabradish.com/dba/t-sql-date-calculations.

The MDX in SSRS' Query Designer is this -->
//get all values returned in string format
SELECT {} on 0,
([Date Filters].[Date Filter Name].Children,
 [Date Filters].[Date Filter Value].Children) ON 1
FROM [Time Calc POC]

If you just want date data types for your pick list and you have used naming conventions in your [Date Filter Name] attribute, your MDX would be as follows:
//get filter values that are of a date data type only
SELECT {} on 0,
{
 FILTER([Date Filters].[Date Filter Name].Children,
    Right([Date Filters].[Date Filter Name].CurrentMember.Name,2) <> 'No'
    ) *
    [Date Filters].[Date Filter Value].Children 
} ON 1
FROM [Time Calc POC]

Get a list of attribute values from a hierarchy.
SELECT {} on 0,
[Product].[Product Categories].[Product].Members on 1
FROM [Adventure Works]

Get a list of attributes including the SSRS parameter value.
WITH 
MEMBER [Measures].[ParameterCaption] AS [Promotion].[Promotion Category].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Promotion].[Promotion Category].CURRENTMEMBER.UNIQUENAME
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS ,
[Promotion].[Promotion Category].Children on ROWS
FROM [Adventure Works]

Include the All member
WITH 
MEMBER [Measures].[ParameterCaption] AS [Promotion].[Promotion Category].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Promotion].[Promotion Category].CURRENTMEMBER.UNIQUENAME
SET [Promotion Category] AS [Promotion].[Promotion Category].ALLMEMBERS
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS ,
[Promotion Category] on ROWS
FROM [Adventure Works]

I hope this little blog post helps to get you started on extracting attribute values without using a related measure.  Enjoy!