英文:
Change SQL Server database of a web published PowerBI report runtime
问题
我有一个PowerBI报告，它使用SQL Server数据源从数据库检索数据database_{number}。
{number}是PowerBI的查询参数。我可以从数据转换 -> 编辑参数更改它，并且它能正常工作。
是否可以编辑 Web 发布的报告的这个参数？例如，更改URL查询参数？
英文:
I've a PowerBI report with a SQL Server datasource thats retrieves data from a database database_{number}.
{number} is a query params of PowerBI. I can change it from Data Transform -> Edit parameters and it works correctly.
It's possibile to edit this parameter of a web published report? for example changing an URL query params?
答案1
得分: 0
100% 这是可以做到的。首先，通常创建到任何一个数据库的连接。转到高级编辑器，您会看到类似于以下内容：
let
Source = Sql.Database("1.1.1.1\address", "database_001")
in
Source
只需修改此部分以引用参数：
let
Source = Sql.Database("1.1.1.1\address", "database_" & number)
in
Source
您可能需要考虑更改参数名称，因为 number 是一个预定义的数据类型，在某些情况下可能会产生歧义。
需要注意的是，根据您的服务器性能，将引用更改为动态生成可能会阻止查询折叠。查询折叠是基于您的 PowerQuery 脚本对 SQL 服务器执行单个查询的能力。如果没有查询折叠，服务器可能会执行更大的查询，导致您的数据模型在刷新时需要更长时间/更多资源。
https://learn.microsoft.com/en-us/power-query/power-query-folding
英文:
100% this can be done. First, create a connection to any one of the databases normally. Go to advanced editor, and you will see something like this:
let
Source = Sql.Database("1.1.1.1\address", "database_001")
in
Source
Simply modify this to reference the parameter:
let
Source = Sql.Database("1.1.1.1\address", "database_" & number)
in
Source
You may want to consider changing the parameter name because number is a predefined data type, that could be ambiguous in some cases.
One note, depending on your server capabilities, changing the reference to be dynamically generated may prevent query-folding. Query-folding is the ability for a single query to be executed against the SQL server based on your PowerQuery script. Without query-folding, a larger query will likely be executed against the server, and your data model will take longer/more resources to refresh.
https://learn.microsoft.com/en-us/power-query/power-query-folding
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论