Is there a way to pass a parameter to alter the direct query?

I have a complex query to get the changes made to a customer’s account. In SQL I can write “select * from complex_query where customer_id = 123456” However, for tens of thousands of customers the query is very slow. One option is to make it a spice but there is limitation on refresh frequency( min is hourly) and doesn’t show the real time data .

Is there a way to have a control of textbox of customer_ID and based on the Input the query is changed to “select * from complex_query where customer_id = ${customer_id }”

Hello, this is not supported today. However QuickSight has this feature in roadmap.

2 Likes

Hi @sdhoju26 , I am curious why aren’t you directly use the filter in the analysis for the customer_id, do you see an obvious degradation for performance on that?
And as Mayank point out, we are developing the new capability to define and use parameter in data prep custom SQL, but we don’t typically recommend use that since the performance should be similar with using filter directly in analysis.
Happy to hear your use case. thanks!

@emilyzhu, I’m going to add my 2 cents worth. We have some queries that use ‘with clauses’. If the parameter can be embedded in this with clause, then redshift can return the data very quickly. Without this option, we are forced to create a spice dataset, and the refresh time for some of our datasets is over an hour and approaching 2 hours. I don’t have an example at the moment. I need to get with Jesse & Jose and provide them with an example.

@cgreenacre - in our dashboards we use direct query datasets connected to RedShift.

In our dashboards, we set up a filter on the customer_id field. This filter is configured to use a Parameter as the filtered value. My understanding is that when the dashboard renders and a parameter value is provided to a field filter, the filter uses the Parameter value when QuickSight determines the query to be send to RedShift per visual.

This should allow you to run direct queries against RedShift data source via Direct Query and query on only a subset of your dataset to display a dashboard.

Hopefully that helps your use case!

1 Like