CREATE SUBCUBE Syntax and Example

Can anyone provide an example of the right syntax for the CREATE SUBCUBE statement? And possibly discuss its use a bit? For example, from a client tool (like Excel), would there be any way to execute a CREATE SUBCUBE statement such that all browsing done within Excel was bound by the scope of the subcube? While perspectives provide a great server-based mechanism for creating scoped sections of a cube, I need a way to do this on the fly, from a client tool like Excel.

Thanks,
Dave Fackler
Just to follow-up on this for everyone else, I determined what syntax to use for the CREATE SUBCUBE statement. If you use the Browse window in BI Dev Studio or SS Management Studio, you can now define filters that the OWC control then respects as you drag and drop dimensions onto the rows and columns of the pivot table.

So, if you fire up Profiler and start a trace on Analysis Services, then use the Browse window to create a query with filters applied, you'll see the CREATE SUBCUBE statement come across. Very informative as you'll also see a host of other commands being executed by the Browse window!

At any rate, the CREATE SUBCUBE statement simply needs to contain a list of the filters you want in place defined on the columns axis of a standard select statement. Thus, the following would create a subcube on the Adventure Works database for just 2003 and 2004 in the US and UK:

CREATE SUBCUBE [Adventure Works] AS

SELECT ({[Customer].[Country].[United States], [Customer].[Country].[United Kingdom]}, {[Date].[Calendar Year].[CY 2003], [Date].[Calendar Year].[CY 2004]}) ON COLUMNS FROM [Adventure Works]

Notice that the subcube has the same name as the original cube in this example. It doesn't have to, but it can. Any subsequent queries to [Adventure Works] in the current session would then use the subcube and be limited to its scope.

Once the need for the subcube is gone, you can manually drop it as follows:

DROP SUBCUBE [Adventure Works]

This is exactly what the Browse windows in BI Dev Studio and SS Management Studio do (along with some other interesting statements).

Kudos to Profiler for helping me discover this on my own!!

Dave Fackler
|||

Hi Dave,

Some times it possible to have same result in MDX either by using CREATE SUBCUBE or by using FILETR function. Which method is better?

Regadrs,
Rakesh