Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a customer that wants to be able to have a report based on data from multiple databases. One thing they want is to be able to add new databases into the data model on demand when they want without actually changing the data model manually. The database schema should be the same. I don't know the current number of databases.
My solution was to build a connection table from a parameter and invoke a custom function to return the data as a table.
An example I've created for testing:
let Source = Json.Document(connectionStr), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"server", "db"}, {"server", "db"}), #"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1", "DimProduct", each fx_getTableData([db], [server], "dw", "DimProduct")) in #"Invoked Custom Function"
The function:
let Source = (db as text, server as text, schema as text, tableName as text) => let Source = Sql.Database(server,db), Table = Source{[Schema=schema,Item=tableName]}[Data] in Table in Source
To get the data in a table I just use Table.Combine
let Source = connectionTable[DimProduct], Result = Table.Combine(Source) in Result
This approach works fine in Power BI Desktop. But when I publish the dataset to the services I'm not able to refresh it and get the following error:
Data source error: Unable to refresh the model (id=2510949) because it references an unsupported data source.
Are there any other ways to have a dynamic data source and have the ability to refresh data manually or scheduled?
Are the custom functions not supported in Power BI Services?
Thanks
Hello @GilbertQ
Thanks for your answer.
Unfortunately update via API will not work for me as the datasource that I use is ODBC and it's not supported.
So, the only solution atm is to add new sources manually (if via API is not an option), correct?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.