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 報表伺服器執行兩種不同的工作負載類型;每種工作負載都需要轉譯不同類型的報表,以及執行各種入口網站作業。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 的 GitHub 專案中 (請參閱 https://github.com/Microsoft/Reporting-Services-LoadTest)。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
  • 模擬執行各種入口網站作業的測試。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.). 它們會藉由向報表伺服器提出一或多個 Web 要求 (透過 API) 來完成此工作。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. 例如,若要轉譯報表,使用者必須前往入口網站,巡覽至報表所在的資料夾,然後按一下報表將它轉譯。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 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 報表伺服器行為,已修正每種機器類型的 VM 設定 (裝載 Power BI 報表伺服器的機器除外)。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/en-us/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 個核心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/en-us/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

如果您想要對自己或 Microsoft Azure 的 Power BI 報表伺服器部署執行 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. 決定您是要對自己或 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