This is to help you get started connecting your data warehouse to QuickSight (https://aws.amazon.com/quicksight/).
SECURITY NOTES
QuickSight and the Schoolzilla Data Warehouse servers encrypt traffic as it flows between the two systems. However, QuickSight, due to the version of the database driver they are using, cannot verify our SSL wildcard certificate. Schoolzilla has decided the risk is low and is an acceptable risk until the time QuickSight upgrades the database driver they are using. Schoolzilla has restricted traffic to allow QuickSight to your Data Warehouse which helps further reduce the security risk.
Get QuickSight
Follow the "Get Started" process at https://aws.amazon.com/quicksight/ to enable QuickSight for your organization.
Use QuickSight
Create Data Sources
- Go to Manage Data.
- Click Create Data Set.
- Choose SQL Server (or Salesforce or PostgreSQL or.... for other data sources besides your data warehouse).
Connect to Data Warehouse
Complete the "New SQL Server data source" form with the following details:
- Data Source Name: Name the data source name with something descriptive and meaningful (e.g., Schoolzilla Data Warehouse).
- Connection type: Select "Public Network" since the Schoolzilla Data Warehouse is not in the same AWS account as your organization.
- Database Server: Use your Schoolzilla provided data server hostname as the value for Database server (usually <your Schoolzilla customer id>-dw.schoolzilla.com, for example A100-dw.schoolzilla.com if your customer id is A100).
- Port: Use the standard port for Microsoft SQL Server of 1433.
- Database Name: Use your Schoolzilla provided database name as the value for Database Name (usually Cust<your Schoolzilla customer id>, for example CustA100 if your customer id is A100).
- Username: Use your Schoolzilla provided data warehouse username.
- Password: Use your Schoolzilla provided data warehouse password.
- Enable SSL: Make sure this is unchecked.
As of 3/17/2019, QuickSight does not verify wildcard SSL certificates, which is what Schoolzilla uses. Customers must uncheck Enable SSL until QuickSight upgrades the driver used to connect to MSSQL databases. Even though the SSL certificate is not verified by QuickSight, our data warehouse servers strictly enforce using SSL to encrypt the traffic. The security risk for unticking Enable SSL is related to QuickSight trusting of certificates and not related to the encryption of traffic and only applies to the acceptance and verification of the certificate.
The current version of QuickSight does not allow you to change any of this information once the data source is created. In order to make changes, you will need to delete, then re-create the data source.
Once the form is complete:
- Click Validate connection.
- If validation is indicated as being successful, then click Create data source.
- In the "Choose your table" dialog box, choose the desired Schema:
- Choose the desired table; then, click Select.
- In the "Finish data set creation" dialog box, select Import into SPICE for quicker analytics or Directly query your data, and then click Visualize.
QuickSight SPICE is the equivalent of Tableau Extracts.
Create the Visualization
- Choose your first anchor table (the fact).
- Add the dims one by one.
- Make sure to specify the relationship.
- Calculated fields can also be added.
Create an Analysis
- Start from the "home page" (click the QuickSight icon in top left if you get lost).
- Click new analysis.
- Choose the dataset you just created and click Create analysis.
- Find a field you want and drag it to the work area.
- Use the interface to change colors and formatting as desired
Share a Dashboard / Analysis
- Click the Share button.
- Fill out this:
- Then, choose your users:
You will now have a data source set and visualization to use in AWS QuickSight.