Selecting dimension table from SSAS

Question

I'm trying to figure out how to get the data in a dimension via SSAS/MDX. Example:

DIMFileStatus
--------------
FileStatusID, FileStatusName
1             New
2             Updated
3             Deleted

If I run a simple query like:

SELECT [DimFileStatuses].[FileStatusName].members ON 0
FROM [status]

I get the following:

All  New  Updated  Deleted
--------------------------
1      1       1        1

What I want is:

FileStatusID  FileStatusName
          1    New
          2    Updated
          3    Deleted

How can I structure my query to get the results in the format I want? Thanks!

MORE INFO:

If I try to create a custom measure:

WITH MEMBER Measures.[custom] AS [DimFileStatuses].[FileStatusID].CURRENTMEMBER.MEMBERVALUE
SELECT [DimFileStatuses].[FileStatusName].members on 1,
[Measures].[custom] ON 0  
FROM [status]

I get:

        custom
--------------
all     (null)
New     (null)
Updated (null)
Deleted (null)

Always include a tag for the primary language the question pertains to (SQL in this case), otherwise your question won't have visibility to subject matter experts. Also, the language tag usually gives your code block snazzy syntax highlighting. — Adi Inbar

FrankPl FrankPl · Accepted Answer · 2014-03-11T18:14:05

You can use

SELECT {}
       ON COLUMNS,
       [DimFileStatuses].[FileStatusID].[FileStatusID].Members
        *
       [DimFileStatuses].[FileStatusName].[FileStatusName].Members
       ON ROWS
  FROM [status]

Using the level that is named like the attribute name excludes the all member, and the Autoexists feature of Analysis Services should take care that all combinations of FileStatusID and FileStatusName returned by the cross join actually exist in the dimension table. This avoids pairs like (1, Updated).

Selecting dimension table from SSAS

1 Answers