Forum Discussion

jsperson's avatar
jsperson
Frequent Visitor
8 years ago

ODBC and Large Data Sets

Hey folks, I'm having an issue with Power Query (both Power BI and Excel) and am casting a wide net looking for options.   Here's my requirement - the data source has about 300M records. I have th...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

In the ODBC connection, expand "Advanced Options" and then slap in your SQL.

  • jsperson's avatar
    jsperson
    Frequent Visitor
    8 years ago

    Thanks - I'm a huge Power BI fan so I'm not being flip here and I really do appreciate your response. Your answer just isn't acceptable for the typical Power BI user base. If the user knew how to write SQL/M then they really wouldn't need Power Query. We're talking about a pretty basic piece of SQL writing for Power Query (hard for an end user, but should be easy for a query tool).

     

    Here's the query I'm looking for:

    SELECT foo1, foo2, SUM(foo3) foo3, SUM(foo4) foo4 FROM bar WHERE foo3 IS NOT NULL GROUP BY foo1, foo2 LIMIT 1000

     

    This is almost exactly what Tableau produces by default - no tweaks, not special connection string etc. One of the primary purposes of my effort here is to justify Power BI over Tableau. I love the integration of Power Query in both PBI and Excel. I just wish it wrote better queries...

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      8 years ago

      I think that there is another perspective here around justifying Power BI versus Tableau. Power BI comes with Power Query, which is an incredibly powerful data mashup/ingest/ETL tool. To get anything even remotely equivalent in Tableau you have to purchase something like Alteryx, which every shop that owns Tableau buys (at least the people that I know that own Tableau).

       

      So, the focus with Power Query is not to write SQL Queries for people but rather allow them to do much more powerful data manipulation. In order to do that, it needs a starting point in terms of the data. The mechanism for that working is not geared toward pre-filtering the data but rather working with the data as defined. It allows for the pre-filtering of data via SQL if you really want but is perfectly capable of working without it on large rowsets.

       

      I understand what you are saying but I think it doesn't take into consideration the entire picture. When you say you wish Power BI wrote better queries, it can write queries that are incredibly more powerful and better than Tableau. Tableau out-of-the-box is really incredibly limited in it's query capabilities. It is like working with tinker toys compared to Power Query.

      • jsperson's avatar
        jsperson
        Frequent Visitor
        8 years ago

        >>It is like working with tinker toys compared to Power Query.

         

        It's funny you say that. As I mentioned I'm a big proponent of Power BI. Unfortunately for me, the tinker toy tool (Tableau) is actaully returning data. Because of the way Power BI writes queries, it's just blowing up. I don't need to look at the entire picture. One tool (Power BI) just flat doesn't work for large data sets via ODBC without hand feeding it data via custom SQL. Even this is pretty hard because if I use a limit clause, Power BI then proceeds to wrap this and aggregate only on my limited number of records. I can then fully develop my report, but I have to remove the limit clause at some point at which time Power BI proceeds to try to download 300M records again thereby choking. Final victory - "tinker toy" Tableau...

         

        Thanks again for your time. I was already pretty sure that there was not a good solution I was just looking for confirmation.

         

        Edit: grammar.