Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Pasting complex SQL query into Power BI

I have been using Toad Data Point to query a large Vertica database with a query given to me by a peer. The query is a rather frightful mixture of joins and window functions that mere mortals like me...
Need Help
EVEAdmin's avatar
EVEAdmin
Post Patron
6 years ago

When it comes to very complex queries, I prefer to run them individually in SQL Server and insert the results into a table. Then I get POwerBI to query that table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    EVE, If I did that, I would have to run the server-side query each time I wanted to use the dashboard, wouldn't I?

     

    That's not very user-friendly, is it? Telling my users, "hey, before you go to powerbi.com for your reports, first log on to this other system and run this query," is a non-starter.

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous how you are connecting to your Vertica data source

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous got it, you are using native connector, why not you create a view in your vertica database and use that to feed to power bi

    • EVEAdmin's avatar
      EVEAdmin
      Post Patron
      6 years ago
      Anonymous wrote:

      EVE, If I did that, I would have to run the server-side query each time I wanted to use the dashboard, wouldn't I?

       

      That's not very user-friendly, is it? Telling my users, "hey, before you go to powerbi.com for your reports, first log on to this other system and run this query," is a non-starter.

      Can you schedule automatic refresh?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Scheduling automatic refresh still doesn't solve the problem of running the server-side query. It's also not compatible with DirectQuery mode, if I understand Power BI correctly.