Forum Discussion
Dataflow and PowerBI Gateway - Sourcing data dynamically
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
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).
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?
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.
Hi miguel , Element115 , v-cboorla-msft
I have built M code to produce the effect that I am trying to achieve as follows:
let// Function to query top 100 rows from a table in a specific databaseGetTableFromDatabase = (server as text, database as text) as any =>letSource = try Sql.Database(server, database) otherwise null,MyTable = if Source <> null then Source{[Schema="dbo",Item="MyTable"]}[Data] else null,Top100Rows = if MyTable <> null then Table.FirstN(MyTable, 100) else nullinTop100Rows,// Get list of databases from the SQL ServerGetDatabaseList = Sql.Databases("MyServer"),DatabaseNames = Table.SelectColumns(GetDatabaseList,{"Name"}),//Iterate through the list of databases, accumulating 100 rows of data from MyTable from each DB into a listLoadDataFromAllDatabases = List.Accumulate(DatabaseNames[Name],{},(state, currentDatabase) =>letDatabaseName = currentDatabase,TableData = GetTableFromDatabase("MyServer", DatabaseName),NewState = if TableData <> null then state & {TableData} else stateinNewState)inLoadDataFromAllDatabasesThis is working for me up to a point, but I am having some issues with credentials which I am working on resolving with the relevant teams at my organisation.So far, this approach seems to be viable and appears to be functioning fine in conjunction with the Gateway to the on-premises data.Once I have the credential issues resolved, I will follow up with confirmation of success and/or any modifications required for the final solution.Naturally, the code above has been masked and pruned to hide specific details for my implementation, but the code is generic and would work as a framework for any similar requirement.Thanks for all of the assistance and guidance thus far.😊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?