Being a Data Analytics company, we would love to share how the AWS analytics tool QuickSight uses a Snowflake warehouse.
Before getting started, let’s have a brief look at AWS QuickSight and the Snowflake warehouse.
AWS QuickSight is a cloud-based Business Intelligence service that is used to analyze data from your own data sources or AWS data sources to deliver insights by using a robust in-memory engine. QuickSight is used to create interactive dashboards and can be shared with others.
Snowflake is an analytic data warehouse that can be used as Software-as-a-Service (SaaS). Its unique data warehouse architecture can handle diverse data in a single system. It processes queries using massively parallel processing computing power.
Procedure to connect QuickSight to Snowflake
A) Log in to your AWS console. You will find Services on the screen; click on it to see the different services provided by AWS. For us, we need Data Analytics services under which QuickSight is present.
B) If you are a first-time user, you have to log in to QuickSight with your Account ID. Once that is done, the QuickSight home page opens, where you see different options like New Analysis and All Analyses. If you are unsure about using the tool, go through the Tutorial videos for further assistance. To start a new report, click on New Analysis on the top left corner of the page.
C) Now a new page appears in front of you where you can create a new data set. A data set is a collection of tables in databases or a file if you are connecting to Amazon S3, which is one of the data sources. A data set stores the raw data as well as the changes you have performed, such as renaming the field or changing its data type.
D) After clicking on Data sets, now you have to choose the data source from the existing data sources which you have created already or connect to a new data source, for which Amazon provides different choices, such as
- A local file or Excel file using upload a file option.
- Salesforce – credentials must be provided.
- Amazon Redshift, Snowflake, Amazon RDS and other databases need to provide details for server, database hostname, and valid credentials for the connection.
E) In this blog, we will connect to the data source Snowflake. For that, we have to provide the database server, database name, warehouse name, username and password. Then click on Validate Connection to test the connection and, once it is verified, create a data source. Whatever credentials are given are case-sensitive.
F) Once the validation is completed, the connection to Snowflake has been set up. Here we have two ways to select a table:
- Use Custom SQL or
- Choose a table from the list.
When writing a custom SQL query with the Use Custom SQL option, you can choose Edit/Preview Data for data preparation or choose Confirm Query to validate the SQL query without errors.
G) We can import data set into SPICE (Super-fast Parallel In-memory Calculation Engine). It is used for storage, and its processing capacity speeds up analytical queries. By using SPICE we don’t have to retrieve the data from the database every time while doing the analysis, because it imports the data into its own storage.
A direct query retrieves data from the database every time during the analysis, which takes more time compared with SPICE.
H) By using Add Calculated Field, we can write a formula with an aggregate function for analysis or transform field types using functions like dateDiff, avg, addDateTime, etc. For further information, refer to this link: https://docs.aws.amazon.com/quicksight/latest/user/adding-a-calculated-field-analysis.html
I) If we have a requirement for data analytics where we have to use more than one table, in those scenarios we can use Add Data to add a new table to the data source and configure the joins between the tables.
J) QuickSight Visuals are graphical representations of the data using different visual types like pie charts, bar charts, line charts, maps, etc. We can also apply filters to both regular and calculated fields of the visual data. QuickSight filters are also used to exclude a visual element representing a particular value. The story option is used when we have to show two different charts with different filters in a single report. We can access the dashboard by using parameters.
K) We can create and publish a dashboard from the analysis made from the data sources for the business requirement. Authorized users can view and filter the dashboard without changing the underlying data.