Hi,
I was reading the article here: Advanced use cases of dataset parameters - Amazon QuickSight
on how to setup a dataset parameter but still allow filters in my Analysis to show all values in the drop down. Is this still the best way to do this? It’s a little painful to create a “select distinct” dataset for every filter but not terrible.
The bigger issue I have is how can I have cascading filters in my analysis if I set things up like this. “Show Relevant Values” seems to require that the data comes from the same dataset but if each filter in the analysis is connected to a different dataset, do I need to write something like:
“select distinct filter_a_column, filter_b_column, filter_c_column,from MyTable” and then use that single dataset for my filters?
I was thinking that having dataset parameters tied to filters in the analyses would be more efficient then only using analysis filters, but if I need to first run additional queries to do that, I’m not sure if the work is worth the benefit.
Is my thinking correct here? Anyone have experience with this issue to hopefully save me a bunch of manual testing?
E.g. if you have a datasetA (select * from table1) . you will create a parameter for product and add it into the statement (select * from table2 where product=parameter)
Then you can use the parameter within the analysis as a control (not as a filter). The drop-down of the control contains only distinct products by default.
The cascading only works for the same dataset. Do you really need many datasets?
@ErikG thanks for the reply.
I was just following the steps in that article, specifically, this section:
When you use dataset parameters that are inserted into the custom SQL of a dataset, the dataset parameters commonly filter data by values from a specific column. If you create a dropdown control and assign the parameter as the value, the dropdown only shows the value that the parameter filtered. The following procedure shows how you can create a control that is mapped to a dataset parameter and shows all unfiltered values.
Lets say I have datasetA with query:
select CaseId, stuff from tableA where Caseid = <<$CaseId>>
and in my Analysis, I have a Parameter CaseId that’s mapped to Dataset Parameter CaseId. I create a drop down Control from this Analysis parameter and link it to datasetA. This drop down will not show any values since the dataset is pre-filtered
Note that the data that feeds this analysis is dynamically changing over time so I don’t know what CaseIds will be there ahead of time.
Maybe this is not what dataset parameters are intended to accomplish and I’m thinking about this wrong?
The cascading only works for the same dataset. Do you really need many datasets?
This is partly based on how I understood the above. I thought I needed a select distinct for each Control in the Analysis and then I would not be able to cascade them.
So I was able to create a separate dataset to feed the Controls in my Analysis by just doing:
select distinct dimension1, dimension2, dimension3 from Table.
That obviously allows me to cascade them and combine together with the dataset parameters (which do in fact help performance by an order of magnitude in terms of MB scanned and query time).
I guess alternatively I could have the DataSource for Control2 include a Parameter that’s tied to the value of Control1 and do the cascading via SQL but maybe the incremental benefit is not worth the effort here…I basically want to do as little as possible
