When you connect Panobi with your data in Redshift, you’ll be able to see your team’s work side-by-side with the impact it’s making on your most important metrics.
Connecting Redshift lets you...
see the effect of each project on your core business metrics like activation rate or DAU/MAU
contextualize metrics with concurrent real-world events like marketing campaigns and holidays
share consistent updates across your company
How to connect Redshift with Panobi
These steps can also be found in your Panobi Integration Settings, if you have not yet configured this integration.
Locate the data to share with Panobi
Decide which tables you want to permit Panobi to access and figure out the relevant databases. You’ll need this information along with Redshift admin privileges in order to complete the following steps.Navigate to the data configuration / dashboard
Log in to your account at https://console.aws.amazon.com/console/home.
Navigate to Redshift: Enter “Redshift” into the search bar at the top, and then click on the search result.
Choose the correct region from the dropdown in the top right.
If using Redshift Serverless, click on the correct Workgroup from the relevant column of the Namespaces / Workgroups section. If using Redshift Provisioned Clusters, open the sidebar and click on “Cluster” to select the correct Cluster.
Create a user
Once you’re on the correct configuration page or dashboard, click “Query Data” in the top right. (This will open a query editor in a new tab.)
In the query editor, click the “plus” button to start a new query.
Copy the the code below and paste it in the editor.
Before running this and the following commands, replace <user_name> with whatever you’d like — we suggest using “panobi_user” or something similar. Remove angle brackets.
Replace <password> with a strong, unique password, removing angle brackets.
-- create a user account for Panobi. you can name it anything you like
CREATE USER <user_name> WITH PASSWORD '<password>';
Grant permissions to the new user
To grant the new Panobi user the necessary permissions, copy the code below and paste it in the editor. You may not need to copy all of it, though — read on to determine which lines apply to your situation.
If you’re using tables in the public schema, you may skip the command in line 2.
If you’re granting permission to all current tables in the schema, use the command in line 4.
If you’re granting permission to one or more specific tables, use line 6, repeating the command for each table.
Before running these commands, replace any instances of <user_name> with the username you created above, removing angle brackets.
Replace all instances of <schema_name> and <table_name> with the names of the schemas and tables you want Panobi to access.
Click the blue Run button at the top of the window.
-- if the tables you're using live in the public schema, you can skip line 2, as public is accessible to all users
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
-- grant select on all tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
-- or, grant select on a specific table
GRANT SELECT ON <schema_name>.<table_name> TO <user_name>;
Enable public access for Panobi’s IP
Close the tab and return to the tab with your Workgroup Configuration or Clusters Dashboard open.
Navigate to “Data access” in the tab bar below the “General information” section.
Under “Network and security”, make sure the “Publicly accessible” setting is set to “On”. If it’s on, skip ahead to Step 6.
If it’s set to “Off”, click “Edit” in the top right corner of the “Network and security” section.
At the bottom of the new page, check the “Turn on Publicly accessible” checkbox, and then click “Save changes”.
Create an inbound security group rule
Under “Network and security”, click on the link under “VPC security group”. (This will open a new tab.)
In the new tab, which is the “Security Group” page, find the “Inbound rules” tab and click on it.
Click “Edit inbound rules” on the right of this section.
Click the “Add rule” button, and set “Type” to “Redshift”. Set “Source” to “Custom” and paste 35.238.161.148/32 in the “Source” text field.
Click “Save rules”. This will allow Panobi to access the data you’ve specified.
Enter your account and user credentials in Panobi
Return to the Workgroup Configuration or Cluster Dashboard tab and copy the Endpoint under “General Information”.
In Panobi, navigate to your Settings, click on "Integrations", and choose Redshift from the list of available integrations. Open the Configuration tab and find the input fields for Endpoint, Username, and Password (you may need to scroll).
Enter the relevant information into those fields and click "Connect".
How to add a Redshift metric
Click + Add metric on the Metrics page and select Redshift from the data source dropdown. Then, paste the SQL query into the window, check that the correct columns of data have been returned, and choose the appropriate visualization.
Only metrics configured as a time series chart will be available on your timeline. This is how you should represent your KPI and OKR-level metrics such as “rate of activation” “new users” or “DAU”. You can also include other metrics you’re tracking, such as any baseline health indicators.
After creating your key metrics in Panobi, you can reference them quickly in any project or insight by typing # and typing to find the metric title. Remember to include information like which team or teams own the metric, the individual collaborators who contributed to the analysis, and any appropriate tags that will help someone find this metric in the future.
Troubleshooting
If you run into any issues with your Redshift integration, check out our troubleshooting article for help diagnosing and fixing the problem.