从Excel和SSMS连接到Power BI Desktop模型

Power BI桌面 电子表格 SSMSPower BI桌面是一个出色的报表创作工具。我也有很多与Tableau合作的经验,我可以说,伙计,Power BI的增长非常快。 Power BI中添加了许多很棒的想法,并且还会有更多的想法。但是,对于您中的某些人来说,是否可能从Excel,SQL Server Management Studio(SSMS)或SQL Server Profiler连接到Power BI Desktop模型?答案是可以的。但是,到底该如何从Excel,SSMS或SQL Server Profiler连接到Power BI Desktop模型?好吧,它对于以下情况可能有用:

  • 使用SQL Server Profiler连接到模型以进行性能调整,监视等
  • 同样,如果您遇到一些性能问题,则可能需要从SSMS连接到模型
  • 您有一个复杂的模型,很难理解,但是您是一个出色的Excel开发人员,因此可以从Excel连接到Power BI Desktop模型,以便可以使用Excel中可用的覆盖范围功能(如命名集)
  • 只是出于好奇!您对在现有模型上编写MDX代码感到好奇,想在Excel等中查看模型的外观

在本文中,我将向您展示如何连接到Power BI Desktop模型,而不管任何用例情况如何。因此,无论您出于何种原因想要连接到Power BI Desktop模型,这篇文章都将帮助您实现目标。

这个怎么运作

Power BI桌面使用xVelocity技术并将数据加载到内存中。它使用SQL Server Analysis Services(SSAS)的本地实例。它通过运行msmdsrv.exe文件来完成工作,该文件位于Power BI Desktop安装文件夹(通常位于程序文件下)的“ bin”文件夹中。 msmdsrv.exe确实是SSAS服务文件。因此,即使您尚未在计算机上安装SSAS,Power BI Desktop也会运行msmdsrv.exe。当Power BI Desktop运行msmdsrv.exe时,它将创建SSAS的本地实例。此本地SSAS实例使用随机端口号,因此在Power BI Desktop未关闭或未从任务管理器中终止msmdsrv.exe之前它将一直有效。

在Power BI桌面文件夹中找到msmdsrv.exe

因此,我们有一个使用随机端口号的SSAS本地实例。因此,仅当我们知道端口号时,我们才应该能够从Excel,SSMS或SQL Server Profiler连接到实例。

注意: 如果您在计算机上安装了SSAS实例,则可以从SQL Server安装路径的“ \ OLAP \ bin”文件夹下找到msmdsrv.exe:

%ProgramFiles%\ Microsoft SQL服务器 \ msasXX.INSTANCE_NAME \ OLAP \ bin

哪个XX是您的SQL Server版本。因此对于SQL Server 2008R2,XX将为10,对于SQL Server 2012,XX将为11,依此类推。 Power BI桌面 \ bin文件夹中的本地msmdsrv.exe文件与SQL Server安装文件夹下的另一个msmdsrv.exe文件之间的区别在于,Power BI Desktop运行的一个是控制台程序,而另一个运行的是控制台程序。 Windows服务程序。

如何查找Power BI Desktop本地端口?

您可以通过多种方法获取端口号。在这篇文章中,我将解释其中的三个。

  • 使用Windows命令提示符(CMD)查找Power BI Desktop本地端口
  • 使用DAX Studio
  • 从Power BI Desktop临时目录中查找本地端口号

使用Windows命令提示符(CMD)查找Power BI Desktop本地端口
  • 以管理员身份运行Windows命令提示符
  • 复制,粘贴并运行以下命令:

任务清单/ FI“映像名称eq msmdsrv.exe” /FI “sessionname eq
安慰”

使用Windows命令提示符(CMD)查找Power BI Desktop本地端口

  • 你应该看到这样的东西

使用Windows命令提示符(CMD)查找Power BI Desktop本地端口

  • 我们需要的是PID
  • 现在运行以下命令,并将从上一条命令获得的PID号放在引号之间

netstat / ano | findstr“13944”

使用Windows命令提示符(CMD)查找Power BI Desktop本地端口

  • 结果应该类似于下面的屏幕快照,其中显示了活动的连接,它们的本地地址是本地IP地址,后跟端口号。这就是我们正在寻找的。

使用Windows命令提示符(CMD)查找Power BI Desktop本地端口

注意: 结果表的列名称分别从左到右为:

活动连接,本地地址,外部地址,状态,PID

如前所述,活动连接是我们感兴趣的连接,因此冒号后面的数字是端口号。

使用DAX Studio查找Power BI Desktop本地端口

达克斯 Studio是一个很棒的工具,可以针对SSAS表格模型和Power BI桌面模型运行DAX查询。有趣的是,它是免费的,您可以从 这里.

  • 在您的计算机上安装DAX Studio并运行它
  • 选择“ Power BI Designer”作为数据源,然后单击“连接”

使用DAX Studio查找Power BI Desktop本地端口

  • 在程序窗口的底部,您可以在冒号后找到端口号

使用DAX Studio查找Power BI Desktop本地端口

从Power BI Desktop临时目录中查找Power BI Desktop本地端口

在现实世界中,当您在客户的网站上工作时,您可能无权打开CMD命令提示符。您可能无权安装DAX Studio等新软件。但是,即使您在现场为客户工作,您也始终可以访问本地文件。

每当您运行Power BI Desktop时,它都会在本地磁盘的temp目录中创建一堆文件和文件夹。您会发现许多有关Power BI Desktop正在运行的实例的有趣信息,例如SQL Server Profiler跟踪数据文件,Analysis Services日志文件的本地实例和端口号。出于本文的目的,我们只需要端口号,但是,我鼓励您查看Power BI Desktop临时目录中的其他文件。我敢打赌,您会发现它提供了很多信息。每当您在计算机上运行Power BI Desktop时,它都会打开一个随机端口号。端口号与型号无关,因此,如果您未连接任何数据源或未打开任何已保存的Power BI Desktop(* .PBIX)文件,则实际上并没有关系。该端口号存储在名为“ msmdsrv.port.txt”的文本文件中。因此,我们唯一需要的是浏览Power BI Desktop的temp目录并打开上面的文本文件。

注意: 这是一个临时目录,每当您运行Power BI Desktop时都会创建一个临时目录,并在关闭Power BI Desktop时将其删除。

您可以在此处找到Power BI Desktop临时文件夹:

  • 导航%LocalAppData%\ Microsoft \ Power BI桌面 \ AnalysisServicesWorkspaces

Power BI桌面临时文件夹

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

在临时目录中查找Power BI桌面本地端口号

Power BI桌面本地端口号

从Excel连接到Power BI Desktop模型

现在我们有了端口号,可以轻松连接到模型。

  • 开启Excel
  • 从功能区的“数据”选项卡中单击“来自其他来源”
  • 单击“来自Analysis Services”

从Excel连接到Power BI Desktop模型

  • 输入服务器名称为“ localhost:60575”,其中“ 60575”将是您之前检索到的端口号,然后单击“下一步”。

从Excel连接到Power BI Desktop模型

  • 再点击一次

从Excel连接到Power BI Desktop模型

  • 点击完成

从Excel连接到Power BI Desktop模型

  • 选择您要查看数据的方式,然后单击“确定”。

从Excel连接到Power BI Desktop模型

  • 现在,您可以在Excel中对Power BI Desktop模型数据进行切片和切块

从Excel连接到Power BI Desktop模型

从SSMS连接到Power BI Desktop模型

拥有端口号后,从SSMS连接到Power BI Desktop模型真的很容易。

  • 打开SSMS
  • 选择“ Analysis Services”作为服务器类型
  • 输入服务器名称为“ localhost:60575”,其中60575是端口号,然后单击“连接”。

从SSMS连接到Power BI Desktop模型

现在,我们已成功连接到Power BI Desktop模型。展开模型元素,您会发现许多有趣的东西,它们用于模型,表格等的各种连接。

从SSMS连接到Power BI Desktop模型

有趣吗?

当我从SSMS连接到Power BI Desktop时,引起我注意的第一件事是模型中有许多额外的LocalDate表。我认为它们在那里支持自动生成的日期层次结构。我注意到您的模型中的每个日期列确实存在一个LocalDate表。因此,如果您的模型中有10列“日期”数据类型,那么您将有10个LocalDate表。不太确定这是否是实现日期层次结构的最有效方法,但事实就是如此!

注意: 您可以轻松地查看自动生成的日期层次结构的工作方式。您只需要在模型的表格中有一个日期列即可。将日期列拖放到可视表(例如表格)上时,您可以看到该日期列将自动生成日期层次结构,而模型中没有实际的日期维度。

Power BI桌面生成日期层次结构

使用DAX查询Power BI桌面模型

您可以对模型运行DAX查询。在我们的示例中,我运行了一个简单的DAX查询以查看LocalDate表的内容。

  • 在SSMS中,右键单击“对象资源管理器”中的数据库
  • 单击“新建查询”,然后单击“ MDX”(是的,这里没有DAX,但是,我们将在MDX查询编辑器中编写和执行DAX查询。太棒了!)

使用DAX查询Power BI桌面模型

    • 现在,只需键入以下代码,然后按F5键即可执行它并查看结果:

       评估

              所有 (LocalDateTable_4535931b-fab6-4ea2-b2d7-0979ef27c9e5′)

注意: 您显然需要将LocalDate表名称放在“ 所有()”函数中。

使用DAX查询Power BI桌面模型

针对Power BI桌面模型运行MDX表达式

如您所知,您可以对SSAS表格模型数据库运行MDX表达式。 Power BI桌面 Model也不例外。

到目前为止,您已经有了如何从SSMS连接到Power BI桌面模型并使用DAX查询表的想法。相同的过程适用于运行MDX表达式。

以下MDX表达式显示了2014日历年按产品分类的Internet总销售量(我将数据从AdventureWorksDW2016CTP3数据库导入Power BI Desktop):

选择

  {

    [衡量]。[总销售额]

  } ,

  {

    (

      [DimProduct]。[EnglishProductName]。[EnglishProductName]。所有成员

    )

  }

( 选择

       (

         {

           [DimDate]。[CalendarYear]。&[2014]

         }

       )

     [模型])

哪里 (

        [DimDate]。[CalendarYear]。&[2014]

      )

MDX和Power BI桌面

从SSMS浏览Power BI桌面模型

您也可以从SSMS浏览模型。为此,只需在对象资源管理器中右键单击数据库,然后选择“浏览”。

从SSMS浏览Power BI桌面模型

从SSMS浏览Power BI桌面模型

注意: 从SSMS浏览模型时,将度量从事实表拖放到网格中时可能一无所获。这是因为您拖动了隐式度量而不是显式度量。一般而言,隐式度量是您尚未创建的一种度量。它们确实是表中的数字列。但是,显式度量是使用DAX函数创建的度量。创建显式度量并将所有隐式度量隐藏在模型中的最佳实践。 查看此内容以了解有关DAX中度量类型的更多信息.

从DAX Studio浏览Power BI桌面模型

根据我的经验,从DAX Studio浏览Power BI Desktop模型可能是最简单的方法。您不仅可以从DAX Studio中浏览模型,而且还是查询Power BI Desktop或SSAS表格模型的最佳工具。该工具中有很多功能可用于性能调整等。

  • 打开DAX Studio并连接到Power BI Desktop模型
  • 它立即打开查询编辑器,以便您可以编写DAX
  • 编写一些DAX代码并运行它

从DAX Studio浏览Power BI桌面模型

达克斯 Studio有很多有趣的东西,例如查看查询计划,服务器定时等,因此,我建议您下载并使用它。如果要调整DAX性能,这是必备工具。

使用SQL Server Profiler对Power BI桌面模型进行性能分析

您可以使用SQL Server Query Profiler捕获查询计划并将其用于性能调整。

  • 打开SQL Server查询分析器
  • 选择Analysis Services作为服务器类型
  • 输入“ localhost:60575”作为服务器名称,然后单击“连接”

使用SQL Server Profiler对Power BI桌面模型进行性能分析

  • 点击“事件选择”标签
  • 勾选“显示所有事件”

使用SQL Server Profiler对Power BI桌面模型进行性能分析

  • 从列表中查找并勾选以下所有事件,然后取消选择所有其他事件,然后取消选中“显示所有事件”

使用SQL Server Profiler对Power BI桌面模型进行性能分析

  • 点击运行
  • 现在返回到Power BI Desktop,并将一列拖放到画布上

Power BI桌面

  • 您将立即在SQL Server Profiler中看到跟踪

使用SQL Server Profiler对Power BI桌面模型进行性能分析

我将不解释性能调优以及我们在SQL Server Profiler中选择这些事件的原因,这不在本文讨论范围之内。如果您有兴趣了解有关DAX性能调优的更多信息,建议您来看看这一惊人的功能 SQLBI的白皮书。

26 thoughts on “从Excel和SSMS连接到Power BI Desktop模型

  1. 嗨Soheil,

    这是很棒的帖子。我一直在寻找它。
    最后,我能够从Excel连接到Power Bi Desktop中的数据模型。

    非常感谢

    一月

  2. 一个很棒且非常方便的帖子。谢谢!这是一个批处理脚本,用于自动化获取端口的过程:

    “`
    @回声关闭& SETLOCAL
    FOR / F“tokens=2 delims=,” %%F IN (‘TASKLIST /NH /FI “IMAGENAME EQ msmdsrv.exe” /FI “会话名称eq控制台” /FO CSV’) DO (
    SET pid=%%F
    )
    FOR / F“tokens=2 delims=:” %%F IN (‘NETSTAT / ANO ^ | FINDSTR%pid%^ | FINDSTR“127.0.0.1”‘) DO (
    SET ipport=%%F
    )
    FOR / F“tokens=1” %%F IN (“%ipport%”) DO (
    SET port=%%F
    )
    在端口上运行的ECHO Local Power BI实例:
    回声%port%
    “`

    1. 嗨扎克,

      对于不太熟练的人,如何部署此脚本?

      I’m使用SSIS将数据从Power BI移到SQL表,并希望删除更新端口号的步骤。

      谢谢
      理查德

    2. 谢谢Zach提供的脚本,效果很好!请技术人员注意:您必须将所有定向(左/左,右/闭)双引号更改为通用双引号,将单引号更改为通用单引号,仅此而已。如果您在记事本中对其进行编辑并将其保存为.bat文件(如getPowerBI.bat),则只需在c提示符/命令行中通过键入getPowerBI.bat和voila即可执行该文件。

  3. 非常感谢Soheil Bakhshi!如此出色的写作。也感谢Zach的脚本来获取端口。我试图通过将代码粘贴到文本编辑器中并作为批处理文件保存到桌面来运行批处理脚本。然后我双击该文件,它打开了命令提示符一秒钟,但随后消失了,现在我’我不确定下一步该怎么做。一世’我真的很新。你能告诉我我什么’m missing?

    1. 泰勒,你好
      谢谢你的评论。

      I’我不太确定为什么要将端口号存储在本地驱动器中为:
      1-端口号是临时存储在本地驱动器中的。您只需要打开文件。看着那(这“从Power BI Desktop临时目录中查找Power BI Desktop本地端口”部分以获取更多详细信息。注意:如果您’从Microsoft Store重新使用Power BI Desktop App,然后将本地文件存储在“%USERPROFILE%\ Microsoft \ Power BI桌面 Store App \ AnalysisServicesWorkspaces \ AnalysisServicesWorkspacesXXXXXXXXXX\数据
      2-端口号是一个随机端口号,仅在打开Power BI文件的时间内有效。因此,关闭文件后,该端口号不再有效。
      3-如果要从CMD(命令外壳)复制端口号,请按Ctrl + M,用鼠标选择端口号,然后按Enter

      如果以上都不可行,请说明您实际想要实现的目标。

      干杯。

  4. 非常感谢。

    下一步:

    使用以上批处理示例自动更新/连接到给定的power bi文件(* .pbix)
    开始之前:Power bi必须正在运行并且必须打开相应的pbix文件,对吗?
    只有这样,我才能通过Excel读取端口并接收数据/连接到数据模型,对吗?

    如果是,我将尝试自动建立连接/更新连接参数。

    让我们看看这是如何工作的

  5. 感谢Soheil分享此技巧。不幸的是,我’我使用正确的端口连接到本地主机后,无法查看数据库下的任何数据。我看到空的连接,表和角色。当我用XMLA编写脚本时,我一无所获…
    虽然当我在MDX中打开时,我可以看到多维数据集的尺寸和暗淡。 pbix仍在使用导入设置运行。

  6. 这真是内容丰富的文章,因为在Microsoft下我找不到Power BI桌面folser,在其他地方可以找到它吗?

  7. 你好,
    这是一个不错的帖子,我已经使用了一段时间了…但是,最近,数字字段尚未加载到数据透视表的“值”面板中。

    你知道如何解决这个问题吗?

    干杯

  8. 嗨Sohail,
    感谢您的来信。但是可以从powerbi.com进行此操作吗?我有能力。我的数据得到批处理,并且从Power BI端创建了一些度量和列。我希望能够连接到该Power BI服务实例,并能够从SSAS端查看它。我该如何做呢?

  9. 嗨Sohail,

    有没有办法查看在Power Query中为csv,txt等平面文件或Excel文件的步骤生成的原始SQL?我知道对平面文件禁用本机查询。为了优化功耗查询,是否有其他方法可以查看哪些步骤需要花费大量时间并且哪些步骤可以快速执行?

    1. 嗨西法尔,

      欢迎来到BIInsight.com。
      如前所述,查询折叠仅适用于某些数据源(主要是关系数据库系统,例如SQL Server,Oracle,OData等)。
      但是无论查询折叠如何,您始终可以使用Diagnostics Tool来查看花费更多时间来处理的步骤,如下所示:

    2. 在里面 编辑, 来自 工具类 标签,点击 诊断选项 纽扣
    3. 来自 诊断级别汇总的详细 (我们不’不需要其他诊断选项,它’由您决定。您可以勾选或取消勾选它们)
    4. 请点击
    5. Power BI诊断选项

    6. 从“查询”窗格中选择所需的查询
    7. 请点击 开始诊断 从按钮 工具类 功能区中的标签
    8. 在Power BI中启动诊断

    9. 点击 首页 标签
    10. 请点击 全部刷新
    11. 在Power BI Desktop 功率查询编辑器中刷新查询

    12. 回到 工具类 标签并点击 停止诊断
    13. 在Power BI中停止诊断
      点击 Diagnostics_Aggregated 查询,您可以在“ 独家时长
      在Power BI Desktop中的Power Query Editor中读取诊断查询
      您可以在 诊断_详细信息 查询。

      希望有帮助。
      干杯

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

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