THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Dimension cubes

This is a quickfire blog post about a feature in Analysis Services that I have learnt about recently – Dimension Cubes. As of Analysis Services 2005 every single dimension in an Analysis Server database implicitly has a dimension cube created for it. Dimension cubes enable you to get around the limitation that every MDX query must have a FROM clause that references a cube and thus enable you to query over a single dimension without worrying what cube it is in. The name of a dimension cube is the same as that of the dimension itself but with a dollar sign pre-pended to it.

Did that make any sense? Perhaps rather than try and explain what a dimension cube is I could probably just show you instead, I’ll use the [Adventure Works DW Standard Edition] sample database in SQL Server Analysis Services 2008 as an example. The following query returns all members of the [Product] level in the [Product Categories] hierarchy in the [Product] dimension:

select  [Product].[Product Categories].[Product].members on columns
from    [$Product]

And here’s the result:

image

Notice how we are not querying over a regular cube but we are still able to interrogate the members of a dimension; I find this to be a really useful feature, especially when building reports. Still, I  would rather the members were arranged in a list vertically rather than horizontally but that doesn’t seem possible because a dimension cube by definition only has one dimension and the first axis referenced in an MDX query MUST be ‘columns’. Well, whoever came up with the notion of dimension cubes obviously thought of that because every dimension cube also contains a single measure that has the same name as the dimension itself. Hence we can write this:

select   [Product].[Product Categories].[Product].members on rows
,        [Measures].[Product] on columns
from    [$Product]

and see our dimension members in a much more readable format:

image

It transpires that we can also use an empty set on the ‘columns’ axis in order to achieve the same thing:

select   [Product].[Product Categories].[Product].members on rows
,        {} on columns
from    [$Product]

I’m sure many people reading this already knew about dimension cubes but until recently I did not and hence I assume others who might find them useful might not know about them either – hence this blog post. Did I miss anything?

@Jamiet

Published Thursday, December 31, 2009 12:14 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Webb said:

I blogged about this a while ago: http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!152.entry

As Mosha pointed out at the time, the major problem with this functionality - and the reason it's of little practical use - is that it is only available to administrators... so you probably shouldn't be using it to build SSRS reports.

December 30, 2009 7:04 PM
 

jamiet said:

Chris,

yeah I should have clarified. I don't actually have dimension cube queries in my reports, I just mean that they are useful for interrogating the dimensions  - and that's a useful thing to be able to do when you are building reports.

-Jamie

December 31, 2009 4:22 AM
 

tahereh said:

tanx,

so useful!!!!!!!!!!

regards Tahereh

August 21, 2010 12:24 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement