Power BI is a self-service business intelligence tool from Microsoft which has been steadily gaining momentum in the last couple of months. One of the well-known disadvantages of Power BI is that it is basically cloud only. A lot of companies are not yet at the point where they feel comfortable having their data in the cloud or are premise bound for some other reasons such as data-sensitivity, data-sovereignty or compliance.
Power BI是Microsoft的一种自助式商业智能工具,在最近几个月中一直稳步增长。 Power BI的众所周知的缺点之一是它基本上仅是云。 许多公司还没有适应将数据存储在云中或由于某些其他原因(例如数据敏感性,数据主权或合规性)而受到局限的感觉。
Last year Microsoft released a Power BI on SQL Server Reporting Services preview for some then “future” version of SQL Server. This version basically allowed you to deploy your Power BI reports to SQL Server Reporting Services to share them with your organization. That version was based on the Power BI embedded which was extremely limited.
去年,Microsoft在SQL Server Reporting Services的预览版中发布了Power BI,用于某些后来的“未来”版本SQL Server。 此版本基本上允许您将Power BI报表部署到SQL Server Reporting Services,以与组织共享它们。 该版本是基于Power BI嵌入式的,该版本极为有限。
In answer to this problem Microsoft has now created Power BI Report Server.
为了解决此问题,Microsoft现在创建了Power BI Report Server。
Power BI报表服务器简介 (Introducing Power BI Report Server)
Power BI report server is a new component which comes as included in the licensing of SQL Server Enterprise Edition with SA or as a free extension of Power BI premium. It is not a replacement for SQL Server Reporting services as originally speculated.
Power BI报表服务器是一个新组件,包含在带有SASQL Server Enterprise Edition许可中或作为Power BI premium的免费扩展。 它不能替代最初推测SQL Server Reporting服务。
Interestingly, if you do not have an on-premise SQL Server license but you purchased Power BI Premium and you want to run Power BI Report Service on-premise, you will have the right to run a server with the equivalent number of cores in your premium subscription for it.
有趣的是,如果您没有本地SQL Server许可证,但购买了Power BI Premium,并且想要在本地运行Power BI Report Service,则您将有权在您的服务器上运行具有相同核心数量的服务器它的高级订阅。
Power BI Report Server安装和配置 (Power BI Report Server Installation and Configuration)
Installation of the Power BI Report Server is fairly straight forward. Just download and install following the wizard steps.
Power BI Report Server的安装相当简单。 只需按照向导步骤下载并安装。
The only thing to note is that it does not install SQL Server, so you need to install one or have access to one before commencing your configuration. As you can see in the image below it says “Install Database Engine”, which is actually just a reminder that you need one.
唯一需要注意的是它不会安装SQL Server,因此您需要先安装SQL Server或可以访问SQL Server,然后才能开始配置。 如您在下图中所看到的,它说“安装数据库引擎”,实际上只是在提醒您需要一个。
![](https://www.sqlshack.com/wp-content/uploads/2017/06/word-image-121.png)
Once the Power BI Report Server is installed, you need to configure it. When you open Report Server Configuration Manager, you will get a distinct sense of Déjà vu, since it looks exactly like Reporting Services Configuration Manager except for one additional option at the bottom: Power BI Service (cloud), which allows you to specify a Power BI account to connect to the Power BI Service in the cloud.
安装Power BI Report Server之后,您需要对其进行配置。 当您打开Report Server Configuration Manager时,您会感觉到Déjàvu的独特感觉,因为它看起来与Reporting Services Configuration Manager完全一样,除了底部的一个附加选项:Power BI Service(云),它允许您指定Power BI帐户以连接到云中的Power BI Service。
![](https://www.sqlshack.com/wp-content/uploads/2017/06/word-image-122a.png)
Power BI报表 (Power BI Reports)
The advantage of being able to use Power BI reports is that you can create interactive reports. If you are familiar with Power BI, you will know that as a developer you can create datasets and allow your business users to create their own reports to change the visualizations and layouts to suit them without having to request that from development, saving everyone a ton of time.
能够使用Power BI报表的优点是可以创建交互式报表。 如果您熟悉Power BI,您将知道作为开发人员,您可以创建数据集并允许您的业务用户创建自己的报告,以更改可视化效果和布局以适合他们,而无需从开发中请求,从而节省了很多人时间。
创作报告 (Authoring Reports)
Creating Power BI reports is where the catch comes in. Even if you are running everything on-premise and you are not using any Power BI in the cloud. You will still require a Power BI Pro license for each person who wants to author and publish reports.
创建Power BI报表是要抓住的地方。即使您在内部运行所有内容,并且没有在云中使用任何Power BI。 对于仍要编写和发布报告的每个人,您仍然需要Power BI Pro许可证。
To be able to publish reports to Report Server you need a special version of Power BI Desktop, currently known as Power BI Desktop (Report Server) which comes as part of the Power BI Report Server download, the version of which needs to be kept in sync with the version on Power BI Report server. As explained in the section up upgrades later on.
为了能够将报告发布到Report Server,您需要Power BI Desktop的特殊版本,当前称为Power BI Desktop(Report Server),它是Power BI Report Server下载的一部分,需要保留在该版本中。与Power BI Report服务器上的版本同步。 如本节所述,稍后进行升级。
流动性 (Mobility)
Power BI Report Server will allow you to view your SQL Server Reporting Services Reports as well as your Power BI reports through the Power BI app on any device, through the Power BI Report Server Web Portal.
Power BI Report Server将允许您通过Power BI Report Server Web门户通过任何设备上的Power BI应用程序查看SQL Server Reporting Services报告以及Power BI报告。
工作流程 (Workflow)
The typical work flow would be:
典型的工作流程为:
Important things to note here is the data is not imported into the Power BI Service as with typical Power BI reports. Instead the Report Server connects to Analysis Services (in this case on-premise) live, to ensure that the data stays safe within the boundary of the customer’s firewall.
这里要注意的重要事项是,数据不像典型的Power BI报表一样导入Power BI服务。 相反,Report Server 实时连接到Analysis Services(在这种情况下为本地),以确保数据在客户防火墙的边界内保持安全。
数据源 (Data Sources)
When Power BI Report Server is released initially it will only be able to connect to SQL Server Analysis Services (SSAS). Both multi-dimensional and tabular models are supported.
最初发布Power BI Report Server时,它将只能连接到SQL Server Analysis Services(SSAS)。 同时支持多维模型和表格模型。
If you try to deploy a report which is connected to Excel for example you will encounter the below error:
例如,如果尝试部署连接到Excel的报表,则会遇到以下错误:
发布到SQL Server Reporting Services(SSRS) (Publishing to SQL Server Reporting Services (SSRS))
Even though deploying a report to Power BI Report Server is referred to as publishing, you won’t find Report Server under the Publish Menu.
即使将报表部署到Power BI Report Server称为发布,您也不会在“发布”菜单下找到Report Server。
You have to Save As… to save the report to the Report Server.
您必须另存为…才能将报表保存到报表服务器。
从SQL Server Reporting Services(SSRS)迁移 (Migrating from SQL Server Reporting Services (SSRS))
You can migrate existing reports from your SQL Server Reporting Services to Power BI Report Server with minimal effort for native mode at least:
对于本机模式,您可以花费最少的精力将现有报告从SQL Server Reporting Services迁移到Power BI Report Server,至少:
- Backup existing SQL Server Reporting Services Database 备份现有SQL Server Reporting Services数据库
- Install the Power BI Reporting Server 安装Power BI报表服务器
- Restore Reporting Server Databases to Power BI report server instance 将报表服务器数据库还原到Power BI报表服务器实例
- Connect to Reporting Server Databases using the Report Server Configuration Manager. 使用报表服务器配置管理器连接到报表服务器数据库。
If you are using SharePoint mode there is a script you can use which can be downloaded here.
如果使用的是SharePoint模式,则可以使用一个脚本,可以在此处下载该脚本。
系统要求 (System Requirements)
To be able to install Power BI Report Server, you need at least:
为了能够安装Power BI Report Server,您至少需要:
- Windows Server 2012 Windows Server 2012
- SQL Server 2008 or later SQL Server 2008或更高版本
- SQL Analysis Services 2012 or later SQL Analysis Services 2012或更高版本
In terms of licensing, as long as you have an active SQL Server license with Software Assurance, you are goo to go. Alternatively, you need Power BI Premium.
在许可方面,只要您拥有具有软件保障的有效SQL Server许可,您就可以继续使用。 或者,您需要Power BI Premium。
更新 (Updates)
Unlike SQL Server which only gets updated with Service Packs and new versions. Power BI Report Server will get updated 3 times a year and have additional security patched in between if that should be required. This is known as the Modern Life Cycle Policy.
与仅使用Service Pack和新版本进行更新SQL Server不同。 Power BI Report Server每年将进行3次更新,并且如果需要的话,还会在其间修补其他安全性。 这被称为现代生命周期政策。
Since Power BI Report Server goes hand in hand with Power BI desktop, you need to ensure that the Power BI desktop version which will be used by all users will be upgraded at the same time as the Power BI Report Server, to ensure that the versions remain in sync.
由于Power BI Report Server与Power BI桌面并驾齐驱,因此您需要确保所有用户使用的Power BI桌面版本将与Power BI Report Server同时升级,以确保这些版本保持同步。
![](https://www.sqlshack.com/wp-content/uploads/2017/06/Image_2.png)
结论 (Conclusion)
If you are looking for more interactivity than what your typical SSRS paginated reports can provide, and you are not yet ready to go to the cloud; then Power BI Report Server is probably the right solution for you.
如果您正在寻找比典型的SSRS分页报告所能提供的更多的交互性,并且还没有准备好进入云环境; 那么Power BI Report Server可能是适合您的解决方案。
The preview release is now available for download on the Power BI website.
现在可以在Power BI网站上下载预览版本。
参考资料 (References)
- Power BI Report Server Preview Power BI报表服务器预览
- Reporting Services Web Portal Reporting Services Web门户
- Modern Life Cycle Policy 现代生命周期政策
翻译自: https://www.sqlshack.com/reporting-in-sql-server-power-bi-report-server/