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

Skip to main content