Forum Discussion
ODBC and Large Data Sets
Thanks for the comment - you are right. Power BI tries to bring over the whole dataset to the client.
I'd be interested in revisiting this as well. Iam hopeful that Power BI will get a fix. I was a big proponent of Power BI until I ran across this problem and ended up dropping it almost entirely out of our practice. Tableau is working great because it simply writes queries that are more appropriate for large datasets.
If you are on a recent release - not sure when it was added -, you can specify the clause to limit rows on previews in Query editor.
See below for an external BigQuery ODBC driver (from Google itself). This should be available for any driver.
I did test by looking at the SQL's BigQuery get:
- if you don't set this, PBI won't know what synthax to use and hence would load the whole set
- once set, PBI appended the SQL with LIMIT 1000
Should work for you too if you don't make any aggregation.
If you aggregate either in Editor or in charts, if PBI deems it's not possible to use query folding, it will load all and aggregate locally. The conditions for folding are very opaque (=undocumented).
If you are in luck, there may be a datatype with which PBI has no issue that is very similar to the "datatype-in-error".
For BigQuery, NUMERIC is problematic but FLOAT64 isn't so I can use a view that cast to float and folding works.
EDIT: I think the solution may be found somewhere here ! https://docs.microsoft.com/en-us/power-query/odbc
by trial-n-error
Try adding SoftNumbers=true in the DataSource option i.e.
Odbc.DataSource("dsn=xxx", [HierarchicalNavigation=true,SoftNumbers=true])
It did the trick for me, PBI sent a proper sum() SQL