How to create cube through vb or java code

Last Post 01 Sep 2006 12:09 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

22 Mar 2006 10:26 PM

I want to create cube through vb or java code.
I want to create new cube.
I am having MS-SQL as backend.
For eg. There is a company database, and having tables as Employee, Department etc.
I want to create new cube as EMP which has dimensions as dno, age, sex etc.
And Measures as Salary.
Currently there is no cube for this database.
So I have to create new cube as well as process it through vb or java code.

I have found CREATE LOCAL CUBE and CREATE GLOBAL CUBE but they work on existing cubes only.
I want to create new cube on server.

I want to know if it is possible or not?
If it is possible then HOW?

New Member
New Member

01 Sep 2006 12:09 AM
This is a sample for creating a cube:

Private Sub AddCube()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore

Dim strDBName As String
Dim strCubeName As String
Dim strJoin As String

' Initialize variables for the database and
' cube names.
strDBName = "InventoryDB"
strCubeName = "SalesCube"

' Join the fact table to the Product table.
' sales_fact_2005.product_id = product.product_id
strJoin = "(""sales_fact_2004"".""product_id""=""product"".""product_id"")"
strJoin = strJoin & " AND "

strJoin = strJoin & "(""sales_fact_2004"".""store_id""=""store"".""store_id"")"

dsoServer.Connect "LocalHost"

If dsoServer.MDStores.Find(strDBName) = False Then
MsgBox "Database " & strDBName & " is not found."
Set dsoDB = dsoServer.MDStores(strDBName)

If dsoDB.DataSources.Count = 0 Then
' No data source
MsgBox "Database " & strDBName & " has no data sources."
ElseIf dsoDB.Dimensions.Count = 0 Then
' No dimensions
MsgBox "Database " & strDBName & " has no dimensions."
ElseIf dsoDB.MDStores.Find(strCubeName) Then
' Cube already exists
MsgBox "Cube " & strCubeName & " already exists " & _
"in database" & strDBName
' Add the cube to the database.
Set dsoCube = dsoDB.MDStores.AddNew(strCubeName)

' Further define the cube.
With dsoCube
' Provide the data source for the cube.
.DataSources.AddNew dsoDB.DataSources(1).Name

' Provide the fact table for the cube.
.SourceTable = """sales_fact_2004"""

' Provide an estimated number of rows.
.EstimatedRows = 100000

' Add the Products and Stores shared dimensions.
.Dimensions.AddNew "Products"
.Dimensions.AddNew "Stores"

' Join the fact table with the dimension tables.
.JoinClause = strJoin

' Update the database.
End With

' Inform the user.
MsgBox "Cube " & strCubeName & _
" created and dimensions added"

End If
End If

End Sub

Kalman Toth, Database, Data Warehouse and BI Architect
The Best SQL Server 2005 Training in the World

Acceptable Use Policy