I want to create a QuickSight dashboard for my event based application to visualize the events. I am receiving events from customers daily and I am using AWS Lambda function to store the events into AWS S3 bucket like this YYYY-MM-DD/EventA and YYYY-MM-DD/EventB. I want to fetch that data into QuickSight on daily basis (Every 24Hrs) in order to visualize and analyze the data as per the need.
Can someone help me understand how I could fetch the data from S3 to QuickSight every 24 hours (like automate the process)?
Also I would like to understand the possible ways, I can arrange the data on QS Dashboard. For each day, I want to show some metrics for an entity and that entity could be same.
The design I am considering: Events → Lambda → S3 → QuickSight.
Do you suggest any improvements on this design? Can you explain?
For example: entityId= ABC123, number of errors = 8, Date = 10/28/2023.
Hi @deeppatel7981 - Welcome to AWS QuickSight and thanks for posting the question. Couple of improvements on the design.
- Is your source data is in compressed format? If not, I will advise to change the file format to parquet before writing to S3 and on top of that create an athena table with 2 partition keys ( date and the event type).
- Integrate athena table with QuickSight .
- For performance improvements, you can think of using SPICE and make the data refresh time as 1 day ( possibly see whether FULL or incremental refresh).
I believe you can update the logic of lambda to compressed the data to parquet and and append the data in the athena table with partition.
Let’s hear from other experts as well, tagging @sagmukhe @David_Wong on this.
Regards - Sanjeeb
Thanks @Sanjeeb2022 After considering other things, I proposed a new design:
Redshift → AWS Glue Crawler → AWS Glue Data Catalog → AWS Glue ETL → S3 → QuickSight.
- No, the source data is not compressed. Definitely, I will consider changing as per.
- Is there a need to do that with the above design?
I also want to understand the cost estimations for using Quicksight here.
Hi @deeppatel7981 - Thanks for sharing the details. If you are data is already in Redshift, you really no need to crawl and put the data in S3. QuickSight can directly connect with Redshift and you can do the reporting on top of that.
If you want to have a quick discussion, we can connect.
Regards - Sanjeeb
Hi @Sanjeeb2022 - Thanks for a quick reply. Let’s connect whenever you have sometime.