hi ,

I am trying to create a local cube olap cube populating data in it from an Ms-Access database.

When i execute the code , it is not able to recognise the level names mentioned.

What must I check to make sure it finds the level names in the access table.

SourceDSN= MyShopDSN - This is a dsn created which links to the Ms-Access database.

I think the connection string has some problem... Pls suggest me a way in which my cube is created using the data from the access database...

Thnx

Loydon
////////////////////////////////////////////////////
Private Sub cmdCreateCube_Click()
On Error GoTo ErrorCall

Dim strConnection As String
Dim strCreateCube As String
Dim strSourceDSN As String
Dim strInsertInto As String
Dim adocon As New ADODB.Connection
Dim strLocation As String

strLocation = "LOCATION=c:\CubeCreated.cub"
strSource = CubeConnection(1)
strCreateCube = GetCreateCubeString
strInsertInto = GetInsertCubeString
strConnection = strLocation & ";" & strSource & ";" & vbCrLf & strCreateCube & ";" & vbCrLf & strInsertInto

adocon.ConnectionString = strConnection
adocon.Provider = "MSOLAP"
MsgBox strConnection

adocon.Open
MsgBox ("Cube Created")
adocon.Close
MsgBox Err.Number & Err.Description

ProcExit:
Exit Sub
ErrorCall:
MsgBox Err.Number & Err.Description & Err.Source
Resume ProcExit

End Sub

Private Function GetCreateCubeString() As String

Dim strCreateCube As String

strCreateCube = strCreateCube & "CREATECUBE= CREATE CUBE SHOP " & vbCrLf
strCreateCube = strCreateCube & " (" & vbCrLf

strCreateCube = strCreateCube & " DIMENSION STORE," & vbCrLf
strCreateCube = strCreateCube & " LEVEL [ALL STORE] TYPE All," & vbCrLf
strCreateCube = strCreateCube & " LEVEL [STORE_NAME]," & vbCrLf
strCreateCube = strCreateCube & " LEVEL [STORE_CITY ]," & vbCrLf
strCreateCube = strCreateCube & " DIMENSION SProduct," & vbCrLf
strCreateCube = strCreateCube & " LEVEL [All Products] TYPE All," & vbCrLf
strCreateCube = strCreateCube & " LEVEL [SProduct_Style]," & vbCrLf
strCreateCube = strCreateCube & " LEVEL [SProduct_Color]," & vbCrLf
strCreateCube = strCreateCube & " MEASURE [Unit Sales] Function SUM "

strCreateCube = strCreateCube & ")"

GetCreateCubeString = strCreateCube

End Function

Private Function GetInsertCubeString() As String

Dim strInsertInto As String

strInsertInto = strInsertInto & "INSERTINTO= INSERT INTO SHOP " & vbCrLf
strInsertInto = strInsertInto & " (" & vbCrLf
strInsertInto = strInsertInto & " SStore.[SStore.NAME]," & vbCrLf
strInsertInto = strInsertInto & " SStore.[SStore.CITY]," & vbCrLf
strInsertInto = strInsertInto & " SProduct.[SProduct.Style]," & vbCrLf
strInsertInto = strInsertInto & " SProduct.[SProduct.Color]," & vbCrLf
strInsertInto = strInsertInto & " Measures.[Unit Sales]" & vbCrLf
strInsertInto = strInsertInto & ")" & vbCrLf

strInsertInto = strInsertInto & "SELECT" & vbCrLf
strInsertInto = strInsertInto & " sales_fact_1997.[SStoreStore Name]," & vbCrLf
strInsertInto = strInsertInto & " sales_fact_1997.[SStoreStore City]," & vbCrLf
strInsertInto = strInsertInto & " sales_fact_1997.[SProductProduct Style]," & vbCrLf
strInsertInto = strInsertInto & " sales_fact_1997.[SProductProduct Color]," & vbCrLf
strInsertInto = strInsertInto & " sales_fact_1997.[measures:Unit Sales]" & vbCrLf
strInsertInto = strInsertInto & " FROM sales_fact_1997 " & vbCrLf
GetInsertCubeString = strInsertInto

End Function

Private Function CubeConnection(iDBType As Long) As String

CubeConnection = "Provider=Microsoft OLE DB Provider for OLAP Services 8.0;DATA SOURCE=SERVER NAME;SOURCE_DSN=MyShopDSN"""

End Function