将Power BI数据导出到SQL Server

将Power BI数据导出到SQL Server

2019年4月更新:如果您有兴趣将数据模型从Power BI Desktop或Power BI Service导出到CSV或SQL Server 看一下这个. The method 在这里解释 仅适用于具有XMLA端点连接的Power BI Premium或嵌入式功能。

在之前的博客文章中,我解释了如何将Power BI数据导出到Excel和CSV 这里这里。如本文所承诺的,我将解释如何将数据从Power BI Desktop导出到SQL Server。

汉斯·彼得·菲斯特 已经解释了如何 export data from Power BI桌面 to SQL服务器 using R scripts。尽管汉斯做得很出色,但是如果您没有任何R经验,甚至不知道如何安装和调用R库,要使其正常工作可能会有些困难。关于我自己,我不是R家伙,但是,谁知道,也许我会做到。 微笑 

但是,如果您不想使用R,该怎么办?如果您对BI的投入比对分析的投入更大,那么使用R可能并不是您的最佳选择。幸运的是,还有另一种将Power BI数据导出到SQL Server的方法,它对BI更友好。您可以使用SSIS(SQL Server集成服务)将Power BI数据导出到SQL Server。因此,如果您熟悉SSIS,则可能是您的首选。

关于Hans,在本文中,我将详细解释他将数据从Power BI Desktop导出到SQL Server的方法,以便对R不太熟悉的任何人都可以使用它。我还将说明如何使用SSIS将数据从Power BI Desktop导出到SQL Server。如果您知道其他方法,请在下面的评论部分中告诉我。

Exporting Data from Power BI桌面 to SQL服务器 with R

如前所述,汉斯已经解释了这种方法 这里。因此,我无法确切解释他的所作所为,但是,我使用他的方法将数据从现有的Power BI Desktop模型导出到SQL Server,并逐步进行解释。

要求

要使此方法有效,您需要:

  • Latest version of Power BI桌面, you can download it from 这里
  • 可以访问您自己的计算机上或本地网络中的服务器上的SQL Server实例,以将数据导出到
  • 无论是安装Windows R还是可以从以下位置下载 这里 或使用现有R-Server或安装SQL Server 2016 R服务
  • 安装R的RODBC库,可以从以下位置下载该库 这里

注意: 我尚未安装R Studio,也没有出现任何问题。

为R和SQL Server R服务安装RODBC库

如前所述,您可以安装R或SQL Server R服务或R-Server,但是由于我自己还没有尝试过R-Server,所以我仅说明如何在R和SQL Server R服务中安装RODBC。

您必须从上面提供的链接下载该库,然后解压缩包含“ 远程ODBC”文件夹的zip文件的内容。然后,您真正需要做的就是将“ 远程ODBC”复制到“ Program Files”文件夹中R或SQL Server 2016文件夹中的“ library”文件夹中。

R中的库文件夹

SQL服务器 2016中的库文件夹

它是如何工作的?

打开一个您愿意将其数据导出到SQL Server表中的现有Power BI Desktop模型,然后执行以下步骤:(我使用在“ AdventureWorksDW. You can download my Power BI桌面 model at the end of this post.)

  • Open the Power BI桌面 model
  • 点击“编辑查询”
  • 右键单击要导出到SQL Server的任何所需表,然后单击“参考”

在Power BI中复制查询

  • 将重复的表重命名为“ Internet Sales Export”

Query Editor in Power BI桌面

  • 现在,从功能区的“转换”选项卡中单击“运行R脚本”
  • 复制并粘贴以下R脚本:
library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server; server=SQL_SERVER_INSTANCE; Database=SQLDB")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="TABLE_NAME",rownames=FALSE, safer=FALSE, append=TRUE)
close(conn)
  • 用对象名称替换突出显示的部分,然后单击“确定”。

使用R将Power BI数据导出到SQL Server

注意: 如果您有多个SQL Server实例,则需要在服务器名称中添加一个额外的“ \”(反斜杠)。第一个反斜杠被认为是转义字符。因此,您的脚本应如下图所示。

用于将Power BI数据导出到SQL Server的R脚本

如果一切正常,那么您将在查询编辑器中看到一个空表,而没有任何错误消息。现在打开SSMS并查询“ Exported_From_PowerBI”表,也可以看到导出的数据。

SQL服务器管理Studio

注意: 如您在“运行R脚本”窗口中所见,脚本块后有一个描述,显示您当前的R主目录。

R 首页 Directory in Power BI桌面

您可以从Power BI桌面选项中进行如下更改:

  • 点击“文件”菜单
  • 点击“选项和设置”
  • 点击“选项”

Power BI桌面 Options

  • 点击“ R脚本”
  • 从下拉列表中选择“ Detected R home directory”,然后单击“确定”。

Changing R 首页 Directory in Power BI桌面 Options

注意: 如您所见,我们正在修改查询。这就是为什么我从基本查询中创建了重复项的原因。您可能需要将R脚本放入基本查询中,然后在将数据导出到SQL Server之后将其删除。

Hans简要介绍了R脚本,因此,我鼓励您看一下他的文章,以更好地理解R脚本。有关更多详细信息 看一下这个.

我在包含超过1100万行的更大的表上尝试了上述方法,我应该说性能不是很好。导出大约150万行花了超过15分钟的时间,这并不令人印象深刻。

Exporting Power BI桌面 Data to SQL服务器 Using SSIS

通过这种方法,我从SSIS连接到Power BI Desktop,以将数据导出到SQL Server。我详细解释了如何连接到Power BI Desktop 这里.

要求

  • Latest version of Power BI桌面
  • 访问SQL Server的实例
  • 访问SSIS(SQL Server集成服务)
  • 固态硬盘(SQL Server数据工具),可以下载 这里。我使用SSDT 2015

它是如何工作的?

如前所述,我们将使用SSIS程序包中的随机本地端口连接到Power BI Desktop,然后将数据从所需表导出到SQL Server。

注意:请记住,如果关闭Power BI Desktop文件并重新打开,则随机端口号将更改。因此,此方法是用于将大量数据从Power BI Desktop导出到SQL Server的临时解决方案。 

首先,我们需要查找Power BI Desktop创建的本地SSAS实例的端口号。要了解有关Power BI Desktop端口号的更多信息 看一下这个.

要快速找到端口号:

  • 导航到以下目录:

%LocalAppData%\ Microsoft \ Power BI桌面 \ AnalysisServicesWorkspaces

Power BI桌面 Temp Folder

  • 应该有一个“ AnalysisServicesWorkspaceXXX”文件夹,其中XXX是一个随机数。打开那个文件夹
  • 打开“数据”文件夹
  • 在文件夹中找到“ msmdsrv.port.txt”并打开它。它包含Power BI Desktop使用的随机本地SSAS实例端口号

Finding Power BI桌面 Local Port Number in Temp Dir

现在我们需要在SSDT中创建一个SSIS项目。

  • 打开SSDT
  • 创建一个新的“集成服务”项目
  • 在控制流上放置“数据流任务”
  • 在数据流中添加“ OLEDB源”
  • 双击OLEDB Source打开“ OLEDB Source Editor”
  • 单击“新建”以创建新的OLEDB连接管理器
  • 再点击一次“新建”
  • 将“提供程序”设置为:“ Analysis Services的本地OLEDB \ Microsoft OLEDB提供程序”
  • 将“ Location:”设置为“ localhost:XXXXX”,其中XXXXX是您的本地Power BI端口号
  • 从下拉列表中选择“初始目录”
  • 单击“测试连接”以确保连接成功
  • 您已多次单击“确定”以返回到“ OLEDB源代码编辑器”

创建SSIS连接管理器

  • 到目前为止,我们已经创建了一个连接管理器,您可以在“ OLEDB源代码编辑器”中看到它。
  • 通过单击“表名称或视图”下拉列表,您可以看到Power BI Desktop模型中存在所有表。

Browsing Power BI桌面 in SSIS

  • 如您所见,模型中有一堆在Power BI Desktop中看不到的“ LocalDate Table”。它们是Power BI Desktop自动创建的隐藏日期表,以支持时间智能。我们不会从列表中选择一个表,因为最后会出现错误消息。
  • 将“数据访问模式”设置为“ SQL命令”,然后在“ SQL命令文本”框中编写以下简单的DAX代码。我知道,它说的是SQL命令,但是,我们放置了DAX查询。相信我,它有效 微笑

在SSIS中运行DAX查询

  • 点击“列”以查看表格的列,然后点击确定

OLE DB源代码编辑器

  • 在数据流上放置“ SQL服务器目标”
  • 将“ OLE DB源”连接到“ SQL服务器目标”
  • 双击“ SQL服务器目标”
  • 单击“新建”为我们的SQL数据库创建一个新的连接管理器
  • 再次点击“新建”
  • 提供程序应为“本地OLE DB \ SQL服务器本机客户端”
  • 输入您要将数据导出到的“服务器名称”
  • 选择或输入数据库名称
  • 测试连接
  • 然后,您需要多次单击“确定”以返回到“ SQL目标编辑器”

创建SSIS连接管理器

  • 单击“新建”以创建一个新表以获取我们的数据
  • 查看生成的T-SQL,您将很快了解create table语句将不起作用。复制代码并粘贴到文本编辑器中,或粘贴到SSMS中并整理。您可能还想为新表使用一个有意义的名称。

从SSIS在SQL Server中创建新表

  • 将代码复制/粘贴回“创建表”窗口,然后单击“确定”。

从SSIS在SQL Server中创建新表

SQL目标编辑器

  • 单击“映射”,然后将所有“输入列”映射到“目标列”,然后单击确定

SQL目标编辑器中的映射列

  • 而已。您只需要立即运行该程序包

SSIS包运行

即使导出更多的行,此方法也能很好地工作。

如果您有其他任何想法,反馈等,我很想知道。因此,请在下面的评论部分中留下您的想法。

Download Power BI桌面 model used in 本文

24 thoughts on “将Power BI数据导出到SQL Server

  1. Muito Bom dia!
    Achei要求我解决所有问题。
    哥斯达黎加国务部长
    Veja! Fiz tudo da forma comovocêexplicou e deu tudo certo,no entanto,quando efetua a consulta no SQL服务器算法,os dadossãotriplicados。想象一下没有实例,例如Minha Tabela原始Tinha apenas 10 linhas,通过Power BI进行批量生产,或通过30 linhas进行SQL结果查询。
    Muito estranho! Nunca vi isso!
    Se puder me ajudar ficarei grato。
    E novamenteparabénspelo artivo。

  2. 感谢您分享您的想法。

    我有个问题。

    在教程的第五步中:(将“位置:”设置为“本地主机:XXXXX”,其中XXXXX是您的本地Power BI端口号),’不知道我的端口号在哪里。

    你可以帮帮我吗?

    1. 嗨,费利佩,

      欢迎使用BI Insight。
      也许你’re using Power BI桌面 Store edition.
      有几种方法可以获取我在其中介绍过的端口号 本文.
      以下方法将帮助您使用任何版本的Power BI Desktop查找本地端口号:
      1-Open any desired PBIX file in Power BI桌面
      2-打开Windows任务管理器
      三按“More 细节 ”
      4查找“Power BI Desktop” from “Apps” list
      5扩展“Power BI Desktop”
      6右键单击“Power BI Desktop”在较低级别中,然后单击“Go to 细节 ”
      Windows任务管理器

      7在“details”标签上,您将看到正在运行的Power BI Desktop实例的PID号
      Windows任务管理器详细信息

      8-Open CMD(命令行),然后键入以下命令,后跟您在先前步骤中获得的PID编号,然后按Enter
      netstat / ano |的发现484
      9-下面的屏幕截图中突出显示的部分是您的Power BI Desktop本地端口号:
      Use CMD to get Power BI桌面 Port Number

      希望能有所帮助。

      干杯

  3. 哈罗·索海尔(Halo Soheil),谢谢你的精彩文章。
    如果模型不在本地文件中而是已发布,则可以帮助您如何从Power BI服务获取数据到SQL。
    原因是,该模型会定期从许多来源刷新,我们需要将正确的新数据返回到SQL(本地或Azure)。
    非常感谢您的帮助。
    瓦塞克

      1. 嗨Soheil:
        从PBI服务获取数据时,可以从DAX度量和计算列中获取输出吗?
        伟大的文章顺便说一句!

        1. 嗨,您好,
          欢迎使用BIInsight,并感谢您的反馈。
          那’的确是一个很大的问题。
          答案是不。原因是,当您连接到Power BI Service中的数据集时,’重新连接到没有’在Power BI Desktop中不存在。从Power BI Desktop将Live连接到SSAS实例时,也是如此。
          在本文描述的方法中,我们将Power BI Desktop视为SSAS Tabular的实例。因此,原理类似于您连接到SSAS实例时的原理。这意味着你’d需要连接到实例,然后浏览/指定当前目录(数据模型)。
          尽管数据模型位于另一个实例中,但没有要指定的目录。因此,您仍然可以连接到实例,但是没有模型可以触发您的查询。
          希望能有所帮助。
          干杯

  4. 嗨,myy家庭成员!我想说的是这篇文章很棒,写得很好,并附带了几乎所有重要的信息。

    I’d想看看这样的额外帖子。

  5. 嗨Soheil,

    这篇文章真棒,我’我对此表示感谢。我有一个问题:
    我们有Power BI随机指定的端口号。我想我们每天都有不同的随机端口号。我们如何针对这些端口号优化连接管理器?当我们想将数据导入到Sql Server时,由于随机端口号,我们需要构建一个不同的连接管理器。有什么方法可以优化此连接?

    感谢土耳其!

    最好的祝福

      1. 你好
        我需要将PowerBI数据导出到另一台计算机 –并与描述的方法我有一条错误消息“所选数据源在远程计算机上。批量插入操作只能在此数据源所在的计算机上执行。”
        您能帮我解决问题吗?
        预先感谢您,Jiri h

  6. 你好

    感谢提供这篇好文章。我设法使用第一种方法(R)从Power Bi连接到SQL Server–但是,我得到此异常错误:

    DataFormat.Error:类型的异常‘Microsoft.Analytics.Modules.R.ErrorHandling.RException.Primitives.NotValidRDataException’ was thrown.

    谁知道为什么?

  7. 他们是否可以通过计划任何作业来自动加载此数据的任何方式?一世’我正在使用SQL SERVER数据库中的SSRS导出数据,我可以’•每月手动进行一次。请让我知道

  8. 感谢本文!我通过R和SSIS尝试了两种方法。通过R的方法对我来说比较成功,因为它至少运行了ALMOST而没有出错。我确实遇到的错误是两个字段,它们是日期字段,尽管它们是导入的,但它们的值仅读为“Microsoft.OleDb.Date”。想知道您是否对这些日期字段有任何建议吗?

    但是不幸的是,对于SSIS方法,它只是行不通。我成功设置了初始源和目标组件,但问题仍然出在日期字段上。由于DT_DATE转换问题,任务无法处理日期字段,因此执行不会运行。我试图在源和目标之间输入一个转换任务以转换日期字段,但这也不起作用。最后,我确实删除了两个日期字段,并且执行任务至少开始了:最初它运行顺利,但是由于某种未知原因,它失败了大约70%(即,还有30%的记录要导入) )。 SSIS真是个问题!

    不知道您是否可以就日期字段提出建议,将尝试研究至少在没有日期字段导入错误的情况下使R方法有效…

  9. 尼斯邮报,我正在尝试从共享给我的PowerBi报表中导入数据。我无权访问他们的数据库。该报告包含多个数据集。这种方法对我没有用。

  10. 很棒的文章。很有帮助。是通过R脚本连接到Power BI服务的唯一方法。一世’m assuming you can’通过您的SSIS路线来做到吗?尝试在Power BI中找到自动刷新数据进入SQL的最佳方法。谢谢!

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.