使用CopyFromRecordset仅导入1条记录，SQL SSAS Cube存在多条数据

问题根源分析 #

你的代码存在两个核心问题导致仅返回单条记录：

1. MDX查询行集语法错误：用逗号分隔Rows轴的层级，会将三个层级的成员视为单个元组而非交叉所有可能组合，结果集被限制；
2. Where子句语法错误：成员引用中多余的空格（如. & [P004]）会导致MDX解析异常，过滤逻辑未正确生效。

修改后的完整代码 #

Sub OLAP_Connect()
    Dim Cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set Cn = New ADODB.Connection

    '1) 连接配置
    Cn.ConnectionString = "Provider=MSOLAP; Data Source=CZSTRDWHSQL01; Initial Catalog=CPI_SSAS;"

    '2) 修正后的MDX查询
    Dim Row_1 As String, Row_2 As String, Row_3 As String, Col_1 As String, SQL As String
    Row_1 = "[Company].[Company].[Company]"
    Row_2 = "[Account].[Account Code].[Account Code]"
    Row_3 = "[Account].[Account Full Name].[Account Full Name]"
    Col_1 = "[Measures].[Turnover Accounting Currency]"
    
    ' 关键修正：用*替代逗号实现层级交叉，同时修正Where子句的空格问题
    SQL = "SELECT " & _
          "NON EMPTY (" & Col_1 & ") ON COLUMNS, " & _
          "NON EMPTY (" & Row_1 & " * " & Row_2 & " * " & Row_3 & ") ON ROWS " & _
          "FROM [cpi dwh] " & _
          "WHERE (" & _
              "[Company].[Company Code].&[P004], " & _
              "[Company].[Company CountryCode].&[POL], " & _
              "[Transaction Date].[Month Number].&[1], " & _
              "[Transaction Date].[Year].&[2022]" & _
          ")"

    '3) 连接并导入数据
    Cn.Open
    Set rs = New ADODB.Recordset
    ' 针对OLAP优化游标设置
    rs.CursorLocation = adUseClient
    rs.Open SQL, Cn, adOpenForwardOnly, adLockReadOnly
    
    Range("A4").CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing

    ' 添加友好列名
    Cells(1, 1) = "Company"
    Cells(1, 2) = "Account Code"
    Cells(1, 3) = "Account Full Name"
    Cells(1, 4) = "Turnover (Accounting Currency)"
End Sub

关键修改点说明 #

• MDX行集逻辑修正：MDX中用*表示层级间的笛卡尔积，替换逗号后会返回所有符合条件的成员组合；
• Where子句语法修正：移除成员引用中的多余空格，确保过滤条件被正确解析；
• 游标适配优化：设置CursorLocation = adUseClient并使用adOpenForwardOnly游标，更适配OLAP数据源的读取场景；
• 列名可读性优化：将原始层级路径替换为简洁易懂的列名，提升表格可读性。

验证步骤 #

1. 先在SSMS的MDX查询窗口执行修正后的SQL语句，确认返回多条记录；
2. 运行VBA前，确保Excel已启用ADODB引用（VBA编辑器→工具→引用→勾选"Microsoft ActiveX Data Objects x.x Library"）。

内容的提问来源于stack exchange，提问作者Tom KS