Amazon QuickSight is a fully managed business intelligence (BI) service from AWS that allows users to easily create and share rich visualizations and insights from a variety of data sources. It provides fast and flexible analytics, allowing users to analyze data, create dashboards, and share insights without the complexity of managing infrastructure.

One of the core features of QuickSight is its ability to integrate with multiple data sources, ranging from cloud-native AWS services like Amazon RDS, Redshift, and S3, to on-premises databases and third-party applications. This knowledgebase provides a comprehensive guide to integrating data sources into QuickSight, from the basics to advanced configurations.

Overview of Amazon QuickSight Data Sources

Amazon QuickSight supports a wide variety of data sources, allowing users to connect, import, and analyze data from multiple systems seamlessly. The supported data sources can be divided into the following categories:

AWS Native Data Sources

These include the most common AWS services that QuickSight integrates with natively:

• Amazon S3: For analyzing data stored as files (e.g., CSV, Parquet, JSON).
• Amazon RDS: Relational Database Service for managing and querying structured databases.
• Amazon Redshift: A fully managed data warehouse service.
• Amazon Aurora: A relational database compatible with MySQL and PostgreSQL.
• Amazon Athena: A serverless interactive query service that allows querying S3 data using SQL.
• AWS Glue Data Catalog: A metadata catalog for data stored in Amazon S3.

External Data Sources

QuickSight also supports non-AWS data sources, such as:

• On-Premises Databases: Connect to databases hosted on local or on-premises environments.
• MySQL: A popular open-source relational database.
• PostgreSQL: An advanced open-source relational database.
• SQL Server: Microsoft's relational database management system.
• Oracle: A relational database for enterprise applications.
• Salesforce: A customer relationship management (CRM) platform for sales, marketing, and customer service.
• Excel: Import data directly from Excel spreadsheets.

APIs and Third-Party Data Sources

In addition to native support for specific databases, QuickSight can also integrate with:

• JDBC/ODBC Connections: For connecting to other relational and non-relational databases not natively supported.
• Custom Datasets: Upload datasets from local files in CSV, TSV, JSON, or Excel format.

Connecting to AWS Data Sources

Amazon S3

Amazon S3 is one of the most flexible and widely used data storage services in AWS, and QuickSight allows seamless integration with S3 for analytics.

Steps to Connect QuickSight to S3:

1. Create an S3 Bucket: Store your datasets (CSV, JSON, or Parquet files) in an S3 bucket.
2. Grant Permissions: Ensure that the QuickSight service role has permission to access the S3 bucket. This can be done by assigning the appropriate S3 permissions to the AmazonQuickSight-AccessRole.
3. Connect to S3 in QuickSight:
   • Open QuickSight and navigate to the Manage Data section.
   • Select New Dataset and choose S3 as the data source.
   • Enter the S3 bucket name and path to the file.
   • Once connected, QuickSight allows you to preview and select the data you want to import.
4. Prepare the Data: After importing, QuickSight allows you to prepare, transform, and filter the data before visualizing it.

Amazon RDS

Amazon RDS (Relational Database Service) enables users to create scalable databases in AWS. QuickSight supports direct integration with RDS databases such as MySQL, PostgreSQL, SQL Server, and Oracle.

Steps to Connect QuickSight to RDS:

1. Create an RDS Database: Set up an Amazon RDS instance for your application.
2. Configure Security Groups: Ensure that the security group for the RDS instance allows inbound connections from the QuickSight IP range.
3. Grant Permissions: Ensure that the IAM role associated with QuickSight has the necessary permissions to connect to the RDS database.
4. Connect to RDS in QuickSight:
   • Open QuickSight, navigate to the Manage Data section and select New Dataset.
   • Choose RDS from the list of data sources.
   • Enter the connection details, including the database name, port, and credentials.
   • Select the tables or views you want to import.
5. Data Preparation: After the connection is established, you can use QuickSight's data preparation tools to clean and filter the dataset for analysis.

Amazon Redshift

Amazon Redshift is AWS's fully managed data warehousing service designed for large-scale data storage and analysis.

Steps to Connect QuickSight to Redshift:

1. Create a Redshift Cluster: Ensure that your Redshift cluster is set up and running in your AWS account.
2. Grant Access: Ensure that the Redshift cluster security group allows connections from QuickSight’s IP range, and the AmazonQuickSight-AccessRole has access to the Redshift database.
3. Connect to Redshift in QuickSight:
   • In the QuickSight console, go to Manage Data and select New Dataset.
   • Choose Redshift as the data source.
   • Enter your Redshift cluster connection details, including the cluster name, database name, port, and user credentials.
   • Once connected, you can select specific schemas, tables, and views to analyze.
4. Analyze Data: Use QuickSight to visualize and create dashboards based on your Redshift data.

Amazon Athena

Amazon Athena allows you to query data directly from Amazon S3 using SQL without needing to move the data. QuickSight natively integrates with Athena for fast and scalable data analysis.

Steps to Connect QuickSight to Athena:

1. Set Up Athena Queries: Ensure that you have queries set up in Athena to fetch data from your S3 buckets.
2. Grant QuickSight Permissions: Assign the necessary IAM permissions to QuickSight to access Athena, S3, and AWS Glue.
3. Connect to Athena in QuickSight:
   • Navigate to Manage Data and select New Dataset.
   • Choose Athena as the data source.
   • Enter the Athena connection details, including the database name and SQL query.
   • Select the data you want to analyze and load it into QuickSight.

AWS Glue Data Catalog

The AWS Glue Data Catalog serves as the metadata repository for datasets stored in Amazon S3. QuickSight can integrate with the Glue Data Catalog to discover, manage, and query structured datasets.

Steps to Connect QuickSight to AWS Glue:

1. Register Datasets in AWS Glue: Use AWS Glue crawlers to automatically detect and register datasets from S3.
2. Grant Access: Ensure that the QuickSight service role has permission to access AWS Glue and the datasets registered in the Glue Data Catalog.
3. Connect to Glue in QuickSight:
   • Go to Manage Data and select New Dataset.
   • Choose AWS Glue Data Catalog as the source.
   • Select the registered datasets from Glue and load them into QuickSight.

Connecting to External and Third-Party Data Sources

QuickSight supports various non-AWS data sources, allowing you to bring in data from on-premises environments or third-party systems.

MySQL and PostgreSQL

Both MySQL and PostgreSQL are popular relational databases that QuickSight can connect to for BI and analytics purposes.

Steps to Connect QuickSight to MySQL or PostgreSQL:

1. Configure Database Security: Ensure that your MySQL or PostgreSQL instance is accessible from the QuickSight IP range.
2. Create a New Dataset:
   • Navigate to Manage Data and select New Dataset.
   • Choose either MySQL or PostgreSQL as the data source.
3. Enter Connection Details:
   • Provide the database name, server address, port, and credentials.
   • Test the connection to ensure successful access.
4. Select Tables/Views: Once connected, select the tables or views you want to analyze.
5. Analyze Data: Start visualizing and analyzing data using QuickSight’s dashboards.

Microsoft SQL Server and Oracle

QuickSight also supports enterprise databases like Microsoft SQL Server and Oracle for integrating large-scale business applications with BI reporting.

Steps to Connect to SQL Server or Oracle:

1. Ensure Connectivity: Configure the on-premises SQL Server or Oracle instance to accept connections from AWS QuickSight by opening the necessary ports.
2. New Dataset in QuickSight:
   • Go to Manage Data, choose New Dataset, and select either SQL Server or Oracle.
   • Provide the necessary connection details, including server address, database name, and user credentials.
3. Prepare Data: Choose tables or views for analysis and load them into QuickSight for visualization.

Salesforce and Third-Party Applications

For business users relying on CRM data from Salesforce, QuickSight provides an easy integration mechanism.