Forum Discussion
Programmatic Data Sources?
Assuming a few things like:
- All databases come from the same server
- The same authentication / credentials used has access to all databases
- You have a logic in place to determine what tables to pick from each database correctly
You can use the SQL Database connector, enter just the server name and that will yield a table with all the available databases inside of the server. From there, you can create your own query with the custom logic of your own or even package your logic as a function that could run against every single database in that server and yield a single query that you could later sink / output / load to a destination of your choice.
The article below might be able to help you with how to create functions:
Using custom functions in Power Query - Power Query | Microsoft Learn
For the SQL Database connector, you can also simply use the function Sql.Databases:
So with Azure Data Factory I can do something like this, where I get the list of databases that need to have data pulled from (they are all identical, except iterated for each fiscal year), :
and then for each one run a loop that copies in data from specified tables to the sink database:
This is all accomplished using the Expression Builder to input dynamic values where needed.
With Dataflow Gen 2 I need to be able to populate the queries dynamically the same way. As you can see below in my first test of creating a flow, for a single table I had to add the last 10 years of a single table manually:
These all get combined into a single destination table which is then published to the lakehouse. I have ~60 or so such tables, each of which has 10 years of iterations that need to be brought in so that we can access the data with Direct Lake mode in Power BI. That's my current goal for utilizing Fabric, so if I can avoid having to update 60 different flows every fiscal year to add a new year's table to the list that would be great. There are also various csv and excel files that are used in some cases, but they are incidental to the core data.
Assuming a few things like:
- All databases come from the same server
- The same authentication / credentials used has access to all databases
- You have a logic in place to determine what tables to pick from each database correctly
You can use the SQL Database connector, enter just the server name and that will yield a table with all the available databases inside of the server. From there, you can create your own query with the custom logic of your own or even package your logic as a function that could run against every single database in that server and yield a single query that you could later sink / output / load to a destination of your choice.
The article below might be able to help you with how to create functions:
Using custom functions in Power Query - Power Query | Microsoft Learn
For the SQL Database connector, you can also simply use the function Sql.Databases:
Ahh! That's really good, I totally missed that I could just grab everything into one super query and then split off from it as needed. That sounds very doable and I'll give it a try asap. Thank you!
Hi wcarter ,
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.