Is it possible to update a multi-select parameter’s default values programmatically?
For example, lets say that I have a analysis reporting baseball player batting averages for the prior day. There are 100 players in my league, but only 20 play each day. On my dashboard, I do not want a multi-select parameter containing 100 players, 80 of whom do not have values for the prior day. I only want to show the 20 players that played the prior day.
I have a lookup table in my database which contains a list of players who were active each day. I would like to write a script that queries that table, pulls the names of the 20 players who were active the prior day, and pushes that to my parameter so that only those 20 options are available.
Is this something this is possible to do with the QuickSight API, or something similiar?
Unfortunately not - in the actual business use case, the source dataset is 50m+ rows; what we don’t want to do is read in that much information via a SPICE ingestion, and then come up with the list of active players from there. If we can create a parameter, then we can feed that value into a live connection only bring in a small subset of data relevant to what the user selects.
I think this can be done through the UpdateAnalysis call which allows us to reset parameters. However, making changes to the analysis will be followed up by publishing new version of the dashboard from the analysis which needs to be taken care of post this step.
But I would second what @ErikG suggested, create a dataset out of the lookup table to fetch the 20 values and assign the parameter value from this table. This way, all you will need to ensure is the dataset refresh feeding the 20 players. Incase the source to this table is 50M records and you do not want to put them in spice, you can parameterize the dataset to load only the players based on the date parameter.
Maybe you can use Custom SQL to filter the full dataset with your lookup table so you only get the relevant players? If this lookup table needs to be passed the date to get the active players, you can also use a parameter to pass it to your lookup table to have this done dynamicaly.
If it is accessing by console, I agree with @ErikG and @prantika_sinha, instead of using script in API, you may use custom SQL to create a direct query/SPICE dataset to only query the 20players. And use the results of this dataset to setup dynamic default parameter, add this 20players as default parameter values. In the dashboard, apply filter to filter out according to the parameter values