Microsoft Azure users can connect their Azure Synapse Analytics Data Warehouse (formerly Azure SQL Data Warehouse) to Panoply. This article explains how to set up the connection from Azure to Panoply.
Before you start:
- You need an Azure Synapse Analytics data warehouse,
- Ensure you have admin credentials to your Azure Synapse Analytics data warehouse.
1. Subscribe to the Panoply app in the Azure marketplace
From the Azure marketplace, locate the Panoply page. Click Get It Now and follow the instructions to subscribe to and deploy Panoply on your warehouse. After the Azure marketplace shows a successful deployment message, stop at the point where Azure requests to ‘Configure Account’ for Panoply.
Currently, you can only add Panoply to Azure Synapse Analytics through the Azure marketplace.
2. Whitelist Panoply IP Addresses in Azure
For Panoply to access the data warehouse, you must update your Azure Synapse Analytics warehouse to allow Panoply’s whitelist IP addresses. See Microsoft’s instructions to create a server-level firewall rule.
3. Enable permissions in Azure
For Panoply to write data into the warehouse, permissions need to be granted. Execute the SQL statements shown below. The SQL statement allows for the creation of a schema where Panoply can write data. Please note:
- In the SQL statement replace
<user>with the user name of your choosing.
- You can choose your own SQL user name or create a new user in the warehouse. We recommend creating a new user.
- You may execute the query from any SQL workbench connected to the warehouse.
- The SQL statement does not create the schema, it grants the permissions to do so. Creating the schema is done in another step.
GRANT CREATE SCHEMA TO <user>; GRANT CREATE TABLE TO <user>; GRANT ALTER TO <user>; GRANT INSERT TO <user>; GRANT ADMINISTER DATABASE BULK OPERATIONS TO <user>;
4. Configure Panoply
You’ll need these details to complete the remaining steps:
- Server Name - Sometimes called “fully qualified server name”
- Database Name - The name of your Azure Synapse Analytics Data Warehouse
- Port Number - usually 1433
- The SQL username and password you selected earlier.
From your installed Apps in Azure find Panoply, click Configure Account. This opens a new page where Panoply will establish the data pipeline to your Azure Synapse Analytics data warehouse. Follow these steps as directed on the page:
- Enter your first name and work email address, and click Continue.
- Enter your server name, port number, and database name. Then click Continue.
- Enter the SQL user name and password you selected earlier. In other words, this is the same
<user>from the executed SQL statements. Click Validate.
- Validation requires that you have successfully whitelisted Panoply and have provided correct credentials.
- If the validation passes, a new page opens.
- If the validation fails, you’ll see an error message describing the error. In that case, correct the error and try again.
- Click Allow Panoply to Create Schema. This creates a schema called “Panoply” in the warehouse where Panoply can write data.
- This step requires permissions to be added, as described earlier.
Supported data sources
Our list of supported data sources for Azure Synapse Analytics is always growing. At present, our integrated data source options include:
- Amazon Redshift
- Amazon S3
- Bing Ads
- Facebook Ads
- Facebook Pages
- File upload
- Google AdWords
- Google Analytics
- Google Sheets
- Microsoft SQL Server