Edit parameter functionality options

[ppgandhi11]

Hi,

I have a query that brings data into PowerBI Desktop. This query uses input parameters (StartDate, EndDate). The reports will be used by senior management that are non tech savvy. Currently, in order to change the parameters, the users have to click on Home - Edit Query - Edit parameters and then input the parameters.  Once the parameters are change, the users have to click on "apply changes" that appears at the top yellow bar and then the "Native Database Query" window shows up that displays the whole query and user have to run the query.

Basically there are series of steps involved to change the parameters and refresh the data based on new parameters.

Is there more elegant/sophisticated way to replace this functionality? Something like 2 input boxes and submit button would appear in the main report page where users can change the values and refresh the report based on new parameters.

[Anonymous]

@ppgandhi11,

Have you considered passing parameters to a stored procedure? I found this link, not sure if it helps or not

[ppgandhi11]

I reviewed the link. Thats exactly the process I am following (multi step). So basically query having a parameter like '"&StartDate&"' or stored proc having a parameter '"&StartDate&"' will have same steps when you need to change the parameters and refresh the universe. Thanks!

[Greg_Deckler]

So, generally, you bring in all of the data and then use Slicers functionality to limit the data that you see in the report. Is there a reason why that wouldn't work for you?

[ppgandhi11]

Thanks for your response Greg - I am not even at a slicer part yet. I am just talking about getting the data (based on input parameters). If I have data worth of 10 years, but my area of focus for further slice and dice is only for 1 year, then bringing all the data worth of 10 years and doing slice on time period will be very in efficient in my view. The system resources may not be even enough.

In this type of scenarios, I feel that parameters should be used to get the universe of interest. That is the entire purpose of parameters I believe. Currently it works, but it does not seem to be an elegant way of working. There are series of steps involved to change the universe based on input parameters.

Also, I don't want the end users to see what queries are being executed, that does not seem sophisticated. Thoughts?

[Stachu]

are you sharing the reports using the service, or via pbix files?

if it's the service I would still import the full dataset, and interact with it via what if parameters
https://docs.microsoft.com/en-us/power-bi/desktop-what-if

if it's the latter then you can save as pbit - files stores no data, when users open it it applies the parameters to query the data
changing the parameter from original value will have do still be done via Query Editor

[Greg_Deckler]

Well, generally you bring in all of the data into the model and slice it, that's what Power BI was designed to do. It has an extremely efficient columnar compression data store and in-memory engine that tends to make this scale to millions of rows of data with ease. Alternatively, you could always potentially just connect live to the data source as well. This is really the model of how Power BI was conceived of and designed to work, versus manually changing the data that is imported each time. I think you will likely be pleasantly surprised by the performance of Power BI if you go down this route. I work with millions of rows of data quite frequently and Power BI handles it with ease. Then the user experience is also phenomenal because you can use a date slicer to set start and end date and move it around via a slider and do all of the analysis you want without the clunky process of changing parameters in the Query Editor.

[ppgandhi11]

Thanks again!

My data comes from connecting multiple huge tables (millions or at least 100s of 1000s of records in each). Without date range in my where clause, wouldn't it take so long for database to get those records to power BI?

Even if Power BI would be able to handle it, I wonder the SQL Server DB engine would take lot longer to get ALL of the data. Isn't it?

[Greg_Deckler · Accepted solution]

Absolutely would take longer but generally you automate the refresh of the data in the Service. Also, Power BI recently released Incremental Refresh so after the initial data load, you could potentially configure incremental refresh to only grab the new data when refreshing the data.

Or, it might make sense in your case to use a Direct Query (live) connection to the database. In that case, you aren't loading any data at all but rather as the user selects dates in the slicer, Power BI is live querying the database to bring back the information. I've seen this scale to millions or even billions of rows of data and it is amazingly fast.

Bottom line, there is no better way to improve the user interface/experience if you are going to do this via query parameters. What you are doing is the best you are going to get right now. It's just how query parameters work. If you want a better user experience, you are going to need to go another route.

[ppgandhi11]

When I say "connecting multiple tables", I mean joining tables. Just to be clear. So in power BI, i see a Query1 built that has all the data and Start/End dates as params which are initial filters to reduce the universe to the required volume.