Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Direct Query with multiple datasources

I'm working with (almost) real time data. I have multiple datasources (SQL servers) and multiple tables. All queries use "Import" mode (instead of Direct Query). I'm not using Append Queries function. Each query gets data from all servers at the same time.

I would like to understand which one is the best approach: Import or Direct Query. Should I use Direct Query with multiple datasources? Will the datasets refresh faster if I use Direct Query? Will automatic refresh still work?

Below is an example:

New Source -> SQL Server -> Set "ServerA" and "Database" -> Select "Direct Query" -> Ok -> Go to "Advanced Editor" -> Add "SourceB part of the query" -> Done.

Final query:

 

let
    SourceA = Sql.Database("ServerA", "Database", [Query="SELECT * FROM ...]),
    #"ColSourceA" = Table.AddColumn(SourceA, "Server", each "ServerA"),

    SourceB = Sql.Database("ServerB", "Database", [Query="SELECT * FROM ...]),
    #"ColSourceB" = Table.AddColumn(SourceB, "Server", each "ServerB"),

    #"CombinedTables" = Table.Combine({
    #"ColSourceA", 
    #"ColSourceB"
    })

in
    #"CombinedTables"

 

Thank you!!

  • Anonymous , If you want to do a transformation like append/merge. Import mode is best.

1 Reply

  • Anonymous , If you want to do a transformation like append/merge. Import mode is best.