Forum Discussion
Power BI Paginated Report with BigQuery DirectQuery – Filters not pushed down
Hi BeaBF,
Thanks a lot for your response! I’ve got a few follow-up questions and comments regarding your points:
That would require an ODBC connection, right? Unfortunately, I can’t use ODBC. The whole reason I’m connecting my Power BI reports directly to BigQuery is to avoid the need for a gateway server.
I only created the semantic model for these reports.
This solution (using ODBC) would basically be the same as option 1, correct? In Power BI Report Builder, I can’t find a “Power BI connector” data source option. If there is a way to connect directly to BigQuery from there, could you point me to where/how to set that up?
3./5. The data is already partitioned by day. But without the right SQL query being pushed down, that doesn’t really help me. Further pre-aggregations aren’t possible at the moment (or at least I’d need to discuss that with the project team first).
In this report, users can select larger time ranges, so I can’t really predict how many rows will be needed. Is there any way for a Dataflow to fetch missing data from the source dynamically, as needed? (As far as I know, that’s not possible.)
Pre-aggregation might be an option if we revise the report’s purpose — that’s something I’ll need to clarify internally.
To sum it up:
I need to run a parameterized query from a Paginated SSRS Report against BigQuery data, without using an ODBC connection and without copying the data into a Power BI Dataflow first.
From what I understand so far, it seems that there’s currently no way to achieve this.
I appreciate any confirmation or workaround ideas from the community!
Hi m_andel,
The behaviour here is expected because the paginated reports that connect to a power bi semantic model execute DAX queries against that model and the BigQuery connector currently has limited predicate pushdown support for DAX. That is why your BigQuery logs show full table scans without the WHERE clause.
To make the filters execute in BigQuery without ODBC or dataflows, connect the Paginated Report directly to BigQuery using Power Query:
- In Report Builder, create a new dataset using Power Query.
- Use a parameterized native query via Value.NativeQuery and pass your date parameters so that BigQuery runs the filtered SQL directly.
- This runs fully cloud to cloud and no gateway or ODBC needed and the filter is pushed down to BigQuery.
This is currently the only supported approach to make sure parameter filters are executed in BigQuery when using Paginated Reports.
Thanks and regards,
Anjan Kumar Chippa
I can't find Power Query as a ConnectionType
Hi m_andel,
The Power Query connector option is only available in the new Power BI Report Builder, older or standalone report builder versions do not show Power Query as a data source type.
Please download and install the latest Power BI Report Builder, after installing you will be able to see the Power Query option
Thanks and regards,
Anjan Kumar Chippa
You are talking about this version, right?
Download Microsoft® Power BI Report Builder from Official Microsoft Download Center
(15.7.1815.334)
I have already installed that version.
If I am not using the correct app - you have written "standalone report builder versions" - I am not aware that there is another one- please provide me a link.