Forum Discussion
Dataflow and PowerBI Gateway - Sourcing data dynamically
Hi all,
I have a Dataflow Gen2 which is sourcing data from an on-premises SQL Server via PowerBI Gateway.
The server contains many databases, each representing a retail POS.
These databases may change from day to day, i.e., there may be new databases today that were not there yesterday, without notice.
The Dataflow allows me to access the server in a fixed manner (nothing changes in this regard), but I need to be able to look up the databases to be queried and access them dynamically.
The tables in question will be consistent between the databases.
Based on what I have seen so far, the Dataflow does not support this level of flexibility.
Is there something I am missing in the capabilities of the tool?
I can obviously push the data from the source environment and pre-consolidate to solve this issue, however, I was hoping to solve this issue from the Fabric environment due to constraints on the source side.
Any thoughts and comments are appreciated.
Regards,
Andrew
20 Replies
Thanks for using Microsoft Fabric Community.
At this time, we are reaching out to the internal team to get some help on this.
We will update you once we hear back from them.Appreciate your patience.
I was re-reading your original post and something occurred to me... have you tried to maintain a table with meta data, that is, a table that will keep track of all the DB servers and DB names as they come and go? This would allow you to create a query that connects to the DB containing this meta data table, and using that table, you can in M select any or all of your databases and thus ingest their data.
Hi Element115
I gather that I will be able to use the M "sql.databases" function to acquire the details of the databases that I need to access, so no additional metadata tables should be necessary.
I do appreciate the time and thought you have taken to respond to this query. I am open to any further comments you may have to offer. 🙂
Thanks and regards
Interesting! You're right, that should do the trick. I was not aware M had such a function. Pretty nifty. 😊
This is good news for me. Depending on the release date, of course. Sooner would be better... 😁
(I suspect a Pipeline will facilitate the dynamic sourcing more easily than the Dataflow.)
The internal team has confirmed that, there is no dynamic ability to switch between different databases today. The connections themselves must be validated and are thus static for this reason.
I hope this helps. Please do let us know if you have any further questions.
Thanks
I appreciate that the connection would need to be validated and static. However, I would hope that database selection within the context of a static connection could be dynamic. Privileges from the established connection would apply.
I also appreciate that this would introduce levels of complexity to the static mechanisms of the Dataflow which are hard-wired to a particular table for example, to which the transformation steps would be applied.
It feels like this would be an ideal application of the Notebook capabilities, for example, to select a data source and manage the data definition, sourcing, and push to the final destination in code, rather than using a graphical tool.
Thanks
