Following Deepak's question, asked in his comment on my previous UDM post, I quizzed our internal Yukon alias and received the following useful info regarding using SQL against a UDM; thanks to Akshai Mirchandani.

AS2005 supports a limited set of SQL features – I can’t say for sure what the level of SQL compatibility is. Basically this is what it looks like:

-A cube is a schema (like “dbo”)

-A dimension in the cube is a table

-A measure group in the cube is a table

-Dimensions and measure groups can be joined together using the NATURAL JOIN clause

-You can only aggregate measures and group by attributes

-You can only aggregate measures by their aggregation type (e.g. SUM a measure with aggregation type SUM, MIN a measure with aggregation type MIN, etc.) – its simplest to just use the Aggregate function

-You can apply functions to attributes like NAME/UNIQUENAME/KEY/MEMBERVALUE/etc.

An example would look something like this:

SELECT

Aggregate( [Sales].[Sales MG].[Unit Sales] ) AS [Unit Sales],

Name( [Sales].[Customers].[Country] ) AS [Customer Country Name],

Key( [Sales].[Customers].[Country], 0 ) AS [Customer Country Key 0]

FROM

[Sales].[Sales MG] NATURAL JOIN [Sales].[Customers]

GROUP BY

Name( [Sales].[Customers].[Country] ),

Key( [Sales].[Customers].[Country], 0 )

-[Sales] is the schema (name of the cube)

-[Sales MG] is the name of a measure group

-[Customers] is the name of a cube dimension

Note that although you can specify Name/Key etc in the GROUP BY, they get ignored – the GROUP BY is only performed based on the uniqueness of the attribute.

We also support SELECT * without GROUP BY, but it can be very slow without a good slice.

Thanks for researching this, Mat! I’ll have to put myself into my "UDM" frame-of-mind, to fully appreciate the implications. But it does look like, as with AS 2000, server-defined MDX Calculations may not be accessible via SQL?