"This dataset includes a dynamic data source." - Help

[Shamatix]

Hi there,

So I made a report and getting this error when I try and refresh it on the website.
I have a table with a database names in a column and a couple of other queries that loops through this table and queries data from these databases...
I find it both annoying and stupid if this is "too much" for Power Bi to handle..?
Why do I need to make my report "dumber" just to get the refresh stuff to work?

Can someone please help me out here....

https://i.imgur.com/OILP29w.png

Example:

let
// Define a parameter named "DatabaseTable" that contains a list of records with both DatabaseName etc...
DatabaseData = Table.ToRecords(#"DatabaseTable"),

// Define a function that takes a record as input and returns data from the "ProductLines" table with an extra column for the database name.
GetProductLines = (record) =>
let
DatabaseName = record[DatabaseName],
Integration = record[Integration],

// Connect to the database.
Source = Sql.Database("XXXXXXXXXXXXXXXXXX", DatabaseName),

// Check if the "ProductLines" table exists.
ProductLinesExists = List.Contains(Source[Name], "ProductLines"),

// Determine which query to use based on the Integration value.
Query = "SELECT * FROM XXXXXXXXXX WHERE Id IN (SELECT ProductId FROM XXXXXXXXXX)",

// If the table exists and the Query is not empty, get its data.
Data = if ProductLinesExists and Query <> "" then
Sql.Database("XXXXXXXXXXXXX", DatabaseName, [Query=Query])
else
#table({}, {}),

// Add a new column for the database name.
AddedDatabaseName = Table.AddColumn(Data, "DatabaseName", each DatabaseName)
in
AddedDatabaseName,

// Now I call the "GetProductLines" function for each record in the "DatabaseData" parameter.
ProductLinesList = List.Transform(DatabaseData, each GetProductLines(_)),

// Now I combine all tables in the "ProductLinesList" into one table using a union all.
UnionAll = Table.Combine(ProductLinesList),
#"Filtered Rows" = Table.SelectRows(UnionAll, each true)
in
#"Filtered Rows"

[Martin_D]

Hi @Shamatix ,

The reason why Power BI Service does not support this query is security.

1. This way Power BI prevents that someone can inject the loading of unauthorized data by adding a row to a source table from which the dynamic queries are created.
2. Since you load a list of databases dynamically and Power BI Service does manage credentials on database level (not on server level) it does not even know what credentials to ask for when you set up the database connections and credentials for the dataset in Power BI Service. And there is no interaction intended at (manual or scheduled) dataset refresh time for asking for credentials once a new data source appears in your database table.

Power BI Desktop also gives you a security warning and also asks you for credentials, but you can turn off these security warnings completely and once they are confirmed they also do not appear again.

Possible solutions are:

• If your list of databases and tables to load is rather static over time consider implementing static Power Query code that allows Power BI to determine the data sources and credentials at dataset setup time.
• If you want to build a truly metadata driven loading process, consider adding an upstream data warehouse. This is exactly why Power BI advanced to Fabric, since this is a frequent use case for larger or more versatile data projects than within the scope of Power BI Desktop. Alternatively to Fabric, you can use SQL-Server/SSIS on premises or Azure SQL Database/Data Factory as cloud tools to implement your solution.

BR

Martin