Dimension cubes

jamiet — Wed, 30 Dec 2009 23:14:08 GMT

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:

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:

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

SSIS Junkie : SSAS, sql server, dimension cubes

Dimension cubes