Parameterizing where clause in SQL queries

[cramkumar86]

Hi,

I am fairly new to Power BI. I am currently working on a report that connects to Impala and fetches 9 million rows using Import option. The import option is being used here, as I am using ODBC connector that doesnt support direct query. Unfortunately, the native Impala connector wont work in our organization, for other reasons. 

As I am fectching 9M rows into Power BI desktop, it takes a lot of time for fetching entire data. Hence, I am using a parameter to modify the where clause and retrieve only a subset of data into PBI desktop for development purpose. Is it possible to manage the parameter from PBI report server, once saved? If not, how do we ensure that we dont end up fetching all data before saving the file to report server?

[jdbuchanan71]

Wasn't sure if you could access the dataset on the report server like you can on the service.

[jdbuchanan71]

Does this help?  The 2nd section is about managing parameters in the PowerBI service.

https://learn.microsoft.com/en-us/power-bi/connect-data/service-parameters

[cramkumar86]

This doesnt help as well, as I am using report server for managing the published reports and not Power BI service. The instructions are meant for Power BI service.

[cramkumar86]

Thanks for the response. I have watched the video. It is about query folding and does not answer my question. I am not looking at enabling or disabling query folding. I would like to know if the parameter that I created within Power Query for passing values to where clause can be controlled from the report server once published.

[jdbuchanan71]

@cramkumar86 

Take a look at this video that discusses query folding and how to see if it is happening.
https://www.youtube.com/watch?v=QEFze-LdLqo&ab_channel=GuyinaCube

I think you can apply a filter in PowerQuery and it may fold back as a WHERE clause.