Forum Discussion
"Basic" SQL editor required for ODBC connection to DB2
Hi Sergei,
My DB2 source tables contain (literally) billions of rows of performance data and so I need to use the SQL to perform filtering and return just the data I want, especially if I want to join multiple tables. I then want to be able to re-use the query so that I can change things easily e.g. time periods. I used to be able to do this in older versions of Excel, but can't in Office 365.
If I enter the SQL when I establish the connection, the data gets brought back in a "preview" mode and then the query gets run a second time when I chose the option to Load the data - seems a bit of an overhead to do things in this way.
If I skip adding the SQL query, I get presented with a list of tables / views and then don't seem to be able to enter an SQL query to perform the filtering I need.
Power Query supports folding mechanism. For example, you query the table with few dozens millions of rows, on second step remove unused columns, on next step filter what needed. If use correctly PQ generates SQL query in background (it could be seen from right click menu on steps). Thus it doesn't load millions of rows on first step and perform other operations one by one on them, It loads final result.
Relationships in many cases could be recognized and related records are returned.
If course, if you do not rely on above mechanisms you may use native SQL query, with that folding won't work. One or another. And again, if old query interface was designed to generate SQL query, Power Query use another mechanisms.
However, in File-Options->Data you may activate legacy interfaces (they are disabled by default)
After activation you may use them from this menu
SergeiBaklan
Hi Sergei,I think there's probably something wrong with my setup, as I can't use the legacy Data Connection Wizard option in 365. If I select this and chose my DB2 database, Excel just disappears - no error messages on the screen at all. I don't know why / how this is different from establishing an ODBC connection from Other Sources. I'll see about raising a fault with our internal support team.
We've recently migrated from Win 7 to Win 10 and I've had to update my IBM ODBC drivers to a new version as well.
Some of my source tables have about 400 columns and I might only want 20 columns in my data, so it's much easier if I can use an existing SQL query rather than having to remove 380 columns 🙂
Thanks for your help
Sorry, I had no experience with DB2, have no idea what could be an issue with the driver.
Of course, if you are more comfortable with SQL query, use it. I also use native queries from time to time, it depends on concrete project. The only my point PQ editor is not designed to work with SQL from user interface.
Just in case, for your sample it's not necessary to remove 380 columns, it's enough to select 20. That's easy enough from user interface only