Forum Discussion

AndrewWestran's avatar
AndrewWestran
Helper I
2 years ago

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...
Dataflows
AndrewWestran's avatar
AndrewWestran
Helper I
2 years ago

Hi miguel 

 

As long as this functionality works with the on-premises data gateway, it sounds viable.

I will look into this option.

 

Thanks

AndrewWestran's avatar
AndrewWestran
Helper I
2 years ago

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 database
    GetTableFromDatabase = (server as text, database as text) as any =>
    let
        Source = 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 null
    in
        Top100Rows,
 
    // Get list of databases from the SQL Server
    GetDatabaseList = 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 list
    LoadDataFromAllDatabases = List.Accumulate(
        DatabaseNames[Name],
        {},
        (state, currentDatabase) =>
            let
                DatabaseName = currentDatabase,
                TableData = GetTableFromDatabase("MyServer", DatabaseName),
                NewState = if TableData <> null then state & {TableData} else state
            in
                NewState
    )    
 
in
    LoadDataFromAllDatabases
 
This 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.
😊
  • miguel's avatar
    miguel
    Community Admin
    2 years ago

    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).

  • miguel's avatar
    miguel
    Community Admin
    2 years ago

    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?

  • miguel's avatar
    miguel
    Community Admin
    2 years ago

    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.

  • AndrewWestran's avatar
    AndrewWestran
    Helper I
    2 years ago

    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?