Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    24

    Unanswered: Create a local cube using MS-Access database

    hi ,

    I am trying to create a local 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

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    I tried your code and got the following:
    1) see attachment
    2) error: Run-time error 3706. Provider cannot be found...

    #2 was encountered on this board earlier: ".... solved the problem myself. Some tables were not placed in the right folder. Sorry ! Julien Rouiller"

    Does yours error out?
    Attached Thumbnails Attached Thumbnails OlapCube2.bmp  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •