Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
User | Count |
---|---|
21 | |
16 | |
15 | |
15 | |
12 |
User | Count |
---|---|
42 | |
25 | |
22 | |
18 | |
11 |