We have a use case where we are bringing over 7k tables from redshift which has mostly archival data and will be used for storage if someone wants to come and view the data in the tables.
Our team thinks this: We can give access to users directly at redshift database, but users are not very technical, so they can’t write queries and filter the data. Instead what they wanted to check if Power bi/quicksight can read all these 7k tables, and we give access to the users and they can just preview the data in the table and apply basic filters.
I dont like the idea of bringing 7k tables into a visualization tools like power bi/quicksight, but business thinks that this approach would be helping them, as they should be only needing basic effort from thier end to view data in the tables.
Question: Is there a way in quicksight where can just preview the tables without actually building a visualization? It should be just view the table, and they should be able to filter on any column in the table.
I know this is not a usual use-case, but hoping someone might have an idea. TIA.
@Naveed thanks for your reply. Is there a feature in qs, where we can pass table name as parameter and use it in a filter to view the data of a particular table when filtered on that table?
example: user will have a dropdown or drilldown on table names, and when selected, it should show the data related to that table?
Hi @yesh - Welcome to AWS QuickSight community and thanks for posting this use case. One of the approach to create folder structure in QuickSight and organize your whole 7K tables as per LOB or domain or any business understanding and import all these tables to respective folders programmatically and user can use the data set to create simple tabular reports. However i agree with all the above conversations and possibly as MVP you can discuss with users and bring those tables which are used more frequently ( this can be extracted from Redshift) and make a priority to bring those tables first to QS.
@Sanjeeb2022 thanks much. Couple of last questions i have:
When publishing the report, i dont see any option to publish to a particular folder(like workspace in powerbi). Is that not an option? As of now? I only see that once we publish and then we can move the report to a folder
And if we have both pii and non pii data, access handling will only be at report level and may be at dataset level using rls. But i dont see any option at a folder level( like workspace level in pbi). Is this true?
Hi @yesh - You can use the create_folder_membership api to add the contents to a particular folder. Please see the below documentation for the same. I will suggest to collect all your requirements and start exploring to develop a tool in which turn call quicksight apis to perform the relevant work.
@yesh interesting question
I can’t think of a solution to use dashboard with a drop down list allow user to select and preview table
But I can think of another solution, please see if it works.
If yes, please mark as solution.
You can create 7k datasets by using API CreateDataSet - Amazon QuickSight
And grant your user access to view this dataset and create new analysis. The benefit is your user is able to pick any one of the 7k datasets to view the data by creating analysis. It’s not as user friendly as a filter in dashboard but also not a very difficult steps. you may refer to the steps here Tutorial: Create an Amazon QuickSight analysis - Amazon QuickSight
The dataset can be grouped into different folders like what @Sanjeeb2022 has mentioned, it helps user to find out the related dataset and better for governance. You may govern the access by folder instead of individual dataset. You may also govern access by user group
And there may had a chance that users have created many dummy analysis and don’t clean up. You may use API to automate this clean up process DeleteAnalysis - Amazon QuickSight
@yesh, let us know if solutions recommendation from @royyung, @Naveed and others helped you address your requirement needs by marking appropriate responses as solution.
@Sanjeeb2022 , thanks for all the help. From all the above responses. I could gather these:
Each dataset is a table in Quicksight. We can create folders to club all the datasets, but we need to bring in all the 7k tables as 7k datasets and then do the clubbing.
Datasets can be created through the boto3 API
All users need to be authors if at all they are looking to browse the data and apply filters on the data
Access can be governed based on Folders created for datasets rather than individual datasets.
Thanks for the community for looking into our use case and trying to help out. Much appreciated and is welcoming for new users like me. I think we can close this case, because since we now know what to do, i will explore and come up with any questions in the future
Hi @yesh,
if you really want to create 7k datasets, I would recommend to implement some kind of monitoring on database level, to see which tables are actually used by users.
Because if they were my users I would be highly interested in usage.
Maybe you can join a few tables to reduce the number of dataset and create ola better experience to the users.
Don’t get me wrong. I still can’t imagine a case where you need to analyze 7k tables even in a whole company. And I did a few projects so far (with not half of the tables in total :-D).
Thank you @yesh . Good to see QuickSight community helped you in clarifying some of your problem statements. The suggestions provided by @ErikG@royyung are also good. Please have a look on them as well. The post which helped you in getting the info and detail , please mark that as solution so that it will help wider QuickSight community.
Hi @Kristin - just fyi, good to see that QuickSight community helped in providing the details and solutioning to one of the interesting problem.
So we will do it.