I have this national cube from wich I want to build sub-cubes. One for each member in the "Sales Territory" level of a Dimension.

The tricky part is that I want to include all the descendants of that Territory as well as all of it's ancestors. I am trying to build the cube using the CREATE CUBE Statement and then doing 3 inserts.

One for the territory and it's descendants, One for it's imediate ancestor that is located 1 level above, and One for it's imediate ancestor that is located 2 levels above.

My code is long but here it is anyway:
Code:
Option Explicit

Private Sub Command1_Click()
Dim cnCube As New Connection
Dim s As String
Dim strProvider As String
Dim strDataSource As String
Dim strSourceDSN As String
Dim strSourceDSNSuffix As String
Dim strCreateCube As String
Dim strInsertInto As String

On Error GoTo Error_cmdCreateCubeFromDatabase_Click

strProvider = "PROVIDER=MSOLAP"

strDataSource = "DATA SOURCE=c:\A1.cub"

strSourceDSN = "Provider=MSOLAP.2;Integrated Security=SSPI;Persist Security Info=False;Data Source=lavwd000337;Initial Catalog=TSA;Client Cache Size=25;Auto Synch Period=10000"

strCreateCube = "CREATECUBE=CREATE CUBE TSAA1( "
strCreateCube = strCreateCube & "DIMENSION [Years],"
        strCreateCube = strCreateCube & "LEVEL [All Years]  TYPE ALL,"
        strCreateCube = strCreateCube & "LEVEL [Year] ,"
        strCreateCube = strCreateCube & "LEVEL [Month] ,"

strCreateCube = strCreateCube & "DIMENSION [Divisions],"
        strCreateCube = strCreateCube & "LEVEL [All Divisions]  TYPE ALL,"
        strCreateCube = strCreateCube & "LEVEL [Division] ,"
        strCreateCube = strCreateCube & "LEVEL [District] ,"
        strCreateCube = strCreateCube & "LEVEL [Territory] ,"
        strCreateCube = strCreateCube & "LEVEL [Zone] ,"
        strCreateCube = strCreateCube & "LEVEL [FSA] ,"
        strCreateCube = strCreateCube & "LEVEL [Outlet] ,"

strCreateCube = strCreateCube & "DIMENSION [Products],"
        strCreateCube = strCreateCube & "LEVEL [All Products]  TYPE ALL,"
        strCreateCube = strCreateCube & "LEVEL [Market] ,"
        strCreateCube = strCreateCube & "LEVEL [Class] ,"
        strCreateCube = strCreateCube & "LEVEL [Brand] ,"
        strCreateCube = strCreateCube & "LEVEL [Product] ,"

strCreateCube = strCreateCube & "MEASURE [Aventis Sales] "
    strCreateCube = strCreateCube & "Function Sum "
    strCreateCube = strCreateCube & "Format '#.#$')"

strInsertInto = strInsertInto & "INSERTINTO=INSERT INTO TSAA1( Years.[Year], " & _
                                                               "Years.[Month] , " & _
                                                               "Divisions.[Division], " & _
                                                               "Divisions.[District], " & _
                                                               "Divisions.[Territory], " & _
                                                               "Divisions.[Zone], " & _
                                                               "Divisions.[FSA], " & _
                                                               "Divisions.[Outlet], " & _
                                                               "Products.[Market], " & _
                                                               "Products.[Class], " & _
                                                               "Products.[Brand], " & _
                                                               "Products.[Product], " & _
                                                               "Measures.[Sales]) "

strInsertInto = strInsertInto & "SELECT [TSA National].[Years:Year], " & _
                                "[TSA National].[Years:Month], " & _
                                "[TSA National].[Divisions:Division], " & _
                                "[TSA National].[Divisions:District], " & _
                                "[TSA National].[Divisions:Territory], " & _
                                "[TSA National].[Divisions:Zone], " & _
                                "[TSA National].[Divisions:FSA], " & _
                                "[TSA National].[Divisions:Outlet], " & _
                                "[TSA National].[Products:Market], " & _
                                "[TSA National].[Products:Class], " & _
                                "[TSA National].[Products:Brand], " & _
                                "[TSA National].[Products:Product], " & _
                                "[TSA National].[Products:Sales] " & _
                                "FROM [TSA National]"

s = strSourceDSN & ";" & strCreateCube & ";" & strInsertInto & ";"

Screen.MousePointer = vbHourglass
cnCube.Open s
Screen.MousePointer = vbDefault
Exit Sub

Error_cmdCreateCubeFromDatabase_Click:
    Screen.MousePointer = vbDefault
    MsgBox Err.Description
    If Err.Number <> 0 Then
   
   MsgBox "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & Err.Description, , "Error", Err.HelpFile, Err.HelpContext
   End If
End Sub
When I execute this code, I get the following error message:
"Cannot accept cube file creation for server connection"

I ave been bagning my head on my desk for such a long time. Can anybody help me with this