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 Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
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
You are not authorized to post a reply.

Acceptable Use Policy