• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

SQL Server Analysis Services

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

12/2/2015

3 Comments

 
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.
Picture
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.)
Picture
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.
Picture
Click the Show Empty Cells button, and you will get a list of dimension attribute values without a measure.
Picture
The result is a list of dimension attribute(s) in a PowerPivot table with no measure needed.
Picture
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]
Picture
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]
Picture
 SELECT  { }  on 0,
[Customer].[Education].Children on 1
FROM [Adventure Works]
Picture
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]
Picture
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]
Picture
Get a list of attribute values from a hierarchy.
SELECT {} on 0,
[Product].[Product Categories].[Product].Members on 1
FROM [Adventure Works]
Picture
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]
Picture
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]
Picture
I hope this little blog post helps to get you started on extracting attribute values without using a related measure.  Enjoy!
3 Comments
Bahaa
7/17/2018 11:53:01 pm

Depending on your cube design, you may need to change the properties of the pivot table to Show items with no data on rows. Could you please specify which option or deign affects this. Because sometimes when i use Excel to connect to a cube sometimes the attribute members are listed even if the "show items with no data on rows"" is not checked,. on other cubes i have to check it. What option in Visual Studio Cube design affects this?

Reply
Delora link
7/23/2018 12:23:54 pm

I would think that "sometimes the attribute members are listed..." might be the difference between columns that have data and those that do not.

You may want to read up on your MultiD UnknownMember and NullProcessing properties. Link --> https://docs.microsoft.com/en-us/sql/analysis-services/lesson-4-7-defining-the-unknown-member-and-null-processing-properties?view=sql-server-2017.

Reply
Dylan Weeks link
10/13/2024 04:57:44 pm

Good jobb

Reply



Leave a Reply.

    Categories

    All
    Cache
    DBA
    Excel
    Extended Events
    HOLAP
    MOLAP
    Multidimensional
    ROLAP
    Server Mode
    SSAS
    Tabular

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About