How can I parameterize schema name in quick sight dashboard created via direct query from database.
I don’t think you can. I’ve been trying to replace quotes in it, but it doesn’t seem to work.
Can I ask why you would do that? The dataset needs to always have the same schema. Columns and data types
1 Like
We have need to test our dashboards against lower lifecycle environment with different DB schemas; before pushing/pointing them to production environment DB with different schema name - hence the ask. How would you do something like that in QuickSight using Direct Query to DB?
@sourceease - Thank you for posting your query. I have thought of a workaround and trying to depict it below. Please go through it and let me know if this helps you in anyway. Thank you!
Step 1 : Create a parameter in your dataset named paramSchema of type string.
Step 2 : Create a union query with all the different environment schema queries appended to one another and referencing the parameter in the where clause of each query segment. Something of the form like :
select column_a, column_b, column_c
from SchemaDev.Table
where <<$paramSchema>> = ‘SchemaDev’
union all
select column_a, column_b, column_c
from SchemaTest.Table
where <<$paramSchema>> = ‘SchemaTest’
union all
select column_a, column_b, column_c
from SchemaPreProd.Table
where <<$paramSchema>> = ‘SchemaPreProd’
Step 3: Refer this parameter in your visualization and add that parameter as control in your analyses sheet.
This method would allow you to input the Schema name dynamically from the report and which would force only one segment of your dataset query WHERE clause to evaluate to True which will only return that segment data which is basically what you want.
I have tried to replicate it in similar way using other column but by following the same methodology that I explained. Sharing the snapshots of the same.
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
I am unable to use the parameters in dataset queries like you suggested. Is there a documentation link you can share on how to do it?
@sourceease - Unfortunately, I don’t have any ready reference of any documentation at this point of time. Because of that only, I shared snapshot for details. In which step you are facing challenge? I can try to provide more details based on the same so that you get the required direction. Thanks!
I tried your suggested solutions but I am getting syntax error in the query at point where I am inserting the custom parameter in the SQL query. Attaching the screenshot below
of the same .
No, could you please tell me how to decide a default value ?
@sourceease - While creating the parameter, it gives you an option to provide a default value. You can put any of the schema name e.g. dev_bak as the default value to it. Please see the below snapshot of that parameter default value provision section. Hope this helps!
@sourceease - This is weird as I am unable to replicate the issue at my end; rather it is working perfectly. I hope you have created the parameter at the dataset level and not in the analysis (how you have shown in the snapshot). Also, I believe the SQL without the parameter reference is running properly!
Hi @sourceease ,
Custom SQL with parameters feature is currently in preview and not generally available. Once the feature is released, you should be able to test the solution from @sagmukhe .
Kind Regards,
Koushik
1 Like
Hi Koushik,
This is going to be an issue for us. We build and test dashboards against lower lifecycle environment and then push it to production. Inability to point my dashboards to different DB & Schema means lot of manual work to recreate and test dashboards. I need a solution for that. Either
- Custom SQL with ability to point to different DB/Schema at will
- Or Ability to export dashboard code/config-metadata in readable format; followed by text replacement for DB connectivity configurations ; followed by import of the updated dashboard artifact to QuickSight pointing to new DB/Schema.
Without this basic configurability it would be difficult to pursue further with Quicksight in enterprise setups.
I would like to know how other customers are dealing with this. Because I cannot fathom someone working on dashboards directly pointing to production DB or reworking the entire dashboard again and again for every new environment.
Hi @sourceease ,
As I understand, you are looking more at being able to migrate QuickSight assets between 2 different environments.
I posted a few links in an another question which covers asset migration
Workshop on content porting using CLI
Kind Regards,
Koushik