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 Login like another login...

One bit of functionality I like in Oracle's Enterprise manager, that I
haven't located in SQL Server is the ability to create a new login with all
the same rights, roles etc of an existing user.
I am using SQL Server 2005. I have tried
USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I ge
t
an error when executing it.
==================
Msg 213, Level 16, State 7, Line 2
Insert Error: Column name or number of supplied values does not match table
definition.
==================
Does anyone point me to a way of doing this?
PaulThat's a pretty old script you're using and it's written for SQL Server
2000, calling objects in the master database.
You can update the script using some of the security catalog views instead
of tables in master. For example, fn_my_permissions will tell you what
effective permissions the current user has.
As for the error message below, it looks like you probably had a typo
somewhere when creating or running the sproc. Probably a datatype mismatch.
joe.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:CFECA19B-9C34-4D52-B9FC-1FA95817EE78@.microsoft.com...
> One bit of functionality I like in Oracle's Enterprise manager, that I
> haven't located in SQL Server is the ability to create a new login with
> all
> the same rights, roles etc of an existing user.
> I am using SQL Server 2005. I have tried
> USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I
> get
> an error when executing it.
> ==================
> Msg 213, Level 16, State 7, Line 2
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> ==================
> Does anyone point me to a way of doing this?
> Paul

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Database failed.

Hi!

First of all: I don't really know a lot about MSSQL-Servers, so this question might sound a bit silly ;)

Today I wanted to test an application, that uses a MSSQL-Server for the databases. As they said, that a MSSQL7-Server is required, I thought that a MSSQL-Express 2005-Server would also do the job.

But when it's time to set up the database, I get the following error:

Procdure:CreateDatabase Message:[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.

With searching the net, I didn't found a solution to my problem...

Does this in the end mean, that I need to install (and buy!) a MSSQL7-Server, because the application won't run with a MSSQL-Express2005-Server? Or is there any way to get this thing up'n'running?

Thanks a lot for your answers!

Martin
Hi,

thats usally a problem of obsolete functions in the procedures in your database. THe best thing is to install the database first on a SQL Server 7.0 (Perhaps you can get somewhere a Developer Edition or another edition which shouldn′t be expensive because they are right now two editions *old*). If you want to leave it on the 7.0 Machine your are fine. If you want to migrate it, do a backup of the database and a restore on the SQl Server 2005 Express Machine. Then search after the *now* buggy procedures and try to eliminate them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Hi Jens!

Thanks a lot for your answer.

OK, if it's like this, I'll try to find a MSSQL7-Server...

Martin