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:
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]
[Sales].[Sales MG] NATURAL JOIN [Sales].[Customers]
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.
We don’t support HAVING but do support a simple WHERE clause like:
UniqueName( [Sales].[Customers].[Country] ) = ‘[Customers].[Country].[USA]’
AND Name( [Sales].[Customers].[State] ) = ‘[Customers].[State].[WA]’
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?