CODEWITHSUNDEEP
ssasmdx
Redshirt73
Redshirt73

Reputation: 23

Selecting dimension table from SSAS

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)

Upvotes: 2

Views: 721

Answers (1)

FrankPl
FrankPl

Reputation: 13315

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).

Upvotes: 3

Related Questions