从 Power BI Web 刷新 AWS RDS 数据库 [英] Refresh AWS RDS database from Power BI Web

查看:41
本文介绍了从 Power BI Web 刷新 AWS RDS 数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 Power BI Desktop 中创建了一个报告,连接到 AWS RDS 数据库,并将其发布到 Power BI Web 应用程序,目的是从 Web 应用程序刷新数据集.

I created a report in Power BI Desktop, connected to an AWS RDS database, and published it to the Power BI Web App, with an intent to refresh the dataset from the web app.

我尝试使用 MySQL 和 SQL Server(在 RDS 上)这样做.但是,Power BI Web 不允许我刷新数据集,而是要我安装本地网关.

I tried doing so with both MySQL as well as SQL Server (on RDS). However, Power BI web does not let me refresh the dataset and instead wants me to install an on-premise Gateway.

我不知道为什么这是一项要求,因为我的数据库位于云端而不是本地,并且位于公共 VPC 上.

I am not sure why this is a requirement as my database is on the cloud and not on-premise, and on a public VPC.

是否可以在 Power BI Web 应用程序中刷新 AWS RDS 数据集?怎么样?

Is it possible to refresh an AWS RDS dataset in the Power BI web app? How?

推荐答案

要刷新 Web 应用程序上的 Power Bi Visuals,它需要与数据库成功连接.现在,在连接到 AWS RDS 实例的情况下,由于 AWS 数据库服务器安装在 AWS 虚拟机上,因此它充当该机器上的内部部署源 [6].因此,您必须在 AWS 服务器上安装本地网关,并可能在同一 VPC 上或在最佳安全实践的帮助下访问 RDS.

To refresh the Power Bi Visuals on the Web Application, it needs to have a successful connection with the Database. Now, in the case of connecting to an AWS RDS instance, since the AWS database server is installed on an AWS Virtual Machine, this acts as an on-premise source on that machine [6]. Therefore you would have to install the on-premises gateway on the AWS server with an access to the RDS possibly on the same VPC or with the help of best security practices.

创建本地数据网关和 AWS 的步骤:

  1. 创建一个 EC2 Windows 实例,有关更多详细信息,请参阅此 AWS 文档 [3].有关基于操作系统要求的实例的详细信息,请参阅此 Microsoft 公共链接 [4].以下是对我有用的实例配置,可以根据您的要求而有所不同:一种.AMI 名称:Windows_Server-2019-English-Full-Base-2021.10.13湾实例类型:t2.2xlarge

  1. Create an EC2 Windows instance, please see this AWS documentation [3] for more details. Refer to this public Microsoft link [4] for details around the instance(s) based on the operating system requirements. Following are the instance configurations that have worked for me and can be different based on your requirements: a. AMI Name: Windows_Server-2019-English-Full-Base-2021.10.13 b. Instance Type: t2.2xlarge

请检查您需要在实例安全组入站和出站规则中允许的所需网络端口.请参阅此 AWS 文档 [5] 以了解如何使用安全组.

Please check the required network ports you will need to allow in your instance security group inbound and outbound rules. Please see this AWS documentation [5] to know how to work with security groups.

确保您将密钥对"添加到实例并有权访问 PEM 文件.这将需要 RDP 进入机器.

Ensure that you add a "Key Pair" to the instance and have access to the PEM file. This will be needed to RDP into the machine.

创建实例后,将 RDP 导入其中并安装任何所需的软件.
例如,在我连接到 AWS Postgres 服务器的情况下,我必须安装 Npgsql 4.0.9 版.不幸的是,最新版本不起作用,因此请注意版本兼容性.此外,虽然不是必需的,但我想在远程服务器上安装 Chrome 浏览器作为我浏览器的首选.为此,我必须在 PowerShell CLI 上运行以下命令

Once the instance is created, RDP into it and install any required software.
For instance in my case to connect to an AWS Postgres Server, I had to install the Npgsql version 4.0.9. The latest version unfortunately didn’t work so be mindful of version compatibility. Also though not essential, I wanted to install the Chrome browser on the Remote Server as a preferred choice of my browser. To do so I had to run the following command on the PowerShell CLI

$Path = $env:TEMP;$Installer = "chrome_installer.exe";Invoke-WebRequesthttp://dl.google.com/chrome/install/375.126/chrome_installer.exe"-OutFile $Path$Installer;Start-Process -FilePath $Path$Installer -Args "/silent/install";-动词RunAs -Wait;删除项目 $Path$Installer

$Path = $env:TEMP; $Installer = "chrome_installer.exe"; Invoke-WebRequest "http://dl.google.com/chrome/install/375.126/chrome_installer.exe" -OutFile $Path$Installer; Start-Process -FilePath $Path$Installer -Args "/silent /install" -Verb RunAs -Wait; Remove-Item $Path$Installer

打开远程桌面,安装 Power BI 本地网关 [4].

With the Remote Desktop open, install the Power BI on-Premises Gateway [4].

现在在远程服务器上搜索本地数据网关"并注册您的帐户.请使用连接到您的 PowerBi Web 应用程序的帐户.这可以是您和 Power Bi 有权访问的基于角色的电子邮件……或者您自己的与 PowerBi Web 应用程序关联的电子邮件地址.

Now search for the "On-Premises Data Gateway" on the Remote Server and register your account. Please use the account that is connected to your PowerBi Web App. This could be either the role based email to which you and the Power Bi has access to … or your own email address associated with the PowerBi Web App.

有关创建数据源的详细信息,请参阅参考链接 [6] 以获取更多详细信息.

For details on creating a data source, please see the reference link [6] for more details.

数据刷新调度

  1. 现在返回 Power Bi Web 应用程序,当您单击设置按钮时 >管理网关,在左侧您应该能够看到网关出现.添加凭据并测试连接是否正常工作.如果您看到绿色勾号,则您应该可以在网络上安排数据刷新.
  2. 您可以配置 PowerBi 以刷新数据.有关详细信息,请参阅参考链接 [7].

问题排查

注意当您尝试连接到网络上的数据库时出现的错误.它通常会提供可能遗漏的有用提示.

Pay attention to the error that is given when you try to connect to the database on the web. It would usually have a useful hint of what might be missing.

参考链接:

[3] 使用启动实例向导启动实例 https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/launching-instance.html

[3] Launch an instance using the Launch Instance Wizard https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/launching-instance.html

[4] 安装 PowerBi 网关 - https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

[4] Install PowerBi gateway - https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

[5] 与安全组合作 - https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/working-with-security-groups.html

[5] Work with security groups - https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/working-with-security-groups.html

[6] 将 Microsoft Power BI 服务连接到 AWS 数据源 - https://docs.aws.amazon.com/en_us/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsoft-power-bi-service-to-aws-data-sources.html

[6] Connecting the Microsoft Power BI service to AWS data sources - https://docs.aws.amazon.com/en_us/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsoft-power-bi-service-to-aws-data-sources.html

[7] Power BI 中的数据刷新 - https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

[7] Data refresh in Power BI - https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

[8] https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

[9] 积分/作者本人:https://smart5.co.uk/article/25/on-premises-data-gateway-installation-aws-power-bi

[9] credits / author myself: https://smart5.co.uk/article/25/on-premises-data-gateway-installation-aws-power-bi

这篇关于从 Power BI Web 刷新 AWS RDS 数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆