使用 Power BI 连接到 Microsoft Dynamics CRMConnect to Microsoft Dynamics CRM with Power BI

用于 Power BI 的 Microsoft Dynamics CRM Online 使你能够轻松地访问和分析数据。Microsoft Dynamics CRM Online for Power BI allows you to easily access and analyze your data. Power BI 使用 OData 数据源以创建描述性模型,其中具有所需的所有实体和度量值,如帐户、活动、商机、产品、潜在客户、用户等。Power BI uses the OData feed to create a descriptive model, with all the entities and measures needed such as Accounts, Activities, Opportunities, Product, Leads, Users and more. 安装应用后,可以在 Power BI 服务 (https://powerbi.com) 和 Power BI 移动应用中查看仪表板和报表。After you install the app, you can view the dashboard and reports in the Power BI service (https://powerbi.com), and in the Power BI mobile apps.

连接到 Dynamics CRM Online销售经理服务管理器Connect to Dynamics CRM Online Sales Manager or Service Manager. 阅读有关使用 Power BI 与 Dynamics CRM Online 集成的详细信息。Read more about Dynamics CRM Online integration with Power BI.

此连接需要 Microsoft Dynamics CRM Online 2016 或更高版本。This connection requires Microsoft Dynamics CRM Online 2016 or later. 以下是有关要求的详细信息。More details on requirements below.

如何连接How to connect

  1. 选择左侧导航窗格中的“应用”,然后选择右上角的“获取应用”。Select Apps in the left navigation pane > select Get apps in the upper-right corner.

    “获取应用”图标

  2. 在 AppSource 中,选择“应用”选项卡,然后搜索所需的服务。In AppSource, select the Apps tab, and search for the service you want.

    AppSource 中的“应用”选项卡

  1. 选择 Microsoft Dynamics CRM 销售经理Microsoft Dynamics CRM 服务管理器,然后单击连接Select Microsoft Dynamics CRM Sales Manager or Microsoft Dynamics CRM Service Manager and click Connect.

  2. 提供与你的帐户相关联的服务 URL。Provide the Service URL associated with your account. 这将在窗体 https://company.crm.dynamics.com 中,请参阅以下详细信息。This will be in the form https://company.crm.dynamics.com, see more details below.

  3. 出现提示时,请提供凭据(如果你已使用了浏览器登录,则可跳过此步骤)。When prompted, provide your credentials (this step might be skipped if you are already signed in with your browser). 对于身份验证方法,输入 oAuth2,然后单击登录For Authentication Method, enter oAuth2 and click Sign In:

  4. 连接后,你将看到为销售经理或服务管理器自定义的仪表板,该仪表板是使用你自己的数据进行填充的:After connecting, you'll see a dashboard customized for a Sales Manager or Service Manager, populated with your own data:

查看 Microsoft Dynamics CRM 仪表板和报表View the Microsoft Dynamics CRM dashboard and reports

导入完成后,新的应用将显示在“应用”页上。When the import is complete, the new app will appear on the Apps page.

  1. 选择左侧导航窗格中的“应用”,然后选择应用。Select Apps in the left navigation pane > select the app.

    左侧导航窗格中的“应用”

  2. 可以通过在问答框中键入来提出问题,或单击磁贴打开基础报表。You can ask a question by typing in the Q&A box, or click a tile to open the underlying report.

下一步?What now?

  • 在仪表板顶部,尝试在“问答”框中提问Try asking a question in the Q&A box at the top of the dashboard.
  • 选择磁贴以打开基础报表。Select a tile to open the underlying report.
  • 可以筛选和突出显示报表中的数据,但不能保存所做的更改。You can filter and highlight the data in the report, but you can't save your changes.
  • 计划每日刷新数据集。Your dataset is scheduled to refreshed daily. 可以更改刷新计划或根据需要使用“立即刷新”来尝试刷新。You can change the refresh schedule or try refreshing it on demand using Refresh Now.

包含的内容What's included

以下部分详细介绍了销售经理服务管理器角色中所包含的内容。The sections below detail what's included for the Sales Manager and Service Manager personas.

请注意,额外的数据将基于分配到 Dynamics CRM Online 用户的安全角色而受限制。Note that data in addition is limited based on the security role assigned to the Dynamics CRM Online user.

仪表板和报表旨在提供侧重于团队或组的有关近期数据的运营报表。The dashboard and reports are meant to provide operational reporting on near term data with focus on a team or group. 每个查询受限于从 Dynamics CRM Online 中检索最大为 100K 的记录。Each query is limited to retrieve a maximum of 100K records from Dynamics CRM Online. 如果由于组织中的数据量大而超过此限制,设置将失败,因为数据刷新 Dynamics CRM Online 将被终止。If this limit is exceeded due to high volume of data in your organization, provisioning will fail as data refresh Dynamics CRM online will be terminated. 如果你的帐户过大,请考虑通过 Power BI Desktop 进行连接以生成自定义解决方案。If your account is too large, consider connecting through the Power BI Desktop to build a custom solution.

销售经理Sales Manager

仪表板和报表包含关键指标,例如:The dashboard and reports contain key metrics such as:

  • 获胜收入Won Revenue
  • 胜率Win Rate
  • 往来收入Open Revenue
  • 失败收入Lost revenue
  • 预期收入Expected Revenue
  • 平均每笔成交额等。Average Deal Size and more.

它们还包含关键图表,例如:They also contain key charts such as:

  • 获胜和失败收入趋势,获胜收入 Vs 估计收入趋势Won and Lost Revenue Trend, Won Revenue Vs Estimated Revenue Trend
  • 按照各个方面(如行业、地区、区域)的获胜收入Won Revenue by various dimensions such as Industry, Region, Territory
  • 按收入、活动划分的销售主管,Sales Leaders By Revenue, Activities,
  • 排名靠前的帐户,排名靠前的获胜/失败交易,Top Accounts, Top Won/Lost Deals,
  • 新潜在客户趋势、销售漏斗等。New Leads Trend, Sales Pipeline and more.

这些指标和图表有助于了解你的销售组织业绩并且对你的销售团队进行销售漏斗分析。These metrics and charts help to understand your sales organization performance and analyze sales pipeline across your sales team.

下表列出了此服务中可用的 CRM 实体,并且还提供了有关应用于每个实体记录的筛选器的详细信息。Following table lists the CRM entities available for thsi service and also gives details on the filters applied to each of the entity records.

CRM 实体CRM Entity 应用的筛选器Filters applied
帐户Account 有在过去的 365 天内修改的相关商机的所有帐户。All accounts which have related opportunities that have been modified in the last 365 days.
活动Activity 在过去的 90 天内修改的所有活动All activities modified in the last 90 days
[modifiedon] > 今天 - 90 天[modifiedon] > today - 90 days
业务部门Business Unit 所有未禁用的业务部门All business units which are not disabled
[isdisabled] = false[isdisabled] = false
潜在客户Lead 在过去的 180 天内修改的所有潜在客户All leads modified in the last 180 days
[modifiedon] > 今天 - 180 天[modifiedon] > today - 180 days
商机Opportunity 在过去的 365 天内修改的所有商机All opportunities modified in last 365 days
[modifiedon] > 今天 - 365 天[modifiedon] > today - 365 days
商机产品Opportunity Product 在过去的 365 天内修改的所有商机产品All opportunity products modified in last 365 days
[modifiedon] > 今天 - 365 天[modifiedon] > today - 365 days
产品Product 所有活动产品All active products
[statecode] <> 1[statecode] <> 1
区域Territory 所有区域All territories
用户User 所有非委派管理员的活动用户All active users and not delegated admins
[isdisabled] = false,并且 [accessmode] <> 4[isdisabled] = false and [accessmode] <> 4

服务管理器Service Manager

这些仪表板和报表包含关键指标,例如:These dashboard and reports contain key metrics such as:

  • CSAT 百分比CSAT Percentage
  • SLA Met 百分比SLA Met percentage
  • 已提升案例百分比Escalated Cases Percentage
  • 平均处理时间Average Handling Time
  • 已解决案例总数Total Resolved Cases
  • 活动案例总数Total Active Cases
  • 知识库文章在案例中的使用次数等。Number of Times KB Article Used in cases and more.

它们还包含关键图表,例如:They also contains key charts such as:

  • 新近案例、已解决案例、已提升案例的案例数量趋势Case Volume Trends for Incoming Cases, Resolved Cases, Escalated Cases
  • 按照各个方面(如来源、位置、优先级、类型)划分的案例数量Case Volume by various dimensions such as Origin, Location, Priority, Type
  • 按 CSAT 百分比、SLA met 百分比、活动、已解决案例划分的负责人Leaders by CSAT percentage, SLA met percentage, Activities, Resolved cases
  • 最常使用和查看次数最多的知识库文章等。Most Used and Most viewed KB Articles and more.
     这些指标和图表有助于了解你的支持组织业绩并且对你的服务团队和服务队列进行活动案例工作量分析。  These metrics and charts help to understand your support organization performance and analyze active cases workload across your service team and service queues.

下表列出了此服务中可用的 CRM 实体以及有关应用于每个实体记录的筛选器的详细信息。Following table lists the CRM entities available for this service, as well as details on the filters applied to each of the entity records.

CRM 实体CRM Entity 应用的筛选器Filters applied
帐户Account 有在过去的 90 天内修改的相关案例的所有帐户。All accounts which have related cases that have been modified in the last 90 days.
活动Activity 在过去的 90 天内修改的所有活动All activities modified in the last 90 days
[modifiedon] > 今天 - 90 天[modifiedon] > today - 90 days
案例Case 在过去的 90 天内修改的所有案例All cases modified in the last 90 days
[modifiedon] > 今天 - 90 天[modifiedon] > today - 90 days
案例解决活动Case Resolution Activity 在过去的 90 天内修改的所有案例解决活动All case resolution activities modified in the last 90 days
[modifiedon] > 今天 - 90 天[modifiedon] > today - 90 days
联系人Contact 有在过去的 90 天内修改的相关案例的所有联系人。All contacts which have related cases that have been modified in the last 90 days.
知识文章Knowledge Article 所有最新版本的知识文章All latest version of knowledge articles
[islatestversion] = true[islatestversion] = true
知识文章事件Knowledge Article Incident 在过去的 90 天内修改的所有知识文章事件All knowledge article incidents which are modified in the last 90 days
[modifiedon] > 今天 - 90 天[modifiedon] > today - 90 days
队列Queue 所有活动队列All active queues
[statecode] = 0[statecode] = 0
队列项Queue Item 在过去的 365 天内创建的所有案例相关的队列项All case related queue items created on the past 365 days
[createdon] > 今天 - 365 天[createdon] > today - 365 days and
[objecttypecode] = 112[objecttypecode] = 112
用户User 所有活动用户All active users
[isdisabled] = false[isdisabled] = false

系统要求System requirements

  • 一个有效的 Dynamics CRM Online 2016 或更高版本实例(Power BI 不适用于本地 CRM 版本)。A valid Dynamics CRM Online 2016 or later instance (Power BI won't work with an on-premises CRM version). 如果没有 2016 或更高版本:If you do not have 2016 or later:
  • 管理员必须启用站点设置中的 OData 终结点。An administrator must enable the OData endpoint in the site settings.
  • 任何表格中小于 100k 条记录的帐户。An account with less than 100k records in any of the tables. 请注意,如果该帐户有权访问超过 100k 条记录,导入将会失败。Note if the account has access to more than 100k records the import will fail.

查找参数Finding parameters

在浏览器的 URL 栏中可以找到该实例的地址。The address of the instance can be found in the URL bar of your browser. 它通常具有以下格式:https://[instance_name].crm.dynamics.comIt typically has the format: https://[instance_name].crm.dynamics.com.

Power BI 仅支持 Dynamics CRM 2016 终结点。Power BI only supports Dynamics CRM 2016 endpoints. 该连接不适用于早期版本的 CRM Online。The connection will not work with earlier versions of CRM Online. 使用 Power BI Desktop 直接连接到你的帐户。Use Power BI Desktop to connect directly to your account.

故障排除Troubleshooting

如果你遇到连接问题,请确认:If you're having trouble connecting, confirm:

  • 你提供的是正确的实例 URL(使用管理员帐户检查)you're providing the correct instance URL (check with your admin)
  • 该实例是 CRM Online 2016the instance is CRM Online 2016
  • 已启用 OData 终结点the OData endpoint is enabled

此外,请尝试使用 OData URL https://[instance_name].crm.dynamics.com/api/data/v8.0/ 直接在 Power BI Desktop 中进行连接。Also, try connecting directly in Power BI Desktop, with the OData URL https://[instance_name].crm.dynamics.com/api/data/v8.0/.

如果确认拥有 Dynamics CRM Online 2016,但是连接时仍出现问题,请与 CRM 管理员联系以确认已拥有所有可用的更新。If you confirm you have Dynamics CRM Online 2016 but you're still hitting issues connecting, contact your CRM Admin to confirm you have all available updates.

如果没有 CRM Online 2016 或更高版本,请使用 Power BI Desktop 直接连接到你的帐户。If you do not have CRM Online 2016 or later, use the Power BI Desktop to connect directly to your account.

如果看到错误“数据刷新失败,因为查询超过 100000 条记录的最大限制”,请考虑直接从 Power BI Desktop 连接或使用 CRM 解决方案模板。If you see an error "Data refresh failed as query exceeded the maximum limit of 100000 records," consider connecting directly from the Power BI Desktop or leveraging the CRM solution template.

后续步骤Next steps