Using SQL against the Unified Dimension Model in Microsoft SQL Server 2005 Analysis Services


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.


We don’t support HAVING but do support a simple WHERE clause like:


             WHERE


                           UniqueName( [Sales].[Customers].[Country] ) = ‘[Customers].[Country].[USA]’


                          AND Name( [Sales].[Customers].[State] ) = ‘[Customers].[State].[WA]’


 


 

Comments (1)

  1. Deepak Puri says:

    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?

    – Deepak