Power BI 报表服务器容量计划指南Capacity planning guidance for Power BI Report Server

Power BI 报表服务器是自助式 BI 和企业报表解决方案,客户可以在本地(防火墙后)进行部署。Power BI Report Server is a self-service BI and enterprise reporting solution that customers can deploy on their premises, behind their firewall. 它将 Power BI Desktop 的交互式报表功能与 SQL Server Reporting Services 的本地服务器平台相结合。It combines the interactive report capability of Power BI Desktop with the on-premises server platform of SQL Server Reporting Services. 随着企业中对分析和报表的大量日益频繁使用,对衡量企业用户群所需的硬件基础结构和软件许可证进行预算可能会成为一项挑战。With heavy and growing usage of analytics and reporting within enterprises, budgeting the hardware infrastructure and software licenses required to scale to an enterprise user base can be a challenge. 本文旨在通过共享针对报表服务器的各种工作负载的大量加载测试执行的结果,提供 Power BI 报表服务器的容量计划指南。This paper aims to offer guidance on capacity planning for Power BI Report Server by sharing results of numerous load test executions of various workloads against a report server. 虽然组织的报表、查询和使用模式差异巨大,但是本文中显示的结果,以及所用的实际测试和测试执行方式的详细描述,均可用作部署 Power BI 报表服务器早期阶段计划过程中的任何用户的参考点。While organizations’ reports, queries, and usage patterns vary widely, the results presented in this paper, along with the actual tests used and a detailed description of how they were executed, serve as a reference point for anyone in the early-stage planning process of deploying Power BI Report Server.

执行摘要Executive summary

我们针对 Power BI 报表服务器执行两种不同类型的工作负载;每个工作负载由呈现不同类型的报表、以及执行各种 Web 门户操作组成。We executed two different types of workloads against Power BI Report Server; each workload consisted of rendering different types of reports as well as performing various web portal operations.

  • 在“Power BI 报表重负载”工作负载中,最常执行的操作(即 60% 的时间中执行的操作)是呈现 Power BI 报表。In “Power BI Report Heavy” workload, the most frequently executed operation (i.e. the operation executed 60% of the time) was rendering Power BI reports.
  • 在“分页报表重负载”工作负载中,最常执行的操作是呈现分页报表。In “Paginated Report Heavy” workload, the most frequently executed operation was rendering paginated reports.

在 Power BI 报表服务器的四服务器拓扑和不超过 5% 的用户将在任何一个时间访问报表服务器的预期下,下表介绍了 Power BI 报表服务器以至少 99% 的可靠性能够处理的最大用户数。Under a four-server topology of Power BI Report Server and the expectation that no more than 5% of users will access a report server at any one time, the following table describes the maximum number of users Power BI Report Server can handle with at least 99% reliability.

工作负载Workload 8 核/32 GB RAM8 Core/32 GB RAM 16 核/64 GB RAM16 Core/64 GB RAM
Power BI 报表重负载 (>60%)Power BI Report Heavy (>60%) 1,000 个用户1,000 users 3,000 个用户3,000 users
分页 (RDL) 报表重负载 (> 60%)Paginated (RDL) Report Heavy (>60%) 2,000 个用户2,000 users 3,200 个用户3,200 users

在每个运行中,最过载的资源是 CPU。In each run, the most overwhelmed resource was CPU. 因此,与增加内存或硬盘空间量相比,增加 Power BI 报表服务器的内核数将能更好地提升系统的可靠性。Due to this, increasing the number of cores to Power BI Report Server would yield a higher gain in the reliability of the system than increasing the amount of memory or hard-disk space.

测试方法Test methodology

所用的测试拓扑均基于 Microsoft Azure 虚拟机,而不是供应商特定的物理硬件。The testing topology used was based on Microsoft Azure Virtual Machines instead of vendor-specific physical hardware. 所有计算机均托管在美国地区中。All machines were hosted in US regions. 这反映了本地和公有云中的硬件虚拟化的总趋势。This reflects the general trend of hardware virtualization both on premises and in the public cloud.

Power BI 报表服务器拓扑Power BI Report Server topology

Power BI 报表服务器部署由以下虚拟机组成:The Power BI Report Server deployment consisted of the following virtual machines:

  • Active Directory 域控制器:SQL Server 数据库引擎、SQL Server Analysis Services 和 Power BI 报表服务器需要它来安全地对所有请求进行身份验证。Active Directory Domain Controller: this was needed by SQL Server Database Engine, SQL Server Analysis Services, and Power BI Report Server to securely authenticate all requests.
  • SQL Server 数据库引擎和 SQL Server Analysis Services:这是我们存储报表的所有数据库的位置,以便在呈现它们时使用。SQL Server Database Engine and SQL Server Analysis Services: this was where we stored all the databases for the reports to consume when we rendered them.
  • Power BI 报表服务器Power BI Report Server
  • Power BI 报表服务器数据库。Power BI Report Server Database. 报表服务器数据库托管在 Power BI 报表服务器以外的其他计算机上,以便它不需要与 SQL Server 数据库引擎竞争内存、CPU、网络和磁盘资源。The report server database is hosted on a different machine than Power BI Report Server so that it does not need to compete with SQL Server Database Engine for memory, CPU, network, and disk resources.

请参阅附录 1.1 Power BI 报表服务器拓扑和附录 1.2 Power BI 报表服务器虚拟机配置,了解拓扑中所用的每台虚拟机的全面配置。See Appendix 1.1 Power BI Report Server Topology and Appendix 1.2 Power BI Report Server Virtual Machine Configuration for a thorough configuration of each virtual machine used in the topology.

测试Tests

负载测试运行中所用的测试在名为 Reporting Services LoadTest(请参阅 https://github.com/Microsoft/Reporting-Services-LoadTest)的 GitHub 项目公开可用。The tests used in the load test runs are publicly available in a GitHub project called Reporting Services LoadTest (See https://github.com/Microsoft/Reporting-Services-LoadTest). 此工具可以让用户研究 SQL Server Reporting Services 和 Power BI 报表服务器的性能、可靠性、可伸缩性和可恢复性特征。This tool allows users to study the performance, reliability, scalability and recoverability characteristics of SQL Server Reporting Services and Power BI Report Server. 此项目包含四组测试用例:This project consists of four groups of test cases:

  • 模拟呈现 Power BI 报表的测试、Tests simulating rendering Power BI reports,
  • 模拟呈现移动报表的测试、Tests simulating rendering mobile reports,
  • 模拟呈现小型和大型分页报表的测试以及Tests simulating rendering small and large paginated reports, and
  • 模拟执行各种类型的 Web 门户操作的测试。Tests simulating performing various types of web portal operations.

所有测试均编写用于执行端到端操作(例如,呈现报表、创建新数据源等)。All tests were written to perform an end-to-end operation (such as rendering a report, creating a new data source, etc.). 它们通过向报表服务器(通过 API)发出一个或多个 Web 请求完成此操作。They accomplish this by making one or more web requests to the report server (via APIs). 在实际情况中,用户可能需要执行几个中间操作才能完成其中的一个端到端操作。In the real world, a user may need to perform a few intermediate operations to complete one of these end-to-end operations. 例如,要呈现报表,用户将需要转到 Web 门户,导航到报表所在的文件夹,然后单击该报表来呈现它。For example, to render a report a user will need to go to the web portal, navigate to the folder where the report is, then click the report to render it. 虽然测试不执行完成端到端任务所需的所有操作,但它们仍会施加 Power BI 报表服务器会体验到的大部分负载。While tests don’t perform all the operations needed to accomplish an end-to-end task, they still impose most of the load that Power BI Report Server would experience . 可以通过浏览 GitHub 项目详细了解所使用的不同类型报表以及执行的操作的多样性。You can learn more about the different types of reports used as well as the variety of operations performed by exploring the GitHub project.

工作负载Workloads

测试中使用了 2 个工作负载配置文件:Power BI 报表重负载和分页报表重负载。There are 2 workload profiles used in testing: Power BI Report Heavy and Paginated Report Heavy. 下表介绍了针对报表服务器执行的请求的分布。The table below describes the distribution of requests executed against the Report Server.

活动Activity Power BI 报表重负载,出现频率Power BI Report Heavy, Frequency of occurrence 分页报表重负载,出现频率Paginated Report Heavy, Frequency of occurrence
呈现 Power BI 报表Rendering Power BI reports 60%60% 10%10%
呈现分页 (RDL) 报表Rendering paginated (RDL) reports 30%30% 60%60%
呈现移动报表Rendering mobile reports 5%5% 20%20%
Web 门户操作Web portal operations 5%5% 10%10%

用户负载User load

对于每个测试运行,测试均基于在这两个工作负载之一中指定的频率执行。For each test run, tests were executed based on the frequency specified in one of the two workloads. 测试从 20 个并发用户对报表服务器发出请求开始。Tests started with 20 concurrent user requests to the report server. 然后逐渐增加用户负载,直到可靠性降低至 99% 目标以下。The user load was then gradually increased until reliability dropped below the 99% target.

结果Results

并发用户容量Concurrent user capacity

如上文所述,测试从 20 个并发用户对报表服务器发出请求开始。As stated earlier, tests started with 20 concurrent users making requests to the report server. 然后逐渐增加并发用户的数量,直到所有请求的 1% 失败。The number of concurrent users was then gradually increased until 1% of all requests were failing. 下表中的结果告诉我们,服务器能够在峰值负载下处理的且请求失败率不超过 1% 的并发用户请求数。The results in the following table tell us the number of concurrent user requests that the server would be able to handle under peak load with a failure rate of less than 1%.

工作负载Workload 8 核/32 GB8 Core/32 GB 16 核/64 GB16 Core/64 GB
Power BI 报表重负载Power BI Report Heavy 50 个并发用户50 concurrent users 150 个并发用户150 concurrent users
分页报表重负载Paginated Report Heavy 100 个并发用户100 concurrent users 160 个并发用户160 concurrent users

总用户容量Total user capacity

在 Microsoft,我们有一个 Power BI 报表服务器的生产部署,几个团队已使用过。At Microsoft, we have a production deployment of Power BI Report Server that several teams used. 当我们分析此环境的实际使用情况时,我们观察到,任何给定时间(即使在每日的峰值负载期间)的并发用户数往往不超过总用户群的 5%。When we analyze actual usage of this environment, we observe that the number of concurrent users at any given time (even during daily peak load) doesn’t tend to exceed 5% of the total user base. 将此 5% 并发率用作基准,我们推测,总用户群 Power BI 报表服务器能够处理 99% 的可靠性。Using this 5% concurrency ratio as a benchmark, we extrapolated the total user base Power BI Report Server could handle with 99% reliability.

工作负载Workload 8 核/32 GB8 Core/32 GB 16 核/64 GB16 Core/64 GB
Power BI 报表重负载Power BI Report Heavy 1,000 个用户1,000 users 3,000 个用户3,000 users
分页报表重负载Paginated Report Heavy 2,000 个用户2,000 users 3,200 个用户3,200 users

查看结果View results

选择一个报表,查看负载测试的结果。Select a report to view the results of the load test.

工作负载Workload 8 核/32 GB8 Core/32 GB 16 核/64 GB16 Core/64 GB
Power BI 报表重负载Power BI Report Heavy 视图 - 8 核View - 8 core 视图 - 16 核View - 16 core
分页报表重负载Paginated Report Heavy 视图 - 8 核View - 8 core 视图 - 16 核View - 16 core

摘要Summary

对于每个负载测试运行,CPU 是 Power BI 报表服务器计算机上的峰值负载点中最过载的资源。For each load test run, CPU was the most overwhelmed resource at the point of peak load on the Power BI Report Server machine. 因此,应增加的第一个资源是内核数。Due to this, the first resource that should be increased is the number of cores. 或者,可以考虑通过在拓扑中添加更多托管 Power BI 报表服务器的服务器来进行纵向扩展。Alternately, you can consider scaling out by adding more servers hosting Power BI Report Server in your topology.

本文中显示的结果源自执行一个特定的报表集,该报表集使用一个以特定方式重复的特定数据集。The results presented in this paper were derived from executing a specific set of reports consuming a specific set of data, repeated in a specific way. 它是一个有用的参考点,但请记住,使用情况将取决于报表、查询、使用模式以及 Power BI 报表服务器的部署。It’s a useful reference point, but keep in mind that your usage will depend on your reports, queries, usage patterns and deployment of your Power BI Report Server.

附录Appendix

1 拓扑1 Topology

1.1 Power BI 报表服务器拓扑1.1 Power BI Report Server Topology

为专注于不同配置下的 Power BI 报表服务器行为,修复了每种类型的计算机(托管 Power BI 报表服务器的计算机除外)的 VM 配置。To focus solely on Power BI Report Server behavior under different configurations, the VM configuration for each type of machine (except for the machine hosting Power BI Report Server) was fixed. 根据具有高级存储磁盘的第二代 (v2) D 系列计算机对每台计算机进行了预配。Each machine was provisioned according to the second-generation (v2) D Series machines with Premium Storage Disks. 可以在 https://azure.microsoft.com/zh-cn/pricing/details/virtual-machines/windows/ 上的“常规用途”部分下找到有关每个 VM 大小的详细信息。You can find detailed information about each VM size under the “General Purpose” section on https://azure.microsoft.com/en-us/pricing/details/virtual-machines/windows/.

虚拟机类型Virtual Machine Type 处理器Processor 内存Memory Azure VM 大小Azure VM Size
Active Directory 域控制器Active Directory Domain Controller 双核2 Cores 7 GB7 GB Standard_DS2_v2Standard_DS2_v2
SQL Server 数据库引擎和 Analysis ServicesSQL Server Database Engine and Analysis Services 16 核16 Cores 56 GB56 GB Standard_DS5_v2Standard_DS5_v2
报表服务器数据库Report Server Database 16 核16 Cores 56 GB56 GB Standard_DS5_v2Standard_DS5_v2

1.2 Power BI 报表服务器虚拟机配置1.2 Power BI Report Server Virtual Machine Configuration

不同的处理器和内存配置用于托管 Power BI 报表服务器的虚拟机。Different configurations of processor and memory were used for the Virtual Machine hosting Power BI Report Server. 和其他 VM 不同,此计算机根据具有高级存储磁盘的第三代 (v3) D 系列计算机进行了预配。Unlike the other VMs, this machine was provisioned according to the third-generation (v3) D Series Machines with Premium Storage Disks. 可以在 https://azure.microsoft.com/zh-cn/pricing/details/virtual-machines/windows/ 上的“常规用途”部分下找到有关此 VM 大小的详细信息。You can find detailed information about this VM size under the “General Purpose” section on https://azure.microsoft.com/en-us/pricing/details/virtual-machines/windows/.

虚拟机Virtual Machine 处理器Processor 内存Memory Azure VM 大小Azure VM Size
Power BI 报表服务器(小型)Power BI Report Server (Small) 8 核8 Cores 32 GB32 GB Standard_D8S_v3Standard_D8S_v3
Power BI 报表服务器(大型)Power BI Report Server (Large) 16 核16 Cores 64 GB64 GB vStandard_D16S_v3vStandard_D16S_v3

2 运行 LoadTest 工具2 Run the LoadTest tool

如果想针对自己的 Power BI 报表服务器部署或 Power BI 报表服务器的 Microsoft Azure 部署运行 Reporting Services LoadTest 工具,请遵循以下步骤。If you’d like to run the Reporting Services LoadTest tool against your or a Microsoft Azure deployment of Power BI Report Server, follow these steps.

  1. 从 GitHub (https://github.com/Microsoft/Reporting-Services-LoadTest) 中克隆 Reporting Services LoadTest 项目。Clone the Reporting Services LoadTest project from GitHub (https://github.com/Microsoft/Reporting-Services-LoadTest).
  2. 在项目目录中,将找到一个名为 RSLoadTests.sln 的解决方案文件。In the project directory, you will find a solution file called RSLoadTests.sln. 在 Visual Studio 2015 或更高版本中打开此文件。Open this file in Visual Studio 2015 or later.
  3. 确定是要针对自己的 Power BI 报表服务器部署,还是针对 Microsoft Azure 中的 Power BI 报表服务器部署运行此工具。Determine whether you want to run this tool against your deployment of Power BI Report Server or against a deployment of Power BI Report Server in Microsoft Azure. 如果要对自己的部署运行此工具,请转到步骤 5。If you are going to run it against your own deployment, go to step 5.
  4. 按照 https://github.com/Microsoft/Reporting-Services-LoadTest#create-a-sql-server-reporting-services-load-environment-in-azure 中列出的说明,在 Azure 中创建 Power BI 报表服务器环境。Follow the instructions listed on https://github.com/Microsoft/Reporting-Services-LoadTest#create-a-sql-server-reporting-services-load-environment-in-azure to create a Power BI Report Server environment in Azure.
  5. 完成部署环境后,按照 https://github.com/Microsoft/Reporting-Services-LoadTest#load-test-execution 中列出的说明运行测试。Once you finish deploying the environment, follow the instructions listed on https://github.com/Microsoft/Reporting-Services-LoadTest#load-test-execution to run the tests.

更多问题?More questions? 尝试咨询 Power BI 社区Try asking the Power BI Community