How can I parameterize schema name in quick sight dashboard created via direct query from database

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 .

@sourceease - Did you define the parameter in your dataset with a default value?

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!

I did as you suggested but still it is not working and getting same error

@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