Forum Discussion

KoenJ's avatar
KoenJ
New Member
1 year ago
Solved

Dynamic data sources with a Sql.Database call

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
 

Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    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"

9 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    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

      • KoenJ's avatar
        KoenJ
        New Member
        1 year ago

        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

         

  • KoenJ's avatar
    KoenJ
    New Member
    1 year ago

    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's avatar
      SamWiseOwl
      Super User
      1 year ago

      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's avatar
        KoenJ
        New Member
        1 year ago

        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