CREATE SUBCUBE syntax in 2K5B2

In SQL Server Books Online I find this definition for SUBCUBE:
CREATE SUBCUBE
Subcube_identifier
AS
Subcube_expression
Which is the syntax for 'Subcube_expression'?
Thanks,
Valter
Message posted via http://www.droptable.comSucube_expression means <select statement>. For example,
CREATE SUBCUBE UsaSales
AS
SELECT (Customer.Country.Country.Usa, *) FROM Sales
Ohjoo Kwon
"Valter Paiotti via droptable.com" <forum@.droptable.com> wrote in message
news:2a5773b6715349249192a8fdf288046c@.SQ
droptable.com...
> In SQL Server Books Online I find this definition for SUBCUBE:
> CREATE SUBCUBE
> Subcube_identifier
> AS
> Subcube_expression
> Which is the syntax for 'Subcube_expression'?
> Thanks,
> Valter
> --
> Message posted via http://www.droptable.com

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

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

sql

CREATE SUBCUBE in Microsoft Visual Studio for SQL2005

Hi,

I am trying to create a SUBCUBE in Microsoft Visual Studio (Analysis Services) but

It keeps on giving me an error when trying to build the Subcube.

The MDX statement is correct because I have tested it in SQL2005 Management Studio and it works.

My question is that “am I doing it in the right place?”:

This is under the Cubes Folder > Calculations Tab> New Script Command

My understanding is that under the “New Script Command” I can type any MDX statement and it should work? Or should this be done in another place?

When processing the cube the error I am getting back is:

Parser: The script contains the statement, which is not allowed

What I am trying to do is to create a slice of a cube by e.g. Brand which one of my departments need to use and should not see the other Brands. All the other dimensions and measures should still apply.

I would appreciate it if someone can please assist me with this.

Thank you in advance

Pieter Nelson

im no expert in olap cubes and am only tinkering with them for the last few months, but ive never heard of sub cubes. in that suituation, id either use a front end to create a report that filters out all but the brand your interested in, or create a seperate cube, based on the exact same data source view, but just import the brand you require. there may be a more elegant way, but those ways are pretty simple.

Regards,

Winston.

|||

Hi Pieter,

It sounds like you need to be using dimension security, not subcubes here. See
http://msdn2.microsoft.com/en-us/library/ms175366.aspx

for details on how to do this.

HTH,

Chris

|||

Unfortuanately I can not use Security or Another Measure Group.

I went into the Calculations Tab in Visual Studio and edited the Calculate Script that look like this:

-

CALCULATE;

CREATE SUBCUBE [Cube_AcqMgmtGF_Mthly] AS SELECT

{[All Brands].[All Brands].[Brand].&[Goldfishka B]} ON 0

FROM [Cube_AcqMgmtGF_Mthly];

CREATE MEMBER CURRENTCUBE.[MEASURES].[Ave first Purchase]

AS '[Measures].[First Purch]/[Measures].[Usaccountno]',

FORMAT_STRING = "Currency",

VISIBLE = 1;

-

I am still trying to filter this cube to one specific brand, but the deployment keeps on failing with the following Description:

"An MDX Statement was expected. An MDX expression was specified" "The script contains the statement, which is not allowed"

To be honest here, I am now getting really confused.

SUMMARY:

Is my code in the right place "Create Subcube" or is there an alternative way of doing this.

PLEASE HELP ANYONE?

|||

Hi Pieter,

To be honest, I can't see anything wrong with your MDX (which service pack are you runnning?) but even if you could create a subcube in your MDX Script I don't think it's going to do what you want - even though I'm able to deploy a CREATE SUBCUBE command in an MDX Script it doesn't look like it does anything useful.

Why can't you use security or another measure group? Can you explain what you want to do in more detail?

Chris

|||

Hi,

I am trying to create subcubes(by Brands) from a Master cube (by only processing one cube), but the situation is that if I create a cube for every brand then it will be different processing for every cube. Unfortuanately these fact tables contains millions of records and processing all these records for every cube will take much longer. The other problem is that if there is changes in the logic of one fact table then I will have to apply these changes to all the other cubes individually.

Hope this helps?

|||

This sounds exactly what dimension security is intended for. Why do you say you can't use it?

Chris

Create subcube action from currently sliced cube?

Hi guys! I'm wondering if its possible to create an action that would create a subcube by right clicking on a measure cell? For example, the user narrows the cube to 1000 accounts in a particular market. Now they want a cube based on only these 1000 accounts... so they right click on the "1000" cell, pick actions (just like drill through) and there's a chioce to create a subcube. And advice?

Thanks!

You can create a statement action that could execute some MDX against your Cube, but depending on your client browser, the statement action may not be exposed to the end user. Another option would be to create a reporting action that would launch an SSRS report that executed some MDX against your cube. Even if you were able to create an action that would create a subcube, I don't see how your front end tbrowser would be able to use it. The only thing that I've encountered that may solve your problem is by using proclarity. I believe Proclarity gives you the option to browse the cube and then save selected dimension members as named sets.

Hope this helps.

Van Dieu