Dynamic data sources with a Sql.Database call

[KoenJ]

Dear community,

For context, I have 10s of databases on the same Azure SQL server to run a SQL query on.

My current approach in Power BI has:

1. A static table called "table_Tenants", which is the result of Power BI's "Enter Data" function, with a column called "DatabaseName"

2. The following steps to invoke the SQL query on each row of that table:

let
    Source = table_TENANTS,
    #"Invoked Custom Function" = Table.AddColumn(Source, "fx_LP", each
    let
        SourceConnection = Sql.Database(
            "REDACTED_SERVER_NAME", 
            [DatabaseName], 
            [
                CreateNavigationProperties=false, 
                CommandTimeout=#duration(0, 2, 0, 0), 
                MultiSubnetFailover=true
            ]
        ),
        Query = Value.NativeQuery(
            SourceConnection,
            "SELECT ... FROM .. ",
            null, // No parameters to pass to the SQL query itself
            [EnableFolding=false] 
        )
        in
            Query
    ),    
    #"Expanded fx_LP" = Table.ExpandTableColumn(#"Invoked Custom Function", "fx_LP", {"Date", "NbOrders"}, {"fx_LP.Date", "fx_LP.NbOrders"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded fx_LP",{{"fx_LP.Date", type datetime}, {"fx_LP.NbOrders", Int64.Type}})
in
    #"Changed Type"

This all works fine in Power BI desktop. However, after publishing, the following error is shown on refresh:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

I know there are a number of limitations on building dynamic data sources; the documentation is not very specific on what actually is supported. Is there any way to achieve what I'm trying to do here?


Thanks,
Koen
 

[KoenJ]

This question is more about "combining the same table over multiple databases", not combining multiple tables in 1 database.

Doing that in a stored Procedure is far from ideal; the goal was to handle all "business insights" data collection outside of SQL, in Power BI. Is there any other way to iterate a (fixed) list of databases from Power BI?

[SamWiseOwl]

Can you do this on yours?

Connect to the Server, filter to the databases, expand table names, filter to tablename, expand tables:
let
Source = Sql.Databases(".\sql2019"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Construct-a-Creature" or [Name] = "Create_A_Creature_02" or [Name] = "Movies2021")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Name", "Data"}, {"Name.1", "Data.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Kind"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Name.1] = "Product")),
#"Expanded Data.1" = Table.ExpandTableColumn(#"Filtered Rows1", "Data.1", {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost", "Family", "Habitat", "Purchase", "Sales"}, {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost", "Family", "Habitat", "Purchase", "Sales"})
in
#"Expanded Data.1"

File attached.

[KoenJ]

Thanks Sam,

This is the first time I see the datasource getting successfully refreshed while dynamically iterating databases. (I guess not having the dynamic Sql.Database clears the "dynamic data source" check).

However, I need to be able to execute a specific SQL statement on those databases; is there any way to achieve that from the dynamically listed databases?

Thanks again!
Koen

[SamWiseOwl · Accepted solution]

let
Source = Sql.Databases(".\sql2019"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ( [Name] = "Construct-a-Creature" or [Name] = "Create_A_Creature_02" or [Name] = "Movies2021")),

Rows = Table.AddColumn(#"Filtered Rows", "Return", each Value.NativeQuery(Source{[Name=[Name]]}[Data],"select * from product",null,[EnableFolding = true])),
#"Expanded Return" = Table.ExpandTableColumn(Rows, "Return", {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost"}, {"Return.ProductId", "Return.ProductName", "Return.Animal", "Return.HabitatId", "Return.Legs", "Return.FamilyId", "Return.WeightGrams", "Return.ProductionCost"})

in #"Expanded Return"

[KoenJ]

Thanks, Sam.

I did not succeed to get it 100% working, because Power BI started complaining I needed to setup a Gateway. I am assuming that when it iterates too many databases, it requires the gateway.

[SamWiseOwl]

Hi @KoenJ 

Have you tried using a power bi parameter for the  [DatabaseName] then you could run the custom function as a new column within the table_TENANTS.

This wouldn't count as a dynamic data source.

I've mocked it up in the attached file

[SamWiseOwl]

Worked example:

[KoenJ]

Thanks Sam,

Yes, that actually was my initial approach. Afterwards, I tried to make it "less dynamic" by removing the function call.

While your function runs fine locally, it does trigger the same error when it gets published to app.powerbi.com:

So, it sill looks at this as a dynamic data source?

Thanks again,
Koen

[SamWiseOwl]

Dang it. Seems like you can parameterise each bit of the single table and its fine.
As soon as the function is added it throws a wobbly.

Could you create a stored procedure in SQL that combines all the tables?
Sadly you can't pass parameters into a stored procedure as that counts as dynamic string.