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:


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]

             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:


                           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