Integrating PostgreSQL Data with Excel-Defined Dashboard Structure in AWS Quicksight

Question & Answer
, , , , , , ,
1

Hello AWS Quicksight Community,

I’m embarking on a project to create a detailed dashboard in AWS Quicksight, with the structure, aggregations, metrics and visualization types defined in an Excel file. This Excel file doesn’t contain the actual data but outlines the desired table, metrics, and types of visualizations for the report. The challenge lies in fetching all the necessary data from a PostgreSQL database (RDS), according to SQL queries that correspond to each metric and aggregation defined for the dashboard.

Here are the key points of my project:

  1. Dashboard Design from Excel: The Excel file details the structure of the report, including fields and visualization types (charts, tables, etc.), without containing actual data. The aim is to mirror this structure in Quicksight.
  2. Data Retrieval from PostgreSQL: All data for the dashboard should be dynamically pulled from a PostgreSQL database. This includes executing SQL queries with JOIN operations across various tables to gather the necessary data for fields, metrics, and aggregations.
  3. Daily Data Updates: It’s crucial that the dashboard data is automatically refreshed daily to ensure that the displayed information is always up-to-date.

My request for guidance from the community focuses on how to efficiently integrate the Excel-defined dashboard structure with data from PostgreSQL. Specifically, I am looking for advice on:

  • Strategies for translating the Excel file’s structure and visualization requirements into a Quicksight dashboard.
  • Best practices for setting up and managing the SQL queries within Quicksight to fetch data from PostgreSQL, especially considering the need for JOINs and complex aggregations.
  • Implementing an automated process for daily data refresh in Quicksight to keep the dashboard current.

I’ve attached screenshots from Excel file.

I would greatly appreciate any insights, detailed instructions, or examples on how to approach this project. If anyone has tackled similar challenges or knows of resources that could help bridge the gap between Excel-defined structures and PostgreSQL data in Quicksight, your input would be invaluable.

image
image1849×819 73.1 KB

Thank you for your support and looking forward to your recommendations!

2

Hi @AlexQuickSight -
A few thoughts/resources to share.
Here’s a link to a QuickSight Community session on using Custom SQL in QuickSight datasets. This has a couple of different examples and discussion of advantages/disadvantages. QuickSight supports both direct query and SPICE (in-memory datasets). QuickSight SPICE datasets support both incremental and full refresh schedules. Documentation is linked here. As far as creating the dashboard itself, you could utilize the Generative BI capabilities for authors currently in preview to describe the visualizations using nlq and then QuickSight will create the visuals for you. Information on the Generative BI capabilities in preview can be found here.