The following example demonstrates how to connect to an OLAP server and create a new database, attach a data source, and add a shared dimension and level using the sample FoodMart data provided. After building and running the example code, you should be able to view the new database using the OLAP Manager.
Option Explicit
Public dsoServer As DSO.Server
Const strConnect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=FoodMart;Connect Timeout=15"
'Note: Add command control to form to enable
' the cmdCreateDatabase_Click method
Private Sub cmdCreateDatabase_Click()
On Error GoTo CreateDatabase_Err
Dim dsoDB As DSO.MDStore
Dim dsoDS As DSO.Datasource
'Create Database and add connection string
Set dsoDB = dsoServer.MDStores.AddNew("MyDatabase")
Set dsoDS = dsoDB.Datasources.AddNew("NewSales")
dsoDS.ConnectionString = strConnect
dsoDS.Update
'Create Dimension and set Datasource
Dim dsoDim As DSO.Dimension
Set dsoDim = dsoDB.Dimensions.AddNew("Products")
Set dsoDim.Datasource = dsoDS
dsoDim.FromClause = "product"
dsoDim.JoinClause = ""
'Add Levels
Dim dsoLev As DSO.Level
Set dsoLev = dsoDim.Levels.AddNew("Product Id")
'Point to table and column
dsoLev.MemberKeyColumn = """product_class"".""product_family"""
dsoLev.ColumnSize = 4 'Width of column in bytes
dsoLev.ColumnType = adInteger 'ADODB Data Type
dsoDim.Update
Debug.Print "<<success>>"
Exit Sub
CreateDatabase_Err:
Debug.Print "Error creating new Database"
Debug.Print Err.Description
Err.Clear
End Sub
Private Sub Form_Load()
On Error GoTo FormLoad_Err
'Connect to OLAP Server
Set dsoServer = New DSO.Server
'MyServer is the name of the OLAP server
dsoServer.Connect ("MyServer")
Debug.Print ("Connected")
Exit Sub
FormLoad_Err:
Debug.Print ("Error connecting to server")
Debug.Print Err.Description
Err.Clear
End Sub
Connection string examples are also provided for the following data source providers.
JET 3.51 OLE DB
ConnectionString="Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Persist Security Info=False;" & _
"Data Source=C:\Program Files\OLAP Services\Samples\FoodMart.mdb"
JET 4.0 OLE DB
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=C:\Program Files\OLAP Services\Samples\FoodMart.mdb;" & _
"JET OLEDB:SFP=True;"
OLE DB Provider for ODBC Drivers (Access)
ConnectionString="Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"Data Source=FoodMart;" & _
"Connect Timeout=15"
SQL Server
ConnectionString = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Initial Catalog=FoodMart;" & _
"Data Source={SQL Server};" & _
"Connect Timeout=15"