Hi!
I'm searching if it is possible to use DSO library to execute a create cube
statement (like = Create cube myCube...Insert into.... Select from
MyCubeOnline)


Example :
Set dso = Server.CreateObject("DSO.Server")
dso.Connect "Localhost"

set dsoDB = dso.MDStores("MyCATALOG")
txt = "PROVIDER=MSOLAP.2;SOURCE_DSN='PROVIDER=MSOLAP.2;D ATA
SOURCE=Localhost;INITIAL CATALOG=myCatalog;';CREATECUBE=Create CUBE [MyCube]
(DIMENSION [myDimension],LEVEL [(ALL) myDimension] TYPE ALL,LEVEL [myLevel]
,MEASURE [Hits] FUNCTION COUNT); INSERTINTO ... etc

Thanks!

--
Message posted via http://www.sqlmonster.com

You can create local cubes using the CREATE LOCAL CUBE statement using the
ADO library. You can also create local cubes using CREATE GLOBAL CUBE using
the ADOMD library. VB6 code samples of both below.

When you use CREATE LOCAL CUBE, you include the definition of all calculated
members. When you use CREATE GLOBAL CUBE, all calculated members are
included automatically. The two local cubes created by these procedures
should be identical, except for the calculated members. The first one will
have the one defined calculated member, Profit. The second one will include
all calculated members that currently exist in the server cube.

Tim Peterson
www.localcubetask.com

CREATE LOCAL CUBE:

'You must include the following in the project's references:
'Microsoft ActiveX Data Objects 2.x Library

'When you run this procedure, the cube will be created in the location
specified
'on the first line after the variable declarations

'This code module was generated by the OLAP Client Manager Add-In.
'Free demo available from www.localcubetask.com
'Version: 8.40
'Build: 124

Public Sub Main()
On Error GoTo ProcErr

Dim adocon As New ADODB.Connection 'If this errors, you need to include
references as stated above!
Dim sConnection As String
Dim sLocation As String
Dim sSourceDSN As String
Dim sCreateCube As String
Dim sInsertInto As String
Dim sSelect As String

sLocation = "LOCATION=C:\Program Files\OLAP Client Management
System\Data\FoodMart 2000_Sales.cub; "
sSourceDSN = "SOURCE_DSN=""Provider=MSOLAP.2;Data
Source=SDG-1RVSXZBCI5K;INITIAL CATALOG=FoodMart 2000"";"

sCreateCube = ""
sCreateCube = sCreateCube & "" & vbCrLf
sCreateCube = sCreateCube & "CREATECUBE=CREATE CUBE [Sales]" & vbCrLf
sCreateCube = sCreateCube & " (" & vbCrLf
sCreateCube = sCreateCube & " DIMENSION [Product]," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [All Products] TYPE ALL," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Product Family]
OPTIONS(SORTBYNAME)," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Product Department]
OPTIONS(SORTBYNAME)," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Product Category]
OPTIONS(SORTBYNAME)," & vbCrLf
sCreateCube = sCreateCube & " DIMENSION [Time] TYPE TIME," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Year] TYPE YEAR
OPTIONS(SORTBYNAME)," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Quarter] TYPE QUARTER
OPTIONS(SORTBYNAME)," & vbCrLf
sCreateCube = sCreateCube & "" & vbCrLf
sCreateCube = sCreateCube & " MEASURE [Store Cost] FUNCTION SUM FORMAT
'Standard' TYPE DBTYPE_R8," & vbCrLf
sCreateCube = sCreateCube & " MEASURE [Store Sales] FUNCTION SUM FORMAT
'Currency' TYPE DBTYPE_R8," & vbCrLf
sCreateCube = sCreateCube & "" & vbCrLf
sCreateCube = sCreateCube & " " & vbCrLf
sCreateCube = sCreateCube & " COMMAND (CREATE MEMBER
[Sales].Measures.[Profit] AS '[Measures].[Store Sales]-[Measures].[Store
Cost]', FORMAT_STRING = 'Standard')" & vbCrLf
sCreateCube = sCreateCube & " )"
sInsertInto = ""
sInsertInto = sInsertInto & "" & vbCrLf
sInsertInto = sInsertInto & "INSERTINTO=INSERT INTO [Sales]" & vbCrLf
sInsertInto = sInsertInto & " (" & vbCrLf
sInsertInto = sInsertInto & " " & vbCrLf
sInsertInto = sInsertInto & " " & vbCrLf
sInsertInto = sInsertInto & " [Product].[Product Family].NAME,
[Product].[Product Family].KEY," & vbCrLf
sInsertInto = sInsertInto & " [Product].[Product Department].NAME,
[Product].[Product Department].KEY," & vbCrLf
sInsertInto = sInsertInto & " [Product].[Product Category].NAME,
[Product].[Product Category].KEY," & vbCrLf
sInsertInto = sInsertInto & " " & vbCrLf
sInsertInto = sInsertInto & " [Time].[Year].NAME, [Time].[Year].KEY," &
vbCrLf
sInsertInto = sInsertInto & " [Time].[Quarter].NAME, [Time].[Quarter].KEY,"
& vbCrLf
sInsertInto = sInsertInto & "" & vbCrLf
sInsertInto = sInsertInto & " [Measures].[Store Cost]," & vbCrLf
sInsertInto = sInsertInto & " [Measures].[Store Sales]" & vbCrLf
sInsertInto = sInsertInto & " )"
sSelect = ""
sSelect = sSelect & "" & vbCrLf
sSelect = sSelect & "SELECT " & vbCrLf
sSelect = sSelect & " " & vbCrLf
sSelect = sSelect & " " & vbCrLf
sSelect = sSelect & " " & vbCrLf
sSelect = sSelect & " [Sales].[Product:Product Family],
[Sales].[Product:Product Family!KEY]," & vbCrLf
sSelect = sSelect & " [Sales].[Product:Product Department],
[Sales].[Product:Product Department!KEY]," & vbCrLf
sSelect = sSelect & " [Sales].[Product:Product Category],
[Sales].[Product:Product Category!KEY]," & vbCrLf
sSelect = sSelect & " " & vbCrLf
sSelect = sSelect & " [Sales].[Time:Year], [Sales].[Time:Year!KEY]," &
vbCrLf
sSelect = sSelect & " [Sales].[Time:Quarter], [Sales].[Time:Quarter!KEY],"
& vbCrLf
sSelect = sSelect & "" & vbCrLf
sSelect = sSelect & " [Sales].[Measures:Store Cost]," & vbCrLf
sSelect = sSelect & " [Sales].[Measures:Store Sales]" & vbCrLf
sSelect = sSelect & "FROM [Sales] " & vbCrLf
sSelect = sSelect & " Where (([Sales].[Product:Product Family] =
'[Product].[All Products].[Drink]')" & vbCrLf
sSelect = sSelect & " Or ([Sales].[Product:Product Family] = '[Product].[All
Products].[Food]'))"

sConnection = sLocation & ";" & vbCrLf & sSourceDSN & ";" & vbCrLf &
sCreateCube & ";" & vbCrLf & sInsertInto & vbCrLf & sSelect

adocon.ConnectionString = sConnection
adocon.Provider = "MSOLAP"

'Create the cube!
adocon.Open

adocon.Close

ProcExit:
Exit Sub
ProcErr:
MsgBox Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub



CREATE GLOBAL CUBE:

'You must include the following in the project's references:
'Microsoft ActiveX Data Objects (Multi-Dimensional) 2.7 Library

'When you run this procedure, the cube will be created in the location
specified
'in the STORAGE parameter on the CREATE GLOBAL CUBE line

'This code module was generated by the OLAP Client Manager Add-In.
'Free demo available from www.localcubetask.com
'Version: 8.40
'Build: 124

Public Sub Main()
On Error GoTo ProcErr

Dim cat As New ADOMD.Catalog 'If an error occurs, you need to include the
project references as stated above!
Dim cset As New ADOMD.Cellset
Dim sConnection As String
Dim sLocation As String
Dim sSourceDSN As String
Dim sCreateCube As String

sSourceDSN = "Provider=MSOLAP;Data Source=SDG-1RVSXZBCI5K;INITIAL
CATALOG=FoodMart 2000;"
sCreateCube = ""
sCreateCube = sCreateCube & "" & vbCrLf
sCreateCube = sCreateCube & "CREATE GLOBAL CUBE [Sales] STORAGE 'C:\Program
Files\OLAP Client Management System\Data\FoodMart_2000_Sales.cub' FROM
[Sales]" & vbCrLf
sCreateCube = sCreateCube & "(" & vbCrLf
sCreateCube = sCreateCube & " " & vbCrLf
sCreateCube = sCreateCube & " MEASURE [Sales].[Store Cost]," & vbCrLf
sCreateCube = sCreateCube & " MEASURE [Sales].[Store Sales]," & vbCrLf
sCreateCube = sCreateCube & " MEASURE [Sales].[Sales Count] HIDDEN," &
vbCrLf
sCreateCube = sCreateCube & "" & vbCrLf
sCreateCube = sCreateCube & " DIMENSION [Sales].[Product]" & vbCrLf
sCreateCube = sCreateCube & " (" & vbCrLf
sCreateCube = sCreateCube & " LEVEL [(All)]," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Product Family]," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Product Department]," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Product Category]," & vbCrLf
sCreateCube = sCreateCube & " MEMBER [Product].[All Products].[Drink],"
& vbCrLf
sCreateCube = sCreateCube & " MEMBER [Product].[All Products].[Food]" &
vbCrLf
sCreateCube = sCreateCube & " " & vbCrLf
sCreateCube = sCreateCube & " )," & vbCrLf
sCreateCube = sCreateCube & " DIMENSION [Sales].[Time]" & vbCrLf
sCreateCube = sCreateCube & " (" & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Year]," & vbCrLf
sCreateCube = sCreateCube & " LEVEL [Quarter]" & vbCrLf
sCreateCube = sCreateCube & " " & vbCrLf
sCreateCube = sCreateCube & " )" & vbCrLf
sCreateCube = sCreateCube & ")"

cat.ActiveConnection = sSourceDSN
cset.Source = sCreateCube
Set cset.ActiveConnection = cat.ActiveConnection

'Create the cube!
cset.Open

set cat = nothing

ProcExit:
Exit Sub
ProcErr:
MsgBox Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub



"Ernesto LEITE via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote in
message news:7a7b4ccaf7eb4b0d89790b613c76af70 (AT) SQLMonster (DOT) com...

I would like to add a few more comments to my response.

The CREATE CUBE statement is only used to create local cube files. It cannot
be used to create cubes on the Analysis Server. You can use DSO to create
server cubes programmatically, but that is not done with a single statement.
You have to create each object and add it to the cube separately. Here are
some of the steps:

1. Create a new MDStore object for the database (if necessary) and add it to
the Analysis Server's MDStores collection.
2. Create a new DataSource object (if necessary)
2. Create and add shared dimensions (if necessary) to the Dimensions
collection of the database.
3. Create a new MDStore object for the cube and add it to the database's
MDStores collection.
4. Set the DataSource, the fact table, and other properties of the cube.
5. Add dimensions and measures to the cube.

"Ernesto LEITE via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote in
message news:7a7b4ccaf7eb4b0d89790b613c76af70 (AT) SQLMonster (DOT) com...

Tim

Many thanks for the code for the creation of a local cube.

Once the cube is created how do we do just a full or a incremental
refresh of the cube.

Thanks
Karen

If you have create a local cube using the first code sample (CREATE LOCAL
CUBE), you can use the following code to refresh:

Dim adoCommand As New ADODB.Command
adoCommand.CommandText = "Refresh Cube Sales"
adoCommand.ActiveConnection = adocon
adoCommand.Execute

This code assumes, of course, that you have a connection object (adocon)
which is an open connection to a local cube. If you close the connection and
open it again, by opening it you will already have recreated the local cube.

I don't believe it's possible to do an incremental refresh of a local cube.
The refresh operation creates a completely new cube.

In my experience, if any client application is browsing the local cube, the
refresh will fail, because the client application has a lock on the file.

Hope this is helpful.


<karenmiddleol (AT) yahoo (DOT) com> wrote

Thanks a lot for all yours comments!

--
Message posted via http://www.sqlmonster.com