0

I have the query that runs OK on SSAS.

SELECT  DIMENSION_NAME
FROM  $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE LEFT(CUBE_NAME,1) = '$'
AND [DIMENSION_UNIQUE_NAME] <> '[Metrics]'
AND [DIMENSION_UNIQUE_NAME] <> '[Measures]'
ORDER BY DIMENSION_NAME

How should I specify NOT LIKE condition to get all Dimension Names that do not start with "Fact"? In T-SQL it is written such as 

AND [DIMENSION_UNIQUE_NAME] NOT LIKE 'Fact%'

but how to write it in MDX?

Edit based on comments:

How to write the query so I can filter out elements starting with "Fact" in the name?

The following query returns an error:

SELECT  DIMENSION_NAME ,*
FROM  $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE LEFT(CUBE_NAME,1) = '$'
AND [DIMENSION_UNIQUE_NAME] <> '[Metrics]'
AND [DIMENSION_UNIQUE_NAME] <> '[Measures]'
AND [DIMENSION_UNIQUE_NAME] NOT LIKE 'Fact%'
ORDER BY DIMENSION_NAME

-

Executing the query ...
Query (6, 29) The syntax for 'NOT' is incorrect.
Execution complete
Cœur
  • 37,241
  • 25
  • 195
  • 267
DNac
  • 2,663
  • 8
  • 31
  • 54
  • DMV are not meant to be written in MDX. MDX is for querying cube's data, where DMV's are meant for querying cube properties. – SouravA Nov 16 '15 at 13:33
  • Maybe I shuffled the terms a bit. What I want to do is to run this on SSAS server in SSMS to get the cube properties, but filter the dimensions / elements by name. – DNac Nov 16 '15 at 13:38
  • I agree with SouravA - stick to sql: I think the dmv will be a relational table and hence mdx will not help – whytheq Nov 16 '15 at 13:40

1 Answers1

1

Replace the second last line with this:

AND LEFT([DIMENSION_UNIQUE_NAME], 4) <> 'Fact'

Hope you are running the statement in a new DMX query window on SSMS

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • Yes, that actually clever. I will play around with this as the unique name starts with [ .. as [unique_name] .. but that should be OK. Thanks. – DNac Nov 16 '15 at 16:07