Mat Stephen's SQL Server WebLog

All things SQL Server. Complied by Matthew Stephen - SQL Server Product Specialist, Microsoft UK

Blogs

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

  • Comments 1
  • Likes

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
  • 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