Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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
Can you migrate some of the data to a lakehouse? Perhaps an aggregate version of the source data? Storage cost is very inexpensive from what I've seen.
Thanks for your comment, @Element115.
To manage this migration to the lakehouse without the dynamic DB access capability, we would need to move the functionality to the source system, e.g., export the data to Azure Blob storage and then import it into the lakehouse.
This is our only alternative at this point and is unnecessarily complicated, where Fabric touts a unified toolset, which falls short in this scenario.
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.
Thanks.
I have responded to all replies on this thread.
Please let me know if there is a specific comment that I have missed.
Many thanks
Apologies for my previous comment. I just wanted to check whether your query got resolved. If no please let us know.
Otherwise, will respond back with the more details and we will try to help.
Thanks.
No problem 🙂
This issue is not resolved.
I would be interested if this functionality is possibly supported by Notebooks/PySpark or other features of the Fabric environment. Perhaps I have missed something.
Regards
If you're trying to connect to a single source that can have many databases (such as SQL Server), then you can use the SQL Server connector without defining a database to connect to and then define your own M query logic as to what table you'd like to query from which specific database inside of the server.
Dataflows is flexible enough in this matter, but it also relies on your own requirements and the logic that you wish to apply.
The SQL Server connector without defining the database will use the Sql.Databases function. If you prefer to write your own M code and more of a code-first approach like in a notebook you can also take that route with Dataflows.
Hope this helps.
Hi @miguel
As long as this functionality works with the on-premises data gateway, it sounds viable.
I will look into this option.
Thanks
Hi @miguel , @Element115 , @v-cboorla-msft
I have built M code to produce the effect that I am trying to achieve as follows:
I don't think that there's a need to use Sql.Database when you're already querying and gaining access to all databases from the server with Sql.Databases. You're introducing the issue of unbinded connections when you're using new data source functions to the mix - if you just leverage the tree view given by Sql.Databases you should be able to achieve the same without incurring into accessing new data sources (and then have the credentials issue that you're seeing).
Hi @miguel
I am using the Sql.Database object since I will need to address 2,000 databases containing the same table name (MyTable) to extract the data from these. Will using the tree view not imply me having to manually select all 2,000 tables to achieve the same effect?
Perhaps I am not clear on the tree view functionality that you are referring to here.
Are you perhaps suggesting using navigation code from the Sql.Databases output to draw the second level detail in stead of Sql.Database with unbound connections as you say?
only seeing this now, but yes. The output of a Sql.Databases is a table with all the databases inside of that server.
You can then create a function that goes through each of those databases from the output of the Sql.Databases, retrieves the data that you want and combines it as you see fit.
is not really an object, but rather a data source function. Every time that you use that function it has the potential to require a new linkage of a connection to its data source path.
Are those databases in the same server? or are you trying to access different servers?
If they are in the same server, have you considered using Sql.Databases instead and creating a function that explores the databases in that server and then extracts the data from each table within that Server and then combines them as you see fit?
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.
Check out the October 2024 Fabric update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
4 | |
3 | |
3 | |
1 | |
1 |
User | Count |
---|---|
6 | |
5 | |
4 | |
3 | |
2 |