Python可连接Power BI端点，R的DBI&odbc包连接失败求解决

针对你用R的DBI/odbc包连接Power BI端点失败的问题，结合你已成功运行的Python代码，调整后的可行方案如下：

已验证的Python连接代码 #

# 定义连接字符串参数
Provider = 'MSOLAP.8'
Integrated_Security = 'SSPI'
Persist_Security_Info = 'True'
Initial_Catalog = 'xxx'
Source = 'pbiazure://api.powerbi.com'
MDX_Compatibility = '1'
SafetyOptions = '2'
MDX_Missing_Member_Mode = 'Error'
Identity_Provider = 'https://login.microsoftonline.com/common'
API_Link = 'https://analysis.windows.net/powerbi/api'
Model_ID = 'xxx'
Update_Isolation_Level = '2'

# 定义连接字符串
dataset_conn_string = f'''
    Provider={Provider}
;   Integrated Security={Integrated_Security}
;   Persist Security Info={Persist_Security_Info}
;   Initial Catalog={Initial_Catalog}
;   Data Source={Source}
;   MDX Compatibility={MDX_Compatibility}
;   Safety Options={SafetyOptions}
;   MDX Missing Member Mode={MDX_Missing_Member_Mode}
;   Identity Provider={Identity_Provider}, {API_Link}, {Model_ID}
;   Update Isolation Level={Update_Isolation_Level}
'''

# 建立连接
conn = adodbapi.connect(dataset_conn_string, timeout = 600)

调整后的R连接代码及关键说明 #

核心调整点 #

• 必须指定对应MSOLAP.8的ODBC驱动名称，常见为"Microsoft OLE DB Provider for Analysis Services 18.0"（可通过系统ODBC数据源管理器查看实际驱动名称）
• 参数名称需与ODBC驱动要求严格匹配，比如Python中的Data Source对应R中的Data_Source
• 保留SSPI认证配置，确保权限验证流程一致

完整R代码 #

# 定义连接参数
Integrated_Security <- "SSPI"
Persist_Security_Info <- "True"
Initial_Catalog <- "xxx"
Source <- "pbiazure://api.powerbi.com"
MDX_Compatibility <- "1"
SafetyOptions <- "2"
MDX_Missing_Member_Mode <- "Error"
Identity_Provider <- "https://login.microsoftonline.com/common"
API_Link <- "https://analysis.windows.net/powerbi/api"
Model_ID <- "xxx"
Update_Isolation_Level <- "2"

# 建立连接
conn <- DBI::dbConnect(
  odbc::odbc(),
  Driver = "Microsoft OLE DB Provider for Analysis Services 18.0", # 根据你的系统驱动调整
  Data_Source = Source,
  Initial_Catalog = Initial_Catalog,
  Integrated_Security = Integrated_Security,
  Persist_Security_Info = Persist_Security_Info,
  MDX_Compatibility = MDX_Compatibility,
  Safety_Options = SafetyOptions,
  MDX_Missing_Member_Mode = MDX_Missing_Member_Mode,
  Identity_Provider = paste(Identity_Provider, API_Link, Model_ID, sep = ", "),
  Update_Isolation_Level = Update_Isolation_Level,
  timeout = 600
)

额外注意事项 #

• 确保系统已安装Microsoft Analysis Services OLE DB Provider（MSOLAP.8对应的驱动），版本需与R的位数（32/64位）一致
• 若驱动名称不匹配，打开ODBC数据源管理器（64位对应C:\Windows\System32\odbcad32.exe，32位对应C:\Windows\SysWOW64\odbcad32.exe）查看“驱动”列表中的准确名称
• 检查当前Windows用户是否拥有目标Power BI数据集的访问权限，SSPI认证会使用当前用户身份进行验证

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