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 same Database connection on multiple computers

Hi,
is there a possibility to create automaticaly a ODBC database connection on
the multiple client computers (equal for all of them), in ex. on windows
2000 Profesional or XP?
And secont thing. Is there a possibility to automaticaly change all of the
ODBC connections without changing one by one?
Thanks for help.
Marcin
yes it is possible. Just create(or modify) the regkey from
HKEY_LOCAL_MACHINE
SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\
SOFTWARE\ODBC\ODBC.INI\
You can do very easy a program to do all this change. I've made one, and it
works well
Gabriel Anton
Software Developer
"#'ak" <mkrzyz@.beretta.pl> wrote in message
news:cgfjtn$4v3$1@.atlantis.news.tpi.pl...
> Hi,
> is there a possibility to create automaticaly a ODBC database connection
on
> the multiple client computers (equal for all of them), in ex. on windows
> 2000 Profesional or XP?
> And secont thing. Is there a possibility to automaticaly change all of the
> ODBC connections without changing one by one?
> Thanks for help.
> Marcin
>
sql

create same Database connection on multiple computers

Hi,
is there a possibility to create automaticaly a ODBC database connection on
the multiple client computers (equal for all of them), in ex. on windows
2000 Profesional or XP?
And secont thing. Is there a possibility to automaticaly change all of the
ODBC connections without changing one by one?
Thanks for help.
Marcinyes it is possible. Just create(or modify) the regkey from
HKEY_LOCAL_MACHINE
SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\
SOFTWARE\ODBC\ODBC.INI\
You can do very easy a program to do all this change. I've made one, and it
works well
Gabriel Anton
Software Developer
----
--
"#'ak" <mkrzyz@.beretta.pl> wrote in message
news:cgfjtn$4v3$1@.atlantis.news.tpi.pl...
> Hi,
> is there a possibility to create automaticaly a ODBC database connection
on
> the multiple client computers (equal for all of them), in ex. on windows
> 2000 Profesional or XP?
> And secont thing. Is there a possibility to automaticaly change all of the
> ODBC connections without changing one by one?
> Thanks for help.
> Marcin
>

Create Pull Subscription from client

I am trying to create a pull subscription through RMO as described in books online. However, the only way that the creation (or deletion) will succeed is if the windows user that is creating the subscription is a member of the sysadmin role on the publisher. The windows login has been added to the PAL for the publication, but this seems to have no effect. The windows login has been added to the subscription database and is a member of the dbo role.

Please help!

Thanks!

More Info:

This is a nonupdateable transactional publication on SS05 64bit standard edition SP1. The client is .net 2 Sql Express SP2.

Create Link from SQL 2000 to AS400

Hi
I have found an
issue when I try to view or access the tables on the AS400 from my client machine. I receive the following error when I make that attempt:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data Source Name not found and no default driver specified.

Any help anyone can give me on this issue would be greatly appreciated

Thanks
PhilHi,

Could you be more specific?

You need to create link from SQL 2000 to AS400 but which is the AS400 database?
You have installed the adequate driver?

My apologies for my english. I currently write in portuguese.

Regards,

Dlson Castro.
delson_castro@.ig.com.br

Originally posted by pholtquist
Hi
I have found an
issue when I try to view or access the tables on the AS400 from my client machine. I receive the following error when I make that attempt:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data Source Name not found and no default driver specified.

Any help anyone can give me on this issue would be greatly appreciated

Thanks
Phil|||It sounds like the error is from the linked server defined incorrectly on SQL Server.

You say yhou are accessing the tables from your client machine. If the SQL Server is on a different machine, remember that you need to set up the ODBC DSN (or the 'DB2OLEDB' driver) on the server, not your client.

See sp_addlinkedserver in BOL for a description of what is needed.

Please provide more information if this does not help or is unclear.

HueyStLoui

Originally posted by pholtquist
Hi
I have found an
issue when I try to view or access the tables on the AS400 from my client machine. I receive the following error when I make that attempt:

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
Data Source Name not found and no default driver specified.

Any help anyone can give me on this issue would be greatly appreciated

Thanks
Phil

CREATE ENDPOINT to Expose a single DB instance

I have been looking at this statement for allowing access from a remote location to a specific database on my SQL Server (2005) from a client application.

Is it possible to do this using this method? I see lots of examples using this to expose Web Services of DB objects, however I need the client application to be able to authenticate to and access all objects in the database. If so, could some one provide a simple example of the arguments to use? For example, which protocol arguments are valid for this? I would like to use SQL Server Authentication. Is this possible?

Regards,

-Troy

I think the answer is to create a new instance of SQL Server on a non-standard port and move the DB to that instance. At least that is what I've chosen to do. If anyone knows of a better way, let me know.

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha