Forum Discussion
Edit parameter functionality options
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.
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?
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?
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.
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?
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
