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 }”
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.
in our project we want manage roles and access.
we want pass that the client id to query which is assigned to the dashboard user.
Means only that clients dashboard dashboard must be visible to the dashboard user that he have access.
Rest of clients data should not be visible to dashboard user.
This might be a different topic. One of the solution(unsecure) to your requirement is to use the dynamic default in parameter.
Create a dataset of user_ID and client_ID. Then in your dashboard create a parameter clientID. Set the dynamic default for clientID from the dataset. Create a filter (not control) cliend_id = clientID. This is fun unsecure trick that works. Disclaimer, this is not secure and can cause data leak if someone hardcode the default value using URL
I use it so my agents don’t have to mess with controls and we are okay to show all clients to all agents. This steps make their life easier by skipping selecting their names in control.
Thanks for the solution.
We already implemented the same solution.
This solution works but we are not able update the Show relevant values of the dropdown.
Is there any way to update relevant values in dropdown using same solution or any alternative way.